将任意Excel读取为对应的JAVA类集合

这个方法的优点是任意类都可以转，缺点是Excel第一行要写java类对应的字段

excel结构

导入的pom.xml依赖：

 

 <!--读取excel文件-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

 对应的java代码：

package com.ragefrog.utils;


import com.fasterxml.jackson.databind.exc.InvalidFormatException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {

   
     /**
     * Excel读取为对应的JAVA类集合
     * @param filePath excel文件路径
     * @param tClass  转换的java类
     * @return 类集合
     */
    public static <T> List<T> excelToList(String filePath, Class<T> tClass) throws Exception {

        //定义要返回的list
        List<T> resultList = new ArrayList<>();
        File file = new File(filePath);
        FileInputStream fis = new FileInputStream(file);
        HSSFWorkbook wb = new HSSFWorkbook(fis);
        //获取数据sheet
        HSSFSheet dataSheet = wb.getSheetAt(0);
        //获取excel文件中隐藏的驼峰字段名
        ArrayList<String> beanKey = new ArrayList<>();
        //获取属性名row
        Row titleRow = dataSheet.getRow(0);
        for (int i = 0; i < titleRow.getLastCellNum(); i++) {
            beanKey.add(titleRow.getCell(i).getStringCellValue());
        }
        Field[] fields = tClass.getDeclaredFields();
        T uBean = null;
        for (int i = 2; i < dataSheet.getLastRowNum() + 1; i++) {
            uBean = (T) tClass.newInstance();
            //获取每一行的数据
            Row dataRow = dataSheet.getRow(i);
            //读取到数据没有数据的时候，结束读取
            if (dataRow == null) {
                break;
            }
            for (int j = 0; j < beanKey.size(); j++) {
                //获取excle里的cell对应的值
                Cell cell = dataRow.getCell(j);
                String dataString = "";
　　　　　　　　　　//使excel里不同的值都可以获取
                if (null != cell) {
                    HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
                    dataString = hSSFDataFormatter.formatCellValue(cell);
                }
                //将excle的值，赋值到对应的JAVA类里
                for (Field field : fields) {
                    field.setAccessible(true);
                    String name = field.getName();
                    if (dataString.length() > 0 && StringUtils.isNotBlank(dataString) && beanKey.get(j).equals(name)) {
                            Class<?> type = field.getType();
                            // 只支持8中基本类型和String类型 如有其他类型 请自行添加
                            if (type == String.class) {
                                field.set(uBean, dataString);
                            } else if (type == Integer.class || type == int.class) {
                                field.set(uBean, Integer.parseInt(dataString));
                            } else if (type == Double.class || type == double.class) {
                                field.set(uBean, Double.parseDouble(dataString));
                            } else if (type == Float.class || type == float.class) {
                                field.set(uBean, Float.parseFloat(dataString));
                            } else if (type == Long.class || type == long.class) {
                                field.set(uBean, Long.parseLong(dataString));
                            } else if (type == Boolean.class || type == boolean.class) {
                                field.set(uBean, Boolean.parseBoolean(dataString));
                            } else if (type == Short.class || type == short.class) {
                                field.set(uBean, Short.parseShort(dataString));
                            } else if (type == Byte.class || type == byte.class) {
                                field.set(uBean, Byte.parseByte(dataString));
                            } else if (type == Character.class || type == char.class) {
                                field.set(uBean, dataString.charAt(0));
                            }
                        }
                    }
                }
                resultList.add(uBean);

            }
            return resultList;
        }
    }
    

 测试代码：

  @Test
    public  void  excleToList1() throws Exception {
        String filePath= "C:\\Users\\Administrator\\Desktop\\车辆基本信息-200.xls";
        List<ManualVehicleInfo> excelUserEntities = ExcelUtil.excelToList(filePath, ManualVehicleInfo.class);
        for (ManualVehicleInfo excelUserEntity : excelUserEntities) {
            System.out.println(excelUserEntity.toString());
        }
    }

测试成功：