Java使用EasyExcel進行單元格合並的問題詳解
1.項目場景:
簡介:報銷單導出要根據指定的excel模板去自動替換對應,然後重新生成一份新的excel。在給定的excel模板中,有部分字段進行瞭單元格合並,如下所示。
2.問題描述
由於一張報銷單可能存在多條報銷內容,可以看到,當超過模板中預先給定的一條時,則會自動換行,但換行時並不會自動依照模板中的樣式進行單元格合並,如下所示。
3.原因分析:
首先可以直觀的看到excel進行數據插入並自動換行的時候,換行的數據並沒有按照上一行的樣式進行自動合並。
於是便想著用代碼把這幾列手動合並,然後再加上邊框樣式就可以解決瞭。
4.解決方案:
- 需要註意的是,按照以上的思路,直接進行單元格合並,然後加上邊框並不能直接解決問題。
- 需要將後邊空的每一個單元格先創建出來,然後將其一塊合並才可以解決,創建單元格代碼在下方
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!
推薦閱讀:
- Java導出Excel統計報表合並單元格的方法詳解
- Java使用EasyExcel動態添加自增序號列
- SpringBoot實現excel文件生成和下載
- C#利用NPOI操作Excel(單元格設置)
- C#使用NPOI實現Excel導入導出功能