注:此为SSH框架
首先配置jar,导入依赖
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
<version>2.3.20</version>
</dependency>
<dependency>
<groupId>org.xhtmlrenderer</groupId>
<artifactId>flying-saucer-pdf</artifactId>
<version>9.0.9</version
</dependency>
创建FreemarkerUtil工具类
package com.ff.util;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.StringWriter;
import java.io.Writer;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import javax.servlet.http.HttpServletRequest;
import org.xhtmlrenderer.pdf.ITextFontResolver;
import org.xhtmlrenderer.pdf.ITextRenderer;
import com.lowagie.text.DocumentException;
import com.lowagie.text.pdf.BaseFont;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
public class FreemarkerUtil {
/**
* 生成excel和word文件
*/
public static File generateExcelAndWord(String foderName,String templateName,List dataList,HttpServletRequest request){
OutputStreamWriter out = null;
File file = null;
try {
// 创建配置类
Configuration cfg = new Configuration();
// 设置模板所在位置
cfg.setClassForTemplateLoading(FreemarkerUtil.class, foderName);
// 设置编码格式
cfg.setDefaultEncoding("utf-8");
// 加载具体的模板
Template template = cfg.getTemplate(templateName);
// 创建数据模型
Map<String,Object> dataModel = new HashMap<>();
dataModel.put("dataList", dataList);
// 将生成出来的excel放到项目中
String path = request.getServletContext().getRealPath("");
file = new File(path + "/" + UUID.randomUUID() + ".xlsx");
out = new OutputStreamWriter(new FileOutputStream(file));
template.process(dataModel, out);
} catch (IOException | TemplateException e) {
e.printStackTrace();
}finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return file;
}
/**
* 生成pdf文件
*/
public static File generatePdf(String foderName,String templateName,List dataList,HttpServletRequest request){
OutputStreamWriter out = null;
File file = null;
try {
// 1.创建配置类
Configuration cfg = new Configuration();
// 2.设置模板所在位置
cfg.setClassForTemplateLoading(FreemarkerUtil.class, foderName);
// 3.设置编码格式
cfg.setDefaultEncoding("utf-8");
// 4.加载具体的模板
Template template = cfg.getTemplate(templateName);
// 5.创建Write对象
Writer writer = new StringWriter();
// 创建数据模型
Map<String,Object> dataModel = new HashMap<>();
dataModel.put("dataList", dataList);
// 6.调用模板对象中的process方法填充数据并输出内容到指定的文件
template.process(dataModel,writer);
// 7.从Writer对象中得到生成的html内容
String html = writer.toString();
// 8.创建Pdf渲染器
ITextRenderer renderer = new ITextRenderer();
// 9.设置字体,目前只支持黑体和宋体,否则Pdf中的中文将不会显示
ITextFontResolver fontResolver = renderer.getFontResolver();
//simsun.ttc 此为简体字体下方可以下载
fontResolver.addFont("/template/simsun.ttc", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
// 10.将html内容放到Pdf渲染器中
renderer.setDocumentFromString(html);
// 11.调用Pdf渲染器中layout方法
renderer.layout();
// 将生成出来的excel放到项目中
String path = request.getServletContext().getRealPath("");
file = new File(path + "/" + UUID.randomUUID() + ".pdf");
FileOutputStream os = new FileOutputStream(file);
// 13.调用PDF渲染器中创建PDF文件的方法
renderer.createPDF(os);
renderer.finishPDF();
os.close();
writer.close();
} catch (IOException | TemplateException | DocumentException e) {
e.printStackTrace();
}finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return file;
}
}
注:需要配置文件和字体
resources/template下所有文件和字体工具下载
前端页面
条件查询表单中加个id,查询字段都加个name值以便传到后台进行查询
在前端操作中加按钮
<button type="button" onclick="exportExcel2()" class="btn btn-primary">
<span class="glyphicon glyphicon-circle-arrow-down"></span>导出Excel
</button>
<button type="button" onclick="exportWord()" class="btn btn-default">
<span class="glyphicon glyphicon-circle-arrow-down"></span>导出Word
</button>
<button type="button" onclick="exportPdf()" class="btn btn-success">
<span class="glyphicon glyphicon-circle-arrow-down"></span>导出Pdf
</button>
定义方法传参
<script type="text/javascript">
// 导出Excel
function exportExcel2(){
// 获取条件查询的form表单
var searchForm = document.getElementById("searchForm");
// 给form表单设置提交路径
searchForm.action = "<%=request.getContextPath()%>/movie/exportExcel2";
// 提交form表单
searchForm.submit();
}
// 导出Word
function exportWord(){
// 获取条件查询的form表单
var searchForm = document.getElementById("searchForm");
// 给form表单设置提交路径
searchForm.action = "<%=request.getContextPath()%>/movie/exportWord";
// 提交form表单
searchForm.submit();
}
// 导出Pdf
function exportPdf(){
// 获取条件查询的form表单
var searchForm = document.getElementById("searchForm");
// 给form表单设置提交路径
searchForm.action = "<%=request.getContextPath()%>/movie/exportPdf";
// 提交form表单
searchForm.submit();
}
</script>
在Controller接参
package com.ff.controller;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import com.ff.model.area.Area;
import com.ff.model.movie.Movie;
import com.ff.model.movie.MovieVO;
import com.ff.service.MovieService;
import com.ff.util.DataTableUtil;
import com.ff.util.ExcelUtil;
import com.ff.util.FileUtil;
import com.ff.util.FreemarkerUtil;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
@Controller
@RequestMapping("movie")
public class MovieController {
@Autowired
private MovieService movieService;
@RequestMapping("toMovie")
public String toMovie(){
return "movie/movie-list";
}
@RequestMapping("exportExcel2")
@ResponseBody
public Map<String,Object> exportExcel2(MovieVO vo,HttpServletRequest request,HttpServletResponse response){
Map<String,Object> map = new HashMap<>();
try {
// 1.获取要导出的数据
List<Movie> movieList = movieService.queryMovieList(vo);
// 2.通过模板生成Excel
File file = FreemarkerUtil.generateExcelAndWord("/template", "excel.ftl", movieList, request);
// 3.将生成好的excel文件下载下来
ExcelUtil.downloadFile(file, "电影列表.xlsx", request, response);
// 4.将服务器上的垃圾数据删除
file.delete();
map.put("state", true);
} catch (Exception e) {
e.printStackTrace();
map.put("state", false);
}
return map;
}
@RequestMapping("exportWord")
@ResponseBody
public Map<String,Object> exportWord(MovieVO vo,HttpServletRequest request,HttpServletResponse response){
Map<String,Object> map = new HashMap<>();
try {
// 1.获取要导出的数据
List<Movie> movieList = movieService.queryMovieList(vo);
// 2.通过模板生成Excel
File file = FreemarkerUtil.generateExcelAndWord("/template", "word2.ftl", movieList, request);
// 3.将生成好的excel文件下载下来
ExcelUtil.downloadFile(file, "电影列表.docx", request, response);
// 4.将服务器上的垃圾数据删除
file.delete();
map.put("state", true);
} catch (Exception e) {
e.printStackTrace();
map.put("state", false);
}
return map;
}
@RequestMapping("exportPdf")
@ResponseBody
public Map<String,Object> exportPdf(MovieVO vo,HttpServletRequest request,HttpServletResponse response){
Map<String,Object> map = new HashMap<>();
try {
// 1.获取要导出的数据
List<Movie> movieList = movieService.queryMovieList(vo);
// 2.通过模板生成Excel
File file = FreemarkerUtil.generatePdf("/template", "pdf.ftl", movieList, request);
// 3.将生成好的excel文件下载下来
ExcelUtil.downloadFile(file, "电影列表.pdf", request, response);
// 4.将服务器上的垃圾数据删除
file.delete();
map.put("state", true);
} catch (Exception e) {
e.printStackTrace();
map.put("state", false);
}
return map;
}
}
dao层执行
package com.ff.dao.impl;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;
import com.ff.dao.MovieDao;
import com.ff.model.movie.Movie;
import com.ff.model.movie.MovieVO;
import com.ff.util.DataTableUtil;
@Repository
public class MovieDaoImpl implements MovieDao {
@Autowired
private HibernateTemplate hibernateTemplate;
//查询数据
@Override
public void queryMovieList(DataTableUtil<Movie> page, MovieVO vo) {
Criteria criteria = hibernateTemplate.getSessionFactory().getCurrentSession().createCriteria(Movie.class);
whereSearch(vo, criteria);
page.setRecordsTotal(criteria.list().size());
criteria.setFirstResult(page.getStart());
criteria.setMaxResults(page.getLength());
page.setData(criteria.list());
}
//条件导出
@Override
public List<Movie> queryMovieList(MovieVO vo) {
Criteria criteria = hibernateTemplate.getSessionFactory().getCurrentSession().createCriteria(Movie.class);
whereSearch(vo, criteria);
return criteria.list();
}
//提取封装
private void whereSearch(MovieVO vo, Criteria criteria) {
if (vo != null) {
if (StringUtils.isNotBlank(vo.getMovieName())) {
criteria.add(Restrictions.like("movieName", "%"+vo.getMovieName()+"%"));
}
if (vo.getMinPrice() != null) {
criteria.add(Restrictions.gt("moviePrice", vo.getMinPrice()));
}
if (vo.getMaxPrice() != null) {
criteria.add(Restrictions.lt("moviePrice", vo.getMaxPrice()));
}
if (vo.getMinDate() != null) {
criteria.add(Restrictions.gt("pubDate", vo.getMinDate()));
}
if (vo.getMaxDate() != null) {
criteria.add(Restrictions.lt("pubDate", vo.getMaxDate()));
}
if (vo.getIsup() != null) {
criteria.add(Restrictions.eq("isup", vo.getIsup()));
}
if (StringUtils.isNotBlank(vo.getPerson())) {
String[] arr = vo.getPerson().split(",");
// Disjunction(抵死军可申)添加的是逻辑或(OR)的关系,另外还有个Conjunction,添加的是逻辑与(AND)的关系
// Restrictions.disjunction() 逻辑或
Disjunction disjunction = Restrictions.disjunction();
for (int i = 0; i < arr.length; i++) {
// MatchMode.ANYWHERE --> 字符串在中间匹配.相当于"like '%value%'"
disjunction.add(Restrictions.like("person", "%"+arr[i]+"%", MatchMode.ANYWHERE));
}
criteria.add(disjunction);
}
if(vo.getAreaId() != -1 && vo.getAreaId() != null){
criteria.add(Restrictions.eq("area.areaId", vo.getAreaId()));
}
}
}
}