EasyExcel合并相同内容单元格及动态标题功能的实现

Myth丶恋晨 2024-02-05 16:51 34阅读 0赞

一、最初版本  
导出的结果：  
![在这里插入图片描述][9bf65845adf8467696cce83c5f93bb3d.png]  
对应实体类代码：

import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.annotation.write.style.ColumnWidth;
    import com.alibaba.excel.annotation.write.style.ContentLoopMerge;
    import com.alibaba.excel.annotation.write.style.ContentRowHeight;
    import com.alibaba.excel.annotation.write.style.HeadRowHeight;
    import lombok.*;
    
    import java.io.Serializable;
    
    @Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @Data
    @ContentRowHeight(30)
    @HeadRowHeight(40)
    @ColumnWidth(25)
    public class StudentExportVo implements Serializable {
        
    
        private static final long serialVersionUID = -5809782578272943999L;
    
        @ExcelProperty(value = "学校", order = 1)
        @ContentLoopMerge(eachRow = 3)
        private String school;
    
        @ExcelProperty(value = "姓名", order = 2)
        private String name;
    
        @ExcelProperty(value = "性别", order = 3)
        private String sex;
    
        @ExcelProperty(value = "年龄", order = 4)
        private String age;
    
        @ExcelProperty(value = "城市", order = 5)
        private String city;
    
        @ExcelProperty(value = "备注", order = 6)
        private String remarks;
    
        }

对应业务代码：

@ApiOperation(value = "导出学生信息", notes = "导出学生信息")
    @PostMapping("/exportStudent")
    public void exportStudent(@RequestBody String str, HttpServletResponse response) {
        
        List<StudentExportVo> list = this.getStudentExportVos();
        ExcelUtils.writeExcel(response, StudentExportVo.class, list, "导出学生信息", "sheet1");
    }
    //数据制造
    private List<StudentExportVo> getStudentExportVos() {
        
        List<StudentExportVo> list = new ArrayList<>();
        StudentExportVo v1 = new StudentExportVo();
        v1.setSchool("北京大学");
        v1.setName("张三");
        v1.setSex("男");
        v1.setAge("20");
        v1.setCity("北京");
        v1.setRemarks("无");
        list.add(v1);
        StudentExportVo v2 = new StudentExportVo();
        v2.setSchool("北京大学");
        v2.setName("李四");
        v2.setSex("男");
        v2.setAge("22");
        v2.setCity("上海");
        v2.setRemarks("无");
        list.add(v2);
        StudentExportVo v3 = new StudentExportVo();
        v3.setSchool("北京大学");
        v3.setName("王五");
        v3.setSex("女");
        v3.setAge("22");
        v3.setCity("青岛");
        v3.setRemarks("无");
        list.add(v3);
        StudentExportVo v4 = new StudentExportVo();
        v4.setSchool("清华大学");
        v4.setName("赵六");
        v4.setSex("女");
        v4.setAge("21");
        v4.setCity("重庆");
        v4.setRemarks("无");
        list.add(v4);
        StudentExportVo v5 = new StudentExportVo();
        v5.setSchool("武汉大学");
        v5.setName("王强");
        v5.setSex("男");
        v5.setAge("24");
        v5.setCity("长沙");
        v5.setRemarks("无");
        list.add(v5);
        StudentExportVo v6 = new StudentExportVo();
        v6.setSchool("武汉大学");
        v6.setName("赵燕");
        v6.setSex("女");
        v6.setAge("21");
        v6.setCity("深圳");
        v6.setRemarks("无");
        list.add(v6);
        StudentExportVo v7 = new StudentExportVo();
        v7.setSchool("厦门大学");
        v7.setName("陆仟");
        v7.setSex("女");
        v7.setAge("21");
        v7.setCity("广州");
        v7.setRemarks("无");
        list.add(v7);
        return list;
    }

二、使用注解的版本  
导出的结果：  
![在这里插入图片描述][24ae4df7a98c4c789c433a921164f49c.png]  
对应实体类代码：

@Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @Data
    @ContentRowHeight(30)
    @HeadRowHeight(40)
    @ColumnWidth(25)
    public class StudentExportVo implements Serializable {
        
    
        private static final long serialVersionUID = -5809782578272943999L;
    
        @ExcelProperty(value = {
        "学生信息","学校"}, order = 1)
        @ContentLoopMerge(eachRow = 3)
        private String school;
    
        @ExcelProperty(value = {
        "学生信息","姓名"}, order = 2)
        private String name;
    
        @ExcelProperty(value = {
        "学生信息","性别"}, order = 3)
        private String sex;
    
        @ExcelProperty(value = {
        "学生信息","年龄"}, order = 4)
        private String age;
    
        @ExcelProperty(value = {
        "学生信息","城市"}, order = 5)
        private String city;
    
        @ExcelProperty(value = {
        "学生信息","备注"}, order = 6)
        private String remarks;
    
        }

对应业务代码：同上

@ContentLoopMerge(eachRow = 3) 可以合并单元格，但是他是按指定行数合并，并不能实现内容相同的合并

@ExcelProperty(value = \{“学生信息”,“备注”\}，能实现多个标题，但标题是固定的，不是动态的

三、自定义改造  
导出的结果：  
![在这里插入图片描述][8ceca20564a94440935d9bf0fd3c5b16.png]

对应实体类代码：

@Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @Data
    @ContentRowHeight(30)
    @HeadRowHeight(40)
    @ColumnWidth(25)
    public class StudentExportVo implements Serializable {
        
    
        private static final long serialVersionUID = -5809782578272943999L;
    
        @ExcelProperty(value = {
        "学生信息","学校"}, order = 1)
        //@ContentLoopMerge(eachRow = 3)
        private String school;
    
        @ExcelProperty(value = {
        "学生信息","姓名"}, order = 2)
        private String name;
    
        @ExcelProperty(value = {
        "学生信息","性别"}, order = 3)
        private String sex;
    
        @ExcelProperty(value = {
        "学生信息","年龄"}, order = 4)
        private String age;
    
        @ExcelProperty(value = {
        "学生信息","城市"}, order = 5)
        private String city;
    
        @ExcelProperty(value = {
        "学生信息","备注"}, order = 6)
        private String remarks;
    
    }

对应业务代码：

@ApiOperation(value = "导出学生信息", notes = "导出学生信息")
        @PostMapping("/exportStudent")
        public void exportStudent(@RequestBody String dynamicTitle, HttpServletResponse response) {
        
            List<StudentExportVo> list = this.getStudentExportVos();
            try {
        
                String fileName = "学生信息";
                fileName = URLEncoder.encode(fileName, "UTF-8");
                response.setContentType("application/json;charset=utf-8");
                response.setCharacterEncoding("utf-8");
                response.addHeader("Pargam", "no-cache");
                response.addHeader("Cache-Control", "no-cache");
                response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
                ServletOutputStream output = response.getOutputStream();
                //需要合并的列
                int[] mergeColumeIndex = {
        0};
                // 从第二行后开始合并
                int mergeRowIndex = 2;
                //设置动态标题
                List<List<String>> headers = this.getHeaders("学生信息" + dynamicTitle);
                // 头的策略
                WriteCellStyle headWriteCellStyle = new WriteCellStyle();
                // 背景设置为白色
                headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
                /*WriteFont headWriteFont = new WriteFont();
                headWriteFont.setFontHeightInPoints((short)20);
                headWriteCellStyle.setWriteFont(headWriteFont);*/
    
                // 内容的策略
                WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
                //contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                // 背景绿色
                //contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                //设置 自动换行
                contentWriteCellStyle.setWrapped(true);
                //设置 垂直居中
                contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                //设置 水平居中
                contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
                /*WriteFont contentWriteFont = new WriteFont();
                // 字体大小
                contentWriteFont.setFontHeightInPoints((short)20);
                contentWriteCellStyle.setWriteFont(contentWriteFont);*/
                // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
                HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                        new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    
                EasyExcel.write(output, StudentExportVo.class)
                        .sheet("学生信息")
                        .head(headers)
                        .registerWriteHandler(new ExcelMergeHandler(mergeRowIndex, mergeColumeIndex))
                        .registerWriteHandler(horizontalCellStyleStrategy)
    //                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(30))
                        .registerWriteHandler(new AbstractColumnWidthStyleStrategy() {
        
                            @Override
                            protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        
                                Sheet sheet = writeSheetHolder.getSheet();
                                int columnIndex = cell.getColumnIndex();
                                if(columnIndex == 5){
        
                                    // 列宽100
                                    sheet.setColumnWidth(columnIndex, 10000);
                                }else {
        
                                    // 列宽50
                                    sheet.setColumnWidth(columnIndex, 5000);
                                }
                                // 行高40
                                sheet.setDefaultRowHeight((short) 4000);
                            }
                        })
                        .doWrite(list);
                output.flush();
            } catch (IOException e) {
        
                log.error(e.getMessage(), e);
            }
        }
    
    
        private List<List<String>> getHeaders(String dynamicTitle) {
        
            List<List<String>> headers=new ArrayList<>();
            List<String> schoolHead=new ArrayList<>();
            schoolHead.add(dynamicTitle);
            schoolHead.add("学校");
            List<String> nameHead=new ArrayList<>();
            nameHead.add(dynamicTitle);
            nameHead.add("姓名");
            List<String> sexHead=new ArrayList<>();
            sexHead.add(dynamicTitle);
            sexHead.add("性别");
            List<String> ageHead=new ArrayList<>();
            ageHead.add(dynamicTitle);
            ageHead.add("年龄");
            List<String> cityHead=new ArrayList<>();
            cityHead.add(dynamicTitle);
            cityHead.add("城市");
            List<String> remarksHead=new ArrayList<>();
            remarksHead.add(dynamicTitle);
            remarksHead.add("备注");
            headers.add(schoolHead);
            headers.add(nameHead);
            headers.add(sexHead);
            headers.add(ageHead);
            headers.add(cityHead);
            headers.add(remarksHead);
            return headers;
        }
    
        //数据制造
        private List<StudentExportVo> getStudentExportVos() {
        
            List<StudentExportVo> list = new ArrayList<>();
            StudentExportVo v1 = new StudentExportVo();
            v1.setSchool("北京大学");
            v1.setName("张三");
            v1.setSex("男");
            v1.setAge("20");
            v1.setCity("北京");
            v1.setRemarks("无");
            list.add(v1);
            StudentExportVo v2 = new StudentExportVo();
            v2.setSchool("北京大学");
            v2.setName("李四");
            v2.setSex("男");
            v2.setAge("22");
            v2.setCity("上海");
            v2.setRemarks("无");
            list.add(v2);
            StudentExportVo v3 = new StudentExportVo();
            v3.setSchool("北京大学");
            v3.setName("王五");
            v3.setSex("女");
            v3.setAge("22");
            v3.setCity("青岛");
            v3.setRemarks("无");
            list.add(v3);
            StudentExportVo v4 = new StudentExportVo();
            v4.setSchool("清华大学");
            v4.setName("赵六");
            v4.setSex("女");
            v4.setAge("21");
            v4.setCity("重庆");
            v4.setRemarks("无");
            list.add(v4);
            StudentExportVo v5 = new StudentExportVo();
            v5.setSchool("武汉大学");
            v5.setName("王强");
            v5.setSex("男");
            v5.setAge("24");
            v5.setCity("长沙");
            v5.setRemarks("无");
            list.add(v5);
            StudentExportVo v6 = new StudentExportVo();
            v6.setSchool("武汉大学");
            v6.setName("赵燕");
            v6.setSex("女");
            v6.setAge("21");
            v6.setCity("深圳");
            v6.setRemarks("无");
            list.add(v6);
            StudentExportVo v7 = new StudentExportVo();
            v7.setSchool("厦门大学");
            v7.setName("陆仟");
            v7.setSex("女");
            v7.setAge("21");
            v7.setCity("广州");
            v7.setRemarks("无");
            list.add(v7);
            return list;
        }

