在项目中需要对各种报表进行excel导出,主要使用了两种方式:esayExcel模板导出,poi手工导出。
esayExcel模板导出
使用模板导出相对而已比较简单,其过程如下:
- 准备excel模板
按照需求设计展示样式,同时填入对应的字段名称。字段的名称和填充的数据相关。
如果是填充的集合,则需通过“对象名.属性”的方式进行模板设计。
- 通过模板得到ExcelWriter
public static ExcelWriter getExcelWriterWithMergeStrategy(HttpServletResponse response, String fileName, String templateFileName,
ExcelFillCellMergeStrategy excelFillCellMergeStrategy,
ExcelFillCellMergeStrategy detailMergeStrategy) throws Exception {
OutputStream outputStream = response.getOutputStream();
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName,"UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");//设置类型
response.setHeader("Pragma", "No-cache");//设置头
response.setHeader("Cache-Control", "no-cache");//设置头
response.setDateHeader("Expires", 0);//设置日期头
ExcelWriter excelWriter = EasyExcel.write(outputStream)
.registerWriteHandler(excelFillCellMergeStrategy)
.registerWriteHandler(detailMergeStrategy)
.withTemplate(EasyExcelUtil.class.getClassLoader().getResourceAsStream("template/excel/"+templateFileName))
.build();
return excelWriter;
}
主要是通过模板文件得到一个ExcelWriter 对象,其中xxxMergeStrategy为合并策略类。
- 构建WriteSheet 并填充数据
WriteSheet writeSheet = EasyExcel.writerSheet("总体资源使用情况").build();
writeSheet.setAutoTrim(true);
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); // 不加这个配置,第一行无法合并
//填充普通变量
excelWriter.fill(data, writeSheet);
excelWriter.fill(new FillWrapper("resourceScale", resourceScaleStatisticsVoList), fillConfig, writeSheet);
- 模板填充整个过程比较简单,主要是考虑单元格合并问题。单元格合并需要传入一个合并策略类,主要的逻辑是在数据填充过程中,会比较当前行和前一行的数据是否相同,如果相同则合并。
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
private String sheet;
private List<MergeStrategyParam> mergeStrategyParams;
public ExcelFillCellMergeStrategy(List<MergeStrategyParam> mergeStrategyParams, String sheet) {
this.sheet = sheet;
this.mergeStrategyParams = mergeStrategyParams;
}
public static ExcelFillCellMergeStrategy createMergeStrategy(int[] mergeColumnIndex, int mergeRowIndexStart, int mergeRowIndexEnd, String sheet){
ExcelFillCellMergeStrategy.MergeStrategyParam mergeStrategyParam = new ExcelFillCellMergeStrategy.MergeStrategyParam();
mergeStrategyParam.setMergeColumnIndex(mergeColumnIndex);
mergeStrategyParam.setMergeRowIndexStart(mergeRowIndexStart);
mergeStrategyParam.setMergeRowIndexEnd(mergeRowIndexEnd