Open cz1422189551 opened 2 months ago
看到了ExcelWriteFillExecutor类里解析模板列时,会过滤掉非CellType.String类型,因此List
用很粗糙的方式实现了上述需求,利用CellWriteHandler接口的afterCellDispose方法,主动创建为每行创建cell并设置样式,公式,数据格式。以下代码可提供参考: 定义一个Handler
//主动创建cell的Handler
CellWriteHandler createCellWriterHandler = new CellWriteHandler() {
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
WriteCellData<?> cellData = context.getFirstCellData();
Integer columnIndex = cellData.getColumnIndex();
if (columnIndex == null) return;
// 需要加columnIndex+1,因为处理逻辑涉及到cell的创建,afterCellDispose会跳过空的列,因此需要提前创建
String handleKey = (columnIndex.intValue() + 1) + "";
//根据key找到对应的处理函数,这里key就是列索引
Consumer<CellWriteHandlerContext> cellWriteHandlerContextConsumer = cellWriterHandlerConsumerMap.get(handleKey);
if (cellWriteHandlerContextConsumer == null) return;
cellWriteHandlerContextConsumer.accept(context);
}
};
创建cell并复制样式,设置数据格式,设置公式
private Map<String, Consumer<CellWriteHandlerContext>> cellWriterHandlerConsumerMap = new HashMap<>();
private final int entryProcessorColumnIndex = 7;
private void init() {
String entryProcessorKey = entryProcessorColumnIndex + "";
cellWriterHandlerConsumerMap.put(entryProcessorKey, context -> {
WriteCellData<?> cellData = context.getFirstCellData();
Row row = context.getRow();
Cell cell7 = row.getCell(entryProcessorColumnIndex);
if (cell7 != null) return;
cell7 = row.createCell(entryProcessorColumnIndex, CellType.FORMULA);
int rowNum = row.getRowNum() + 1;
CellStyle currentRowStyle = context.getWriteContext().writeWorkbookHolder().getCachedWorkbook().createCellStyle();
currentRowStyle.cloneStyleFrom(cellData.getOriginCellStyle());
CreationHelper createHelper = context.getWriteSheetHolder().getSheet().getWorkbook().getCreationHelper();
short format = createHelper.createDataFormat().getFormat("0.00%");
currentRowStyle.setDataFormat(format);
StringBuilder sb = new StringBuilder("SUM(I").append(rowNum).append(":")
.append("T").append(rowNum).append(")").append("/G").append(rowNum);
//公式=SUM(Ir:Tr)/Gr r为Row-1
String formula = sb.toString();
cell7.setCellFormula(formula);
cell7.setCellStyle(currentRowStyle);
context.getFirstCellData().setWriteCellStyle(null);
});
}
实现非常粗糙,我找不到使用EasyExcel创建单元格的示例,因此Poi原生API和EasyExcel混杂使用。用这种方式实现我上述的需求很low,既需要维护Excel模板,还需要维护代码里的公式和格式,编写这一大坨代码。
最后还有疑问:EasyExcel4.0 填充模板时,是否支持不用{}占位符的模板列,在已创建新行的前提下,依然创建出模板格式的单元格?
EasyExcel 版本为:4.0.1 导出的Excel文档,除第一行外,剩下行该公式列单元格不会生成