EasyExcel 模板+公式填充
使用 CellWriteHandler 的实现类来实现公式写入
@Data
@NoArgsConstructor
public class CustomCellWriteHandler implements CellWriteHandler {
private int maxRowNum = 2000;
// 动态传入列表数量
public CustomCellWriteHandler(int maxRowNum) {
this.maxRowNum = maxRowNum;
}
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
CellWriteHandler.super.afterCellDispose(context);
// 可获取 sheetNo 或 sheetName 用于判断
// Integer sheetNo = context.getWriteSheetHolder().getSheetNo();
Cell cell = context.getCell();
//行
int rowNum = cell.getRowIndex();
//列
int columnNum = cell.getColumnIndex();
// 对第3行第3列的单元格进行替换,设置为公式
if (rowNum == 2 && columnNum == 2) {
cell.setCellFormula("SUM(C4:C" + maxRowNum + ")");
}
}
}
注册填充
// 构建导出表文件
ClassPathResource classPathResource = new ClassPathResource("excel_template/BaseInfoExport.xls");
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(classPathResource.getInputStream()).
excelType(ExcelTypeEnum.XLS).autoCloseStream(Boolean.FALSE).build();
Map<String, Object> map = MapUtils.newHashMap();
// 在 fill 中必须要添加对应的模板,否则会变为空白
map.put("summary", "");
// 构造导出配置
WriteSheet writeSheet = EasyExcel.writerSheet(5).registerWriteHandler(new CustomCellWriteHandler(dataList.size() + 3)).build();
// 执行填充,先填充 dataList,再填充 map
excelWriter.fill(dataList, writeSheet);
excelWriter.fill(map, writeSheet);
excelWriter.finish();
注意:如果使用了 FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
会动态添加新行。如果先填充了 map 再填充列表,会把 map 填充的公式给拉长,导致填充位置与输入不一致。可以先填充列表,再填充 map。