Java POI Excel 表格合并单元格：根据数据集合合并名称、规格、部门、申请人并计算合计

{"title":"Java POI Excel 表格合并单元格：根据数据集合合并名称、规格、部门、申请人并计算合计", "description":"使用 Apache POI 库，根据数据集合 List 合并 Excel 表格中名称、规格、部门、申请人相同的单元格，并计算每个合并后的数据总数量，最后生成包含合计行的 Excel 文件。", "keywords":"Java, POI, Excel, 合并单元格, 合计行, 数据集合, List, AmcOfficeinfosUtil, 名称, 规格, 部门, 申请人", "content":"使用 Apache POI 库来实现根据数据集合合并单元格并计算合计行的功能。以下是一个示例代码：\n\njava\nimport org.apache.poi.ss.usermodel.*;\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 ExcelMergeCellsExample {\n public static void main(String[] args) {\n // 创建数据集合\n List<AmcOfficeinfosUtil> dataList = new ArrayList<>();\n dataList.add(new AmcOfficeinfosUtil("部门1", "申请人1", "名称1", "规格1", 10));\n dataList.add(new AmcOfficeinfosUtil("部门1", "申请人1", "名称1", "规格2", 5));\n dataList.add(new AmcOfficeinfosUtil("部门1", "申请人2", "名称2", "规格1", 8));\n dataList.add(new AmcOfficeinfosUtil("部门2", "申请人1", "名称1", "规格1", 12));\n\n // 创建Excel工作簿和工作表\n Workbook workbook = new XSSFWorkbook();\n Sheet sheet = workbook.createSheet("Data");\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 int rowIndex = 1;\n for (AmcOfficeinfosUtil data : dataList) {\n Row dataRow = sheet.createRow(rowIndex++);\n dataRow.createCell(0).setCellValue(data.get部门());\n dataRow.createCell(1).setCellValue(data.get申请人());\n dataRow.createCell(2).setCellValue(data.get名称());\n dataRow.createCell(3).setCellValue(data.get规格());\n dataRow.createCell(4).setCellValue(data.get数量());\n }\n\n // 合并单元格并计算合计行\n Map<String, Integer> nameCountMap = new HashMap<>();\n Map<String, Integer> specCountMap = new HashMap<>();\n Map<String, Integer> deptCountMap = new HashMap<>();\n Map<String, Integer> applicantCountMap = new HashMap<>();\n\n for (Row row : sheet) {\n if (row.getRowNum() == 0) {\n continue; // 跳过标题行\n }\n\n Cell nameCell = row.getCell(2);\n Cell specCell = row.getCell(3);\n Cell deptCell = row.getCell(0);\n Cell applicantCell = row.getCell(1);\n Cell countCell = row.getCell(4);\n\n String name = nameCell.getStringCellValue();\n String spec = specCell.getStringCellValue();\n String dept = deptCell.getStringCellValue();\n String applicant = applicantCell.getStringCellValue();\n int count = (int) countCell.getNumericCellValue();\n\n // 合并名称相同的单元格\n if (nameCountMap.containsKey(name)) {\n int mergedCount = nameCountMap.get(name) + count;\n nameCountMap.put(name, mergedCount);\n nameCell.setCellValue(""); // 清空名称单元格内容\n CellRangeAddress mergedRegion = new CellRangeAddress(row.getRowNum() - count, row.getRowNum(), 2, 2);\n sheet.addMergedRegion(mergedRegion);\n } else {\n nameCountMap.put(name, count);\n }\n\n // 合并规格相同的单元格\n if (specCountMap.containsKey(spec)) {\n int mergedCount = specCountMap.get(spec) + count;\n specCountMap.put(spec, mergedCount);\n specCell.setCellValue(""); // 清空规格单元格内容\n CellRangeAddress mergedRegion = new CellRangeAddress(row.getRowNum() - count, row.getRowNum(), 3, 3);\n sheet.addMergedRegion(mergedRegion);\n } else {\n specCountMap.put(spec, count);\n }\n\n // 合并部门相同的单元格\n if (deptCountMap.containsKey(dept)) {\n int mergedCount = deptCountMap.get(dept) + count;\n deptCountMap.put(dept, mergedCount);\n deptCell.setCellValue(""); // 清空部门单元格内容\n CellRangeAddress mergedRegion = new CellRangeAddress(row.getRowNum() - count, row.getRowNum(), 0, 0);\n sheet.addMergedRegion(mergedRegion);\n } else {\n deptCountMap.put(dept, count);\n }\n\n // 合并申请人相同的单元格\n if (applicantCountMap.containsKey(applicant)) {\n int mergedCount = applicantCountMap.get(applicant) + count;\n applicantCountMap.put(applicant, mergedCount);\n applicantCell.setCellValue(""); // 清空申请人单元格内容\n CellRangeAddress mergedRegion = new CellRangeAddress(row.getRowNum() - count, row.getRowNum(), 1, 1);\n sheet.addMergedRegion(mergedRegion);\n } else {\n applicantCountMap.put(applicant, count);\n }\n }\n\n // 创建合计行\n Row totalRow = sheet.createRow(sheet.getLastRowNum() + 1);\n totalRow.createCell(0).setCellValue("合计");\n totalRow.createCell(1).setCellValue("");\n totalRow.createCell(2).setCellValue("");\n totalRow.createCell(3).setCellValue("");\n totalRow.createCell(4).setCellValue("");\n\n for (Map.Entry<String, Integer> entry : nameCountMap.entrySet()) {\n String name = entry.getKey();\n int count = entry.getValue();\n\n // 在合计行中写入合并后的数量\n for (Cell cell : totalRow) {\n if (cell.getColumnIndex() == 2) {\n cell.setCellValue(name);\n } else if (cell.getColumnIndex() == 4) {\n cell.setCellValue(count);\n }\n }\n }\n\n // 调整列宽以适应内容\n for (int i = 0; i < headerRow.getLastCellNum(); i++) {\n sheet.autoSizeColumn(i);\n }\n\n // 保存Excel文件\n try (FileOutputStream fileOut = new FileOutputStream("output.xlsx")) {\n workbook.write(fileOut);\n } catch (IOException e) {\n e.printStackTrace();\n }\n\n // 关闭工作簿\n try {\n workbook.close();\n } catch (IOException e) {\n e.printStackTrace();\n }\n }\n}\n\nclass AmcOfficeinfosUtil {\n private String 部门;\n private String 申请人;\n private String 名称;\n private String 规格;\n private int 数量;\n\n public AmcOfficeinfosUtil(String 部门, String 申请人, String 名称, String 规格, int 数量) {\n this.部门 = 部门;\n this.申请人 = 申请人;\n this.名称 = 名称;\n this.规格 = 规格;\n this.数量 = 数量;\n }\n\n public String get部门() {\n return 部门;\n }\n\n public String get申请人() {\n return 申请人;\n }\n\n public String get名称() {\n return 名称;\n }\n\n public String get规格() {\n return 规格;\n }\n\n public int get数量() {\n return 数量;\n }\n}\n\n以上代码会生成一个名为output.xlsx的Excel文件，其中数据集合中相同的部门、申请人、名称和规格的单元格会被合并，并且在最后会有一个合计行显示合并后的数量。请根据实际需求进行调整。