导出exl表，合并单元格

@GetMapping("/export")
    @Operation(summary = "导出采购结果")
    @OperateLog(type = EXPORT)
    public void exportPurchaseResultList(@Validated PurchaseResultQry qry,
                                         HttpServletResponse response) throws IOException {
        LoginUser loginUser = SecurityFrameworkUtils.getLoginUser();
        if (loginUser == null) {
            throw new BizException("500", "用户登录已过期，请重新登录！");
        }
        //查询订单
        List<PurchaseResultCO> list = purchaseResultService.selectList(qry);
        // 需求表排序
        Collections.sort(list, new Comparator<PurchaseResultCO>() {
            @Override
            public int compare(PurchaseResultCO o1, PurchaseResultCO o2) {
                // 提取数字部分
                int week1 = Integer.parseInt(o1.getWeeklyPlan().replaceAll("[^0-9]", ""));
                int week2 = Integer.parseInt(o2.getWeeklyPlan().replaceAll("[^0-9]", ""));

                return Integer.compare(week1, week2);
            }
        });

        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Data");

            // 创建标题行
            Row headerRow = sheet.createRow(0);
            int colNum = 0;

            // 添加主对象字段标题
            // 定义表头
            String[] headers = {
                    "生产订单编号", "生产订单.料品.料号", "备料料号", "备料品名", "料品.规格", "实际需求量",
                    "库存量", "缺料量", "项目名称", "客户名称", "周计划说明或内容",
                    "采购订单号", "供应商名称", "送货数量", "创建人", "要求交货日期"};
            // 填充表头
            for (int i = 0; i < headers.length; i++) {
                headerRow.createCell(colNum++).setCellValue(headers[i]);
                // 设置列宽
                // 参数是列的索引和宽度（以 1/256 个字符宽度为单位）
                sheet.setColumnWidth(i, 256 * 15); // 15 个字符宽
            }

            // 创建日期格式
            CellStyle cellStyle = workbook.createCellStyle();
            CreationHelper creationHelper = workbook.getCreationHelper();
            cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd"));
            int rowNum = 1;
            List<PurchaseResultCO> purchaseResults = new ArrayList<>(list.size());
            // 遍历主对象集合
            for (PurchaseResultCO a : list) {
                for (PurchaseResultOrderCO b : a.getPurchaseResultOrderCOList()) {
                    String preparedMaterialNo = a.getPreparedMaterialNo();
                    String projectName = a.getProjectName();
                    Row row = sheet.createRow(rowNum++);
                    row.createCell(0).setCellValue(a.getProductionOrderNo());
                    row.createCell(1).setCellValue(a.getProductionMaterialNo());
                    row.createCell(2).setCellValue(preparedMaterialNo);
                    row.createCell(3).setCellValue(a.getPreparedMaterialName());
                    row.createCell(4).setCellValue(a.getMaterialSpecification());
                    row.createCell(5).setCellValue(a.getActualRequirementQuantity());
                    row.createCell(6).setCellValue(a.getInventoryQuantity());
                    row.createCell(7).setCellValue(a.getShortageQuantity());
                    row.createCell(8).setCellValue(projectName);
                    row.createCell(9).setCellValue(a.getCustomerName());
                    row.createCell(10).setCellValue(a.getWeeklyPlan());
                    row.createCell(11).setCellValue(b.getPurchaseOrderNumber());
                    row.createCell(12).setCellValue(b.getVendorName());
                    row.createCell(13).setCellValue(b.getQuantityOwed());
                    row.createCell(14).setCellValue(b.getCreatedBy());
                    // 设置日期值和格式
                    Cell deliveryDateCell = row.createCell(15);
                    if (b.getDeliveryDate() != null) {
                        deliveryDateCell.setCellValue(b.getDeliveryDate());
                        deliveryDateCell.setCellStyle(cellStyle);
                    }

                    PurchaseResultCO purchaseResult = new PurchaseResultCO();
                    purchaseResult.setId(a.getId());
                    purchaseResults.add(purchaseResult);
                }
            }

            // 合并单元格
            List<CellRangeAddress> mergedRegions = new ArrayList<>(purchaseResults.size());
            int row = 0; // 假设从列表的第一个元素开始
            while (row < purchaseResults.size()) {
                int startRow = row; // 记录当前合并的起始行
                Long id = purchaseResults.get(row).getId();

                // 找到相同 key 的连续行
                while (row + 1 < purchaseResults.size() && id == purchaseResults.get(row+ 1).getId()) {
                    row++;
                }

                if (row > startRow) { // 确保至少有两行需要合并
                    // 创建新的合并区域
                    CellRangeAddress newRegionA = new CellRangeAddress(startRow + 1, row + 1, 5, 5); // 假设行号从 1 开始
                    CellRangeAddress newRegionB = new CellRangeAddress(startRow + 1, row + 1, 7, 7);

                    CellRangeAddress newRegionC = new CellRangeAddress(startRow + 1, row + 1, 1, 1);
                    CellRangeAddress newRegionD = new CellRangeAddress(startRow + 1, row + 1, 6, 6);
                    CellRangeAddress newRegionE = new CellRangeAddress(startRow + 1, row + 1, 3, 3);
                    CellRangeAddress newRegionF = new CellRangeAddress(startRow + 1, row + 1, 4, 4);
                    CellRangeAddress newRegionG = new CellRangeAddress(startRow + 1, row + 1, 0, 0);
                 // 检查并确保合并区域不重叠
/*               boolean overlap = false;
                 for (CellRangeAddress existingRegion : mergedRegions) {
                     if (existingRegion.intersects(newRegionA) || existingRegion.intersects(newRegionB)|| existingRegion.intersects(newRegionC)|| existingRegion.intersects(newRegionD)|| existingRegion.intersects(newRegionE)) {
                         overlap = true;
                         break;
                     }
                 }*/

                    //if (!overlap) {
                    mergedRegions.add(newRegionA);
                    mergedRegions.add(newRegionB);
                    mergedRegions.add(newRegionC);
                    mergedRegions.add(newRegionD);
                    mergedRegions.add(newRegionE);
                    mergedRegions.add(newRegionF);
                    mergedRegions.add(newRegionG);
                    //}
                }
                row++; // 移动到下一个未处理的行
            }

            // 添加所有合并区域
            for (CellRangeAddress region : mergedRegions) {
                sheet.addMergedRegion(region);
            }

            // 设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment; filename=\"data.xlsx\"");

            // 写入文件
            // 创建日期格式
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");
            String timestamp = dateFormat.format(new Date());

            // 构建文件名
            String fileName = "采购目标_" + timestamp + ".xlsx";
            try (FileOutputStream outputStream = new FileOutputStream(fileName)) {
                workbook.write(response.getOutputStream());
                outputStream.close();
                workbook.close();
            }
            System.out.println("Excel file has been generated successfully.");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }