java-poi进行单元格合并

主要实现效果

• 图片上传失败 后期更新吧

主要思路

• 先写入数据、后进行单元格合并操作

实现代码

• 数据写入部分正常操作、就不贴了
• 将数据全部写入完成后 计算单元格合并位置（注意合并时会默认保留第一行/列的值）
• 主要实现代码

sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
firstRow：开始行
lastRow：结束行
firstCol：开始列
lastCol：结束列

• 主要代码

   /**
   * 合并单元格 （重点代码）
   *
   * @param sheet
   * @param caseList
   */
  private void cellMerge(XSSFSheet sheet, List<CaseItem> caseList) {
    // 缓存合并开始位置
    int startIndex = 0;
    // 数据绘画
    for (int i = 0; i < caseList.size(); i++) {
      int startRow = i + 2;
      // 数据
      CaseItem caseItem = caseList.get(i);
      // 需要合并的单元格个数
      int cellNum = caseItem.getSignerList().size();
      int startRowIndex = startRow + startIndex;
      int endRowIndex = startRow + startIndex + cellNum - 1;
      for (int j = 0; j < this.getHeaderArr().length - 1; j++) {
        if (cellNum > 1) {
          sheet.addMergedRegion(new CellRangeAddress(startRowIndex, endRowIndex, j, j));
        }
      }
      if (cellNum > 1) {
        startIndex += (cellNum - 1);
      }
    }
  }

 /**
   * 表头内容绘画
   *
   * @param sheet
   * @param cellStyle
   */
  private void excelHeader(XSSFSheet sheet, XSSFCellStyle cellStyle) {
    // 需单元格合并表头
    XSSFRow row = sheet.createRow(0);
    String[] headerArr = this.getHeaderArr();
    for (int i = 0; i < headerArr.length; i++) {
      String[] ranges = headerArr[i].split(",");
      Integer firstRow = Integer.valueOf(ranges[0]);
      Integer lastRow = Integer.valueOf(ranges[1]);
      Integer firstCol = Integer.valueOf(ranges[2]);
      Integer lastCol = Integer.valueOf(ranges[3]);
      String title = ranges[4];
      Integer colWidth = Integer.valueOf(ranges[5]);
      // 设置表头列宽
      sheet.setColumnWidth(i, colWidth * 256);
      sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
      // 绘画G-N表头单元格表框
      if (i == 6) {
        for (int j = 0; j < this.subjectHeaderArr().length; j++) {
          XSSFCell cell = row.createCell(j + 6);
          cell.setCellStyle(cellStyle);
        }
      }
      XSSFCell cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue(title);
    }
    // 子表头
    XSSFRow row1 = sheet.createRow(1);
    String[] subjectHeaderArr = this.subjectHeaderArr();
    for (int i = 0; i < subjectHeaderArr.length; i++) {
      String[] subHeaders = subjectHeaderArr[i].split(",");
      String title = subHeaders[0];
      Integer colWidth = Integer.valueOf(subHeaders[1]);
      // 设置子表头列宽
      sheet.setColumnWidth(i + 6, colWidth * 256);
      if (i < 6) {
        XSSFCell cellRange = row1.createCell(i);
        cellRange.setCellStyle(cellStyle);
      }
      XSSFCell cell = row1.createCell(i + 6);
      cell.setCellStyle(cellStyle);
      cell.setCellValue(title);
    }
  }
  
  /**
   * 设置表头表格样式
   *
   * @param workbook
   * @return
   */
  private XSSFCellStyle excelHeaderStyle(XSSFWorkbook workbook) {
    // 表格样式
    XSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    return cellStyle;
  }

  /**
   * 表格数据样式设置
   *
   * @param book
   * @return
   */
  private XSSFCellStyle excelBodyStyle(XSSFWorkbook book) {
    // 表格样式
    XSSFCellStyle cellBodyStyle = book.createCellStyle();
    cellBodyStyle.setAlignment(HorizontalAlignment.CENTER);
    cellBodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellBodyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellBodyStyle.setAlignment(HorizontalAlignment.CENTER);
    cellBodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellBodyStyle.setBorderBottom(BorderStyle.THIN);
    cellBodyStyle.setBorderLeft(BorderStyle.THIN);
    cellBodyStyle.setBorderTop(BorderStyle.THIN);
    cellBodyStyle.setBorderRight(BorderStyle.THIN);
    cellBodyStyle.setWrapText(true);
    return cellBodyStyle;
  }

  /**
   * 表头字体样式
   *
   * @param workbook
   * @return
   */
  private XSSFFont excelHeaderFont(XSSFWorkbook workbook) {
    XSSFFont font = workbook.createFont();
    font.setBold(true);
    return font;
  }
  
 /**
   * 前四位控制单元格合并，第五位表头列名，第六位列宽（X个字符）
   * @return
   */
  private String[] getHeaderArr() {
    return new String [] {
        "0,1,0,0,标题1,15",
        "0,1,1,1,标题2,35",
        "0,1,2,2,标题3,30",
        "0,0,6,13,标题4,0"
    };
  }

  /**
   * 表头及列宽设置
   *
   * @return
   */
  private String[] subjectHeaderArr() {
    return new String [] {
        "标题4-子标题1,15",
        "标题4-子标题2,15",
        "标题4-子标题3,20"
    };
  }