合并单元格相同内容处理类：ExcelMergeHandler

import com.alibaba.excel.metadata.CellData;
    import com.alibaba.excel.metadata.Head;
    import com.alibaba.excel.write.handler.CellWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    import java.util.List;
    public class ExcelMergeHandler implements CellWriteHandler {
        
        private int[] mergeColumnIndex;
        private int mergeRowIndex;
    
        public ExcelMergeHandler() {
        
        }
    
        public ExcelMergeHandler(int mergeRowIndex, int[] mergeColumnIndex) {
        
            this.mergeRowIndex = mergeRowIndex;
            this.mergeColumnIndex = mergeColumnIndex;
        }
    
        @Override
        public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        
    
        }
    
        @Override
        public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        
    
        }
    
        @Override
        public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        
    
        }
    
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        
    
            //当前行
            int curRowIndex = cell.getRowIndex();
            //当前列
            int curColIndex = cell.getColumnIndex();
    
            if (curRowIndex > mergeRowIndex) {
        
                for (int i = 0; i < mergeColumnIndex.length; i++) {
        
                    if (curColIndex == mergeColumnIndex[i]) {
        
                        mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                        break;
                    }
                }
            }
        }
    
    
    
     /**
         * 当前单元格向上合并
         *
         * @param writeSheetHolder
         * @param cell             当前单元格
         * @param curRowIndex      当前行
         * @param curColIndex      当前列
         */
        private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        
            Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
            Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
            Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
            // 将当前单元格数据与上一个单元格数据比较
            Boolean dataBool = preData.equals(curData);
            //此处需要注意：因为我是按照工程名称确定是否需要合并的，所以获取每一行第二列数据和上一行第一列数据进行比较，如果相等合并，getCell里面的值，是工程名称所在列的下标
            String s1 = cell.getRow().getCell(0).getStringCellValue();
            String s2 = cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue();
            /*BigDecimal d1 = new BigDecimal(cell.getRow().getCell(0).getNumericCellValue());
            BigDecimal d2 = new BigDecimal(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getNumericCellValue());*/
            Boolean bool = s1.compareTo(s2) == 0 ? true:false;
            // 原始的
            // Boolean bool =  cell.getRow().getCell(1).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(1).getStringCellValue());
            if (dataBool && bool) {
        
                Sheet sheet = writeSheetHolder.getSheet();
                List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
                boolean isMerged = false;
                for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
        
                    CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                    // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                    if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
        
                        sheet.removeMergedRegion(i);
                        cellRangeAddr.setLastRow(curRowIndex);
                        sheet.addMergedRegion(cellRangeAddr);
                        isMerged = true;
                    }
                }
                // 若上一个单元格未被合并，则新增合并单元
                if (!isMerged) {
        
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                    sheet.addMergedRegion(cellRangeAddress);
                }
            }
        }
    }

总结：

1、使用自定义合并相同内容单元格时，实体类中的注解@ContentLoopMerge需要去掉，要不然会受到影响

2、该段自定义代码中如内容合并、设置动态标题、内容、以及每个列的宽度及字体都是可以根据自定义策略来进行设置的，具体用法可以参考代码注释说明，根据需要进行使用

[9bf65845adf8467696cce83c5f93bb3d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/3737b19d6f2a41378432ed713428a113.png
[24ae4df7a98c4c789c433a921164f49c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/972d91a9ff5040a1be8404203962d5cb.png
[8ceca20564a94440935d9bf0fd3c5b16.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/1a08c5cd350c447a8f3f24fad00876ac.png