EasyExcel完成复杂表的导出

背景

最近有个需求需要导出复杂excel。需要合并单元格。类似下图：

EasyExcel解决思路

接下来用EasyExcel完成。编程语言:Java。

1.首先创建一个ExcelWriter对象和一个WriteSheet对象

// 创建一个ExcelWriter对象，用于写入Excel文件
ExcelWriter excelWriter = EasyExcel.write("a.xlsx").build();

// 创建一个WriteSheet对象，指定要写入的工作表名称为"Sheet1"
WriteSheet headerSheet = EasyExcel.writerSheet("Sheet1").build();

2.把需要的数据都写入headerSheet中。

        //第一部分:报销日期
        LocalDate date = LocalDate.now();
        List<String> dateRow = Arrays.asList("报销日期:", date.toString(), "", "", "", "", "", "");
        excelWriter.write(Collections.singletonList(dateRow), headerSheet);

        excelWriter.write(Collections.singletonList(""), headerSheet); // 空行

        // 第二部分:表格数据
        List<String> dateRow2 = Arrays.asList("报销人:", "牛马", "部门:", "", "岗位:CV工程师", "", "", "");
        excelWriter.write(Collections.singletonList(dateRow2), headerSheet);
        List<String> dateRow3 = Arrays.asList("序号", "日期", "报销项目", "摘要", "类别", "金额", "发票张数", "备注");
        excelWriter.write(Collections.singletonList(dateRow3), headerSheet);
        //动态数据：这个我们可以将数据库查出的对象动态写入
        List<Reimbursement> dataList = Arrays.asList(
                new Reimbursement(1, new Date(), "项目1", "摘要1", "类别A", BigDecimal.ONE, 5, "备注1"),
                new Reimbursement(2,new Date() , "项目2", "摘要2", "类别B", BigDecimal.TEN, 6, "备注2")
        );
        excelWriter.write(dataList, headerSheet);

        // 第三部分：尾部
        List<String> dateRow4 = Arrays.asList("金额大写", "肆佰元整", "", "", "合计金额", "", "￥200", "");
        excelWriter.write(Collections.singletonList(dateRow4), headerSheet);

        excelWriter.write(Collections.singletonList(""), headerSheet); // 空行

        List<String> dateRow5 = Arrays.asList("部门主管", "", "", "会计主管", "", "", "报销人", "");
        excelWriter.write(Collections.singletonList(dateRow5), headerSheet);

3.完成写入

        excelWriter.finish();

4.完成单元格合并，居中对齐和加边框。

创建WriteSheet时候可以通过加入自定义的处理器。

WriteSheet headerSheet = EasyExcel.writerSheet("Sheet1")
                .registerWriteHandler(new MergeHeaderHandler(0, 0, 0, 7))
                .registerWriteHandler(new MergeHeaderHandler(3, 3, 4, 7))
                .registerWriteHandler(new CustomCellStyleHandler())
                .registerWriteHandler(new BorderCellStyleHandler(2,7))
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .build();

 // 合并单元格处理器
    private static class MergeHeaderHandler extends AbstractRowWriteHandler {

        private final int startRow;
        private final int endRow;
        private final int startCol;
        private final int endCol;

        public MergeHeaderHandler(int startRow, int endRow, int startCol, int endCol) {
            this.startRow = startRow;
            this.endRow = endRow;
            this.startCol = startCol;
            this.endCol = endCol;
        }

        @Override
        public void afterRowDispose(WriteSheetHolder writeSheetHolder,
                                    WriteTableHolder writeTableHolder,
                                    Row row,
                                    Integer relativeRowIndex,
                                    Boolean isHead) {
            if (row.getRowNum() == startRow) { // 根据实际行号调整
                CellRangeAddress region = new CellRangeAddress(
                        startRow,
                        endRow,
                        startCol,
                        endCol
                );
                writeSheetHolder.getSheet().addMergedRegion(region);
//                setCellStyle(writeSheetHolder.getSheet(), startRow, startCol, endCol, true, HorizontalAlignment.CENTER);
            }
        }
    }

// 设置单元格样式：水平和垂直居中 处理器
    public static class CustomCellStyleHandler implements CellWriteHandler {

        @Override
        public void afterCellDispose(CellWriteHandlerContext context) {
            // 获取单元格
            Cell cell = context.getCell();
            Workbook workbook = cell.getSheet().getWorkbook();

            // 创建新的CellStyle
            CellStyle cellStyle = workbook.createCellStyle();

            // 设置水平和垂直居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            // 设置样式回到单元格
            cell.setCellStyle(cellStyle);
        }
    }

//加边框
    public static class BorderCellStyleHandler implements CellWriteHandler {
        private int startRow;
        private int endRow;

        public BorderCellStyleHandler(int startRow,int endRow){
            this.startRow = startRow;
            this.endRow = endRow;
        }
        @Override
        public void afterCellDispose(CellWriteHandlerContext context) {
            int startRow = this.startRow;
            int endRow = this.endRow; // 根据需要调整

            // 获取当前单元格
            Cell cell = context.getCell();
            int rowIndex = cell.getRowIndex();

            // 如果单元格在动态数据范围内，添加边框
            if (rowIndex >= startRow && rowIndex <= endRow) {
                Workbook workbook = cell.getSheet().getWorkbook();

                // 获取单元格的原有样式
                CellStyle originalStyle = cell.getCellStyle();
                CellStyle newStyle = workbook.createCellStyle();

                // 复制原有样式到新的样式
                newStyle.cloneStyleFrom(originalStyle);

                // 添加边框样式
                newStyle.setBorderTop(BorderStyle.THIN);
                newStyle.setBorderBottom(BorderStyle.THIN);
                newStyle.setBorderLeft(BorderStyle.THIN);
                newStyle.setBorderRight(BorderStyle.THIN);

                // 应用新的样式
                cell.setCellStyle(newStyle);
            }
        }


    }

