SpringBoot整合poi解析excel（参考上个导入的配置）

controller

 @RequestMapping(value = "/require/insertRequireAndServiceMore")
    public JsonBean insertRequireAndServiceMore(@RequestParam("file") MultipartFile file) throws IOException, IllegalAccessException, InstantiationException {
        JsonBean jsonBean = new JsonBean();
        try {
            jsonBean = mspRequireInfoSV.insertRequireAndServiceMore(file);
        }catch (IOException e){
            jsonBean.fail("需求和服务绑定失败");
        }
        return jsonBean;
    }

service

 public JsonBean insertRequireAndServiceMore(MultipartFile file) throws IOException, InstantiationException, IllegalAccessException {
        JsonBean jsonBean = new JsonBean();
        String fileName = file.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            jsonBean.fail("上传文件格式不正确");
            return jsonBean;
        }
        Map resultMap = new ConcurrentHashMap<String,String>();
        // 获取当前用户
        String userName = SecurityUtils.getSubject().getPrincipal().toString();
        try {
            // 生成字节流
            InputStream is = file.getInputStream();
            List<MspRequireServiceInfoBean> mspRequireServiceInfoBeansList = new ArrayList<>();
            HashSet<String> deduplicationDmp = new HashSet<>();
            //这里得到解析后的数据，key是sheet页，list里面的String是一行的数据
            Map<String, List<String[]>> map = ExcelUtils.analysisExcel(is);
            is.close();
            // 一个sheet页的数据
            List<String[]> strings1 = map.get("0");
            for (int i = 1; i < strings1.size(); i++) {
            这里就不贴代码了，数据解析为一个map就可以根据逻辑来插表了
            //这里如果有映射可以用一下，但也可以直接用类来接收。
             MspRequireServiceInfoBean mspRequireServiceInfoBean = new Gson().fromJson(new JSONObject(resultMap).toJSONString(), MspRequireServiceInfoBean.class);
                

analysisExcel解析类

package com.ai.service.management.utils;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;


public class ExcelUtils {
    public final static String DATE_OUTPUT_PATTERNS = "yyyy-MM-dd HH:mm:ss";
    public final static SimpleDateFormat simpleDateFormat = new SimpleDateFormat(
            DATE_OUTPUT_PATTERNS);

    public static Map<String,ArrayList<ArrayList<String>>> readExcel(InputStream inputStream) {
        Map<String,ArrayList<ArrayList<String>>> map = new HashMap<String, ArrayList<ArrayList<String>>>();

        try {
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
            Workbook workBook = null;
           try {
               workBook = new XSSFWorkbook(inputStream);
           }catch (Exception e){
               workBook  = new HSSFWorkbook(poifsFileSystem);
           }

            for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
                ArrayList<ArrayList<String>> Row = new ArrayList<ArrayList<String>>();
                Sheet sheet = workBook.getSheetAt(numSheet);
                if (sheet == null) {
                    continue;
                }
                // 循环行Row
                for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }

                    // 循环列Cell
                    ArrayList<String> arrCell = new ArrayList<String>();
                    for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                        Cell cell = row.getCell(cellNum);
                        if (cell == null) {
                            cell = row.createCell(cellNum);
                            cell.setCellValue("");
                        }
                        arrCell.add(getValue1(cell));
                    }
                    Row.add(arrCell);
                }
                map.put(String.valueOf(numSheet), Row);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }

    @SuppressWarnings("static-access")
    private static String getValue(Cell cell) {
        if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        } else {
            return String.valueOf(cell.getStringCellValue());
        }
    }

    private static String getValue1(Cell cell) {
        String ret = "";
        int cellType = cell.getCellType();
        switch (cellType) {
            case 3:
                ret = "";
                break;
            case 4:
                ret = String.valueOf(cell.getBooleanCellValue());
                break;
            case 5:
                ret = null;
                break;
            case 2:
                Workbook wb = cell.getSheet().getWorkbook();
                CreationHelper crateHelper = wb.getCreationHelper();
                FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
                ret = getValue1(evaluator.evaluateInCell(cell));
                break;
            case 0:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date theDate = cell.getDateCellValue();
                    ret = simpleDateFormat.format(theDate);
                } else {
                    ret = NumberToTextConverter.toText(cell.getNumericCellValue());
                }
                break;
            case 1:
                ret = cell.getRichStringCellValue().getString();
                break;
            default:
                ret = "";
        }

        return ret; // 有必要自行trim
    }
    /**
     * 返回map
     * key:sheet页签
     * value:List里面的String[]为一行的数据
     * @param
     * @return
     */
    public static Map<String,List<String[]>> analysisExcel(InputStream inputStream){
        Map<String,ArrayList<ArrayList<String>>> sheet = readExcel(inputStream);
        Map<String,List<String[]>> map = new HashMap<String, List<String[]>>();
        for (int i=0;i<sheet.size();i++) {
            ArrayList<ArrayList<String>> row = sheet.get(String.valueOf(i));
            List<String[]> list = new ArrayList<String[]>();
            if(row.size()>0){
                for(int j=0;j<row.size();j++){
                    ArrayList<String> cell = row.get(j);
                    String[] c = new String[cell.size()];
                    for(int k=0;k<cell.size();k++){
                        c[k] = cell.get(k);
                    }
                    list.add(c);
                }
            }
            map.put(String.valueOf(i), list);
        }
        return map;
    }

    /**
     * 返回某一列的数据
     *
     */
    public static List<String> analysisExcelGetRow(InputStream inputStream,int index){
        Map<String,ArrayList<ArrayList<String>>> sheet = readExcel(inputStream);
        List list = new ArrayList();
        for (int i=0;i<sheet.size();i++) {
            ArrayList<ArrayList<String>> row = sheet.get(String.valueOf(i));
            if(row.size()>0){
                //跳过第一行表头
                for(int j=1;j<row.size();j++){
                    ArrayList<String> cell = row.get(j);
                    if(cell.get(index)!=null||cell.get(index)!=""){
                        list.add(cell.get(index));
                    }

                }
            }
        }
        return list;

    }
}