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);
                }
            }
        }
    }
}

四、总结

拦截器处理每个单元格写入后的操作，将行末，列末用自定义字符占位，最后替换公式

文档是最好的老师，再见！