Java POI Excel表格合并单元格并计算合计行 - 数量、名称、规格、部门、申请人

您可以使用Apache POI库来操作Excel表格。以下是一个示例代码，它根据数据集合"list"对数量、名称、规格、部门和申请人进行合并，并在最后添加合计行来计算名称相同的数量。\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.HashMap;\nimport java.util.List;\nimport java.util.Map;\n\npublic class ExcelMergeCellsExample {\n\n public static void main(String[] args) {\n List<AmcOfficeinfosUtil> dataList = getDataList(); // 获取数据集合\n\n try (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 int rowNum = 1;\n for (AmcOfficeinfosUtil data : dataList) {\n Row row = sheet.createRow(rowNum++);\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 // 合并单元格\n mergeCells(sheet, dataList);\n\n // 添加合计行\n addTotalRow(sheet);\n\n // 保存Excel文件\n try (FileOutputStream outputStream = new FileOutputStream("output.xlsx")) {\n workbook.write(outputStream);\n }\n } catch (IOException e) {\n e.printStackTrace();\n }\n }\n\n private static void mergeCells(Sheet sheet, List<AmcOfficeinfosUtil> dataList) {\n Map<String, CellRangeAddress> mergeMap = new HashMap<>();\n\n for (int i = 1; i <= dataList.size(); i++) {\n Row row = sheet.getRow(i);\n String name = row.getCell(1).getStringCellValue();\n String specification = row.getCell(2).getStringCellValue();\n String department = row.getCell(3).getStringCellValue();\n String applicant = row.getCell(4).getStringCellValue();\n\n String key = name + specification + department + applicant;\n\n if (mergeMap.containsKey(key)) {\n CellRangeAddress mergeRegion = mergeMap.get(key);\n mergeRegion.setLastRow(mergeRegion.getLastRow() + 1);\n } else {\n CellRangeAddress mergeRegion = new CellRangeAddress(i, i, 0, 4);\n mergeMap.put(key, mergeRegion);\n }\n }\n\n for (CellRangeAddress mergeRegion : mergeMap.values()) {\n sheet.addMergedRegionUnsafe(mergeRegion);\n }\n }\n\n private static void addTotalRow(Sheet sheet) {\n Map<String, Integer> totalMap = new HashMap<>();\n\n for (int i = 1; i <= sheet.getLastRowNum(); i++) {\n Row row = sheet.getRow(i);\n String name = row.getCell(1).getStringCellValue();\n int quantity = (int) row.getCell(0).getNumericCellValue();\n\n if (totalMap.containsKey(name)) {\n totalMap.put(name, totalMap.get(name) + quantity);\n } else {\n totalMap.put(name, quantity);\n }\n }\n\n Row totalRow = sheet.createRow(sheet.getLastRowNum() + 1);\n totalRow.createCell(0).setCellValue("合计");\n\n int totalColumn = 1;\n for (String name : totalMap.keySet()) {\n int quantity = totalMap.get(name);\n totalRow.createCell(totalColumn++).setCellValue(quantity);\n }\n }\n\n private static List<AmcOfficeinfosUtil> getDataList() {\n // 返回您的数据集合\n return null;\n }\n}\n\n\n请注意，您需要根据您的实际数据集合和对象类型（"AmcOfficeinfosUtil"）来实现"getDataList()"方法，以便获取数据并返回给"dataList"变量。还要根据实际情况调整单元格的索引和其他逻辑。