So verwenden Sie EasyExcel zum Zusammenführen von Zellen in Java

public static void outExcelBalance(String modelFile, String newFile, Map map, List 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 rowsBorderSet= new HashSet<>(); CustomCellWriteHandler customCellWriteHandler = null; //TODO 单元格合并 List 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(); }*/ } }

public class MyMergeStrategy extends AbstractMergeStrategy { //合并坐标集合 private List cellRangeAddresss; //构造 public MyMergeStrategy(List 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); } } } } }

public class CustomCellWriteHandler implements CellWriteHandler { private static final Logger LOGGER = LoggerFactory.getLogger(CustomCellWriteHandler.class); //标黄行宽集合 private Set rowIndexs; //构造 public CustomCellWriteHandler(Set 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 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()); } } } } } }

1. //创建单元格样式 CustomCellWriteHandler customCellWriteHandler = new CustomCellWriteHandler(参数按需给定); 2. //单元格进行合并 List 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();