完整代码

public static void main(String[] args) {
        ExcelWriter excelWriter = EasyExcel.write("a.xlsx").build();
//设置头居中
        // 第一部分：固定标题
        WriteSheet headerSheet = EasyExcel.writerSheet("Sheet1")
                .registerWriteHandler(new MergeHeaderHandler(0, 0, 0, 7))
                .registerWriteHandler(new MergeHeaderHandler(3, 3, 4, 7))
                .registerWriteHandler(new CustomCellStyleHandler())
                .registerWriteHandler(new BorderCellStyleHandler(2,7))
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .build();

        List<List<String>> headerData = Arrays.asList(
                Arrays.asList("费用报销单") // 固定标题行数据
        );
        excelWriter.write(headerData, headerSheet);

        //第一部分:报销日期
        LocalDate date = LocalDate.now();
        List<String> dateRow = Arrays.asList("报销日期:", date.toString(), "", "", "", "", "", "");
        excelWriter.write(Collections.singletonList(dateRow), headerSheet);

        excelWriter.write(Collections.singletonList(""), headerSheet); // 空行

        // 第二部分:表格数据
        List<String> dateRow2 = Arrays.asList("报销人:", "牛马", "部门:", "", "岗位:CV工程师", "", "", "");
        excelWriter.write(Collections.singletonList(dateRow2), headerSheet);
        List<String> dateRow3 = Arrays.asList("序号", "日期", "报销项目", "摘要", "类别", "金额", "发票张数", "备注");
        excelWriter.write(Collections.singletonList(dateRow3), headerSheet);
        //动态数据：这个我们可以将数据库查出的对象动态写入
        List<Reimbursement> dataList = Arrays.asList(
                new Reimbursement(1, new Date(), "项目1", "摘要1", "类别A", BigDecimal.ONE, 5, "备注1"),
                new Reimbursement(2,new Date() , "项目2", "摘要2", "类别B", BigDecimal.TEN, 6, "备注2")
        );
        excelWriter.write(dataList, headerSheet);

        // 第三部分：尾部
        List<String> dateRow4 = Arrays.asList("金额大写", "肆佰元整", "", "", "合计金额", "", "￥200", "");
        excelWriter.write(Collections.singletonList(dateRow4), headerSheet);

        excelWriter.write(Collections.singletonList(""), headerSheet); // 空行

        List<String> dateRow5 = Arrays.asList("部门主管", "", "", "会计主管", "", "", "报销人", "");
        excelWriter.write(Collections.singletonList(dateRow5), headerSheet);

        excelWriter.finish();
    }

    // 合并单元格处理器
    private static class MergeHeaderHandler extends AbstractRowWriteHandler {

        private final int startRow;
        private final int endRow;
        private final int startCol;
        private final int endCol;

        public MergeHeaderHandler(int startRow, int endRow, int startCol, int endCol) {
            this.startRow = startRow;
            this.endRow = endRow;
            this.startCol = startCol;
            this.endCol = endCol;
        }

        @Override
        public void afterRowDispose(WriteSheetHolder writeSheetHolder,
                                    WriteTableHolder writeTableHolder,
                                    Row row,
                                    Integer relativeRowIndex,
                                    Boolean isHead) {
            if (row.getRowNum() == startRow) { // 根据实际行号调整
                CellRangeAddress region = new CellRangeAddress(
                        startRow,
                        endRow,
                        startCol,
                        endCol
                );
                writeSheetHolder.getSheet().addMergedRegion(region);
//                setCellStyle(writeSheetHolder.getSheet(), startRow, startCol, endCol, true, HorizontalAlignment.CENTER);
            }
        }
    }
    // 设置单元格样式：水平和垂直居中 处理器
    public static class CustomCellStyleHandler implements CellWriteHandler {

        @Override
        public void afterCellDispose(CellWriteHandlerContext context) {
            // 获取单元格
            Cell cell = context.getCell();
            Workbook workbook = cell.getSheet().getWorkbook();

            // 创建新的CellStyle
            CellStyle cellStyle = workbook.createCellStyle();

            // 设置水平和垂直居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            // 设置样式回到单元格
            cell.setCellStyle(cellStyle);
        }
    }
    //加边框
    public static class BorderCellStyleHandler implements CellWriteHandler {
        private int startRow;
        private int endRow;

        public BorderCellStyleHandler(int startRow,int endRow){
            this.startRow = startRow;
            this.endRow = endRow;
        }
        @Override
        public void afterCellDispose(CellWriteHandlerContext context) {
            int startRow = this.startRow;
            int endRow = this.endRow; // 根据需要调整

            // 获取当前单元格
            Cell cell = context.getCell();
            int rowIndex = cell.getRowIndex();

            // 如果单元格在动态数据范围内，添加边框
            if (rowIndex >= startRow && rowIndex <= endRow) {
                Workbook workbook = cell.getSheet().getWorkbook();

                // 获取单元格的原有样式
                CellStyle originalStyle = cell.getCellStyle();
                CellStyle newStyle = workbook.createCellStyle();

                // 复制原有样式到新的样式
                newStyle.cloneStyleFrom(originalStyle);

                // 添加边框样式
                newStyle.setBorderTop(BorderStyle.THIN);
                newStyle.setBorderBottom(BorderStyle.THIN);
                newStyle.setBorderLeft(BorderStyle.THIN);
                newStyle.setBorderRight(BorderStyle.THIN);

                // 应用新的样式
                cell.setCellStyle(newStyle);
            }
        }


    }