Excel读取工具类PoiExcelUtil

1. maven关联maven依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>

2. excel工具类

package com.lq;

import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.NumberFormat;
import java.util.*;

public class PoiExcelUtil {

    public static Map<String,List<List<String>>> readExcel(File file) throws IOException {
        Map<String,List<List<String>>> sheetRowValList = new HashMap<>();
        Workbook workbook =  workbook(file);
        XSSFFormulaEvaluator formulaEvaluator = PoiExcelUtil.formulaEvaluator(workbook);
        for (int x = 0; x < workbook.getNumberOfSheets(); x++) {
            Sheet sheet = workbook.getSheetAt(x);
            sheetRowValList.put(sheet.getSheetName(),realSheetVal(sheet,formulaEvaluator));
        }
        workbook.close();
        return sheetRowValList;
    }

    public static List<List<String>> readExcelSheet(File file,String sheetName) throws IOException {
        Workbook workbook =  workbook(file);
        XSSFFormulaEvaluator formulaEvaluator = PoiExcelUtil.formulaEvaluator(workbook);
        Sheet sheet = workbook.getSheet(sheetName);
        List<List<String>> lists = realSheetVal(sheet, formulaEvaluator);
        workbook.close();
        return lists;
    }

    private static List<List<String>> realSheetVal(Sheet sheet,XSSFFormulaEvaluator formulaEvaluator){
        List<List<String>> rowValList = new ArrayList<>();
        List<MergedRegionValue> mergedRegionValues = getMergedRegionValues(sheet, formulaEvaluator);
        int rows = sheet.getPhysicalNumberOfRows();
        for (int r = 0; r < rows; r++) {
            List<String> cellValList = new ArrayList<>();
            Row row = sheet.getRow(r);
            for (int c = 0; c < row.getPhysicalNumberOfCells(); c++) {
                String cellValue = stringCellValue(row.getCell(c), formulaEvaluator);
                if (cellValue.length()==0){
                    cellValue = getMergedRegionValue(r, c, mergedRegionValues);
                }
                cellValList.add(cellValue);
            }
            rowValList.add(cellValList);
        }
        return rowValList;
    }

    public static String stringCellValue(Cell cell, FormulaEvaluator formulaEvaluator) {
        if (cell==null){
            return "";
        }
        String regEx = "[\n\t]";
        switch (cell.getCellType().name()) {
            case "STRING":
                return cell.getStringCellValue().replaceAll(regEx, "").trim();
            case "NUMERIC":
                NumberFormat numberFormat = NumberFormat.getInstance();
                numberFormat.setGroupingUsed(false);
                return numberFormat.format(cell.getNumericCellValue()).replaceAll(regEx, "").trim();
            case "BOOLEAN":
                return String.valueOf(cell.getBooleanCellValue()).replaceAll(regEx, "").trim();
            case "FORMULA":
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                return evaluate.formatAsString().replaceAll(regEx, "").trim();
            default:
                return "";
        }
    }

    public static Workbook workbook(File file) throws IOException {
        FileInputStream fileInputStream = new FileInputStream(file);
        return Objects.requireNonNull(file.getName()).endsWith(".xlsx") ? new XSSFWorkbook(fileInputStream) :
                new HSSFWorkbook(fileInputStream);
    }

    public static XSSFFormulaEvaluator formulaEvaluator(Workbook workbook){
        return new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
    }

    /**
     * 获取合并单元格的值
     * @param sheet
     * @param formulaEvaluator
     * @return
     */
    public static List<MergedRegionValue> getMergedRegionValues(Sheet sheet, FormulaEvaluator formulaEvaluator) {
        List<MergedRegionValue> mergedRegionValues = new ArrayList<>();
        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();
            Row fRow = sheet.getRow(firstRow);
            Cell fCell = fRow.getCell(firstColumn);
            String value = stringCellValue(fCell, formulaEvaluator);
            mergedRegionValues.add(new MergedRegionValue(firstColumn,lastColumn,firstRow,lastRow,value));
        }
        return mergedRegionValues;
    }

    /**
     * 获取合并单元格的值
     * @param row
     * @param column
     * @param mergedRegionValues
     * @return
     */
    public static String getMergedRegionValue(int row,int column,List<MergedRegionValue> mergedRegionValues){
        for (MergedRegionValue item : mergedRegionValues) {
            if(row >= item.getFirstRow() && row <= item.getLastRow()&&
                    column >= item.getFirstColumn() && column <= item.getLastColumn()){
                 return item.getValue();
            }
        }
        return "";
    }

    @Data
    @AllArgsConstructor
    public static class MergedRegionValue{
        private int firstColumn;
        private int lastColumn;
        private int firstRow;
        private int lastRow;
        private String value;
    }
}