如何将Excel单列按3行分组的数据插入数据库多列？

我有一个Excel文件，所有数据均集中在一列中，每3行数据构成一个完整数据集，需将其插入到数据库的多列中。数据库列定义如下：

RefCode int,
website_name varchar(50),
pointcode varchar(5)

Excel内容示例：

1234
www.google.com
xyz
--(第一组结束)
9999
www.yahoo.com
AAA
--(第二组结束)

期望插入数据库后的格式：

1234    www.google.com       xyz
9999    www.yahoo.com        AAA

我已编写读取Excel数据的Java代码，但卡在如何将分组后的数据插入数据库多列的步骤上，代码如下：

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Iterator;

import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.checkerframework.checker.units.qual.s;

public class ReadExcelFileDemo {
    public static void main(String[] args) throws IOException {

        getRowCount();

    }

    public static void getRowCount() throws IOException {
        String exePath = "./input/Trial.xlsx";
        String connectionURL = "jdbc:sqlserver://localhost:1433;databaseName=amour;user=sa;password=DB_Password;encrypt=true;trustServerCertificate=true";
        int i = 0;
        int x = 0;
        int rowCount = 0;

        try (XSSFWorkbook workbook = new XSSFWorkbook(exePath)) {
            XSSFSheet workSheet = workbook.getSheet("Sheet1");

            Connection connection = DriverManager.getConnection(connectionURL);
            Statement st = connection.createStatement();

            rowCount = workSheet.getPhysicalNumberOfRows();
            System.out.println(rowCount);

            DataFormatter df = new DataFormatter();

            Iterator<Row> rows = workSheet.rowIterator();

            while (rows.hasNext()) {

                for (x = 0; x < 3; x++) {

                    Object cellValue = df.formatCellValue(workSheet.getRow(i).getCell(0));
                    System.out.println(cellValue);
                    i++;

                }

                x = 0;

                System.out.println("End of Set ");

            }

        } catch (Exception ex) {
            // System.out.println(ex.getCause());
            // System.out.println(ex.getStackTrace());
            // ex.printStackTrace();
        }
    }

}

核心思路是每读取3行数据就组成一组，对应数据库的三个字段，然后执行插入操作。以下是修改后的代码，关键调整点：

• 用PreparedStatement替代Statement，避免SQL注入同时提升插入效率
• 收集每组的三个字段值，组装成插入SQL的参数
• 处理Excel总行数不是3的倍数的边界情况
• 完善资源自动关闭逻辑，确保数据库连接、Statement等资源正确释放

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelFileDemo {
    public static void main(String[] args) throws IOException {
        insertExcelDataToDB();
    }

    public static void insertExcelDataToDB() throws IOException {
        String exePath = "./input/Trial.xlsx";
        String connectionURL = "jdbc:sqlserver://localhost:1433;databaseName=amour;user=sa;password=DB_Password;encrypt=true;trustServerCertificate=true";
        // 定义插入SQL，使用占位符?代替具体值
        String insertSql = "INSERT INTO 你的表名 (RefCode, website_name, pointcode) VALUES (?, ?, ?)";

        try (XSSFWorkbook workbook = new XSSFWorkbook(exePath);
             // 自动关闭数据库连接
             Connection connection = DriverManager.getConnection(connectionURL);
             // 预编译SQL，提升效率
             PreparedStatement pstmt = connection.prepareStatement(insertSql)) {

            XSSFSheet workSheet = workbook.getSheet("Sheet1");
            int rowCount = workSheet.getPhysicalNumberOfRows();
            System.out.println("总行数: " + rowCount);

            DataFormatter df = new DataFormatter();

            // 每3行一组循环处理
            for (int i = 0; i < rowCount; i += 3) {
                // 避免最后一组数据不足3行的情况
                if (i + 2 >= rowCount) {
                    System.out.println("跳过不完整的数据集，起始行号: " + i);
                    break;
                }

                // 获取每组的三个字段值
                String refCodeStr = df.formatCellValue(workSheet.getRow(i).getCell(0));
                String websiteName = df.formatCellValue(workSheet.getRow(i + 1).getCell(0));
                String pointCode = df.formatCellValue(workSheet.getRow(i + 2).getCell(0));

                // 设置SQL参数
                pstmt.setInt(1, Integer.parseInt(refCodeStr));
                pstmt.setString(2, websiteName);
                pstmt.setString(3, pointCode);

                // 执行插入
                int affectedRows = pstmt.executeUpdate();
                System.out.println("插入成功，影响行数: " + affectedRows + " | 数据组: " + refCodeStr + ", " + websiteName + ", " + pointCode);
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (NumberFormatException ex) {
            System.out.println("RefCode格式错误，无法转换为整数: " + ex.getMessage());
            ex.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

注意事项 #

1. 替换代码中的你的表名为实际数据库表名
2. 确保Excel中RefCode列的内容都是有效的整数，否则会抛出NumberFormatException，可根据需求添加额外格式校验逻辑
3. 若数据量较大，可改用addBatch()和executeBatch()实现批量插入，进一步提升性能

内容的提问来源于stack exchange，提问作者user434853