HuTool：excel解析并插入数据库Demo代码

<dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.34</version>
        </dependency>
      
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.26</version>
        </dependency>

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version> <!-- Check for the latest version -->
        </dependency>

import lombok.Data;

import java.util.Date;

@Data
public class Equipment {
    // private String id;                  // 唯一标识
    private String deviceName;         // 设备名称
    private int quantity;              // 数量
    private String model;              // 规格型号
    private String deviceCategory;     // 设备类别（字典）
    // private String bumenid;             // 部门id
    private String deviceStatus;       // 设备状态（字典）
    private String condition;          // 完好情况（字典）
    private String location;            // 设备位置
    // private String createBy;       // admin
    // private Date createTime;      // random
    private String responsibleDept;    // 责任部门 103
}

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.sql.*;
import java.util.*;


@RestController
@SpringBootApplication
public class DemoApplication {
	private static final String DB_URL = "jdbc:mysql://192.168.0.186:3307/ypzgpt-data";
	private static final String USER = "root";
	private static final String PASS = "root";

	public static void main(String[] args) {
		SpringApplication.run(DemoApplication.class, args);
		analyzeExcel();
	}

	/**
	 *
	 */
	public static void analyzeExcel() {
		// Excel 文件路径
		String filePath = "D:\\Users\\Administrator\\Desktop\\机电数据.xlsx";

		// 创建 ExcelReader
		ExcelReader reader = ExcelUtil.getReader(new File(filePath));

		// 读取 Excel 数据
		List<Equipment> equipmentList = reader.readAll(Equipment.class);

		// Insert data into MySQL
		insertDataToMySQL(equipmentList);
	}

	private static void insertDataToMySQL(List<Equipment> equipmentList) {
		String sql = "INSERT INTO zg_shebeijbxx (id, shebeimc, guigexh, shebeilb, bumenid, shebeizt, " +
				"wanhaoqk, shebeiwz, createBy, createTime, createDept) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		Map<String, String> categoryMap = new HashMap<>();
		categoryMap.put("采煤", "0");
		categoryMap.put("掘进", "1");
		categoryMap.put("通风", "2");
		categoryMap.put("瓦斯抽放", "3");
		categoryMap.put("取热设备", "4");
		categoryMap.put("供电", "5");
		categoryMap.put("运输", "6");
		categoryMap.put("起吊", "7");
		categoryMap.put("维修", "8");
		categoryMap.put("加工", "9");
		categoryMap.put("洗浴", "10");
		categoryMap.put("压风", "11");
		categoryMap.put("取暖", "12");
		categoryMap.put("选煤", "13");
		categoryMap.put("照明", "14");
		categoryMap.put("排水", "15");
		categoryMap.put("运顺", "16");

		int rows = 0;
		try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
			 PreparedStatement pstmt = conn.prepareStatement(sql)) {

			for (Equipment equipment : equipmentList) {
				int quantity = equipment.getQuantity(); // 获取数量

				for (int i = 0; i < quantity; i++) {
					pstmt.setString(1, UUID.randomUUID().toString()); // 设置UUID作为id
					pstmt.setString(2, equipment.getDeviceName());    // 设备名称
					pstmt.setString(3, equipment.getModel());         // 规格型号
					// 获取设备类别的代码
					String categoryCode = categoryMap.get(equipment.getDeviceCategory());
					pstmt.setString(4, categoryCode != null ? categoryCode : ""); // 设置设备类别代码
					pstmt.setString(5, "137");                        // 部门id
					pstmt.setString(6, "1");                          // 设备状态
					pstmt.setString(7, "1");                          // 完好情况
					pstmt.setString(8, equipment.getLocation());      // 设备位置
					pstmt.setString(9, "admin");                       // 创建人
					pstmt.setTimestamp(10, new Timestamp(System.currentTimeMillis())); // 当前时间
					pstmt.setString(11, "103");                        // 责任部门

					pstmt.addBatch(); // 添加到批处理
					// break;
					System.out.println(rows++ + " rows inserted. " + equipment.getDeviceName() + " " + equipment.getQuantity() +
							" " + equipment.getModel());
				}
			}

			pstmt.executeBatch(); // 执行批处理
			System.out.println("Data inserted successfully!");

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

}