记录一下导出excel合并单元格

/**
 * 导出机票订单数据
 *
 * @param flightOrder 查询条件
 */
@GetMapping("/export-order")
@ApiOperationSupport(order = 5)
@ApiOperation(value = "导出机票数据", notes = "传入flightOrder")
public void exportFlightOrder(FlightOrder flightOrder, HttpServletResponse response) {
      List<FlightOrderExcel> orderExcelList = flightOrderService.exportFlightOrder(flightOrder);
try {
      response.setContentType("application/vnd.ms-excel");
      response.setCharacterEncoding("utf-8");
      String encodedFileName = URLEncoder.encode("机票订单数据" + DateUtil.time() + ".xlsx", "UTF-8");
      response.setHeader("Content-disposition", "attachment;filename=" + encodedFileName);
      //response.setHeader("Content-disposition", "attachment;filename=机票订单数据" + DateUtil.time() + ".xlsx");
      Workbook workbook = new XSSFWorkbook();
      Sheet sheet = workbook.createSheet("机票订单");

      String[] headers = {"订单号", "乘客序号", "乘客订单号", "第三方机票订单号", "ota订单id", "购票时间", "国家/地区",
         "邮箱", "PNR", "票号", "乘机人姓名", "乘机人证件号", "出生日期", "年龄", "国籍", "航段(1)", "航段(2)",
         "航段(3)", "航段(4)", "航班(1)", "航班(2)", "航班(3)", "航班(4)", "出发日期(1)", "出发日期(2)",
         "出发日期(3)", "出发日期(4)", "舱位(1)", "舱位(2)", "舱位(3)", "舱位(4)", "燃油税", "税项",
         "优惠总额", "基础运价", "合计", "同订单支付总额", "支付方式", "币种", "支付流水号", "支付时间"};

      // 创建标题行样式
      CellStyle headerStyle = workbook.createCellStyle();
      Font headerFont = workbook.createFont();
      headerStyle.setAlignment(HorizontalAlignment.CENTER);
      headerFont.setBold(true); // 设置字体加粗
      headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中对齐
      headerFont.setColor(IndexedColors.BLACK.getIndex()); // 设置字体颜色为黑色
      headerStyle.setFont(headerFont);
      headerFont.setFontHeightInPoints((short) 13); // 设置字体大小为12
      headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); // 设置底色为灰色
      headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

      // 设置行高
      Row headerRow = sheet.createRow(0);
      headerRow.setHeightInPoints(22); // 设置行高为40个点，稍微高于数据行高度

      // 创建标题行并应用样式
      for (int i = 0; i < headers.length; i++) {
         Cell cell = headerRow.createCell(i);
         cell.setCellValue(headers[i]);
         cell.setCellStyle(headerStyle); // 应用样式
      }

      // 自动调整列宽
      for (int i = 0; i < headers.length; i++) {
         sheet.setColumnWidth(i, (20 * 256)); // 设置列宽为 20 个字符
      }

      int rowIndex = 1; // 数据行起始索引，从1开始，因为包括表头
      String previousOrderId = ""; // 上一个订单号
      int mergeStartIndex = 0; // 合并单元格的起始行索引
      int mergeEndIndex = 0; // 合并单元格的结束行索引
      for (FlightOrderExcel orderExcel : orderExcelList) {
         Row dataRow = sheet.createRow(rowIndex);

         // 填充字段数据
         dataRow.createCell(0).setCellValue(getValueOrDefault(orderExcel.getOrderId()));
         dataRow.createCell(1).setCellValue(getValueOrDefault(orderExcel.getPassengerIdX()));
         dataRow.createCell(2).setCellValue(getValueOrDefault(orderExcel.getPassengerOrderID()));
         dataRow.createCell(3).setCellValue(getValueOrDefault(orderExcel.getThirdPartyTicketOrderNumber()));
         dataRow.createCell(4).setCellValue(getValueOrDefault(orderExcel.getOtaOrderId()));
         dataRow.createCell(5).setCellValue(getValueOrDefault(orderExcel.getBookingTime()));
         dataRow.createCell(6).setCellValue(getValueOrDefault(orderExcel.getContactCountry()));
         dataRow.createCell(7).setCellValue(getValueOrDefault(orderExcel.getEmail()));
         dataRow.createCell(8).setCellValue(getValueOrDefault(orderExcel.getPnr()));
         dataRow.createCell(9).setCellValue(getValueOrDefault(orderExcel.getTicketNumber()));
         dataRow.createCell(10).setCellValue(getValueOrDefault(orderExcel.getName()));
         dataRow.createCell(11).setCellValue(getValueOrDefault(orderExcel.getIdNo()));
         dataRow.createCell(12).setCellValue(getValueOrDefault(orderExcel.getBirthday()));
         dataRow.createCell(13).setCellValue(getValueOrDefault(orderExcel.getAge()));
         dataRow.createCell(14).setCellValue(getValueOrDefault(orderExcel.getNation()));
         dataRow.createCell(15).setCellValue(getValueOrDefault(orderExcel.getSegListOne()));
         dataRow.createCell(16).setCellValue(getValueOrDefault(orderExcel.getSegListTwo()));
         dataRow.createCell(17).setCellValue(getValueOrDefault(orderExcel.getSegListThree()));
         dataRow.createCell(18).setCellValue(getValueOrDefault(orderExcel.getSegListFour()));
         dataRow.createCell(19).setCellValue(getValueOrDefault(orderExcel.getFlightNo()));
         dataRow.createCell(20).setCellValue(getValueOrDefault(orderExcel.getFlightNoTwo()));
         dataRow.createCell(21).setCellValue(getValueOrDefault(orderExcel.getFlightNoThree()));
         dataRow.createCell(22).setCellValue(getValueOrDefault(orderExcel.getFlightNoFour()));
         dataRow.createCell(23).setCellValue(getValueOrDefault(orderExcel.getFlightDate()));
         dataRow.createCell(24).setCellValue(getValueOrDefault(orderExcel.getFlightDateTwo()));
         dataRow.createCell(25).setCellValue(getValueOrDefault(orderExcel.getFlightDateThree()));
         dataRow.createCell(26).setCellValue(getValueOrDefault(orderExcel.getFlightDateFour()));
         dataRow.createCell(27).setCellValue(getValueOrDefault(orderExcel.getCabin()));
         dataRow.createCell(28).setCellValue(getValueOrDefault(orderExcel.getCabinTwo()));
         dataRow.createCell(29).setCellValue(getValueOrDefault(orderExcel.getCabinThree()));
         dataRow.createCell(30).setCellValue(getValueOrDefault(orderExcel.getCabinFour()));
         dataRow.createCell(31).setCellValue(getValueOrDefault(orderExcel.getYr()));
         dataRow.createCell(32).setCellValue(getValueOrDefault(orderExcel.getOtherTax()));
         dataRow.createCell(33).setCellValue(getValueOrDefault(orderExcel.getPromotionFee()));
         dataRow.createCell(34).setCellValue(getValueOrDefault(orderExcel.getNetFare()));
         dataRow.createCell(35).setCellValue(getValueOrDefault(orderExcel.getTotalFares()));
         dataRow.createCell(36).setCellValue(getValueOrDefault(orderExcel.getPayPrice()));
         dataRow.createCell(37).setCellValue(getValueOrDefault(orderExcel.getPayType()));
         dataRow.createCell(38).setCellValue(getValueOrDefault(orderExcel.getCurrency()));
         dataRow.createCell(39).setCellValue(getValueOrDefault(orderExcel.getBillNO()));
         dataRow.createCell(40).setCellValue(getValueOrDefault(orderExcel.getPayTime()));
         // 设置数据单元格样式并居中对齐
         CellStyle dataCellStyle = workbook.createCellStyle();
         dataCellStyle.setAlignment(HorizontalAlignment.CENTER);
         dataCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中对齐
         for (int i = 0; i < headers.length; i++) {
            Cell cell = dataRow.getCell(i);
            if (cell != null) {
               cell.setCellStyle(dataCellStyle);
            }
         }
         // 合并orderId相同的payPrice和promotionFee字段的单元格
         if (orderExcel.getOrderId().equals(previousOrderId)) {
            mergeEndIndex = rowIndex;
         } else {
            // 合并payPrice字段的单元格
            if (mergeStartIndex != mergeEndIndex) {
               CellRangeAddress mergeRegion1 = new CellRangeAddress(mergeStartIndex, mergeEndIndex, 36, 36);
               if (!isMergedRegionExist(sheet, mergeRegion1)) {
                  sheet.addMergedRegionUnsafe(mergeRegion1);
               }

               CellRangeAddress mergeRegion2 = new CellRangeAddress(mergeStartIndex, mergeEndIndex, 33, 33);
               if (!isMergedRegionExist(sheet, mergeRegion2)) {
                  sheet.addMergedRegionUnsafe(mergeRegion2);
               }
            }
            mergeStartIndex = rowIndex;
            mergeEndIndex = rowIndex;
         }

         previousOrderId = orderExcel.getOrderId();
         rowIndex++;
      }

      // 合并最后一组orderId相同的payPrice和promotionFee字段的单元格
      if (mergeStartIndex != mergeEndIndex) {
         CellRangeAddress mergeRegion1 = new CellRangeAddress(mergeStartIndex, mergeEndIndex, 36, 36);
         if (!isMergedRegionExist(sheet, mergeRegion1)) {
            sheet.addMergedRegionUnsafe(mergeRegion1);
         }

         CellRangeAddress mergeRegion2 = new CellRangeAddress(mergeStartIndex, mergeEndIndex, 33, 33);
         if (!isMergedRegionExist(sheet, mergeRegion2)) {
            sheet.addMergedRegionUnsafe(mergeRegion2);
         }
      }
      // 输出到响应流
      workbook.write(response.getOutputStream());
      workbook.close();
   } catch (IOException e) {
      // 处理异常
      e.printStackTrace();
   }
}
// 辅助方法：获取字段值或默认值
private String getValueOrDefault(String value) {
   return value != null ? value : "";
}

private Integer getValueOrDefault(Integer value) {
   return value != null ? value : 0;
}
private boolean isMergedRegionExist(Sheet sheet, CellRangeAddress region) {
   int numMergedRegions = sheet.getNumMergedRegions();
   for (int i = 0; i < numMergedRegions; i++) {
      CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
      if (mergedRegion.getFirstRow() == region.getFirstRow()
         && mergedRegion.getLastRow() == region.getLastRow()
         && mergedRegion.getFirstColumn() == region.getFirstColumn()
         && mergedRegion.getLastColumn() == region.getLastColumn()) {
         return true;
      }
   }
   return false;
}