java使用poi实现Excel文件导入解析

引用Maven依赖如下：

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

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

List<RowEntity<CellEntity>> rowEntityList = new ArrayList<RowEntity<CellEntity>>();//循环获取和处理数据（排除标题行）
public ImportResponse dealBdcardImport(MultipartFile file, List<RowEntity<CellEntity>> rowEntityList) {
        //获取提交的文件
        if (file == null || file.isEmpty()) {
            return new ImportResponse(3, messageService.getLocale("file.null"));
        }
        String pname = file.getOriginalFilename();
        String suffix = "";
        if (pname.lastIndexOf(".") > -1) {
            suffix = pname.substring(pname.lastIndexOf("."));
        }
        //先判断上传文件类型是否正确`
        if (".XLS.XLSX".indexOf(suffix.toUpperCase()) < 0) {
            return new ImportResponse(3, messageService.getLocale("file.format.error"));
        }
        try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
            if (workbook == null) {
                return new ImportResponse(3, messageService.getLocale("file.format.error"));
            }
            //循环获取和处理数据（排除标题行）
            rowEntityList.addAll(ExcelUtils.getEntityRowList(BdcardImport.getInstance(), workbook.getSheetAt(0)));
            if (rowEntityList.size() == 0) {
                return new ImportResponse(3, messageService.getLocale("nullfile.not.allow.import"));
            }
            StringBuffer bf = ExcelUtils.getIndexList(rowEntityList);
            //对表格空行进行判断
            if (bf.length() > 0) {
                return new ImportResponse(1, messageService.getLocale("file.has.empty.row"));
            }
            List<RowEntity<CellEntity>> rowtitleList = ExcelUtils.getEntityRowList(BdcardImport.getInstance(),
                    workbook.getSheetAt(0), 0);
            if (!rowtitleList.get(0).get(0).getValue().equals("北斗卡号")) {
                return new ImportResponse(3, messageService.getLocale("importfile.error.check.file"));
            }
        } catch (Exception e) {
            return new ImportResponse(3, messageService.getLocale("files.receive.fail"));
        }
        return null;
    }

ExcelUtils：

import com.rdss.common.enums.CellValueType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Excel处理工具
 */
public class ExcelUtils {


