您现在的位置：程式碼之家 >> 編程語言 > >> 更多程式語言 >> 程式設計綜合問答

Java 讀取 Excel 寫入 MySQL 完整實作：Apache POI 處理多種格式與空值終止邏輯

編輯：程式設計綜合問答

將不同格式的 Excel 檔（.xls, .xlsx）資料匯入 MySQL 資料庫，並依據空值列決定停止讀取，是企業級資料遷移常見需求。推薦使用 Apache POI 庫實現。

核心依賴

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

關鍵實作類別

public class ExcelToMySQLImporter {
    public void importData(String filePath, String tableName) throws Exception {
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = WorkbookFactory.create(fis);
             Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
            
            Sheet sheet = workbook.getSheetAt(0);
            PreparedStatement ps = buildInsertStatement(conn, tableName, sheet.getRow(0));
            
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null || isEmptyRow(row)) break; // 空值列終止
                
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    ps.setObject(j + 1, getCellValue(cell));
                }
                ps.addBatch();
            }
            ps.executeBatch();
            conn.commit();
        }
    }
    
    private boolean isEmptyRow(Row row) {
        for (Cell cell : row) {
            if (cell != null && cell.getCellType() != CellType.BLANK) return false;
        }
        return true;
    }
    // ... getCellValue, buildInsertStatement 等輔助方法
}