# 1. 模板下载 模板  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｝创建，创建后使用样式  设置合并和边框后  ## 方法二，假填充数据 上边的方法合并单元格会出现，在office中有此问题（合并单元格有问题提）  解决方法： **假数据单元格，会被创建，样式会起作用**，注意模板要干净，不要有过样式修改，否则在工具处理后出现多创建单元格的现象，此时清空模板多余部分的内容（即时没有内容） 代码如下： 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); } } } ~~~ 下载的文件正确合并单元格，且不会有错误提示