easyexcel合并单元格底色

刺骨的言语ヽ痛彻心扉 2024-03-24 00:03 78阅读 0赞

#### 一、效果图 ####

![483571f4cd054854836ce500af9a1367.png][]

#### 二、导出接口代码 ####

@PostMapping("selectAllMagicExport")
        public void selectAllMagicExport(HttpServletRequest request, HttpServletResponse response) throws IOException {
            ServiceResult<SearchResult<TestMetLineFe2o3Export>> result = success(searcher.search(TestMetLineFe2o3Export.class, MapUtils.flat(request.getParameterMap())));
            SearchResult<TestMetLineFe2o3Export> searchResult = result.getData();
            List<TestMetLineFe2o3Export> dataList = searchResult.getDataList();
    
            // Excel格式：入厂日期相同日期合并
    
            // 设置响应头信息
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=chatEduExport.xlsx");
    
            // 使用EasyExcel进行导出
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), TestMetLineFe2o3Export.class)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerWriteHandler(new ExcelFillCellMergeStrategy(1, new int[]{1, 4, 5, 6, 12, 14}))
                    .build();
            WriteSheet writeSheet = EasyExcel.writerSheet("铁红").build();
            excelWriter.write(dataList, writeSheet);
    
            excelWriter.finish();
        }

#### 三、拦截器 ####

