Java POI Excel 表格合并单元格：根据数量、名称、规格、部门和申请人进行合并，并添加合计行

您可以使用 Apache POI 库来处理 Excel 表格。以下是一个示例代码，它可以根据数据集合中的条件，合并相同数据的单元格，并在最后添加合计行来计算数量：\n\njava\nimport org.apache.poi.ss.usermodel.*;\nimport org.apache.poi.ss.util.CellRangeAddress;\nimport org.apache.poi.xssf.usermodel.XSSFWorkbook;\n\nimport java.io.FileOutputStream;\nimport java.io.IOException;\nimport java.util.ArrayList;\nimport java.util.HashMap;\nimport java.util.List;\nimport java.util.Map;\n\npublic class ExcelMergeCells {\n public static void main(String[] args) {\n List<AmcOfficeinfosUtil> dataList = getDataList(); // 获取数据集合\n\n Workbook workbook = new XSSFWorkbook();\n Sheet sheet = workbook.createSheet("Sheet1");\n\n // 创建表头\n Row headerRow = sheet.createRow(0);\n headerRow.createCell(0).setCellValue("数量");\n headerRow.createCell(1).setCellValue("名称");\n headerRow.createCell(2).setCellValue("规格");\n headerRow.createCell(3).setCellValue("部门");\n headerRow.createCell(4).setCellValue("申请人");\n\n // 设置单元格样式\n CellStyle cellStyle = workbook.createCellStyle();\n cellStyle.setAlignment(HorizontalAlignment.CENTER);\n cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);\n\n // 合并单元格\n Map<String, CellRangeAddress> mergeMap = new HashMap<>();\n int rowIndex = 1;\n for (AmcOfficeinfosUtil data : dataList) {\n Row row = sheet.createRow(rowIndex);\n row.createCell(0).setCellValue(data.getQuantity());\n row.createCell(1).setCellValue(data.getName());\n row.createCell(2).setCellValue(data.getSpecification());\n row.createCell(3).setCellValue(data.getDepartment());\n row.createCell(4).setCellValue(data.getApplicant());\n\n // 合并数量相同的单元格\n String quantityKey = data.getQuantity() + "_" + rowIndex;\n CellRangeAddress quantityRange = mergeMap.get(quantityKey);\n if (quantityRange == null) {\n quantityRange = new CellRangeAddress(rowIndex, rowIndex, 0, 0);\n mergeMap.put(quantityKey, quantityRange);\n } else {\n quantityRange.setLastRow(rowIndex);\n }\n\n // 合并名称相同的单元格\n String nameKey = data.getName() + "_" + rowIndex;\n CellRangeAddress nameRange = mergeMap.get(nameKey);\n if (nameRange == null) {\n nameRange = new CellRangeAddress(rowIndex, rowIndex, 1, 1);\n mergeMap.put(nameKey, nameRange);\n } else {\n nameRange.setLastRow(rowIndex);\n }\n\n // 合并规格相同的单元格\n String specKey = data.getSpecification() + "_" + rowIndex;\n CellRangeAddress specRange = mergeMap.get(specKey);\n if (specRange == null) {\n specRange = new CellRangeAddress(rowIndex, rowIndex, 2, 2);\n mergeMap.put(specKey, specRange);\n } else {\n specRange.setLastRow(rowIndex);\n }\n\n // 合并部门相同的单元格\n String deptKey = data.getDepartment() + "_" + rowIndex;\n CellRangeAddress deptRange = mergeMap.get(deptKey);\n if (deptRange == null) {\n deptRange = new CellRangeAddress(rowIndex, rowIndex, 3, 3);\n mergeMap.put(deptKey, deptRange);\n } else {\n deptRange.setLastRow(rowIndex);\n }\n\n // 合并申请人相同的单元格\n String applicantKey = data.getApplicant() + "_" + rowIndex;\n CellRangeAddress applicantRange = mergeMap.get(applicantKey);\n if (applicantRange == null) {\n applicantRange = new CellRangeAddress(rowIndex, rowIndex, 4, 4);\n mergeMap.put(applicantKey, applicantRange);\n } else {\n applicantRange.setLastRow(rowIndex);\n }\n\n rowIndex++;\n }\n\n // 设置合并单元格样式\n for (CellRangeAddress mergeRange : mergeMap.values()) {\n sheet.addMergedRegion(mergeRange);\n for (int i = mergeRange.getFirstColumn(); i <= mergeRange.getLastColumn(); i++) {\n for (int j = mergeRange.getFirstRow(); j <= mergeRange.getLastRow(); j++) {\n Row row = sheet.getRow(j);\n Cell cell = row.getCell(i);\n cell.setCellStyle(cellStyle);\n }\n }\n }\n\n // 添加合计行\n int totalRow = rowIndex;\n Row totalRowObj = sheet.createRow(totalRow);\n totalRowObj.createCell(0).setCellValue("合计");\n totalRowObj.createCell(1).setCellValue("名称相同数据合并后的数量");\n totalRowObj.createCell(2).setCellValue("");\n totalRowObj.createCell(3).setCellValue("");\n totalRowObj.createCell(4).setCellValue("");\n\n CellRangeAddress totalRange = new CellRangeAddress(totalRow, totalRow, 0, 1);\n sheet.addMergedRegion(totalRange);\n for (int i = totalRange.getFirstColumn(); i <= totalRange.getLastColumn(); i++) {\n for (int j = totalRange.getFirstRow(); j <= totalRange.getLastRow(); j++) {\n Row row = sheet.getRow(j);\n Cell cell = row.getCell(i);\n cell.setCellStyle(cellStyle);\n }\n }\n\n // 计算名称相同数据合并后的数量\n for (int i = 1; i < totalRow; i++) {\n Row row = sheet.getRow(i);\n String name = row.getCell(1).getStringCellValue();\n double quantity = row.getCell(0).getNumericCellValue();\n\n for (int j = i + 1; j < totalRow; j++) {\n Row compareRow = sheet.getRow(j);\n String compareName = compareRow.getCell(1).getStringCellValue();\n\n if (name.equals(compareName)) {\n quantity += compareRow.getCell(0).getNumericCellValue();\n compareRow.getCell(0).setCellValue(0); // 清空被合并的行的数量\n }\n }\n\n row.getCell(0).setCellValue(quantity); // 更新合并后的数量\n }\n\n // 输出到文件\n try (FileOutputStream fileOut = new FileOutputStream("output.xlsx")) {\n workbook.write(fileOut);\n } catch (IOException e) {\n e.printStackTrace();\n }\n }\n\n // 模拟数据集合\n private static List<AmcOfficeinfosUtil> getDataList() {\n List<AmcOfficeinfosUtil> dataList = new ArrayList<>();\n dataList.add(new AmcOfficeinfosUtil(2, "名称1", "规格1", "部门1", "申请人1"));\n dataList.add(new AmcOfficeinfosUtil(3, "名称2", "规格1", "部门1", "申请人2"));\n dataList.add(new AmcOfficeinfosUtil(4, "名称1", "规格2", "部门1", "申请人1"));\n dataList.add(new AmcOfficeinfosUtil(5, "名称1", "规格1", "部门2", "申请人1"));\n dataList.add(new AmcOfficeinfosUtil(6, "名称3", "规格1", "部门1", "申请人3"));\n dataList.add(new AmcOfficeinfosUtil(7, "名称3", "规格1", "部门1", "申请人4"));\n return dataList;\n }\n\n // 模拟数据类\n private static class AmcOfficeinfosUtil {\n private int quantity;\n private String name;\n private String specification;\n private String department;\n private String applicant;\n\n public AmcOfficeinfosUtil(int quantity, String name, String specification, String department, String applicant) {\n this.quantity = quantity;\n this.name = name;\n this.specification = specification;\n this.department = department;\n this.applicant = applicant;\n }\n\n public int getQuantity() {\n return quantity;\n }\n\n public String getName() {\n return name;\n }\n\n public String getSpecification() {\n return specification;\n }\n\n public String getDepartment() {\n return department;\n }\n\n public String getApplicant() {\n return applicant;\n }\n }\n}\n\n\n这段代码将根据数据集合中的数量、名称、规格、部门和申请人进行单元格合并，并在最后添加合计行以计算名称相同数据合并后的数量。请注意，您需要根据实际情况调整代码中的数据类和获取数据集合的方法。最后，代码将生成一个名为"output.xlsx"的 Excel 文件，其中包含合并后的数据。