快捷導(dǎo)航

Java使用EasyExcel進(jìn)行單元格合并的問題詳解

更新時(shí)間：2022年06月20日 10:28:58 作者：李逍遙xy

項(xiàng)目中需要導(dǎo)出并合并指定的單元格,下面這篇文章主要給大家介紹了關(guān)于java評論、回復(fù)功能設(shè)計(jì)與實(shí)現(xiàn)的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

1.項(xiàng)目場景：

簡介：報(bào)銷單導(dǎo)出要根據(jù)指定的excel模板去自動(dòng)替換對應(yīng)，然后重新生成一份新的excel。在給定的excel模板中，有部分字段進(jìn)行了單元格合并，如下所示。

2.問題描述

由于一張報(bào)銷單可能存在多條報(bào)銷內(nèi)容，可以看到，當(dāng)超過模板中預(yù)先給定的一條時(shí)，則會自動(dòng)換行，但換行時(shí)并不會自動(dòng)依照模板中的樣式進(jìn)行單元格合并，如下所示。

3.原因分析：

首先可以直觀的看到excel進(jìn)行數(shù)據(jù)插入并自動(dòng)換行的時(shí)候，換行的數(shù)據(jù)并沒有按照上一行的樣式進(jìn)行自動(dòng)合并。
于是便想著用代碼把這幾列手動(dòng)合并，然后再加上邊框樣式就可以解決了。

4.解決方案：

需要注意的是，按照以上的思路，直接進(jìn)行單元格合并，然后加上邊框并不能直接解決問題。
需要將后邊空的每一個(gè)單元格先創(chuàng)建出來，然后將其一塊合并才可以解決，創(chuàng)建單元格代碼在下方CustomCellWriteHandler類中說明。

這也算是耗費(fèi)一整天時(shí)間踩的坑。。。

public static void outExcelBalance(String modelFile, String newFile, Map<String, Object> map, List<FillDataExpense> fillData, HttpServletResponse response, String fileName){
        //定義model模板中默認(rèn)的行數(shù)
        int firstRow = 7; //excel中表示第八行，即模板中默認(rèn)的一條
        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){
                    //合并每條報(bào)銷單的第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 {

    //合并坐標(biāo)集合
    private List<CellRangeAddress> cellRangeAddresss;

    //構(gòu)造
    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);

    //標(biāo)黃行寬集合
    private Set<Integer> rowIndexs;

    //構(gòu)造
    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進(jìn)行任何操作
        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);//自動(dòng)換行

            //字體
//            Font cellFont = workbook.createFont();
//            cellFont.setBold(true);
//            cellStyle.setFont(cellFont);
//            //標(biāo)黃,要一起設(shè)置
//            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //設(shè)置前景填充樣式
//            cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//前景填充色

            if (rowIndexs.contains(cell.getRowIndex())) {
                Row row = null;
                //循環(huán)創(chuàng)建空白單元格
                for (int i = 0; i < rowIndexs.size(); i++) {
                    for (Integer rowIndex : rowIndexs){
                        //創(chuàng)建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("第{}行，第{}列創(chuàng)建空白格。", cell.getRowIndex(), j);
                        }
                        //創(chuàng)建12列的紅白格
                        cell = row.createCell(11);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(" ");
                        LOGGER.info("第{}行，第11列創(chuàng)建空白格。", cell.getRowIndex());
                        //創(chuàng)建21列的空白格
                        cell = row.createCell(21);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(" ");
                        LOGGER.info("第{}行，第21列創(chuàng)建空白格。", cell.getRowIndex());
                    }
                }
            }
        }
    }
}

5.總結(jié)

核心步驟：

1. 
	//創(chuàng)建單元格樣式
	CustomCellWriteHandler customCellWriteHandler = new CustomCellWriteHandler(參數(shù)按需給定);
2. 
	//單元格進(jìn)行合并
	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();

說明：剛開始修復(fù)的時(shí)候，并沒有想過后邊每個(gè)空的單元格需要先創(chuàng)建出來，才可以進(jìn)行合并。一直以為是工具類的問題，后來不斷的翻閱解決方案，看到有說需要先進(jìn)行創(chuàng)建空白單元格，然后再進(jìn)行合并，最終完美解決了。

關(guān)于代碼部分，由于是業(yè)務(wù)代碼，中間夾雜了許多不需要的。