Java实现Excel导入数据到数据库（支持xls、xlsx）

代码仅供参考，可根据实际业务实现具体逻辑

话不多说，上操作：

1.首先引入相关的依赖

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>4.1.19</version>
            <scope>compile</scope>
        </dependency>

ps：我的测试excel文件内容如下：

 2.新增测试实体类：

package com.fjqwkj.model;

/**
 * @author wdh
 * @create 2023-07-21 17:47
 * @desc
 **/
public class TestData {

    private String custName;
    private String pbkNum;
    private String phone;
    private String bankCard;

    public String getCustName() {
        return custName;
    }

    public void setCustName(String custName) {
        this.custName = custName;
    }

    public String getPbkNum() {
        return pbkNum;
    }

    public void setPbkNum(String pbkNum) {
        this.pbkNum = pbkNum;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getBankCard() {
        return bankCard;
    }

    public void setBankCard(String bankCard) {
        this.bankCard = bankCard;
    }
}

3.新建Excel工具类：

package com.fjqwkj.commons.utils;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.fjqwkj.model.TestData;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.List;

/**
 * @author wdh
 * @create 2023-07-21 15:27
 * @desc
 **/
public class HuToolExcelUtil {

    /**
     * 获取导入数据
     * @param bookStream 文件流
     * @param clazz 转换类
     * @param property 属性名
     * @param <T> 返回值
     * @return
     */
    public static <T> List<T> loadDown(InputStream bookStream, Class<T> clazz, String ...property) {
        ExcelReader reader = ExcelUtil.getReader(bookStream);
        Sheet sheet = reader.getSheet();
        Row sheetTitleRow = sheet.getRow(sheet.getFirstRowNum());
        int physicalNumberOfCells = sheetTitleRow.getPhysicalNumberOfCells();
        //获取表头数量
        int cellCount = 0;
        for (int i = 0; i < physicalNumberOfCells; i++) {
            Cell cell = sheetTitleRow.getCell(i);
            String stringCellValue = cell.getStringCellValue();
            if (StringUtils.isNotBlank(stringCellValue)) {
                cellCount += 1;
            }
        }
        if (property.length != cellCount) {
            throw new RuntimeException("指定属性数量与文件表头数不一致");
        }
        //替换标题为属性名
        for (int i = 0; i < cellCount; i++) {
            Cell cell = reader.getCell(i, 0);
            cell.setCellValue(property[i]);
        }
        return reader.readAll(clazz);
    }

    /**
     * 测试类
     * @param args
     * @throws FileNotFoundException
     */
    public static void main(String[] args) throws FileNotFoundException {
        InputStream is = new FileInputStream("C:\\Users\\28235\\Desktop\\11.xlsx");
        List<TestData> testDataList = HuToolExcelUtil.loadDown(is, TestData.class, "pbkNum", "phone", "custName", "");
        for (TestData testData : testDataList) {
            System.out.println("getCustName = " + testData.getCustName());
            System.out.println("getPbkNum = " + testData.getPbkNum());
            System.out.println("getPhone = " + testData.getPhone());
        }
        //todo 执行数据库保存操作即可
    }
}

4.main方法执行结果如下：

 ps：代码仅供参考，可根据实际业务，将excel数据保存到数据库即可