Java-Excel工具类

package com.minerva.stresstest.common.util;

import com.minerva.stresstest.objs.dataobject.HumitureDO;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author 
 * @date 2022/9/2
 */
public class ParserExcelUtil {
    private Workbook workbook;

    public ParserExcelUtil() {
    }

    public ParserExcelUtil(File excelFile) throws IOException {
        try {
            this.workbook = new XSSFWorkbook(new FileInputStream(excelFile));
        } catch (Exception var3) {
            this.workbook = new HSSFWorkbook(new FileInputStream(excelFile));
        }
    }

    public ParserExcelUtil(InputStream in) throws IOException {
        try {
            this.workbook = new XSSFWorkbook(in);
        } catch (Exception var3) {
            this.workbook = new HSSFWorkbook(in);
        }
    }
    
    public static List<List<CellValue>> listCellValueList(File file) {
        try (InputStream is = FileUtils.openInputStream(file)){
            return listCellValueList(file.getName(), is, 0, 1);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static List<List<CellValue>> listCellValueList(File file, Integer sheetNumber, Integer skipRows) {
        try (InputStream is = FileUtils.openInputStream(file)){
            return listCellValueList(file.getName(), is, sheetNumber, skipRows);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
    
    public static List<List<CellValue>> listCellValueList(String fileName, InputStream is) throws IOException {
        return listCellValueList(fileName, is, 0, 1);
    }

    /**
     * 解析excel数据
     * @param is
     * @return
     */
    public static List<List<CellValue>> listCellValueList(String fileName, InputStream is, Integer sheetNumber, Integer skipRows) throws IOException {
        try {
            List<List<ParserExcelUtil.CellValue>> rowDatas;
            if ("xls".equals(fileName.substring(fileName.lastIndexOf(".") + 1))) {
                ParserExcelUtil parserExcelUtil = new ParserExcelUtil();
                parserExcelUtil.excel2003SheetParser(is);
                rowDatas = parserExcelUtil.getDatasInSheet2CellValue(sheetNumber, skipRows);
            } else {
                rowDatas = new ParserExcelUtil(is).getDatasInSheet2CellValue(sheetNumber, skipRows);
            }
            return rowDatas;
        } catch (Exception e) {
            throw new IOException("解析excel失败");
        }
    }

    /**
     * 实例化解析对象。
     *
     * @param in
     *            待解析的文件流。
     * @throws IOException
     */
    public void excel2003SheetParser(InputStream in) throws IOException {
        try {
            workbook = new HSSFWorkbook(in);
        }catch (Exception e){
            workbook = new XSSFWorkbook(in);

        }
    }
    
    /**
     * 实例化解析对象。
     *
     * @param in
     *            待解析的文件流。
     * @throws IOException
     */
    public void excel2007SheetParser(InputStream in) throws IOException {
        workbook = new XSSFWorkbook(in);
    }
    
    /**
     * 根据所提供的文件解析成java的数据集合。
     *
     * @param sheetNumber
     *            excel里的sheet，从0开始
     * @param skipRows
     *            假如有头部信息，需要指定跳过头几行。
     * @return 数据的集合。
     */
    public List<List<CellValue>> getDatasInSheet2CellValue(int sheetNumber, int skipRows) {

        List<List<CellValue>> result = new ArrayList<List<CellValue>>();

        // 获取指定的sheet
        Sheet sheet = workbook.getSheetAt(sheetNumber);

        int rowCount = sheet.getLastRowNum();
        //获取第一个画布

        CellReference cellReference = new CellReference("A4");
        boolean flag = false;
        for (int i = cellReference.getRow(); i <= sheet.getLastRowNum();) {
            Row r = sheet.getRow(i);
            if(r == null){
                //如果是空行（即没有任何数据、格式），直接把它以下的数据往上移动
                sheet.shiftRows(i+1, sheet.getLastRowNum(),-1);
                continue;
            }
            flag = false;
            for(Cell c : r){
                if(c.getCellType() != Cell.CELL_TYPE_BLANK){
                    flag = true;
                    break;
                }
            }
            if(flag){
                i++;
                continue;
            }else{//如果是空白行（即可能没有数据，但是有一定格式）
                if(i == sheet.getLastRowNum()){
                    //如果到了最后一行，直接将那一行remove掉
                    sheet.removeRow(r);
                }else{
                    //如果还没到最后一行，则数据往上移一行
                    sheet.shiftRows(i+1, sheet.getLastRowNum(),-1);
                }
            }
        }

        System.out.println("有效行数:"+(sheet.getLastRowNum()));
        rowCount=sheet.getLastRowNum();
        int rowIndex = skipRows == 0 ? sheet.getFirstRowNum() : skipRows;

        if (rowCount < 1) {
            return result;
        }

        for (; rowIndex <= rowCount; rowIndex++) {
            Row row = sheet.getRow(rowIndex);

            if (row != null) {
                List<CellValue> rowData = new ArrayList<CellValue>();
                int columnCount = row.getLastCellNum();
                int columnIndex = row.getFirstCellNum();

                for (; columnIndex < columnCount; columnIndex++) {
                    Cell cell = row.getCell(columnIndex);
                    if (cell!=null){
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                    }
                    Object cellStr = this.getCellString(cell);
                    if(cell!=null&&!"".equals(cell)&&!"".equals(cell.getStringCellValue())){
                        CellValue cellvalue = new CellValue(columnIndex, cellStr);
                        rowData.add(cellvalue);
                    }
                    //CellValue cellvalue = new CellValue(columnIndex, cell.getStringCellValue());
                }
                result.add(rowData);
            }
        }
        return result;
    }

    private Object getCellString(Cell cell) {
        Object result = null;
        if (null == cell) {
            return null;
        }
//		cell.setCellType(Cell.CELL_TYPE_STRING);
        if (null != cell) {
            int cellType = cell.getCellType();
            switch (cellType) {
                case Cell.CELL_TYPE_STRING:
                    result = cell.getRichStringCellValue().getString();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    result = getNumbericValue2String(cell);
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    result = getNumbericValue2String(cell);
                    break;
                case Cell.CELL_TYPE_ERROR:
                    result = "";
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    result = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    result = "";
                    break;
                default:
                    result = "";
            }
        }
        return result;
    }

    //处理科学计数法
    private String getNumbericValue2String(Cell cell){

//        cell.setCellType(Cell.CELL_TYPE_STRING);


        if(HSSFDateUtil.isCellDateFormatted(cell)){
//用于转化为日期格式

            Date d = cell.getDateCellValue();

            DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            String format = formater.format(d);
            return format;

        }else{
            DecimalFormat df = new DecimalFormat("0.00");
            String whatYourWant = df.format(cell.getNumericCellValue());
            return whatYourWant;
        }
    }

    /**
     * 获取工作簿名称
     */
    public String getSheetName(Integer index)throws IOException {
        String sheetName = workbook.getSheetAt(index).getSheetName();
        return sheetName;
    }

    public class CellValue{
        private int index;

        private Object value;

        private CellValue() {
            super();
        }

        private CellValue(int index, Object value) {
            super();
            this.index = index;
            this.value = value;
        }


        public int getIndex() {
            return index;
        }


        public void setIndex(int index) {
            this.index = index;
        }


        public Object getValue() {
            return value;
        }


        public void setValue(Object value) {
            this.value = value;
        }
    }
}