一、单个表格导出(一个sheet)
1、导入依赖
<!-- Execl工具包 -->
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.4.6</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>1.0.15</version>
</dependency>
2、编写excel模板
3、相关代码
String jxlsTemplateName="info";
String header="信息表";
String fileName="信息表格";
Map<String, Object> model = new HashMap<>();
model.put("dataList", infoList);
model.put("header", header);
CalculateUtils.createExcel(response, model, jxlsTemplateName, fileName);
/**
* Excel模板加载和导出
*
* @param response
* @param model
* @param jxlsTemplateName
* @param fileName
*/
public static void createExcel(HttpServletResponse response, Map<String, Object> model,
String jxlsTemplateName, String fileName) {
InputStream is = null;
OutputStream os = null;
try {
log.info("export excel file name {}.", fileName);
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;fileName=" + fileName + ".xlsx");
os = response.getOutputStream();
log.info("export excel file name {}.", fileName);
Resource resource = new ClassPathResource("jxls-template/" + jxlsTemplateName + ".xlsx");
is = resource.getInputStream();
JxlsUtils.exportExcel(is, os, model);
os.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
/*e.printStackTrace();*/
}
}
if (is != null) {
try {
is.close();
} catch (IOException e) {
}
}
}
}
二、多sheet导出到同一个excel表格中
表格模板:
相关代码:
String jxlsTemplateName = "info";
Map<String, Object> model = new HashMap<>(2);
//sheet1
model.put("dataList", infoList);
//sheet2
model.put("tradeList",detailList);
createMutilSheetTempExcel(model,jxlsTemplateName,"信息表");
private static void createMutilSheetTempExcel(Map<String, Object> model, String jxlsTemplateName, String tempExcelName) throws Exception {
OutputStream os = new BufferedOutputStream(new FileOutputStream(tempExcelName + ".xlsx"));
Resource resource = new ClassPathResource(jxlsTemplateName + ".xlsx");
InputStream is = resource.getInputStream();
if (os != null && jxlsTemplateName != null) {
Map<Integer, String> sheet = new HashMap<>();
sheet.put(0, "sheet1");
sheet.put(1, "sheet2");
JxlsUtils.exportMultSheetExcel(is, os, model, sheet);
os.close();
is.close();
}
}
public static boolean exportMultSheetExcel(InputStream is, OutputStream os, Map<String, Object> model, Map<Integer, String> sheet) {
Context context = PoiTransformer.createInitialContext();
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
Transformer transformer = jxlsHelper.createTransformer(is, os);
AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
if (model != null) {
for (String key : model.keySet()) {
context.putVar(key, model.get(key));
}
}
try {
//获得配置
JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
Map<String, Object> functionMap = new HashMap<String, Object>();
functionMap.put("utils", new JxlsUtils());
evaluator.getJexlEngine().setFunctions(functionMap);
List<Area> xlsAreaList = areaBuilder.build();
if (sheet != null) {
for (Integer index : sheet.keySet()) {
Area xlsArea = xlsAreaList.get(index);
xlsArea.applyAt(new CellRef(sheet.get(index) + "!A1"), context);
}
}
transformer.write();
is.close();
} catch (Exception e) {
log.error("批量写文件错误", e);
return false;
}
return true;
}