写一个java工具类：将excel中的数据导入数据库中

1、使用到的依赖有POI和mysql-connector

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>

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

2、准备数据库和excel表格

数据库表user有四个字段,id, name, password, age

excel表我放在了d盘根目录，路径为：d:/test.xlsx

3、假设数据库用户为root，密码为password，数据库名为test_database，需要主要excel表格中的数据类型和数据库对应，不一致则进行转换，完整代码如下：

package com.example.demo.utils;

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.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class ExcelToDatabaseUtil {
    // 连接数据使用的url, username和password
    private static final String URL = "jdbc:mysql://localhost:3306/test_database";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    // SQL插入语句
    private static final String INSERT_SQL = "INSERT INTO user (name, password, age) VALUES (?, ?, ?)";

    public static void main(String[] args) {
        String excelFilePath = "d:/test.xlsx";
        ExcelToDatabaseUtil util = new ExcelToDatabaseUtil();
        util.readExcelAndInsertToDatabase(excelFilePath);
    }

    public void readExcelAndInsertToDatabase(String excelFilePath) {
        int rowCount = 0;  // 计数器
        try (FileInputStream fis = new FileInputStream(new File(excelFilePath));
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0); // 确保数据在第一个工作表

            try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
                 PreparedStatement preparedStatement = connection.prepareStatement(INSERT_SQL)) {

                for (Row row : sheet) {
                    if (row.getRowNum() == 0) { // 跳过表头
                        continue;
                    }

                    String name = row.getCell(0).getStringCellValue();
                    String password = String.valueOf(row.getCell(1).getNumericCellValue());
                    int age = (int) row.getCell(2).getNumericCellValue();

                    preparedStatement.setString(1, name);
                    preparedStatement.setString(2, password);
                    preparedStatement.setInt(3, age);

                    preparedStatement.executeUpdate();
                    rowCount++;
                }

                System.out.println("数据插入完成! 共计插入" + rowCount + "条数据");

            } catch (SQLException e) {
                e.printStackTrace();
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}