java动态生成excel并且需要合并单元格

java动态生成excel并且需要合并单元格

先上图看一下预期效果

集成poi

<dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.0.0</version>
</dependency>
<dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>4.0.0</version>
</dependency>
<dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>4.0.0</version>
</dependency>
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.1.2</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>ooxml-schemas</artifactId><version>1.4</version>
</dependency>

通过poi手动制作excel

Workbook workbook = exportMoreSheetByTemplate(request, overviewId);
if (Func.isEmpty(workbook)) {return R.fail("生成清册报告失败");
}
Sheet sheet = workbook.createSheet();
//sheet.setRandomAccessWindowSize(SpreadsheetVersion.EXCEL2007.getMaxRows());
workbook.setSheetName(2, "表3排放量汇总");
sheet.setDefaultRowHeight((short) 380);
//sheet.trackAllColumnsForAutoSizing();
// 手动创建sheet页相关内容
//样式
Font font = workbook.createFont();
font.setBold(true);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//创建第一行
createRow1(workbook, sheet, cellStyle);
// 保存年限 10年 企业名称    测试企业    盘查时间	填表日期	2023-07-06 09:56:28
createRow2(request, workbook, sheet, cellStyle);
createRow3(workbook, sheet, cellStyle);
createMergeRow(workbook, sheet, cellStyle);List<ConfigSourceDetailDTO> excelInfos = accountingConfigMapper.getExcelInfosByOverviewId(overviewId);
for (ConfigSourceDetailDTO excelInfo : excelInfos) {excelInfo.setGroupKey(excelInfo.getGroupKey(excelInfo.getNameDisplay(), excelInfo.getSourceName(), excelInfo.getFormula()));
}
List<ExcelDataDTO> excelDataDtoS = new ArrayList<>();
Map<String, List<ConfigSourceDetailDTO>> excelInfoMap = excelInfos.stream().collect(Collectors.groupingBy(ConfigSourceDetailDTO::getGroupKey));
Set<Map.Entry<String, List<ConfigSourceDetailDTO>>> excelInfoEntrySets = excelInfoMap.entrySet();for (Map.Entry<String, List<ConfigSourceDetailDTO>> excelInfoEntrySet : excelInfoEntrySets) {String key = excelInfoEntrySet.getKey();List<String> keyList = Splitter.on(",").splitToList(key);List<ConfigSourceDetailDTO> excelInfoEntrySetValues = excelInfoEntrySet.getValue();ExcelDataDTO excelDataDTO = new ExcelDataDTO();excelDataDTO.setNameDisplay(keyList.get(0));excelDataDTO.setSourceName(keyList.get(1));excelDataDTO.setFormula(keyList.get(2));List<String> emissionCodes = new ArrayList<>();List<String> emissionNames = new ArrayList<>();List<String> emissionCodeTypes = new ArrayList<>();List<String> emissionUnits = new ArrayList<>();List<String> dataSources = new ArrayList<>();List<String> datas = new ArrayList<>();List<String> factoryDatas = new ArrayList<>();// todo 风险因子数据取值for (ConfigSourceDetailDTO excelInfoEntrySetValue : excelInfoEntrySetValues) {var emissionCode = excelInfoEntrySetValue.getEmissionCode();var obtainingMethod = excelInfoEntrySetValue.getObtainingMethod();var sourceId = excelInfoEntrySetValue.getSourceId();if ("计算值".equals(obtainingMethod)) {//SELECT * FROM g_Inventory_details d WHERE d.is_deleted =0 AND d.obtaining_method = '计算值' and source_id = 1681199756105396225;List<InventoryDetails> inventoryDetails = inventoryDetailsMapper.selectList(new LambdaQueryWrapper<>(InventoryDetails.class).eq(InventoryDetails::getIsDeleted, 0).eq(InventoryDetails::getObtainingMethod, obtainingMethod).eq(InventoryDetails::getSourceId, sourceId));for (InventoryDetails inventoryDetail : inventoryDetails) {// 计算年月或者季度总和datas.add(Func.toStr(BigDecimalUtils.add(Func.isEmpty(inventoryDetail.getJanuary()) ? new BigDecimal(0) : new BigDecimal(inventoryDetail.getJanuary()), Func.isEmpty(inventoryDetail.getFebruary()) ? new BigDecimal(0) : new BigDecimal(inventoryDetail.getFebruary())).add(Func.isEmpty(inventoryDetail.getMarch()) ? new BigDecimal(0) : new BigDecimal(inventoryDetail.getMarch())).add(Func.isEmpty(inventoryDetail.getApril()) ? new BigDecimal(0) : new BigDecimal(inventoryDetail.getApril())).add(Func.isEmpty(inventoryDetail.getMay()) ? new BigDecimal(0) : new BigDecimal(inventoryDetail.getMay())).add(Func.isEmpty(inventoryDetail.getJune()) ? new BigDecimal(0) : new BigDecimal(inventoryDetail.getJune())).add(Func.isEmpty(inventoryDetail.getJuly()) ? new BigDecimal(0) : new BigDecimal(inventoryDetail.getJuly())).add(Func.isEmpty(inventoryDetail.getAugust()) ? new BigDecimal(0) : new BigDecimal(inventoryDetail.getAugust())).add(Func.isEmpty(inventoryDetail.getSeptember()) ? new BigDecimal(0) : new BigDecimal(inventoryDetail.getSeptember())).add(Func.isEmpty(inventoryDetail.getOctober()) ? new BigDecimal(0) : new BigDecimal(inventoryDetail.getOctober())).add(Func.isEmpty(inventoryDetail.getNovember()) ? new BigDecimal(0) : new BigDecimal(inventoryDetail.getNovember())).add(Func.isEmpty(inventoryDetail.getDecember()) ? new BigDecimal(0) : new BigDecimal(inventoryDetail.getDecember()))));}}var formula = excelDataDTO.getFormula();if (Func.isNotEmpty(formula)) {String[] splits = formula.split("=");if (Func.isNotEmpty(emissionCode)) {if (emissionCode.equals(splits[0].replace(" ", ""))) {continue;}}}emissionCodes.add(emissionCode);emissionNames.add(excelInfoEntrySetValue.getEmissionName());emissionCodeTypes.add(excelInfoEntrySetValue.getEmissionCodeType());emissionUnits.add(excelInfoEntrySetValue.getEmissionUnit());dataSources.add(excelInfoEntrySetValue.getDataSource());var factorId = excelInfoEntrySetValue.getFactorId();// 获取公式对应的数据，通过查询风险因子表的JSON字段getFactorDatas(factoryDatas, emissionCode, sourceId, factorId, overviewId);}excelDataDTO.setEmissionCode(emissionCodes);excelDataDTO.setEmissionName(emissionNames);excelDataDTO.setDataSource(dataSources);excelDataDTO.setData(factoryDatas);excelDataDTO.setEmissionUnit(emissionUnits);excelDataDTO.setEmissionCodeType(emissionCodeTypes);excelDataDTO.setTotalData(datas.stream().reduce((d, d1) -> Func.toStr(BigDecimalUtils.add(new BigDecimal(d), new BigDecimal(d)))).orElse("0"));excelDataDtoS.add(excelDataDTO);
}InventoryOverview queryEntity = new InventoryOverview();
queryEntity.setEnterpriseId(Func.toStr(request.getBusinessParam().get("unitId")));
String[] reportYears = Func.toStr(request.getBusinessParam().get("unitReportYear")).split("年");
if (Func.isNotEmpty(reportYears[0])) {queryEntity.setTimeInventory(reportYears[0]);if (reportYears.length > 1) {queryEntity.setTimeUnit(reportYears[1]);}
}
InventoryOverview detail = null;
try {detail = inventoryOverviewMapper.selectById(overviewId);
} catch (Exception e) {log.error("根据inventoryOverviewMapper.selectById查询信息失败" + e);
}
log.info("需要动态生成 excel 的组合对象是:{},传入的参数是:{}", JSONObject.toJSONString(excelDataDtoS), overviewId);
// 动态生成sheet
int tempRow = daynamicCreateRow(workbook, sheet, excelDataDtoS);
Row row7 = this.buildRow(sheet, tempRow, workbook, 12);
CellRangeAddress region7 = new CellRangeAddress(tempRow, tempRow, 0, 9);
sheet.addMergedRegion(region7);
row7.getCell(0).setCellValue("总排放量");
Row row8 = this.buildRow(sheet, tempRow + 1, workbook, 12);
CellRangeAddress region8 = new CellRangeAddress(tempRow + 1, tempRow + 1, 0, 9);
sheet.addMergedRegion(region8);
row8.getCell(0).setCellValue("直接排放量");
CellRangeAddress region9 = new CellRangeAddress(tempRow + 2, tempRow + 2, 0, 9);
sheet.addMergedRegion(region9);
Row row9 = this.buildRow(sheet, tempRow + 2, workbook, 12);
row9.getCell(0).setCellValue("间接排放量");
if (Func.isNotEmpty(detail)) {row7.getCell(10).setCellValue(Func.toStr(detail.getInventoryTotal()));row7.getCell(11).setCellValue(Func.toStr(detail.getInventoryTotal()));row8.getCell(10).setCellValue(Func.toStr(detail.getInventoryFirst()));row8.getCell(11).setCellValue(Func.toStr(detail.getInventoryFirst()));row9.getCell(10).setCellValue(Func.toStr(detail.getInventorySecond()));row9.getCell(11).setCellValue(Func.toStr(detail.getInventorySecond()));
} else {row7.getCell(10).setCellValue(0);row7.getCell(11).setCellValue(0);row8.getCell(10).setCellValue(0);row8.getCell(11).setCellValue(0);row9.getCell(10).setCellValue(0);row9.getCell(11).setCellValue(0);
}
//将sheet写入文件
//将文件保存到指定的位置
try (FileOutputStream outputStream = new FileOutputStream(file)) {workbook.write(outputStream);
}
//上传文件
MultipartFile multipartFile = FileUtil.fileToMultipartFile(file);
GoldNetFileVO goldNetFileVO = fileService.uploadFile(multipartFile);
file.delete();
if (Objects.nonNull(goldNetFileVO)) {log.info("生产文件ID:{}，toLink:{}", goldNetFileVO.getFileId(), goldNetFileVO.getFileLink());return R.data(goldNetFileVO.getFileId());
}