import cn.hutool.core.util.ReUtil;
    import cn.hutool.core.util.StrUtil;
    import com.alibaba.excel.metadata.Head;
    import com.alibaba.excel.metadata.data.WriteCellData;
    import com.alibaba.excel.write.handler.CellWriteHandler;
    import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
    import com.alibaba.excel.write.metadata.style.WriteCellStyle;
    import com.ciih.workshop.entity.TestMetLineFe2o3Export;
    import com.ciih.workshop.utils.HexToRGB;
    import com.ejlchina.searcher.BeanSearcher;
    import lombok.Data;
    import org.apache.poi.hssf.usermodel.HSSFPalette;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
    import org.apache.poi.xssf.usermodel.XSSFRichTextString;
    import org.springframework.stereotype.Component;
    
    import javax.annotation.PostConstruct;
    import javax.annotation.Resource;
    import java.util.HashMap;
    import java.util.List;
    
    /**
     * 合并单元格
     */
    @Component
    @Data
    public class ExcelFillCellMergeStrategy implements CellWriteHandler {
        @Resource
        private BeanSearcher searcher;
        // 先声明一个对象
        private static ExcelFillCellMergeStrategy excelFillCellMergeStrategy;
    
        //启动注入
        @PostConstruct
        public void init() {
            excelFillCellMergeStrategy = this;
            excelFillCellMergeStrategy.searcher = this.searcher;
        }
    
        /**
         * 合并字段的下标，如第一到五列new int[]{0,1,2,3,4}
         */
        private int[] mergeColumnIndex;
        /**
         * 从第几行开始合并，如果表头占两行，这个数字就是2
         */
        private int mergeRowIndex;
    
        public ExcelFillCellMergeStrategy() {
        }
    
        public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
            this.mergeRowIndex = mergeRowIndex;
            this.mergeColumnIndex = mergeColumnIndex;
        }
    
        @Override
        public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                     Head head, Integer integer, Integer integer1, Boolean aBoolean) {
    
        }
    
        @Override
        public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                                    Head head, Integer integer, Boolean aBoolean) {
    
        }
    
        @Override
        public void afterCellDispose(CellWriteHandlerContext context) {
            // 当前单元格
            Cell cell = context.getCell();
            //当前行
            int curRowIndex = context.getCell().getRowIndex();
            //当前列
            int curColIndex = context.getCell().getColumnIndex();
    
            if (curRowIndex > mergeRowIndex) {
                for (int i = 0; i < mergeColumnIndex.length; i++) {
                    if (curColIndex == mergeColumnIndex[i]) {
                        // 合并单元格
                        mergeWithPrevRow(context.getWriteSheetHolder(), context.getCell(), curRowIndex, curColIndex);
                        break;
                    }
                }
            }
    
            // 设置内容居中
            WriteCellData<?> cellData = context.getFirstCellData();
            WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
            writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    
            // 设置边框
            writeCellStyle.setBorderBottom(BorderStyle.THIN);
            writeCellStyle.setBorderLeft(BorderStyle.THIN);
            writeCellStyle.setBorderRight(BorderStyle.THIN);
            writeCellStyle.setBorderTop(BorderStyle.THIN);
    
            // 忽略表头
            if (cell.getRowIndex() > 0) {
                // 底色
                coloring(context, writeCellStyle, cell, curRowIndex, curColIndex);
            }
        }
    
        /**
         * 着色
         *
         * @param writeCellStyle
         * @param cell
         * @param curRowIndex    当前行
         * @param curColIndex    当前列
         */
        private void coloring(CellWriteHandlerContext context, WriteCellStyle writeCellStyle, Cell cell, int curRowIndex, int curColIndex) {
            // 设置单元格颜色
            // 拿到当前行的所有数据
            Cell curInDateCell = cell.getSheet().getRow(curRowIndex).getCell(0); // 唯一编号
            double id = curInDateCell.getNumericCellValue();
            // 完整数据
            HashMap<String, Object> flat = new HashMap<>();
            flat.put("id", (long) id);
            TestMetLineFe2o3Export fe2o3Export = excelFillCellMergeStrategy.searcher.searchFirst(TestMetLineFe2o3Export.class, flat);
    
    
            // 渲染入场批号的颜色
            if (cell.getColumnIndex() == 4) {
                coloringAction(fe2o3Export.getTestMetInnumStandardColor(), writeCellStyle);
                // 加批注
                noteAction(context, cell, fe2o3Export.getTestMetInnumStandardLevel());
            }
            // 氯根着色
            if (cell.getColumnIndex() == 7) {
                coloringAction(fe2o3Export.getLgStandardColor(), writeCellStyle);
                noteAction(context, cell, fe2o3Export.getLgStandardLevel());
            }
            // 水分着色
            if (cell.getColumnIndex() == 8) {
                coloringAction(fe2o3Export.getWaterStandardColor(), writeCellStyle);
                noteAction(context, cell, fe2o3Export.getWaterStandardLevel());
            }
            // 粒度着色
            if (cell.getColumnIndex() == 10) {
                coloringAction(fe2o3Export.getLdStandardColor(), writeCellStyle);
                noteAction(context, cell, fe2o3Export.getLdStandardLevel());
            }
            // Na2O着色
            if (cell.getColumnIndex() == 17) {
                coloringAction(fe2o3Export.getNa2oStandardColor(), writeCellStyle);
                noteAction(context, cell, fe2o3Export.getNa2oStandardLevel());
            }
            // Al2O3着色
            if (cell.getColumnIndex() == 19) {
                coloringAction(fe2o3Export.getAl2o3StandardColor(), writeCellStyle);
                noteAction(context, cell, fe2o3Export.getAl2o3StandardLevel());
            }
            // SiO2着色
            if (cell.getColumnIndex() == 20) {
                coloringAction(fe2o3Export.getSio2StandardColor(), writeCellStyle);
                noteAction(context, cell, fe2o3Export.getSio2StandardLevel());
            }
            // CaO着色
            if (cell.getColumnIndex() == 24) {
                coloringAction(fe2o3Export.getCaoStandardColor(), writeCellStyle);
                noteAction(context, cell, fe2o3Export.getCaoStandardLevel());
            }
            // Cr2O3着色
            if (cell.getColumnIndex() == 26) {
                coloringAction(fe2o3Export.getCr2o3StandardColor(), writeCellStyle);
                noteAction(context, cell, fe2o3Export.getCr2o3StandardLevel());
            }
            // MnO着色
            if (cell.getColumnIndex() == 27) {
                coloringAction(fe2o3Export.getMnoStandardColor(), writeCellStyle);
                noteAction(context, cell, fe2o3Export.getMnoStandardLevel());
            }
            // Fe2O3着色
            if (cell.getColumnIndex() == 28) {
                coloringAction(fe2o3Export.getFe2o3StandardColor(), writeCellStyle);
                noteAction(context, cell, fe2o3Export.getFe2o3StandardLevel());
            }
        }
    
        /**
         * 批注
         */
        private void noteAction(CellWriteHandlerContext context, Cell cell, String content) {
            if (StrUtil.isBlank(content)) {
                return;
            }
            Sheet sheet = context.getWriteSheetHolder().getSheet();
            ClientAnchor anchor = new XSSFClientAnchor();
            //关键修改
            anchor.setDx1(0);
            anchor.setDx2(0);
            anchor.setDy1(0);
            anchor.setDy2(0);
            anchor.setCol1(cell.getColumnIndex());
            anchor.setRow1(cell.getRowIndex());
            anchor.setCol2(cell.getColumnIndex());
            anchor.setRow2(cell.getRowIndex());
    
            Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
            Comment cellComment = drawingPatriarch.createCellComment(anchor);
    
            cellComment.setString(new XSSFRichTextString(content));
            cell.setCellComment(cellComment);
        }
    
        /**
         * 着色动作
         */
        private void coloringAction(String color, WriteCellStyle writeCellStyle) {
            if (color == null) {
                return;
            }
            Integer r = null;
            Integer g = null;
            Integer b = null;
    
            //
            if (color.startsWith("#")) {
                int[] ints = HexToRGB.hexToRGB(color);
                r = ints[0];
                g = ints[1];
                b = ints[2];
            } else {
                List<String> all01 = ReUtil.findAll("(?<=\\().*?(?=\\))", color, 0);
                if (all01 != null && all01.size() > 0 && all01.get(0).split(",").length >= 3) {
                    String[] split = all01.get(0).split(",");
                    // RGB颜色转换
                    r = Integer.parseInt(split[0].trim());
                    g = Integer.parseInt(split[1].trim());
                    b = Integer.parseInt(split[2].trim());
                }
            }
    
            if (r != null && g != null && b != null) {
                HSSFWorkbook wb = new HSSFWorkbook();
                HSSFPalette palette = wb.getCustomPalette();
                HSSFColor hssfColor = palette.findSimilarColor(r, g, b);
    //                writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                writeCellStyle.setFillForegroundColor(hssfColor.getIndex());
                writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
            }
        }
    
        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();
    
            // 如果是日期列,即第一列，只要相同就合并
            if (cell.getColumnIndex() == 1) {
                if (curData.equals(preData)) {
                    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);
                    }
                }
            } else {
                // 如果日期和批号与上一行的日期和批号相同。则进行当前行列的合并
                // 当前行的日期和批号
                Cell curInDateCell = cell.getSheet().getRow(curRowIndex).getCell(1);
                Object curInDate = curInDateCell.getCellTypeEnum() == CellType.STRING ? curInDateCell.getStringCellValue() : curInDateCell.getNumericCellValue();
    
                Cell curInNumCell = cell.getSheet().getRow(curRowIndex).getCell(4);
                Object curInNum = curInNumCell.getCellTypeEnum() == CellType.STRING ? curInNumCell.getStringCellValue() : curInNumCell.getNumericCellValue();
                // 上一行的日期和批号
                Cell preInDateCell = cell.getSheet().getRow(curRowIndex - 1).getCell(1);
                Object preInDate = preInDateCell.getCellTypeEnum() == CellType.STRING ? preInDateCell.getStringCellValue() : preInDateCell.getNumericCellValue();
    
                Cell preInNumCell = cell.getSheet().getRow(curRowIndex - 1).getCell(4);
                Object preInNum = preInNumCell.getCellTypeEnum() == CellType.STRING ? preInNumCell.getStringCellValue() : preInNumCell.getNumericCellValue();
    
    
                if (curInDate.equals(preInDate) && curInNum.equals(preInNum)) {
    
                    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);
                    }
                }
            }
        }
    }

[483571f4cd054854836ce500af9a1367.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/a43135d697e34fa1a80c7d7a17701daa.png