Excel解析不规范数据报错解决方案

本文ExcelParseUtil替代EasyExcel解决easyexcel ioexception: stream closed问题

1.如图中excel(不规范excel数据)

2.使用ExcelParseUtil工具类解析Excel且校验并修正数据，最终读取数据

File file = new File("D:\\xx.xls");
List<QualifiedRateResult> qualifiedRateResults = ExcelParseUtil.parse(file, QualifiedRateResult.class);

3.ExcelParseUtil代码如下

import cn.hutool.core.date.DateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.text.NumberFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelParseUtil {

    public static Workbook workbook(File file) throws IOException {
        FileInputStream fileInputStream = new FileInputStream(file);
        return Objects.requireNonNull(file.getName()).endsWith(".xlsx") ? new XSSFWorkbook(fileInputStream) :
                new HSSFWorkbook(fileInputStream);
    }

    public static <C> List<C> parse(File file, Class<C> clz) throws IOException, IllegalAccessException, InstantiationException {
        return parse(workbook(file), clz, 0, 1, null);
    }

    public static <C> List<C> parse(File file, Class<C> clz, int startRow) throws IOException, IllegalAccessException, InstantiationException {
        return parse(workbook(file), clz, 0, startRow, null);
    }

    public static <C> List<C> parse(File file, Class<C> clz, int sheetIndex, int startRow,
                                    ExcelParseListener<C> excelParseListener) throws IOException, IllegalAccessException, InstantiationException {
        return parse(workbook(file), clz, sheetIndex, startRow, excelParseListener);
    }

    public static <C> List<C> parse(Workbook workbook, Class<C> clz, int sheetIndex) throws IllegalAccessException, InstantiationException {
        return parse(workbook, clz, sheetIndex, 1, null);
    }

    public static <C> List<C> parse(Workbook workbook, Class<C> clz, int sheetIndex, int startRow,
                                    ExcelParseListener<C> excelParseListener) throws InstantiationException, IllegalAccessException {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        List<Field> fields = new ArrayList<>();
        Field[] declaredFields = clz.getDeclaredFields();
        for (Field field : declaredFields) {
            field.setAccessible(true);
            ExcelParseIgnore declaredAnnotation = field.getDeclaredAnnotation(ExcelParseIgnore.class);
            if (declaredAnnotation == null) {
                fields.add(field);
            }
        }
        NumberFormat numberFormat = NumberFormat.getInstance();
        numberFormat.setGroupingUsed(false);
        List<C> cResults = new ArrayList<>();
        for (int i = startRow; i < physicalNumberOfRows; i++) {
            Row row = sheet.getRow(i);
            C o = row2cResult(row, clz, fields,numberFormat);
            if (excelParseListener != null) {
                excelParseListener.invoke(o, row, fields);
            }
            cResults.add(o);
        }
        return cResults;
    }

    private static <C> C row2cResult(Row row, Class<C> clz, List<Field> classFields, NumberFormat numberFormat) throws IllegalAccessException, InstantiationException {
        Object instance = clz.newInstance();
        for (int i = 0; i < classFields.size(); i++) {
            Field field = classFields.get(i);
            String fieldType = field.getType().getName();
            Cell cell = row.getCell(i);
            switch (fieldType) {
                case "java.lang.String":
                    try {
                        ExcelParseDate annotation = field.getAnnotation(ExcelParseDate.class);
                        if (annotation == null) {
                            field.set(instance, stringCellValue(cell,numberFormat));
                        } else {
                            if (cell.getCellTypeEnum().name().equals("NUMERIC")) {
                                String format = DateUtil.format(cell.getDateCellValue(), "yyyy-MM-dd hh:mm:ss");
                                field.set(instance, format);
                            } else {
                                if (cell.getStringCellValue().length()>0){
                                    String date = DateUtil.parse(cell.getStringCellValue()).toString();
                                    field.set(instance, date);
                                }else {
                                    field.set(instance, LocalDate.now().atStartOfDay().format(DateTimeFormatter.ofPattern("yyyy-MM-dd hh:mm:ss")));
                                }
                            }
                        }
                    } catch (Exception e) {
                        field.set(instance, cell != null ? cell.toString() : null);
                    }
                    break;
                case "double":
                    try {
                        field.setDouble(instance, Double.parseDouble(numberCellValue(cell)));
                    } catch (Exception e) {
                        field.setDouble(instance, 0);
                    }
                    break;
                case "int":
                    try {
                        field.setInt(instance, Integer.parseInt(numberCellValue(cell).split("\\.")[0]));
                    } catch (Exception e) {
                        field.setInt(instance, 0);
                    }
                    break;
                case "long":
                    try {
                        field.setLong(instance, Long.parseLong(numberCellValue(cell).split("\\.")[0]));
                    } catch (Exception e) {
                        field.setLong(instance, 0);
                    }
                    break;
                case "float":
                    try {
                        field.setFloat(instance, Float.parseFloat(numberCellValue(cell)));
                    } catch (Exception e) {
                        field.setFloat(instance, 0);
                    }
                    break;
                case "java.lang.Double":
                    try {
                        field.set(instance,Double.parseDouble(numberCellValue(cell)));
                    } catch (Exception e) {
                        field.set(instance, 0);
                    }
                    break;
                case "java.lang.Integer":
                    try {
                        field.set(instance,Integer.parseInt(numberCellValue(cell).split("\\.")[0]));
                    } catch (Exception e) {
                        field.set(instance, 0);
                    }
                    break;
                case "java.lang.Long":
                    try {
                        field.set(instance,Long.parseLong(numberCellValue(cell).split("\\.")[0]));
                    } catch (Exception e) {
                        field.set(instance, 0);
                    }
                case "java.lang.Float":
                    try {
                        field.set(instance,Float.parseFloat(numberCellValue(cell)));
                    } catch (Exception e) {
                        field.set(instance, 0);
                    }
                    break;
                case "boolean":
                    try {
                        if (cell.getCellTypeEnum().name().equals("STRING")) {
                            field.setBoolean(instance, Boolean.parseBoolean(cell.getStringCellValue()));
                        } else {
                            field.setBoolean(instance, cell.getBooleanCellValue());
                        }
                    } catch (Exception e) {
                        field.setBoolean(instance, false);
                    }
                    break;
                case "java.lang.Boolean":
                    try {
                        if (cell.getCellTypeEnum().name().equals("STRING")) {
                            field.set(instance, Boolean.parseBoolean(cell.getStringCellValue()));
                        } else {
                            field.set(instance, cell.getBooleanCellValue());
                        }
                    } catch (Exception e) {
                        field.set(instance, false);
                    }
                    break;
                default:
                    field.set(instance, stringCellValue(cell,numberFormat));
                    break;
            }
        }
        return (C) instance;
    }

    public static String stringCellValue(Cell cell, NumberFormat numberFormat) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellTypeEnum().name()) {
            case "STRING":
                return cell.getStringCellValue();
            case "NUMERIC":
                return numberFormat.format(cell.getNumericCellValue());
            case "BOOLEAN":
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }

    private static String numberCellValue(Cell cell) {
        if (cell == null) {
            return "0";
        }
        switch (cell.getCellTypeEnum().name()) {
            case "NUMERIC":
                return String.valueOf(cell.getNumericCellValue());
            case "STRING":
                String stringNumber = stringNumber(cell.getStringCellValue());
                if (stringNumber.length() > 0){
                    if (stringNumber.contains(".")&&stringNumber.indexOf(".")!=stringNumber.lastIndexOf(".")){
                        return "0";
                    }else {
                        return stringNumber;
                    }
                }else {
                    return "0";
                }
            case "BOOLEAN":
                return cell.getBooleanCellValue()?"1":"0";
            default:
                return "0";
        }
    }

    public static String stringNumber(String str) {
        String regEx = "[^0-9.]";
        Pattern p = Pattern.compile(regEx);
        Matcher m = p.matcher(str);
        return m.replaceAll("").trim();
    }

}

import org.apache.poi.ss.usermodel.Row;

import java.lang.reflect.Field;
import java.util.List;

public interface ExcelParseListener<C> {

    void invoke(C c, Row row, List<Field> fields);

}

package com.nod.excel.util;

import java.lang.annotation.*;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelParseIgnore {
}

package com.nod.excel.util;

import java.lang.annotation.*;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelParseDate {
}