Java POI Excel表格合并单元格和计算合计行 - 优化示例代码

您可以使用Apache POI来操作Excel表格。首先，将数据集合list按照需要合并的条件进行排序。然后，创建一个新的Excel工作簿和工作表，并设置列宽。接下来，使用循环遍历数据集合，比较相邻行的数据是否相同，如果相同则合并单元格。最后，在最后一行添加合计行，并计算名称相同数据合并后的数量。\n\n下面是一个示例代码，您可以根据自己的实际需求进行修改：\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.Collections;\nimport java.util.List;\n\npublic class ExcelMergeExample {\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", 20));\n dataList.add(new AmcOfficeinfosUtil("部门1", "申请人2", "名称2", "规格1", 30));\n dataList.add(new AmcOfficeinfosUtil("部门2", "申请人3", "名称3", "规格1", 40));\n\n // 按照部门、申请人、名称、规格排序\n Collections.sort(dataList);\n\n try (Workbook workbook = new XSSFWorkbook()) {\n Sheet sheet = workbook.createSheet("Sheet1");\n\n // 设置列宽度\n sheet.setColumnWidth(0, 3000);\n sheet.setColumnWidth(1, 4000);\n sheet.setColumnWidth(2, 5000);\n sheet.setColumnWidth(3, 4000);\n sheet.setColumnWidth(4, 3000);\n\n int rowIndex = 0;\n Row headerRow = sheet.createRow(rowIndex++);\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 String prevDept = "";\n String prevApplicant = "";\n String prevName = "";\n String prevSpec = "";\n int totalQuantity = 0;\n\n for (AmcOfficeinfosUtil data : dataList) {\n Row row = sheet.createRow(rowIndex++);\n row.createCell(0).setCellValue(data.getDept());\n row.createCell(1).setCellValue(data.getApplicant());\n row.createCell(2).setCellValue(data.getName());\n row.createCell(3).setCellValue(data.getSpec());\n row.createCell(4).setCellValue(data.getQuantity());\n\n // 合并数量相同的单元格\n if (data.getDept().equals(prevDept) && data.getApplicant().equals(prevApplicant)\n && data.getName().equals(prevName) && data.getSpec().equals(prevSpec)) {\n CellRangeAddress range = new CellRangeAddress(rowIndex - 2, rowIndex - 1, 4, 4);\n sheet.addMergedRegion(range);\n }\n\n // 计算合计\n if (data.getName().equals(prevName)) {\n totalQuantity += data.getQuantity();\n } else {\n if (!prevName.isEmpty()) {\n Row totalRow = sheet.createRow(rowIndex++);\n totalRow.createCell(0).setCellValue("合计");\n totalRow.createCell(4).setCellValue(totalQuantity);\n totalQuantity = data.getQuantity();\n }\n }\n\n prevDept = data.getDept();\n prevApplicant = data.getApplicant();\n prevName = data.getName();\n prevSpec = data.getSpec();\n }\n\n // 添加最后一个名称的合计行\n Row totalRow = sheet.createRow(rowIndex++);\n totalRow.createCell(0).setCellValue("合计");\n totalRow.createCell(4).setCellValue(totalQuantity);\n\n // 保存Excel文件\n try (FileOutputStream fileOut = new FileOutputStream("output.xlsx")) {\n workbook.write(fileOut);\n }\n } catch (IOException e) {\n e.printStackTrace();\n }\n }\n}\n\nclass AmcOfficeinfosUtil implements Comparable<AmcOfficeinfosUtil> {\n private String dept;\n private String applicant;\n private String name;\n private String spec;\n private int quantity;\n\n public AmcOfficeinfosUtil(String dept, String applicant, String name, String spec, int quantity) {\n this.dept = dept;\n this.applicant = applicant;\n this.name = name;\n this.spec = spec;\n this.quantity = quantity;\n }\n\n public String getDept() {\n return dept;\n }\n\n public String getApplicant() {\n return applicant;\n }\n\n public String getName() {\n return name;\n }\n\n public String getSpec() {\n return spec;\n }\n\n public int getQuantity() {\n return quantity;\n }\n\n @Override\n public int compareTo(AmcOfficeinfosUtil o) {\n int result = this.dept.compareTo(o.dept);\n if (result == 0) {\n result = this.applicant.compareTo(o.applicant);\n if (result == 0) {\n result = this.name.compareTo(o.name);\n if (result == 0) {\n result = this.spec.compareTo(o.spec);\n }\n }\n }\n return result;\n }\n}\n\n此代码会创建一个名为"output.xlsx"的Excel文件，根据给定的数据集合生成合并单元格和合计行的结果。请根据您的实际需求进行修改和优化。