easyexcel自定义单元格写入拦截器
一、分析需求
我的上一篇文章是,excel导出具体看easyExcel实现单sheet多子表,并结合动态表头,复杂表头_IM@taoyalong的博客-CSDN博客_easyexcel注解复杂表头
产品又TM加需求了,要加行尾的小计,列末的总计!!!!!!
需求变成了这样:
二、先看文档
自定义拦截器
参照:最简单的写的对象
定义拦截器
/**
* 自定义拦截器。对第一行第一列的头超链接到:https://github.com/alibaba/easyexcel
*
* @author Jiaju Zhuang
*/
@Slf4j
public class CustomCellWriteHandler implements CellWriteHandler {
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
Cell cell = context.getCell();
// 这里可以对cell进行任何操作
log.info("第{}行,第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
if (BooleanUtils.isTrue(context.getHead()) && cell.getColumnIndex() == 0) {
CreationHelper createHelper = context.getWriteSheetHolder().getSheet().getWorkbook().getCreationHelper();
Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
hyperlink.setAddress("https://github.com/alibaba/easyexcel");
cell.setHyperlink(hyperlink);
}
}
}
/**
* 自定义拦截器.对第一列第一行和第二行的数据新增下拉框,显示 测试1 测试2
*
* @author Jiaju Zhuang
*/
@Slf4j
public class CustomSheetWriteHandler implements SheetWriteHandler {
@Override
public void afterSheetCreate(SheetWriteHandlerContext context) {
log.info("第{}个Sheet写入成功。", context.getWriteSheetHolder().getSheetNo());
// 区间设置 第一列第一行和第二行的数据。由于第一行是头,所以第一、二行的数据实际上是第二三行
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 2, 0, 0);
DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"测试1", "测试2"});
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
}
}
三、开始实现
思路就是,定义自己的拦截器,在行末和列末的地方加入公式。
1.先开启公式支持
//3.4 设置强制计算公式:不然公式会以字符串的形式显示在excel中
Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
workbook.setForceFormulaRecalculation(true);
2.在数据处理的时候在行末和列末增加占位符(这种方式不好,有更好方式可以留言一起讨论)
/**
* 生成数据
* @param dataList 数量liso
* @param processNum 数量
* @param processList 工序列表
*/
private void generateDataList(Map<String, List<ProcessCompleterDataDTO>> dataList, List<List<Object>> processNum, List<String> processList) {
dataList.forEach((completer, List) -> {
List<ProcessCompleterDataDTO> processCompleterDataDTOS = List;
if (StringUtils.equals("processList", completer)) {
} else {
List<Object> num = new ArrayList<>();
num.add(completer);
for (int i = 0; i < processList.size(); i++) {
Map<String, List<ProcessCompleterDataDTO>> map = processCompleterDataDTOS.stream().collect(Collectors.groupingBy(ProcessCompleterDataDTO::getProcessName));
if (map.containsKey(processList.get(i))) {
num.add(map.get(processList.get(i)).get(0).getNum());
} else {
num.add(0);
}
}
#在行的末尾加上sum字符串单元格写入后处理这个替换为公式
num.add("sum");
processNum.add(num);
}
});
#在列的末尾加上"total/"+length字符串单元格写入后处理这个替换为公式,为什么要加上length,因为我这个表是多个字表的,我需要计算行的开头在哪里,需要用当前行-length
List<Object> num = new ArrayList<>();
num.add("合计");
int length = dataList.size();
for (int i = 0; i < processList.size(); i++) {
num.add("total/"+length);
}
num.add("total/"+length);
processNum.add(num);
}
3.处理类
@Slf4j
public class ProcessExportSumTotalHandler implements CellWriteHandler {
/**
* Called before create the cell
*
* @param writeSheetHolder
* @param writeTableHolder Nullable.It is null without using table writes.
* @param row
* @param head Nullable.It is null in the case of fill data and without head.
* @param columnIndex
* @param relativeRowIndex Nullable.It is null in the case of fill data.
* @param isHead
*/
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
/**
* Called after the cell is created
*
* @param writeSheetHolder
* @param writeTableHolder Nullable.It is null without using table writes.
* @param cell
* @param head Nullable.It is null in the case of fill data and without head.
* @param relativeRowIndex Nullable.It is null in the case of fill data.
* @param isHead
*/
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
/**
* Called after the cell data is converted
*
* @param writeSheetHolder
* @param writeTableHolder Nullable.It is null without using table writes.
* @param cellData Nullable.It is null in the case of add header.
* @param cell
* @param head Nullable.It is null in the case of fill data and without head.
* @param relativeRowIndex Nullable.It is null in the case of fill data.
* @param isHead
*/
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
/**
* Called after all operations on the cell have been completed
*
* @param writeSheetHolder
* @param writeTableHolder Nullable.It is null without using table writes.
* @param cellDataList Nullable.It is null in the case of add header.There may be several when fill the data.
* @param cell
* @param head Nullable.It is null in the case of fill data and without head.
* @param relativeRowIndex Nullable.It is null in the case of fill data.
* @param isHead
*/
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 先判断是否是字符类型
if (Objects.equals(1, cell.getCellTypeEnum().getCode())) {
// 切分字符串total/length
String[] totalSign = cell.getStringCellValue().split("/");
// 行号 和计算范围有关多试几次确定范围
int row = cell.getRowIndex() + 1;
// 列号
int column = cell.getColumnIndex();
// sum是小计的替换标识
if (Objects.equals(cell.getStringCellValue(), "sum")) {
// 将列号转化为ABC字母
String columnIndex = NumToA.numberToLetter(column);
// 填充公式 =SUM(B3:E3) 我的列是从B开始
cell.setCellFormula("=SUM(B" + row + ":" + columnIndex + row + ")");
} else if (totalSign.length == 2) {
// 进入总计替换 先看是否数组==2 再看第一个是不是total
String openTotalSign = totalSign[0];
if (Objects.equals(openTotalSign, "total")) {
// 把向上汇总的长度拿到
int length = Integer.parseInt(totalSign[1]);
// 确定开始行 多试几次就能确定了
int beginRow = row - length+1;
column++;
row--;
// 列数字转字母
String columnIndex = NumToA.numberToLetter(column);
// 公式替换=SUM(B9:B9)
String formula = "=SUM(" + columnIndex + beginRow + ":" + columnIndex + row + ")";
log.info(formula);
cell.setCellFormula(formula);
}
}
}
}
}
四、总结
拦截器处理每个单元格写入后的操作,将行末,列末用自定义字符占位,最后替换公式