【Java】Apache POI 解析带有合并单元格的Excel

树状结构表格读取，按每一行获取其信息。需要读取的表格如下：

依赖引入

    <!-- 针对2007以上版本的库 -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.2</version>
    </dependency>

    <!-- 针对2003版本的库 -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-scratchpad</artifactId>
      <version>4.1.2</version>
    </dependency>

ReadMergeCellExcelUtil 工具类

import com.riching.cloud.common.exception.BadRequestException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbookFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * 解析带有合并单元格的Excel工具类
 */
public class ReadMergeCellExcelUtil {

  /**
   * Excel读取成对象
   *
   * @param file
   * @return
   */
  public static List<Map<String, String>> readExcelToObj(MultipartFile file) {
    if (file == null) {
      throw new BadRequestException("导入文件不能为空");
    }
    try {
      InputStream inputStream = file.getInputStream();
      Workbook workbook = XSSFWorkbookFactory.create(inputStream);
      return readExcel(workbook, 0, 0, 0);

    } catch (IOException e) {
      throw new BadRequestException("导入文件失败");
    }
  }

  /**
   * 读取Excel内容
   *
   * @param workbook      文件
   * @param sheetIndex    第几个sheet
   * @param startReadLine 开始行
   * @param tailLine      结束行
   * @return
   */
  private static List<Map<String, String>> readExcel(Workbook workbook, int sheetIndex, int startReadLine, int tailLine) {
    Sheet sheet = workbook.getSheetAt(sheetIndex);
    FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
    List<Map<String, String>> result = new ArrayList<>();
    for (int i = startReadLine; i <= sheet.getLastRowNum() - tailLine; i++) {
      Row row = sheet.getRow(i);
      Map<String, String> rowData = parseRow(sheet, row, formulaEvaluator);
      if (rowData != null) {
        result.add(rowData);
      }
    }
    return result;
  }

  /**
   * 解析Excel行
   *
   * @param sheet
   * @param row
   * @param formulaEvaluator
   * @return
   */
  private static Map<String, String> parseRow(Sheet sheet, Row row, FormulaEvaluator formulaEvaluator) {
    if (sheet == null || row == null || formulaEvaluator == null) {
      return null;
    }
    Map<String, String> rowData = new TreeMap<>(Comparator.naturalOrder());
    rowData.put("rowIndex", row.getRowNum() + "");

    int cellIndex = 0;
    for (Cell cell : row) {
      String cellValue = getCellValue(sheet, cell, formulaEvaluator);
      rowData.put("field" + cellIndex, cellValue);
      cellIndex++;
    }
    return rowData;
  }

  /**
   * 获取单元格值
   *
   * @param sheet
   * @param cell
   * @param formulaEvaluator
   * @return
   */
  private static String getCellValue(Sheet sheet, Cell cell, FormulaEvaluator formulaEvaluator) {
    if (sheet == null || cell == null || formulaEvaluator == null) {
      return "";
    }
    if (isMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex())) {
      // 获取合并单元格的值
      return getMergedRegionValue(sheet, cell.getRowIndex(), cell.getColumnIndex(), formulaEvaluator);
    } else {
      // 获取普通单元格的值
      return getCellStringValue(cell, formulaEvaluator);
    }
  }

  /**
   * 判断单元格是不是合并单元格
   *
   * @param sheet
   * @param row
   * @param column
   * @return
   */
  private static boolean isMergedRegion(Sheet sheet, int row, int column) {
    if (sheet != null) {
      for (CellRangeAddress range : sheet.getMergedRegions()) {
        if (range.isInRange(row, column)) {
          return true;
        }
      }
    }
    return false;
  }

  /**
   * 获取合并单元格值
   *
   * @param sheet
   * @param row
   * @param column
   * @param formulaEvaluator
   * @return
   */
  private static String getMergedRegionValue(Sheet sheet, int row, int column, FormulaEvaluator formulaEvaluator) {
    if (sheet == null || formulaEvaluator == null) {
      return "";
    }
    for (CellRangeAddress range : sheet.getMergedRegions()) {
      if (range.isInRange(row, column)) {
        Row firstRow = sheet.getRow(range.getFirstRow());
        Cell firstCell = firstRow.getCell(range.getFirstColumn());
        return getCellStringValue(firstCell, formulaEvaluator);
      }
    }
    return "";
  }

  /**
   * 获取单元格信息
   *
   * @param cell
   * @param formulaEvaluator
   * @return
   */
  private static String getCellStringValue(Cell cell, FormulaEvaluator formulaEvaluator) {
    if (cell == null || formulaEvaluator == null) {
      return "";
    }
    switch (cell.getCellType()) {
      case STRING:
        return cell.getStringCellValue();
      case BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
      case FORMULA:
        try {
          return cell.getCellFormula();
        } catch (Exception var2) {
          return String.valueOf(cell.getNumericCellValue());
        }
        // return formulaEvaluator.evaluate(cell).formatAsString();
      case NUMERIC:
        return String.valueOf(cell.getNumericCellValue());
      default:
        return "";
    }
  }

}

本博客内容根据个人理解所写，有什么错误和误解希望能反映给博主，感谢。