本文主要是个人工作上的一个记录，技术有限，可能没有那么全面，见谅！
背景
项目中用的jxls版本是1.0以下的，导出2万多条数据都慢到不行，提出了2种方案，一种是升级jxls的版本，一种是使用poi导出，因为已经开发好的功能，根据模板生成的excel，所以在原有的基础上升级了2.4，只需要修改一些语法，工作量稍微少一点。
代码
pom.xml
<!-- 生成和读入Excel工具类 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.4.4</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>1.0.14</version>
</dependency>
JxlsExcelView，公司使用的工具类
package com.XXX.common.utils;
import org.springframework.web.servlet.view.AbstractView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Map;
public class JxlsExcelView extends AbstractView {
private static final String CONTENT_TYPE = "application/vnd.ms-excel";
private String templatePath;
private String exportFileName;
/**
* @param templatePath 模版相对于当前classpath路径
* @param exportFileName 导出文件名
*/
public JxlsExcelView(String templatePath, String exportFileName) {
this.templatePath = templatePath;
this.exportFileName = exportFileName;
setContentType(CONTENT_TYPE);
}
@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request,
HttpServletResponse response) throws Exception {
InputStream is = null;
OutputStream os = null;
os = response.getOutputStream();
response.setContentType(getContentType());
// 解决导出文件名中文乱码
String filename = new String(exportFileName.getBytes("gb2312"), "iso8859-1");
response.setHeader("content-disposition", "attachment;filename=" + filename + ".xls");
String webInfPath = request.getSession().getServletContext().getRealPath("/WEB-INF/");
// 获取excel模板
is = new FileInputStream(new File(webInfPath + "/template/" + templatePath));
//转换成excel并输出 ~~这是以前1.0之前的写法
// XLSTransformer transformer = new XLSTransformer();
// Workbook workbook = transformer.transformXLS(is, model);
JxlsUtils.exportExcel(is,os,model);
//将内容写入输出流并把缓存的内容全部发出去
// workbook.write(os);
os.flush();
os.close();
is.close();
}
}
JxlsUtils 这是参考其他大神使用的一个工具类
package com.XXX.common.utils;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
/**
* jxls2.4 导出excle工具类
* @SINCE 2018/10/9 17:06
* @AUTHOR LCM
* @Param
* @return
*/
public class JxlsUtils{
public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException{
Context context = PoiTransformer.createInitialContext();
if (model != null) {
for (String key : model.keySet()) {
context.putVar(key, model.get(key));
}
}
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
Transformer transformer = jxlsHelper.createTransformer(is, os);
//获得配置
JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
//设置静默模式，不报警告
// evaluator.getJexlEngine().setSilent(true);
//函数强制，自定义功能
Map<String, Object> funcs = new HashMap<String, Object>();
funcs.put("utils", new JxlsUtils()); //添加自定义功能
evaluator.getJexlEngine().setFunctions(funcs);
//必须要这个，否者表格函数统计会错乱
jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
}
public static void exportExcel(File xls, File out, Map<String, Object> model) throws FileNotFoundException, IOException {
exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
}
public static void exportExcel(String templatePath, OutputStream os, Map<String, Object> model) throws Exception {
File template = getTemplate(templatePath);
if(template != null){
exportExcel(new FileInputStream(template), os, model);
} else {
throw new Exception("Excel 模板未找到。");
}
}
//获取jxls模版文件
public static File getTemplate(String path){
File template = new File(path);
if(template.exists()){
return template;
}
return null;
}
}
controller导出调用的语法了。
/**
* 按照日期 导出excel
*
* @param producUnitDoc
* @param page
* @param rows
* @return
*/
@RequestMapping(value = "/exportFunctionExcel")
public ModelAndView exportFunctionExcel(MealOrder mealOrder) {
ModelAndView mv = new ModelAndView();
Map<String, Object> dataMap = mealOrderApi.export(mealOrder);
List<Map<String, String>> varList = (List<Map<String, String>>) dataMap.get("varList");
if(varList!=null&&varList.size()>0) {
try {
return new ModelAndView(new JxlsExcelView("mealOrder.xls", (String) dataMap.get("excelName")), dataMap);
} catch (Exception e) {
}
}
return mv;
}
接口的参数格式，这里我们使用list导出
@Override
public Map<String, Object> export(MealOrder mealOrder) {
Map<String, Object> dataMap = new HashMap<String, Object>();
/* // 定义，设置excel头部
List<String> titles = new ArrayList<String>();
titles.add("使用状态(1启用 0禁用)*");
dataMap.put("titles", titles);*/
// 定义，设置excel数据列表
List<Map<String, String>> varList = new ArrayList<Map<String, String>>();
List<MealOrder> list = dao.getAllList(mealOrder);
for (MealOrder mealOrder1 : list) {
dataMap.put("excelName", mealOrder.getExcelName());
if (mealOrder1 != null && mealOrder1.getUuid() != null) {
Map<String, String> vpd = new HashMap<String, String>();
vpd.put("var1", mealOrder1.getTakeCode());
vpd.put("var2", mealOrder1.getFoodNames());
vpd.put("var3", String.valueOf(mealOrder1.getMealNumber()));
vpd.put("var4", mealOrder1.getApplyCompany());
varList.add(vpd);
}
}
dataMap.put("varList", varList);
return dataMap;
}
