通用解析文件转为对象集合插入

在实际工作中，将Excel文件解析成对象集合然后导入数据库是非常常见的需求，面对不同的对象，我们往往需要实现不同的功能，于是萌生了写一个通用方法，来实现不同类的解析，具体实现思路，通过泛型，注解，加反射来实现

导入依赖

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

定义注解

public @interface ExcelColumn {
    String name();
}

具体实现工具类

public class ExcelReader<T> {
    private final Class<T> type;

    public ExcelReader(Class<T> type) {
        this.type = type;
    }

    public List<T> readExcel(String filePath) {
        List<T> dataList = new ArrayList<>();
        try {
            FileInputStream file = new FileInputStream(new File(filePath));
            // Create Workbook instance holding reference to .xlsx file
            Workbook workbook = WorkbookFactory.create(file);
            // Get the first sheet
            Sheet sheet = workbook.getSheetAt(0);
            // Get the header row
            Row headerRow = sheet.getRow(0);
            String[] headers = new String[headerRow.getLastCellNum()];
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                headers[i] = headerRow.getCell(i).getStringCellValue();
            }
            // Iterate through each row
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;
                T obj = type.getDeclaredConstructor().newInstance();
                // Iterate through each cell
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    String header = headers[j];
                    String value = getCellValueAsString(cell);
                    // Find corresponding field using reflection
                    Field field = getFieldByName(header);
                    if (field != null) {
                        field.setAccessible(true);
                        field.set(obj, value);
                    }
                }

                dataList.add(obj);
            }
            workbook.close();
            file.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return dataList;
    }

    private Field getFieldByName(String name) {
        for (Field field : type.getDeclaredFields()) {
            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
            if (annotation != null && annotation.name().equals(name)) {
                return field;
            }
        }
        return null;
    }

    private String getCellValueAsString(Cell cell) {
        String cellValue = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellValue = cell.getDateCellValue().toString();
                    } else {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    cellValue = cell.getCellFormula();
                    break;
                default:
                    cellValue = "";
            }
        }
        return cellValue;
    }

    public static void main(String[] args) {
        ExcelReader<Employee> excelReader = new ExcelReader<>(Employee.class);
        List<Employee> dataList = excelReader.readExcel("your_excel_file.xlsx");

        // Print the data
        for (Employee employee : dataList) {
            System.out.println(employee);
        }
    }

}