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進行單元格合並的文章就介紹到這瞭,更多相關EasyExcel單元格合並內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: