将数据列表导出成excle表格

 <!-- 导入依赖 -->

<dependencies>
    <!-- 其他依赖 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.0.0</version> <!-- 请检查最新版本 -->
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.0.0</version> <!-- 请检查最新版本 -->
    </dependency>
</dependencies>

导出数据到excle表格代码如下：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelExportExample {
    public static void main(String[] args) {
        Workbook workbook = new XSSFWorkbook(); // 创建一个新的Excel工作簿
        Sheet sheet = workbook.createSheet("Sheet1"); // 创建一个工作表

        // 填充数据
        Row row1 = sheet.createRow(0);
        row1.createCell(0).setCellValue("Name");
        row1.createCell(1).setCellValue("Age");
        row1.createCell(2).setCellValue("Address");

        Row row2 = sheet.createRow(1);
        row2.createCell(0).setCellValue("John");
        row2.createCell(1).setCellValue(25);
        row2.createCell(2).setCellValue("New York");

        Row row3 = sheet.createRow(2);
        row3.createCell(0).setCellValue("Jane");
        row3.createCell(1).setCellValue(30);
        row3.createCell(2).setCellValue("San Francisco");

        // 将工作簿写入文件
        try (FileOutputStream outputStream = new FileOutputStream("example.xlsx")) {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

导入excle表格数据到数据库，代码如下：

1.读取excle表格：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public Workbook readExcel(String fileName) {
    Workbook workbook = new XSSFWorkbook(new FileInputStream(fileName));
    return workbook;
}

2.将数据插入到数据库

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public void insertData(Workbook workbook, String url, String user, String password) {
    try (Connection connection = DriverManager.getConnection(url, user, password)) {
        Sheet sheet = workbook.getSheetAt(0);
        for (Row row : sheet) {
            String sql = "INSERT INTO 表名 (列1, 列2, 列3, ...) VALUES (?, ?, ?, ...)";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                // 假设有3列
                preparedStatement.setString(1, row.getCell(0).getStringCellValue());
                preparedStatement.setInt(2, Integer.parseInt(row.getCell(1).getStringCellValue()));
                preparedStatement.setDate(3, new Date(row.getCell(2).getNumericCellValue()));
                preparedStatement.executeUpdate();
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}