    /**
     * 根据ExcelSheet获取所有行信息实体列表.
     * 默认排除第一行（标题行）.
     *
     * @param template Excel内容结构模板
     * @param sheet    Sheet实例
     * @return 行信息实体列表
     */
    public static List<RowEntity<CellEntity>> getEntityRowList(EntityImport template, Sheet sheet) {
        List<RowEntity<CellEntity>> rowEntityList = new ArrayList<RowEntity<CellEntity>>();
        RowEntity<CellEntity> rowEntity = null;
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            if (sheet.getRow(i) != null) {
                // 获取行信息实体
                rowEntity = ExcelUtils.getRowEntity(template, sheet.getRow(i));
                if (rowEntity == null) {
                    dealSheet(sheet, i);
                } else {
                    rowEntity.setIndex(i + 1);
                    rowEntityList.add(rowEntity);
                }
            } else {
                RowEntity<CellEntity> cellEntityRowEntity = new RowEntity<>();
                cellEntityRowEntity.setIndex(-i);
                cellEntityRowEntity.setMessage("第" + (i + 1) + "行为空行");
                rowEntityList.add(cellEntityRowEntity);
            }
        }
        return rowEntityList;
    }
    public static void dealSheet(Sheet sheet, int i){
        for (int c = sheet.getRow(i).getFirstCellNum(); c < sheet.getRow(i).getLastCellNum(); c++) {
            Cell cell = sheet.getRow(i).getCell(c);
            if (cell != null && cell.getCellType() != CellType.BLANK){
                continue;}
        }
    }

    private boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != CellType.BLANK){
                return false;}
        }
        return true;
    }

    /**
     * 对表格中的空行进行判断
     *
     * @param rowEntityList
     * @return
     */
    public static StringBuffer getIndexList(List<RowEntity<CellEntity>> rowEntityList) {
        StringBuffer bf = new StringBuffer();
        //对表格空行进行判断
        for (RowEntity<CellEntity> row : rowEntityList) {
            bf.append(row.getIndex() < 0 ? Integer.valueOf(String.valueOf(row.getIndex()).substring(1)) + "," : "");
        }
        return bf;
    }

    /**
     * 根据ExcelSheet获取所有行信息实体列表.
     *
     * @param typeList Excel内容列结构
     * @param sheet    Sheet实例
     * @return 行信息实体列表
     */
    public static List<RowEntity<CellEntity>> getEntityRowList(
            List<CellValueType> typeList, Sheet sheet) {
        List<RowEntity<CellEntity>> rowEntityList = new ArrayList<RowEntity<CellEntity>>();
        RowEntity<CellEntity> rowEntity = null;
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            if (sheet.getRow(i) != null) {
                // 获取行信息实体
                rowEntity = ExcelUtils.getRowEntity(typeList, sheet.getRow(i));
                if (rowEntity == null) {break;}
                rowEntity.setIndex(i + 1);
                rowEntityList.add(rowEntity);
            } else {
                System.out.println(sheet.getRow(i));
            }
        }
        return rowEntityList;
    }

    /**
     * 根据ExcelSheet获取所有行信息实体列表.
     *
     * @param typeList Excel内容列结构
     * @param sheet    Sheet实例
     * @param start    内容开始行（包含）
     * @return 行信息实体列表
     */
    public static List<RowEntity<CellEntity>> getEntityRowList(
            List<CellValueType> typeList, Sheet sheet, int start) {
        List<RowEntity<CellEntity>> rowEntityList = new ArrayList<RowEntity<CellEntity>>();
        RowEntity<CellEntity> rowEntity = null;
        for (int i = start; i <= sheet.getLastRowNum(); i++) {
            // 获取行信息实体
            rowEntity = ExcelUtils.getRowEntity(typeList, sheet.getRow(i));
            if (rowEntity == null){break;}
            rowEntity.setIndex(i + 1);
            rowEntityList.add(rowEntity);
        }
        return rowEntityList;
    }


    /**
     * 根据ExcelSheet获取所有行信息实体列表.
     * 默认排除第一行（标题行）.
     *
     * @param template Excel内容结构模板
     * @param sheet    Sheet实例
     * @return 行信息实体列表
     */
    public static List<RowEntity<CellEntity>> getEntityRowList(int contentRowIndex, EntityImport template, Sheet sheet) {
        List<RowEntity<CellEntity>> rowEntityList = new ArrayList<RowEntity<CellEntity>>();
        RowEntity<CellEntity> rowEntity = null;
        for (int i = contentRowIndex; i <= sheet.getLastRowNum(); i++) {
            // 获取行信息实体
            rowEntity = ExcelUtils.getRowEntity(template, sheet.getRow(i));
            if (rowEntity == null){break;}
            rowEntity.setIndex(i + 1);
            rowEntityList.add(rowEntity);
        }
        return rowEntityList;
    }

    /**
     * 根据ExcelSheet获取所有行信息实体列表.
     *
     * @param template Excel内容结构模板
     * @param sheet    Sheet实例
     * @param start    内容开始行（包含）
     * @return 行信息实体列表
     */
    public static List<RowEntity<CellEntity>> getEntityRowList(EntityImport template, Sheet sheet, int start) {
        List<RowEntity<CellEntity>> rowEntityList = new ArrayList<RowEntity<CellEntity>>();
        RowEntity<CellEntity> rowEntity = null;
        for (int i = start; i <= sheet.getLastRowNum(); i++) {
            if (sheet.getRow(i) != null) {
                // 获取行信息实体
                rowEntity = ExcelUtils.getRowEntity(template, sheet.getRow(i));
                if (rowEntity == null){ break;}
                rowEntity.setIndex(i + 1);
                rowEntityList.add(rowEntity);
            } else {
                RowEntity<CellEntity> cellEntityRowEntity = new RowEntity<>();
                cellEntityRowEntity.setMessage("第" + i + "行为空行");
                rowEntityList.add(cellEntityRowEntity);
            }
        }
        return rowEntityList;
    }

    /**
     * 根据ExcelSheet获取所有行信息实体列表.
     *
     * @param typeList Excel内容列结构
     * @param sheet    Sheet实例
     * @param start    内容开始行（包含）
     * @param end      内容结束行（包含）
     * @return 行信息实体列表
     */
    public static List<RowEntity<CellEntity>> getEntityRowList(
            List<CellValueType> typeList, Sheet sheet, int start, int end) {
        List<RowEntity<CellEntity>> rowEntityList = new ArrayList<RowEntity<CellEntity>>();
        RowEntity<CellEntity> rowEntity = null;
        for (int i = start; i <= end; i++) {
            // 获取行信息实体
            rowEntity = ExcelUtils.getRowEntity(typeList, sheet.getRow(i));
            if (rowEntity == null){break;}
            rowEntity.setIndex(i + 1);
            rowEntityList.add(rowEntity);
        }
        return rowEntityList;
    }

    /**
     * 根据ExcelSheet获取所有行信息实体列表.
     *
     * @param template Excel内容结构模板
     * @param sheet    Sheet实例
     * @param start    内容开始行（包含）
     * @param end      内容结束行（包含）
     * @return 行信息实体列表
     */
    public static List<RowEntity<CellEntity>> getEntityRowList(
            EntityImport template, Sheet sheet, int start, int end) {
        List<RowEntity<CellEntity>> rowEntityList = new ArrayList<RowEntity<CellEntity>>();
        RowEntity<CellEntity> rowEntity = null;
        for (int i = start; i <= end; i++) {
            // 获取行信息实体
            rowEntity = ExcelUtils.getRowEntity(template, sheet.getRow(i));
            if (rowEntity == null) {break;}
            rowEntity.setIndex(i + 1);
            rowEntityList.add(rowEntity);
        }
        return rowEntityList;
    }

    /**/**
     * 根据行记录生成信息数据.
     *
     * @param typeList 列内容类型列表
     * @param row      行记录对象
     * @return 信息数据
     */
    private static RowEntity<CellEntity> getRowEntity(List<CellValueType> typeList, Row row) {
        if (typeList.isEmpty()) return null;
        RowEntity<CellEntity> rowEntity = new RowEntity<CellEntity>();
        int i = 0;
        Cell cell = null;
        boolean validate = false;
        for (CellValueType type : typeList) {
            cell = row.getCell(i++);
            rowEntity.add(getCellEntity(type, cell));
            if (cell != null && CellType.BLANK != cell.getCellType()) {
                validate = true;
            }
        }
        // 返回信息
        return validate ? rowEntity : null;
    }

    /**
     * 根据行记录生成信息数据.
     *
     * @param template 列内容模板
     * @param row      行记录对象
     * @return 信息数据
     */
    private static RowEntity<CellEntity> getRowEntity(EntityImport template, Row row) {
        if (template.getTypeList().isEmpty()) return null;
        RowEntity<CellEntity> rowEntity = new RowEntity<CellEntity>();
        int i = 0;
        Cell cell = null;
        boolean validate = false;
        for (CellValueType type : template.getTypeList()) {
            cell = row.getCell(i++);
            rowEntity.add(getCellEntity(type, cell));
            if (cell != null && CellType.BLANK != cell.getCellType()) {
                validate = true;
            }
        }
        // 返回信息
        return validate ? rowEntity : null;
    }

    /**
     * 根据列记录生成列信息数据
     *
     * @param type 列记录类型
     * @param cell 列记录对象
     * @return 列信息数据
     */
    private static CellEntity getCellEntity(CellValueType type, Cell cell) {
        CellEntity cellEntity = new CellEntity(type);
        if (cell == null) {
            cellEntity.setValue(null);
        } else {
            // 
            dealGetCellEntity( cellEntity, type,  cell);
        }
        // 返回信息
        return cellEntity;
    }
    /**
     * 功能描述: <br>
     * 
     * @param: [cellEntity, type, cell]
     * @Return: void
     * @Author: wp
     * @Date: 2024/1/12 8:51
     */
    public static void dealGetCellEntity(CellEntity cellEntity, CellValueType type, Cell cell){
        CellType cellType = cell.getCellType();
        switch (type) {
            case STRING:
                if (CellType.NUMERIC == cellType) {
                    cellEntity.setValue(String.valueOf(new Double(cell.getNumericCellValue()).longValue()));
                } else {
                    cellEntity.setValue(StringUtils.trimToEmpty(cell.getStringCellValue()));
                }
                cellEntity.setShowValue(cellEntity.getValue());
                break;
            case INT:
                if (CellType.NUMERIC == cellType) {
                    cellEntity.setValue((int) new Double(cell.getNumericCellValue()).longValue());
                    cellEntity.setShowValue(cellEntity.getValue());
                } else {
                    cellEntity.setValue(CommonUtils.parseInt(cell.getStringCellValue()));
                    cellEntity.setShowValue(cell.getStringCellValue());
                }
                break;
            case LONG:
                if (CellType.NUMERIC == cellType) {
                    cellEntity.setValue(new Double(cell.getNumericCellValue()).longValue());
                    cellEntity.setShowValue(cellEntity.getValue());
                } else {
                    cellEntity.setValue(CommonUtils.parseLong(cell.getStringCellValue()));
                    cellEntity.setShowValue(cell.getStringCellValue());
                }
                break;
            case FLOAT:
                dealGetCellEntity2(cellType,cellEntity,cell);
                break;
            case DOUBLE:
                dealGetCellEntity1(cellType,cellEntity,cell);
                break;
            case BOOL:
                cellEntity.setValue(Boolean.parseBoolean(cell.getStringCellValue()));
                cellEntity.setShowValue(cell.getStringCellValue());
                break;
        }
    }
    /**
     * 功能描述: <br>
     *
     * @param: [cellType, cellEntity, cell]
     * @Return: void
     * @Author: wp
     * @Date: 2024/1/11 16:34
     */
    public static void dealGetCellEntity2(CellType cellType, CellEntity cellEntity, Cell cell){
        if (CellType.NUMERIC == cellType) {
            cellEntity.setValue(new Double(cell.getNumericCellValue()).floatValue());
            cellEntity.setShowValue(cellEntity.getValue());
        } else {
            cellEntity.setValue(CommonUtils.parseFloat(cell.getStringCellValue()));
            cellEntity.setShowValue(cell.getStringCellValue());
        }
    }
    /**
     * 功能描述: <br>
     *
     * @param: [cellType, cellEntity, cell]
     * @Return: void
     * @Author: wp
     * @Date: 2024/1/11 16:34
     */
    public static void dealGetCellEntity1(CellType cellType, CellEntity cellEntity, Cell cell){
        if (CellType.NUMERIC == cellType) {
            cellEntity.setValue(new Double(cell.getNumericCellValue()).doubleValue());
            cellEntity.setShowValue(cellEntity.getValue());
        } else {
            cellEntity.setValue(CommonUtils.parseDouble(cell.getStringCellValue()));
            cellEntity.setShowValue(cell.getStringCellValue());
        }
    }
    /**
     * 功能描述: <br>
     *
     * @param: [cellType, cellEntity, cell]
     * @Return: void
     * @Author: wp
     * @Date: 2024/1/11 16:34
     */
    //根据map的value获取map的key
    public static Integer getKey(Map<Integer, String> map, String value) {
        Integer key = null;
        for (Map.Entry<Integer, String> entry : map.entrySet()) {
            if (value.equals(entry.getValue())) {
                key = entry.getKey();
            }
        }
        return key;
    }
}                

