POI解析excel

package com.meritdata.cloud.multianalysis.utils;

import freemarker.template.utility.NullArgumentException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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

/**

• @Author sunyt

• @Date 2022/6/6 15:22

• @Description
  */
  @Slf4j
  public class ExcelUtil {

  private ExcelUtil() {
  }

  /**

  • 根据excel的版本，解析数据

  • @param

  • @return
    */
    public static List<Map<String, Object>> getWorkbook(InputStream fis, String fileName) throws IOException {
    Workbook workbook = null;

    // xlsx是高版本的Excel文件
    if (fileName.endsWith(“.xlsx”)) {
    workbook = new XSSFWorkbook(fis);
    }
    if (fileName.endsWith(“xls”)) {
    workbook = new HSSFWorkbook(fis);
    }
    if (workbook == null) {
    throw new NullArgumentException();
    }
    if (fis != null) {
    fis.close();
    }
    Sheet sheet = workbook.getSheetAt(0);
    if (sheet == null) {
    throw new NullArgumentException();
    }

    Integer headLine = -1;
    List<Map<String, Object>> excelData = new ArrayList<>();
    // 标题
    List titles = new ArrayList<>();
    int rowSize = sheet.getLastRowNum();
    // 记录表头开始的地方
    int k = 0;
    int y = 0;
    for (int i = 0; i <= rowSize; i++) {
    // 获取到这一行的数据
    Row row = sheet.getRow(i);
    // 如果为空跳过
    if (row == null) {
    continue;
    }
    if (headLine == -1) {
    // 标题行
    headLine = i;
    int cellSize = row.getLastCellNum();
    for (int j = 0; j < cellSize; j++) {
    Cell cell = row.getCell(j);
    if (cell != null) {
    if (y == 0) {
    k = j;
    y = 1;
    }
    titles.add(cell.toString());
    }

         }
     } else {
         // 最大列数
         int maxRol = sheet.getRow(i).getLastCellNum();
    
         Map<String, Object> rowDataMap = new HashMap<>();
         int x = 0;
         //遍历列数
         for (int j = k; j < maxRol; j++) {
             Cell cell = sheet.getRow(i).getCell(j);
             String value = null;
             if (cell == null) {
                 if (StringUtils.isNotBlank(titles.get(x))) {
                     rowDataMap.put(titles.get(x), value);
                     x++;
                 }
                 continue;
             }
    
             CellType cellType = cell.getCellTypeEnum();
             switch (cellType) {
                 case STRING:
                     value = cell.getStringCellValue();
                     break;
                 case NUMERIC: //包含日期和普通数字
                     if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                         Date date = cell.getDateCellValue();
                         DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                         value = df.format(date);
                     } else {
                         value = NumberToTextConverter.toText(cell.getNumericCellValue());
                     }
                     break;
                 case FORMULA:
                     cell.setCellType(CellType.STRING);
                     value = cell.getStringCellValue();
                     break;
                 case BOOLEAN:
                     value = String.valueOf(cell.getBooleanCellValue());
                     break;
                 default:
                     if (cell != null) {
                         value = cell.toString();
                     }
             }
             String key = titles.get(x);
             if (StringUtils.isNotBlank(key)) {
                 rowDataMap.put(key, value);
             }
             x++;
         }
         excelData.add(rowDataMap);
     }
    

    }
    return excelData;
    }

}