poi读取excel工具类

1、添加依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>23.0</version>
        </dependency>

2、工具类代码

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;
//参数excel文件流，sheet的index，标题第几行也就是生成的map->key建议写死，
//开始行数，读取结束行数。
public class ExcelUtil {
    public static List<Map<String, Object>> readeExcelData(InputStream excelInputSteam,
                                                           int sheetNumber,
                                                           int headerNumber,
                                                           int rowStart, Integer rowEnd) throws IOException, InvalidFormatException {
//        System.out.println("请确保传入参数所有的下标都是行-1的index");
        List<Map<String, Object>> result = new ArrayList<>();
        List<String> headers = new ArrayList<>();
        Workbook workbook = WorkbookFactory.create(excelInputSteam);
        //XSSFWorkbook workbook = new XSSFWorkbook(excelInputSteam);
        Sheet sheet = workbook.getSheetAt(sheetNumber);
        Row header = sheet.getRow(headerNumber);
        //最后一行数据
//        System.out.println("整个sheet(index="+sheetNumber+")最后一行index="+sheet.getLastRowNum());
        if(rowEnd==null){
            rowEnd=sheet.getLastRowNum();
            System.out.println("传入结束行号为空，则读取开始行后的所有行");
        }
//        System.out.println("实际目标读取最后一行index="+rowEnd);
        DataFormatter dataFormatter = new DataFormatter();
        //获取标题信息
        for (int i = 0; i < header.getLastCellNum(); i++) {
            Cell cell = header.getCell(i);
            headers.add(dataFormatter.formatCellValue(cell));
        }
        System.out.println("head size="+headers.size());
        //获取内容信息
        for (int i = rowStart; i <= rowEnd; i++) {
            Row currentRow = sheet.getRow(i);
            if (Objects.isNull(currentRow)) {
                continue;
            }
            Map<String, Object> dataMap = new HashMap<>();
//            for (int j = 0; j < currentRow.getLastCellNum(); j++) {
            for (int j = 0; j < header.getLastCellNum(); j++) {
                //将null转化为Blank
                Cell data = currentRow.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                if (Objects.isNull(data)) {     //感觉这个if有点多余
                    dataMap.put(headers.get(j), null);
                } else {
                    dataMap.put(headers.get(j).replaceAll("\n", ""), getCellFormatValue(data));
                }
            }
            result.add(dataMap);
        }
        excelInputSteam.close();
        return result;
    }
    public static String getCellFormatValue(Cell cell){
        String cellValue = "";
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellTypeEnum()){
                case NUMERIC:{
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case ERROR:
                    cellValue="";
                    break; // 错误类型
                case _NONE:
                    cellValue="";
                    break;
                case BLANK:
                    cellValue="";
                    break;
                case FORMULA:{
                    try {
                        BigDecimal decimal=new BigDecimal(cell.getNumericCellValue());
                        cellValue = String.valueOf(decimal.setScale(4,BigDecimal.ROUND_HALF_UP));
                    } catch (IllegalStateException e) {
                        try {
                            cellValue = String.valueOf(cell.getRichStringCellValue());
                        }catch (IllegalStateException e1) {
                            cellValue="";
                        }
                    }
                    break;
                }
                case STRING:{
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        }else{
            cellValue = "";
        }
        return cellValue.trim();
    }
}