springboot+POI读取Excel表格(可以含有合并单元格)

本编文章主要写了如何通过POI获取Excel表格(含合并单元格)的数据，可以获取任意sheet表中的数据，然后返回list<String[]>形式的数据，根据自己的需求处理数据
需要有poi-4.0.0.jar和poi-ooxml-4.0.0.jar2个jar包

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class ExcelUtil {
/**
      *现在传的是MultipartFile ，可以自行修改成String path
      *  当是path 时 ，使用 Workbook wb = WorkbookFactory.create(new File(path));获取
      **/
    public static List<String[]> readExcel(MultipartFile excelInput, int startrow, int sheetnum) {

        Workbook workbook = null;
        InputStream inputStream = null;
        List<String[]> list = new ArrayList<String[]>();
        try {
            inputStream = excelInput.getInputStream();

            workbook = WorkbookFactory.create(inputStream);
            //当使用路径时使用 Workbook wb = WorkbookFactory.create(new File(path));
            int numberOfSheets = workbook.getNumberOfSheets();//sheet表
            if (numberOfSheets >= sheetnum) {
                Sheet sheet = workbook.getSheetAt(sheetnum);
                if (null != sheet) {
                    // 获得当前sheet的开始行
                    //int firstRowNum = sheet.getFirstRowNum();
                    // 获得当前sheet的结束行
                    int lastRowNum = sheet.getLastRowNum();

                    for (int rowNum = startrow; rowNum <= lastRowNum; rowNum++) {
                        // 获得当前行
                        Row row = sheet.getRow(rowNum);
                        if (row == null) {
                            continue;
                        }
                        // 获得当前行的开始列
                        // int firstCellNum = row.getFirstCellNum();
                        // 获得当前行的列数
                        int lastCellNum = row.getLastCellNum();

                        String[] cells = new String[lastCellNum];
                        // 循环当前行
                        for (Cell c : row) {//获取每列数据
                            int regions = sheet.getNumMergedRegions();//是否含有合并的单元格
                            if (regions > 0) {
                                boolean isMerge = isMergedRegion(sheet, rowNum, c.getColumnIndex());
                                // 判断是否具有合并单元格
                                if (isMerge) {
                                    String value = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
                                    cells[c.getColumnIndex()] = value;
                                } else {
                                    cells[c.getColumnIndex()] = getCellValue(c);
                                }
                            } else {
                                cells[c.getColumnIndex()] = getCellValue(c);
                            }
                        }
                        list.add(cells);

                    }
                }
            }

        } catch (Exception e1) {
            e1.printStackTrace();
        } finally {
            if (null != workbook) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (null != inputStream) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return list;
    }

    /**
     * 获取合并单元格的值
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        return null;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param row    行下标
     * @param column 列下标
     */
    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    //读取每个单元格的数据
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: // 数字 又分为纯数字和时间
                cellValue = stringDateProcess(cell);
                break;
            case STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "无";
                break;
        }
        return cellValue;
    }

    /**
     * 时间格式转换
     *
     * @param cell
     * @return
     */
    public static String stringDateProcess(Cell cell) {
        String result = "";
        if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
            SimpleDateFormat sdf = null;
            if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                sdf = new SimpleDateFormat("HH:mm ");
            } else {// 日期
                sdf = new SimpleDateFormat("yyyy-MM-dd");
            }
            Date date = cell.getDateCellValue();
            result = sdf.format(date);
        } else if (cell.getCellStyle().getDataFormat() == 58) {
            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            double value = cell.getNumericCellValue();
            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
            result = sdf.format(date);
        } else {
            double value = cell.getNumericCellValue();
            CellStyle style = cell.getCellStyle();
            DecimalFormat format = new DecimalFormat();
            String temp = style.getDataFormatString();
            // 单元格设置成常规
            if (temp.equals("General")) {
                format.applyPattern("#");
            }
            result = format.format(value);
        }
        return result;
    }
  }