模板處理 · java development

# 1. 模板下載模板 ![](https://img.kancloud.cn/4f/51/4f51e4109c87717b7b7337693e37c2cc_1418x774.png) excel，rowIndex和columnIndex從0開始，所以list列表從第19行開始，屬于新建，會傳入CellWriteHandler 中 > CellWriteHandler * beforeCellCreate：創建一個cell前（模板上沒有的行-從19行開始）調用，本實例在這個方法中創建cell，并指定樣式。其他兩個方法沒有試過 * afterCellDispose：數據填充后調用，用于合并單元格 ## 1.1 controller ~~~java @SneakyThrows @GetMapping("/export-task/{id}") @ApiOperation("下載任務單:/opsTestTask/export-task/{id}") public void exportPlan(HttpServletResponse response, @PathVariable String id) { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding(Charsets.UTF_8.name()); // File file = ResourceUtils.getFile("classpath:tpl" + File.separator + "task_detail_tpl.xlsx"); // ClassPathResource classPathResource = new ClassPathResource("tpl"+ File.separator + "task_detail_tpl.xlsx"); // File file = classPathResource.getFile(); InputStream inputStream = TestTaskController.class.getClassLoader().getResourceAsStream("tpl/task_detail_tpl.xlsx"); String tmpFileName = UUID.randomUUID().toString()+".xlsx"; File file = new File(tmpFileName); FileUtils.copyInputStreamToFile(inputStream,file); TaskExportVo taskExportVo = testTaskService.getTaskExportVoById(id); String fileName = URLEncoder.encode("任務單_"+taskExportVo.getTaskCode(), Charsets.UTF_8.name()); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(file.getAbsolutePath()).registerWriteHandler(new TaskExcelFillCellStrategy(taskExportVo.getTaskExportListVo().size())).build(); WriteSheet writeSheet = EasyExcel.writerSheet().build(); FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); excelWriter.fill(taskExportVo.getTaskExportListVo(), fillConfig, writeSheet); excelWriter.fill(taskExportVo, writeSheet); excelWriter.finish(); Files.delete(Paths.get(file.getAbsolutePath())); } ~~~ ## 1.2 填充數據設置 ~~~ package com.faw_qm.ad_ops.close_test.config.excel; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.handler.CellWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteTableHolder; import lombok.Data; import lombok.NoArgsConstructor; import lombok.NonNull; import lombok.RequiredArgsConstructor; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import sun.awt.HKSCS; import java.lang.reflect.Array; import java.util.ArrayList; import java.util.Arrays; import java.util.Iterator; import java.util.List; /** * @author WongChy * 2021/3/29 */ @Slf4j @NoArgsConstructor @Data @RequiredArgsConstructor public class TaskExcelFillCellStrategy implements CellWriteHandler { /** * 開始的行數 * 從這一行開始才進行列合并操作 */ private int beginRow = 17; @NonNull private int endRowLength; private static List<Integer> columnStartIndex = Arrays.asList(1, 4); @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { setFilledExcellStyle(writeSheetHolder,row); } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { if (isHead) { return; } this.setCellStyle(cell); merge(writeSheetHolder.getSheet(), cell, head, relativeRowIndex); } void setCellStyle(Cell cell){ CellStyle cellStyle = cell.getCellStyle(); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cell.setCellStyle(cellStyle); } //list需要合并單元格 void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex){ if (relativeRowIndex == null) { return; } int columnIndex = cell.getColumnIndex(); int rowIndex = cell.getRowIndex(); if (columnStartIndex.contains(columnIndex) && rowIndex >= beginRow) { sheet.getWorkbook().createCellStyle(); CellRangeAddress cellRangeAddress = new CellRangeAddress( cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex() + 2 ); sheet.addMergedRegionUnsafe(cellRangeAddress); } } //設置單個cell樣式，避免合并后新填充的單元格沒有邊框 public void setFilledExcellStyle(WriteSheetHolder writeSheetHolder, Row row) { CellStyle cellStyle = writeSheetHolder.getSheet().getWorkbook().createCellStyle(); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); Iterator<Integer> iterator = columnStartIndex.iterator(); while (iterator.hasNext()) { Integer columnIndex = iterator.next(); System.out.println("aa:" + row.getRowNum() + ":" + columnIndex); Cell cell1 = row.createCell(columnIndex + 1); Cell cell2 = row.createCell(columnIndex + 2); cell1.setCellStyle(cellStyle); cell2.setCellStyle(cellStyle); } } } ~~~ ## 1.3 數據 ~~~ package com.faw_qm.ad_ops.close_test.vo; import lombok.Data; import java.util.Date; import java.util.List; /** * @author WongChy * 2021/3/29 */ @Data public class TaskExportVo { private String planCode; private String taskCode; private String subjectName; private String subjectAddress; private String vehicleBrand; private String modelName; private String vehicleVin; private String engineNum; private Date planStartTime; private Date planEndTime; private Date actStartTime; private Date actEndTime; private String contactPerson; private String contactPhone; private String regionName; private String testTypeStr; private String videoMonitorListStr; private String obuEquListStr; private String auxEquListStr; private String siteListStr; private List<TaskExportListVo> taskExportListVo; } ~~~ 問題，是因為工具創建單元格是根據｛data｝創建，創建后使用樣式 ![](https://img.kancloud.cn/94/f7/94f794aae7841adf9689b2b70481c69e_1396x683.png) 設置合并和邊框后 ![](https://img.kancloud.cn/a8/a2/a8a29fc6a18b7a377d2bea8d4ec5ea49_1372x574.png) ## 方法二，假填充數據上邊的方法合并單元格會出現，在office中有此問題（合并單元格有問題提） ![](https://img.kancloud.cn/b8/6d/b86d6170f6de7cd17dc3d59b2ed1bc30_1118x515.png) 解決方法： **假數據單元格，會被創建，樣式會起作用**，注意模板要干凈，不要有過樣式修改，否則在工具處理后出現多創建單元格的現象，此時清空模板多余部分的內容（即時沒有內容）代碼如下： 1. controller ~~~ @GetMapping("/export-task/{id}") @ApiOperation("下載任務單:/opsTestTask/export-task/{id}") public void exportPlan(HttpServletResponse response, @PathVariable String id) { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding(Charsets.UTF_8.name()); // File file = ResourceUtils.getFile("classpath:tpl" + File.separator + "task_detail_tpl.xlsx"); // ClassPathResource classPathResource = new ClassPathResource("tpl"+ File.separator + "task_detail_tpl.xlsx"); // File file = classPathResource.getFile(); InputStream inputStream = TestTaskController.class.getClassLoader().getResourceAsStream("tpl/task_detail_tpl.xlsx"); String tmpFileName = UUID.randomUUID().toString()+".xlsx"; File file = new File(tmpFileName); FileUtils.copyInputStreamToFile(inputStream,file); TaskExportVo taskExportVo = testTaskService.getTaskExportVoById(id); String fileName = URLEncoder.encode("任務單_"+taskExportVo.getTaskCode(), Charsets.UTF_8.name()); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(file.getAbsolutePath()).registerWriteHandler(new TaskExcelFillCellStrategy(taskExportVo.getTaskExportListVo().size())).build(); WriteSheet writeSheet = EasyExcel.writerSheet().build(); FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); excelWriter.fill(taskExportVo.getTaskExportListVo(), fillConfig, writeSheet); excelWriter.fill(taskExportVo, writeSheet); excelWriter.finish(); Files.delete(Paths.get(file.getAbsolutePath())); } ~~~ 2. vo ~~~ @Data public class TaskExportVo { private String planCode; private String taskCode; private String subjectName; private String subjectAddress; private String vehicleBrand; private String modelName; private String vehicleVin; private String engineNum; private Date planStartTime; private Date planEndTime; private Date actStartTime; private Date actEndTime; private String contactPerson; private String contactPhone; private String regionName; private String testTypeStr; private String videoMonitorListStr; private String obuEquListStr; private String auxEquListStr; private String siteListStr; private String targetVehicle; private List<TaskExportListVo> taskExportListVo; } @Data public class TaskExportListVo { private Integer sceneOrder; private String detectionItemName; private String sceneName; private String voidCellOne; //填充假數據（數據為null），工具會創建單元格，方便統一設置樣式 private String voidCellTwo; //填充假數據 private String voidCellThree;//填充假數據 private String voidCellFour;//填充假數據 } ~~~ 3. 單元格處理策略 ~~~ package com.faw_qm.ad_ops.close_test.config.excel; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.handler.CellWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteTableHolder; import lombok.Data; import lombok.NoArgsConstructor; import lombok.NonNull; import lombok.RequiredArgsConstructor; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import sun.awt.HKSCS; import java.lang.reflect.Array; import java.util.ArrayList; import java.util.Arrays; import java.util.Iterator; import java.util.List; /** * @author WongChy * 2021/3/29 */ @Slf4j @NoArgsConstructor @Data @RequiredArgsConstructor public class TaskExcelFillCellStrategy implements CellWriteHandler { /** * 開始的行數 * 從這一行開始才進行列合并操作 */ private int beginRow = 17; @NonNull private int endRowLength; private static List<Integer> columnStartIndex = Arrays.asList(1, 4); @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { if (isHead) { return; } this.setCellStyle(cell); merge(writeSheetHolder.getSheet(), cell, head, relativeRowIndex); } void setCellStyle(Cell cell){ CellStyle cellStyle = cell.getCellStyle(); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cell.setCellStyle(cellStyle); } void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex){ if (relativeRowIndex == null) { return; } int columnIndex = cell.getColumnIndex(); int rowIndex = cell.getRowIndex(); if (columnStartIndex.contains(columnIndex) && rowIndex >= beginRow) { sheet.getWorkbook().createCellStyle(); CellRangeAddress cellRangeAddress = new CellRangeAddress( cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex() + 2 ); sheet.addMergedRegionUnsafe(cellRangeAddress); } } } ~~~ 下載的文件正確合并單元格，且不會有錯誤提示