Java怎麼使用EasyExcel進行儲存格合併

1.專案場景：

簡介：報銷單匯出要根據指定的excel範本去自動取代對應，然後重新產生一份新的excel。在給定的excel範本中，有部分欄位進行了儲存格合併，如下所示。

2.問題描述

由於一張報銷單可能存在多條報銷內容，可以看到，當超過範本中預先給定的一條時，則會自動換行，但換行時並不會自動依照範本中的樣式進行儲存格合併，如下所示。

3.原因分析：

#首先可以直觀的看到excel進行資料插入並自動換行的時候，換行的資料並沒有依照上一行的樣式進行自動合併。
於是便想著用程式碼把這幾列手動合併，然後再加上邊框樣式就可以解決了。

4.解決方案：

1. 要注意的是，依照以上的思路，直接進行儲存格合併，然後加上邊框並不能直接解決問題。

2. 需要將後邊空的每個儲存格先建立出來，然後將其一塊合併才可以解決，建立儲存格程式碼在下方CustomCellWriteHandler類別中說明。

這也算是耗費一整天時間踩的坑。 。 。

public static void outExcelBalance(String modelFile, String newFile, Map<String, Object> map, List<FillDataExpense> fillData, HttpServletResponse response, String fileName){
        //定义model模板中默认的行数
        int firstRow = 7; //excel中表示第八行，即模板中默认的一条
        int lastRow = 7;
        InputStream is = null;
        File file = new File(modelFile);
        File file1 = new File(newFile);
        //String file1Name = file1.getName();
        BufferedInputStream bis = null;
        try {
            if (!file.exists()) {
                copyFileUsingJava7Files(file, file1);
            }

            //TODO 单元格样式
            Set<Integer> rowsBorderSet= new HashSet<>();
            CustomCellWriteHandler customCellWriteHandler = null;

            //TODO 单元格合并
            List<CellRangeAddress> cellRangeAddresss = new ArrayList<>();

            if (ListUtils.isNotNull(fillData)){
                if (fillData.size() > 1){
                    //合并每条报销单的第3-10列
                    for (int i = 1; i < fillData.size(); i++) {
                        firstRow++;
                        lastRow++;

                        cellRangeAddresss.add(new CellRangeAddress(firstRow, lastRow, 2, 9));
                        cellRangeAddresss.add(new CellRangeAddress(firstRow, lastRow, 10, 11));

                        rowsBorderSet.add(firstRow);
                    }
                }
            }
            customCellWriteHandler = new CustomCellWriteHandler(rowsBorderSet);
            MyMergeStrategy myMergeStrategy = new MyMergeStrategy(cellRangeAddresss);

            ExcelWriter excelWriter = EasyExcel.write(newFile)
                    //注册单元格式
                    .registerWriteHandler(customCellWriteHandler)
                    //注册合并策略
                    .registerWriteHandler(myMergeStrategy)
                    .withTemplate(modelFile).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            if (!ListUtil.listIsEmpty(fillData)){
                excelWriter.fill(fillData, fillConfig, writeSheet);
                //excelWriter.fill(fillData, fillConfig, writeSheet);
            }
            excelWriter.fill(map, writeSheet);
            excelWriter.finish();
            response.setHeader("content-type", "text/plain");
            response.setHeader("content-type", "application/x-msdownload;");
            response.setContentType("text/plain; charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("utf-8"),"ISO8859-1"));
            byte[] buff = new byte[1024];

            OutputStream os = null;
            os = response.getOutputStream();
            bis = new BufferedInputStream(new FileInputStream(file1));
            int i = bis.read(buff);

            while (i != -1) {
                os.write(buff, 0, buff.length);
                os.flush();
                i = bis.read(buff);
            }
        }
        catch (Exception e){
            LOGGER.error(e.getMessage());
        }
        finally {
            if (bis != null) {
                try {
                    bis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            // 删除生成文件
                /*if (file1.exists()) {
                    file1.delete();
                }*/
        }
    }

單元格合併MyMergeStrategy類別程式碼：

public class MyMergeStrategy extends AbstractMergeStrategy {

    //合并坐标集合
    private List<CellRangeAddress> cellRangeAddresss;

    //构造
    public MyMergeStrategy(List<CellRangeAddress> cellRangeAddresss) {
        this.cellRangeAddresss = cellRangeAddresss;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
        if (ListUtils.isNotNull(cellRangeAddresss)) {
            if (cell.getRowIndex() == 7 ) {
                for (CellRangeAddress item : cellRangeAddresss) {
                    sheet.addMergedRegionUnsafe(item);
                }
            }
        }
    }
}

單元格樣式CustomCellWriteHandler類別程式碼：

public class CustomCellWriteHandler implements CellWriteHandler {
    private static final Logger LOGGER = LoggerFactory.getLogger(CustomCellWriteHandler.class);

    //标黄行宽集合
    private Set<Integer> rowIndexs;

    //构造
    public CustomCellWriteHandler(Set<Integer> rowIndexs) {
        this.rowIndexs = rowIndexs;
    }

    public CustomCellWriteHandler() {
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        LOGGER.info("beforeCellCreate~~~~");
    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        LOGGER.info("afterCellCreate~~~~");
    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

          //获取工作簿
//        HSSFWorkbook wb = new HSSFWorkbook();
//        //获取sheet
//        HSSFSheet sheet = wb.createSheet();
//        HSSFRow row = sheet.createRow();
//        HSSFCellStyle style = wb.createCellStyle();

        // 这里可以对cell进行任何操作
        if (CollectionUtils.isNotEmpty(rowIndexs)) {
            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();

            Sheet sheet = writeSheetHolder.getSheet();
            cellStyle.setAlignment(new HSSFWorkbook().createCellStyle().getAlignment());
            cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
            cellStyle.setBorderTop(BorderStyle.THIN);//上边框
            cellStyle.setBorderRight(BorderStyle.THIN);//右边框
            cellStyle.setWrapText(true);//自动换行

            //字体
//            Font cellFont = workbook.createFont();
//            cellFont.setBold(true);
//            cellStyle.setFont(cellFont);
//            //标黄,要一起设置
//            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置前景填充样式
//            cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//前景填充色

            if (rowIndexs.contains(cell.getRowIndex())) {
                Row row = null;
                //循环创建空白单元格
                for (int i = 0; i < rowIndexs.size(); i++) {
                    for (Integer rowIndex : rowIndexs){
                        //创建4-10列的空白格
                        row = sheet.getRow(rowIndex.intValue());
                        if (row == null){
                            row = sheet.createRow(rowIndex.intValue());
                        }
                        for (int j = 3; j <= 9; j++) {
                            //获取8行的cell列
                            cell = row.createCell(j);
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(" ");
                            LOGGER.info("第{}行，第{}列创建空白格。", cell.getRowIndex(), j);
                        }
                        //创建12列的红白格
                        cell = row.createCell(11);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(" ");
                        LOGGER.info("第{}行，第11列创建空白格。", cell.getRowIndex());
                        //创建21列的空白格
                        cell = row.createCell(21);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(" ");
                        LOGGER.info("第{}行，第21列创建空白格。", cell.getRowIndex());
                    }
                }
            }
        }
    }
}

5.總結

核心步驟：

1. 
	//创建单元格样式
	CustomCellWriteHandler customCellWriteHandler = new CustomCellWriteHandler(参数按需给定);
2. 
	//单元格进行合并
	List<CellRangeAddress> cellRangeAddresss = new ArrayList<>();
	//例如：从firstRow行到lastRow行的2列到9列合并
    cellRangeAddresss.add(new CellRangeAddress(firstRow, lastRow, 2, 9));
    cellRangeAddresss.add(new CellRangeAddress(firstRow, lastRow, 10, 11));
    MyMergeStrategy myMergeStrategy = new MyMergeStrategy(cellRangeAddresss);
3. 
	//注册以上两种策略
	ExcelWriter excelWriter = EasyExcel.write(newFile)
    //注册单元格式
    .registerWriteHandler(customCellWriteHandler)
    //注册合并策略
    .registerWriteHandler(myMergeStrategy)
    .withTemplate(modelFile).build();

說明：剛開始修復的時候，並沒有想過後邊每個空的儲存格需要先建立出來，才可以進行合併。一直以為是工具類的問題，後來不斷的翻閱解決方案，看到有說需要先進行創建空白單元格，然後再進行合併，最終完美解決了。

關於程式碼部分，由於是業務程式碼，中間夾雜了許多不需要的。

以上是Java怎麼使用EasyExcel進行儲存格合併的詳細內容。更多資訊請關注PHP中文網其他相關文章！