1.首先看一下，我们导出的模板；
可以看到，导出的模板需要合并单元格，其中在每个项目中工程编号是唯一的，所以需求中有一点，我们必选按照：项目名称+工程编号，作为唯一性来做每个sheet的区分。下面看代码：
2.在看一下实体类，主要是数据的组合和单元格合并设置
public class EstimatedExcel {/*** 植物数据标题*/private String[] titles = {"植物名称", "型号", "种植面积", "预估种植数量", "实际种植数量"};/*** 列总数*/private int rowCount;/*** 项目数据*/private List<List<String>> projectDatas = new ArrayList<>();/*** 植物数据*/private List<List<String>> estimatedDatas = new ArrayList<>();/*** 总计数据*/private List<List<String>> totalDatas = new ArrayList<>();/*** 种植估算*/private PlantEstimated plantEstimated;public EstimatedExcel(PlantEstimated plantEstimated) {this.plantEstimated = plantEstimated;this.rowCount = titles.length;setProjectData();setEstimatedData();setTotalData();}/*** 表格数据*/public List<List<String>> getData() {List<List<String>> dataList = new ArrayList<>();// 项目数据dataList.addAll(projectDatas);// 植物数据dataList.addAll(estimatedDatas);// 总计数据dataList.addAll(totalDatas);return dataList;}/*** 单元格合并设置*/public List<Integer[]> getCellMergeConfig() {List<Integer[]> result = new ArrayList<>();Integer[] indexs = new Integer[4];// 项目部分for (int i = 0; i < projectDatas.size(); i++) {indexs = new Integer[4];// 下面两行代码其实说明，这组数据行是不需要合并的indexs[0] = i;indexs[1] = i;// 下面两行代码，说明从第二列开始，包含到第五列，合并单元格indexs[2] = 1;indexs[3] = 4;result.add(indexs);}// 合计indexs = new Integer[4];int num = projectDatas.size() + 1 + plantEstimated.getPlantMesses().size();indexs[0] = num;indexs[1] = num;indexs[2] = 0;indexs[3] = 2;result.add(indexs);return result;}/*** 项目数据*/private void setProjectData() {// 空列List<String> nullLine = new ArrayList<>();for (int i = 2; i < rowCount; i++) {nullLine.add(null);}// 第一行List<String> line1 = new ArrayList<>();line1.add("项目名称");line1.add(plantEstimated.getProjectName());line1.addAll(nullLine);// 第二行List<String> line2 = new ArrayList<>();line2.add("工程名称");line2.add(plantEstimated.getEngineeringName());line2.addAll(nullLine);// 第三行List<String> line3 = new ArrayList<>();line3.add("工程编号");line3.add(plantEstimated.getEngineeringNum());line3.addAll(nullLine);// 第四行List<String> line4 = new ArrayList<>();line4.add("工程面积");line4.add(plantEstimated.getEngineeringArea());line4.addAll(nullLine);projectDatas.add(line1);projectDatas.add(line2);projectDatas.add(line3);projectDatas.add(line4);}/*** 种植数据*/private void setEstimatedData() {// 标题estimatedDatas.add(Arrays.stream(titles).collect(Collectors.toList()));// 数据plantEstimated.getPlantMesses().forEach(plantMess -> {List<String> data = new ArrayList<>();data.add(plantMess.getPlantName());data.add(plantMess.getPlantType());data.add(plantMess.getPlantArea());data.add(plantMess.getEstimatedCount());data.add(plantMess.getActualCount());estimatedDatas.add(data);});}/*** 总计数据*/private void setTotalData() {List<String> data = new ArrayList<>();data.add("总计");data.add(null);// 空列data.add(null);// 空列data.add(plantEstimated.getPlantMesses().stream().collect(Collectors.summingInt(plantMess -> Integer.valueOf(plantMess.getEstimatedCount()))).toString());data.add(plantEstimated.getPlantMesses().stream().collect(Collectors.summingInt(plantMess -> Integer.valueOf(plantMess.getActualCount()))).toString());totalDatas.add(data);}}
3.业务代码
@Overridepublic void exportEstimatedData(HttpServletResponse response, PlantEstimated param) {JSONObject params = new JSONObject();params.put("engineeringName", param.getEngineeringName());params.put("engineeringNum", param.getEngineeringNum());params.put("projectName", param.getProjectName());// db数据List<PlantEstimated> plantEstimatedList = plantEstimatedMapper.exportEstimatedData(params);if (null == plantEstimatedList) {throw new IParkException("基础数据为空!");}// 表格复杂，使用手动创建、关闭ExcelWriter excelWriter = null;try {// 文件名String fileName = URLEncoder.encode("种植估算" + DateUtile.toymdhms() + ".xlsx", "UTF-8").replaceAll("\\+", "%20");response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName);// 这里URLEncoder.encode可以防止中文乱码excelWriter = EasyExcel.write(response.getOutputStream()).build();for (int i = 0; i < plantEstimatedList.size(); i++) {PlantEstimated plantEstimated = plantEstimatedList.get(i);WriteSheet sheet = EasyExcel.writerSheet(i, plantEstimated.getProjectName() + plantEstimated.getEngineeringNum()).needHead(Boolean.FALSE).build();//sheet名// 表格数初始化EstimatedExcel estimatedExcel = new EstimatedExcel(plantEstimated);// 表格样式List<WriteHandler> writeHandlerList = new ArrayList<>();estimatedExcel.getCellMergeConfig().forEach(v -> {// 起始行,结束行,起始列,结束列（行从1开始，列从0开始）writeHandlerList.add(new OnceAbsoluteMergeStrategy(v[0], v[1], v[2], v[3]));// 合并单元格});WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 头部策略目前用不到置空WriteCellStyle contentWriteCellStyle = new WriteCellStyle();contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 水平居中contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);contentWriteCellStyle.setBorderTop(BorderStyle.THIN);contentWriteCellStyle.setBorderRight(BorderStyle.THIN);contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);writeHandlerList.add(horizontalCellStyleStrategy);// 样式设置writeHandlerList.add(new LongestMatchColumnWidthStyleStrategy());// 表格设置-自动列宽sheet.setCustomWriteHandlerList(writeHandlerList);// 数据写入excelWriter.write(estimatedExcel.getData(), sheet);};} catch (Exception e) {e.printStackTrace();} finally {// 关闭流if (excelWriter != null) {excelWriter.finish();}}}
4.结果
达到了最初的效果
