How to use EasyExcel to merge cells in Java

1. Project scenario:

Introduction: Exporting reimbursement forms should automatically replace the corresponding ones according to the specified excel template, and then regenerate a new excel. In the given excel template, some fields have been cell merged, as shown below.

2. Problem description

Since a reimbursement form may contain multiple reimbursement contents, you can see that when the number of reimbursement items exceeds the preset value in the template, When a specified line is specified, the line will be automatically wrapped, but the cells will not be automatically merged according to the style in the template when the line is wrapped, as shown below.

3. Cause analysis:

First of all, you can intuitively see that when excel inserts data and automatically wraps the line, the line breaks The data is not automatically merged according to the style of the previous row.
So I thought about using code to manually merge these columns, and then adding the border style would solve the problem.

4. Solution:

1. It should be noted that following the above idea, merging cells directly and then adding borders cannot directly solve the problem. question.

2. You need to create each empty cell at the end first, and then merge them together to solve the problem. The code for creating cells is explained in the CustomCellWriteHandler class below .

This can be regarded as a pit that took a whole day to step on. . .

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();
                }*/
        }
    }

Cell mergeMyMergeStrategyClass code:

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);
                }
            }
        }
    }
}

Cell styleCustomCellWriteHandlerClass code:

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. Summary

Core steps:

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();

Note: When I first started repairing, I didn’t think that every empty cell in the future needs to be created before it can be merged. I always thought it was a tool problem, but then I kept reading through the solutions and saw that it was said that blank cells need to be created first and then merged. Finally, it was solved perfectly.

Regarding the code part, since it is business code, there are many unnecessary things mixed in.

The above is the detailed content of How to use EasyExcel to merge cells in Java. For more information, please follow other related articles on the PHP Chinese website!