RowEntity：

import lombok.Data;
import java.util.ArrayList;
@Data
public class RowEntity<T> extends ArrayList<T> {

    private static final long serialVersionUID = 1L;

    /* 行号 */
    private int index;

    /* 行内容校验消息 */
    private String message;
}

CellEntity：

import com.rdss.common.enums.CellValueType;
import lombok.Data;

@Data
public class CellEntity {
    /* CELL内容 */
    private Object value;
    /* CELL内容类型 */
    private CellValueType type;
    /* CELL显示用内容 */
    private Object showValue;

    /**
     * 构造方法.
     *
     * @param type CELL内容类型
     */
    public CellEntity(CellValueType type) {
        this.type = type;
    }

    /**
     * 构造方法.
     *
     * @param value CELL内容
     */
    public CellEntity(Object value) {
        this.value = value;
    }
}

for (RowEntity<CellEntity> rowEntity : rowEntityList) {
            ImportBdcard importBdcardEntity = new ImportBdcard();
            //每校验一次生成一个ImportBdcard
            entity = new Bdcard();
            int i = 0;
            String num = null;
            String type = null;
            String frequency = null;
            String level = null;
//            String source = null;
            String system = null;
            String broadcastNum = null;
            num = (String) rowEntity.get(i++).getValue();
            type = (String) rowEntity.get(i++).getValue();
            frequency = (String) rowEntity.get(i++).getValue();
            level = (String) rowEntity.get(i++).getValue();
//            source = (String) rowEntity.get(i++).getValue();
            system = (String) rowEntity.get(i++).getValue();
            broadcastNum = (String) rowEntity.get(i++).getValue();
            importBdcardEntity.setRow(rowEntity.getIndex());
            importBdcardEntity.setNum(num);
            importBdcardEntity.setType(type);
            importBdcardEntity.setFrequency(frequency);
            importBdcardEntity.setLevel(level);
            importBdcardEntity.setSource(source);
            importBdcardEntity.setSystem(system);
            importBdcardEntity.setBroadcastNum(broadcastNum);
       }