/*** 4和5合并单元格** @param workbook* @param sheet* @param cellStyle*/
private void createMergeRow(Workbook workbook, Sheet sheet, CellStyle cellStyle) {Row row4 = this.buildRow(sheet, 3, workbook, 12);Row row5 = this.buildRow(sheet, 4, workbook, 12);CellRangeAddress region4_1 = new CellRangeAddress(3, 4, 0, 0);sheet.addMergedRegion(region4_1);CellRangeAddress region4_2 = new CellRangeAddress(3, 4, 1, 1);sheet.addMergedRegion(region4_2);CellRangeAddress region4_3 = new CellRangeAddress(3, 4, 2, 2);sheet.addMergedRegion(region4_3);// 编号	对应活动/设施	排放源row4.getCell(0).setCellValue("编号");row4.getCell(0).setCellStyle(cellStyle);row4.getCell(1).setCellValue("对应活动/设施");row4.getCell(1).setCellStyle(cellStyle);row4.getCell(2).setCellValue("排放源");row4.getCell(2).setCellStyle(cellStyle);CellRangeAddress region4_4 = new CellRangeAddress(3, 3, 3, 11);sheet.addMergedRegion(region4_4);row4.getCell(3).setCellValue("CO2");row4.getCell(3).setCellStyle(cellStyle);// 计算公式	参数	类别	名称	数值	单位	来源	年排放量	年CO2当量row5.getCell(3).setCellValue("计算公式");row5.getCell(3).setCellStyle(cellStyle);row5.getCell(4).setCellValue("参数");row5.getCell(4).setCellStyle(cellStyle);row5.getCell(5).setCellValue("类别");row5.getCell(5).setCellStyle(cellStyle);row5.getCell(6).setCellValue("名称");row5.getCell(6).setCellStyle(cellStyle);row5.getCell(7).setCellValue("数值");row5.getCell(7).setCellStyle(cellStyle);row5.getCell(8).setCellValue("单位");row5.getCell(8).setCellStyle(cellStyle);row5.getCell(9).setCellValue("来源");row5.getCell(9).setCellStyle(cellStyle);row5.getCell(10).setCellValue("年排放量");row5.getCell(10).setCellStyle(cellStyle);row5.getCell(11).setCellValue("年CO2当量");row5.getCell(11).setCellStyle(cellStyle);
}/*** 创建第三行** @param workbook* @param sheet* @param cellStyle*/
private void createRow3(Workbook workbook, Sheet sheet, CellStyle cellStyle) {Row row3 = this.buildRow(sheet, 2, workbook, 12);CellRangeAddress region3_1 = new CellRangeAddress(2, 2, 0, 2);sheet.addMergedRegion(region3_1);row3.getCell(0).setCellValue("基本数据");row3.getCell(0).setCellStyle(cellStyle);
}/*** 创建第二行** @param request* @param workbook* @param sheet* @param cellStyle*/
private void createRow2(CreateStatementRequest request, Workbook workbook, Sheet sheet, CellStyle cellStyle) {Row row2 = this.buildRow(sheet, 1, workbook, 12);CellRangeAddress region2_1 = new CellRangeAddress(1, 1, 0, 1);sheet.addMergedRegion(region2_1);CellRangeAddress region2_2 = new CellRangeAddress(1, 1, 4, 5);sheet.addMergedRegion(region2_2);CellRangeAddress region2_3 = new CellRangeAddress(1, 1, 6, 7);sheet.addMergedRegion(region2_3);CellRangeAddress region2_4 = new CellRangeAddress(1, 1, 9, 10);sheet.addMergedRegion(region2_4);row2.getCell(0).setCellValue("保存年限");row2.getCell(0).setCellStyle(cellStyle);row2.getCell(2).setCellValue("10年");row2.getCell(2).setCellStyle(cellStyle);row2.getCell(3).setCellValue("企业名称");row2.getCell(3).setCellStyle(cellStyle);row2.getCell(4).setCellValue(Func.toStr(request.getBusinessParam().get("unitName")));row2.getCell(4).setCellStyle(cellStyle);row2.getCell(6).setCellValue("盘查时间");row2.getCell(6).setCellStyle(cellStyle);row2.getCell(8).setCellValue(Func.toStr(request.getBusinessParam().get("timeInventoryText")));row2.getCell(8).setCellStyle(cellStyle);row2.getCell(9).setCellValue("填表日期");row2.getCell(9).setCellStyle(cellStyle);row2.getCell(11).setCellValue(DateUtil.format(new Date(), DateUtil.PATTERN_DATETIME));row2.getCell(11).setCellStyle(cellStyle);
}/*** 动态生成row和col** @param workbook* @param sheet* @param excelDataDTOS*/
private int daynamicCreateRow(Workbook workbook, Sheet sheet, List<ExcelDataDTO> excelDataDTOS) {int temp = 4;// 代表从第几行开始合并int rowTemp = 5;// tempNum 代表序号int tempNum = 1;for (int i = 0; i < excelDataDTOS.size(); i++) {ExcelDataDTO excelDataDTO = excelDataDTOS.get(i);List<String> emissionCodes = excelDataDTO.getEmissionCode();List<String> emissionCodeTypes = excelDataDTO.getEmissionCodeType();List<String> emissionNames = excelDataDTO.getEmissionName();List<String> dataList = excelDataDTO.getData();List<String> emissionUnits = excelDataDTO.getEmissionUnit();List<String> dataSources = excelDataDTO.getDataSource();for (int j = 0; j < emissionCodes.size(); j++) {Row row6 = null;// temp + j + 1 代表从第几行开始创建rowrow6 = this.buildRow(sheet, temp + j + 1, workbook, 12);row6.getCell(0).setCellValue(tempNum + j);row6.getCell(1).setCellValue(excelDataDTO.getNameDisplay());row6.getCell(2).setCellValue(excelDataDTO.getSourceName());String formula = excelDataDTO.getFormula();if (Func.isNotEmpty(formula)) {formula = ExpressParseUtil.parseInLatexExpression(formula);formula = formula.replaceAll("\\{/}", "/");}row6.getCell(3).setCellValue(formula);row6.getCell(4).setCellValue(emissionCodes.get(j));row6.getCell(5).setCellValue(emissionCodeTypes.get(j));row6.getCell(6).setCellValue(emissionNames.get(j));// 这个地方放入 g_emission_factor 的 json 中 的data_content 中的 dataif (j < dataList.size()) {row6.getCell(7).setCellValue(dataList.get(j));} else {row6.getCell(7).setCellValue("0");}row6.getCell(8).setCellValue(emissionUnits.get(j));row6.getCell(9).setCellValue(dataSources.get(j));row6.getCell(10).setCellValue(excelDataDTO.getTotalData());row6.getCell(11).setCellValue(excelDataDTO.getTotalData());}tempNum += 1;temp += emissionCodes.size();if (emissionCodes.size() > 1) {CellRangeAddress region0 = new CellRangeAddress(rowTemp, rowTemp + emissionCodes.size() - 1, 0, 0);CellRangeAddress region1 = new CellRangeAddress(rowTemp, rowTemp + emissionCodes.size() - 1, 1, 1);CellRangeAddress region2 = new CellRangeAddress(rowTemp, rowTemp + emissionCodes.size() - 1, 2, 2);CellRangeAddress region3 = new CellRangeAddress(rowTemp, rowTemp + emissionCodes.size() - 1, 3, 3);CellRangeAddress region10 = new CellRangeAddress(rowTemp, rowTemp + emissionCodes.size() - 1, 10, 10);CellRangeAddress region11 = new CellRangeAddress(rowTemp, rowTemp + emissionCodes.size() - 1, 11, 11);sheet.addMergedRegion(region0);sheet.addMergedRegion(region1);sheet.addMergedRegion(region2);sheet.addMergedRegion(region3);sheet.addMergedRegion(region10);sheet.addMergedRegion(region11);}rowTemp += emissionCodes.size();}return rowTemp;
}/*** 创建第一行** @param workbook* @param sheet* @param cellStyle*/
private void createRow1(Workbook workbook, Sheet sheet, CellStyle cellStyle) {Row row1 = this.buildRow(sheet, 0, workbook, 12);CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, 11);sheet.addMergedRegion(region1);row1.getCell(0).setCellStyle(cellStyle);row1.getCell(0).setCellValue("排放量汇总");
}// 创建行
private Row buildRow(Sheet sheet, int row, Workbook workbook, int rowLength) {CellStyle cellStyle = workbook.createCellStyle();cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);Row row4 = sheet.createRow(row);for (int i = 0; i < rowLength; i++) {row4.createCell(i).setCellStyle(cellStyle);sheet.setColumnWidth(i, 12 * 256);}return row4;
}// 根据模版生成sheet
public Workbook exportMoreSheetByTemplate(CreateStatementRequest request, Long overviewId) throws IOException {// 用这个map填充数据 sheet1 sheet2 sheet3 都会使用这个map进行传递Map<String, Object> map = doEnterpriseInfo(request);List<InventoryReportDTO> inventoryReportDTOList = new ArrayList<>(doInventoryInfo(overviewId));map.put("inventoryReports", inventoryReportDTOList);// 设置导出配置// 获取导出excel指定模版String fileCode = "inventoryTemplateDownload";var vo = new ConfTemplateFileVO();vo.setTenantCode("000000");vo.setTemplateCode(fileCode);var confTemplateFileRet = confTemplateFileClient.getConfTemplateFile(vo);String filePath = "";if (confTemplateFileRet.isSuccess()) {filePath = confTemplateFileRet.getData().getTemplateFileLink();} else {throw new RuntimeException("查询清册模版信息失败");}TemplateExportParams templatePath = new TemplateExportParams(filePath, true);log.info("templatePath" + templatePath + ",map:" + JSONObject.toJSONString(map));// 导出exceltry {return ExcelExportUtil.exportExcel(templatePath, map);} catch (Exception e) {log.error("ExcelExportUtil.exportExcel", e);}return null;
}