簡(jiǎn)介這是一份面向Java開(kāi)發(fā)者與數(shù)據(jù)庫(kù)運(yùn)維人員的數(shù)據(jù)處理工具源碼包用于將Excel表格數(shù)據(jù)自動(dòng)解析并生成可執(zhí)行的SQL腳本再通過(guò)JDBC連接寫(xiě)入目標(biāo)數(shù)據(jù)庫(kù)適用于數(shù)據(jù)遷移、批量導(dǎo)入與數(shù)據(jù)分析等場(chǎng)景。壓縮包共56個(gè)文件約24.48MB包含21個(gè)jar依賴(lài)、20個(gè)class與11個(gè)java源碼另有classpath配置、project工程文件、fatjar打包產(chǎn)物及一份xls測(cè)試數(shù)據(jù)模板結(jié)構(gòu)完整便于直接運(yùn)行與二次開(kāi)發(fā)。資源核心圍繞Apache POI解析Excel、構(gòu)造INSERT等SQL語(yǔ)句、配置數(shù)據(jù)庫(kù)驅(qū)動(dòng)并管理連接執(zhí)行源碼可幫助讀者理解從表格字段映射到語(yǔ)句生成、再到JDBC執(zhí)行與異常處理的完整鏈路。目前已有979人學(xué)習(xí)下載適合希望掌握Excel到數(shù)據(jù)庫(kù)自動(dòng)化轉(zhuǎn)換思路的開(kāi)發(fā)者參考借鑒。1. 從手工拼 SQL 到批量生成一個(gè)被低估的提效工具做數(shù)據(jù)遷移或者后臺(tái)初始化的時(shí)候最煩的不是寫(xiě) SQL而是把 Excel 里幾百上千行配置數(shù)據(jù)一行行拼成 INSERT 語(yǔ)句。字段一多引號(hào)、逗號(hào)、NULL 值、日期格式隨便哪個(gè)地方手滑就是一條報(bào)錯(cuò)。更別提產(chǎn)品經(jīng)理隔三差五丟過(guò)來(lái)一個(gè)「補(bǔ)充數(shù)據(jù).xlsx」你還得重新對(duì)一遍字段順序。我最近拆的一個(gè)小工具就是干這件事的讀取 Excel按模板生成可直接執(zhí)行的 SQL 腳本同時(shí)支持通過(guò) JDBC 連接數(shù)據(jù)庫(kù)做校驗(yàn)或直接寫(xiě)入。它解決的核心問(wèn)題很具體——把「人肉拼 SQL」變成「配置一次、批量產(chǎn)出」。適合誰(shuí)做數(shù)據(jù)初始化、配置表導(dǎo)入、測(cè)試數(shù)據(jù)構(gòu)造的后端和運(yùn)維同學(xué)尤其是那種表結(jié)構(gòu)固定、數(shù)據(jù)來(lái)源是 Excel 的場(chǎng)景。下面按「它怎么工作 → 怎么跑起來(lái) → 坑在哪 → 怎么用得更順」的順序拆開(kāi)講。2. 解析 Excel 生成 SQL 的底層邏輯POI、JDBC 與模板三件套2.1 為什么是 POI JDBC 這套組合Excel 解析這塊Java 生態(tài)里最穩(wěn)的還是 Apache POI。它同時(shí)支持.xlsHSSF和.xlsxXSSF能拿到單元格的類(lèi)型、格式、公式結(jié)果這對(duì)生成 SQL 很關(guān)鍵——因?yàn)槟阋袛嘁粋€(gè)格子到底是數(shù)字、字符串還是日期不能一律當(dāng)字符串處理。JDBC 在這里有兩個(gè)用途一是生成腳本前先連庫(kù)校驗(yàn)表結(jié)構(gòu)字段名、類(lèi)型對(duì)不對(duì)二是小批量數(shù)據(jù)可以直接走PreparedStatement寫(xiě)入省去生成腳本再手動(dòng)執(zhí)行的步驟。常見(jiàn)做法是大批量走「生成 .sql 文件 命令行執(zhí)行」小批量或需要即時(shí)反饋的走 JDBC 直連。模板引擎的選擇上簡(jiǎn)單場(chǎng)景用字符串拼接加占位符就夠了復(fù)雜場(chǎng)景多表、條件字段可以上 FreeMarker 或 Velocity。我一般建議先用最樸素的String.format或StringBuilder因?yàn)?SQL 生成的可讀性比「優(yōu)雅」重要——出問(wèn)題時(shí)你得能一眼看出哪條語(yǔ)句拼錯(cuò)了。2.2 一次完整的生成流程拆解整個(gè)流程可以拆成五步每一步都有對(duì)應(yīng)的代碼落點(diǎn)讀取 Excel 文件定位 sheet 和表頭行逐行讀取單元格按列映射到字段對(duì)每個(gè)值做類(lèi)型轉(zhuǎn)換和轉(zhuǎn)義重點(diǎn)字符串單引號(hào)、日期格式、NULL套用 INSERT 模板生成 SQL 語(yǔ)句輸出到文件或通過(guò) JDBC 執(zhí)行先看讀取和映射的核心代碼// 讀取 Excel 并映射為字段-值對(duì) public ListMapString, String readExcel(String filePath, int headerRow) throws IOException { ListMapString, String rows new ArrayList(); try (InputStream is new FileInputStream(filePath); Workbook workbook WorkbookFactory.create(is)) { // 自動(dòng)識(shí)別 xls/xlsx Sheet sheet workbook.getSheetAt(0); Row header sheet.getRow(headerRow); ListString columns new ArrayList(); for (Cell cell : header) { columns.add(cell.getStringCellValue().trim()); // 表頭即字段名 } // 從表頭下一行開(kāi)始讀數(shù)據(jù) for (int i headerRow 1; i sheet.getLastRowNum(); i) { Row row sheet.getRow(i); if (row null) continue; MapString, String record new LinkedHashMap(); for (int j 0; j columns.size(); j) { Cell cell row.getCell(j); record.put(columns.get(j), getCellValue(cell)); // 統(tǒng)一轉(zhuǎn)字符串 } rows.add(record); } } return rows; }這段代碼的關(guān)鍵點(diǎn)WorkbookFactory.create能自動(dòng)判斷文件格式省去手動(dòng)區(qū)分 HSSF 和 XSSF用LinkedHashMap保證字段順序和 Excel 列順序一致生成 SQL 時(shí)字段和值的順序不會(huì)錯(cuò)位表頭直接當(dāng)字段名意味著 Excel 第一行必須寫(xiě)數(shù)據(jù)庫(kù)真實(shí)字段名這是約定也是約束。getCellValue是容易翻車(chē)的地方單獨(dú)拎出來(lái)// 單元格取值處理數(shù)字、日期、布爾、公式等類(lèi)型 private String getCellValue(Cell cell) { if (cell null) return null; switch (cell.getCellType()) { case STRING: return cell.getStringCellValue().trim(); case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // 日期統(tǒng)一轉(zhuǎn)成 yyyy-MM-dd HH:mm:ss return new SimpleDateFormat(yyyy-MM-dd HH:mm:ss) .format(cell.getDateCellValue()); } // 避免 1.0 這種科學(xué)計(jì)數(shù)法尾巴 double d cell.getNumericCellValue(); if (d Math.floor(d)) return String.valueOf((long) d); return String.valueOf(d); case BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case FORMULA: return cell.getCellFormula(); // 或按需取計(jì)算結(jié)果 default: return null; } }參數(shù)說(shuō)明DateUtil.isCellDateFormatted判斷是否是日期格式這一步不能省否則日期會(huì)變成一串?dāng)?shù)字序列號(hào)數(shù)字處理里判斷d Math.floor(d)是為了把100.0還原成100不然生成的 SQL 里 ID 字段會(huì)變成浮點(diǎn)數(shù)插入整型列直接報(bào)錯(cuò)。2.3 生成 SQL 時(shí)的轉(zhuǎn)義與模板拿到字段-值對(duì)之后生成 INSERT 語(yǔ)句// 根據(jù)字段映射生成 INSERT 語(yǔ)句 public String buildInsert(String tableName, MapString, String record) { StringBuilder cols new StringBuilder(); StringBuilder vals new StringBuilder(); for (Map.EntryString, String e : record.entrySet()) { cols.append(e.getKey()).append(,); vals.append(toSqlValue(e.getValue())).append(,); } cols.setLength(cols.length() - 1); // 去掉末尾逗號(hào) vals.setLength(vals.length() - 1); return String.format(INSERT INTO %s (%s) VALUES (%s);, tableName, cols, vals); } // 值轉(zhuǎn) SQL 字面量字符串加引號(hào)并轉(zhuǎn)義null 直接寫(xiě) NULL private String toSqlValue(String value) { if (value null || value.isEmpty()) return NULL; // 單引號(hào)轉(zhuǎn)義防止 SQL 注入和語(yǔ)法錯(cuò)誤 return value.replace(, ) ; }邏輯說(shuō)明toSqlValue里把單引號(hào)替換成兩個(gè)單引號(hào)這是 SQL 標(biāo)準(zhǔn)轉(zhuǎn)義方式能避免「OBrien」這類(lèi)數(shù)據(jù)把語(yǔ)句截?cái)唷？兆址?NULL 處理是個(gè)策略選擇——如果你的表不允許 NULL就得改成寫(xiě)這個(gè)要按實(shí)際表結(jié)構(gòu)定。提示如果字段里有純數(shù)字但數(shù)據(jù)庫(kù)列是 varchar上面的寫(xiě)法會(huì)帶上引號(hào)沒(méi)問(wèn)題但如果列是 int 而 Excel 里混進(jìn)了空值NULL 是合法的空字符串就會(huì)報(bào)錯(cuò)。類(lèi)型邊界一定要提前對(duì)齊。3. 把工具跑起來(lái)環(huán)境、依賴(lài)與 JDBC 直連配置3.1 依賴(lài)引入與最小可運(yùn)行工程Maven 項(xiàng)目里引入 POI 和數(shù)據(jù)庫(kù)驅(qū)動(dòng)即可。POI 建議用 5.x 版本對(duì).xlsx支持更完整dependencies !-- Excel 解析 -- dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml/artifactId version5.2.3/version /dependency !-- JDBC 驅(qū)動(dòng)按實(shí)際數(shù)據(jù)庫(kù)替換 -- dependency groupIdcom.mysql/groupId artifactIdmysql-connector-j/artifactId version8.0.33/version /dependency /dependencies參數(shù)說(shuō)明poi-ooxml已經(jīng)傳遞依賴(lài)了poi核心包不用重復(fù)引驅(qū)動(dòng)版本要和數(shù)據(jù)庫(kù)服務(wù)端匹配MySQL 8 用com.mysql.cj.jdbc.Driver5.x 用com.mysql.jdbc.Driver寫(xiě)錯(cuò)會(huì)報(bào)ClassNotFoundException。3.2 JDBC 連接與批量寫(xiě)入如果選擇直連寫(xiě)入而不是生成腳本核心是用PreparedStatement加批處理// JDBC 批量寫(xiě)入避免逐條提交 public void batchInsert(ListMapString, String rows) throws SQLException { String sql INSERT INTO config_table (id, name, create_time) VALUES (?, ?, ?); try (Connection conn DriverManager.getConnection(URL, USER, PWD); PreparedStatement ps conn.prepareStatement(sql)) { conn.setAutoCommit(false); // 關(guān)閉自動(dòng)提交 for (MapString, String row : rows) { ps.setLong(1, Long.parseLong(row.get(id))); ps.setString(2, row.get(name)); ps.setTimestamp(3, Timestamp.valueOf(row.get(create_time))); ps.addBatch(); // 加入批次 } ps.executeBatch(); // 一次性提交 conn.commit(); } }邏輯說(shuō)明setAutoCommit(false)配合executeBatch能把上千條插入合并成一次網(wǎng)絡(luò)往返性能差距在數(shù)據(jù)量大時(shí)非常明顯。addBatch只是攢著executeBatch才真正發(fā)送。注意Timestamp.valueOf要求字符串格式嚴(yán)格是yyyy-MM-dd HH:mm:ss和前面 Excel 日期轉(zhuǎn)換的格式必須一致否則拋IllegalArgumentException。3.3 生成腳本 vs JDBC 直連怎么選維度生成 .sql 文件JDBC 直連寫(xiě)入適用數(shù)據(jù)量大批量、可分批執(zhí)行中小批量、即時(shí)反饋可審查性高腳本可人工檢查低直接進(jìn)庫(kù)回滾難度低腳本可留存高需事務(wù)控制環(huán)境依賴(lài)只需文件需網(wǎng)絡(luò)和驅(qū)動(dòng)典型場(chǎng)景生產(chǎn)初始化、交付腳本測(cè)試數(shù)據(jù)、臨時(shí)導(dǎo)入我一般建議生產(chǎn)環(huán)境走生成腳本先人工過(guò)一遍再執(zhí)行測(cè)試環(huán)境走 JDBC 直連圖快。兩者不是互斥的同一個(gè)工具里可以都實(shí)現(xiàn)用參數(shù)切換。4. 避坑與排查那些讓腳本跑不通的細(xì)節(jié)4.1 日期變成 45000 這種數(shù)字現(xiàn)象Excel 里明明是2024-01-01生成的 SQL 里卻是45292。原因POI 把日期存為數(shù)字序列號(hào)如果沒(méi)調(diào)DateUtil.isCellDateFormatted判斷就會(huì)按普通數(shù)字取值。解決在getCellValue里先判斷日期格式用cell.getDateCellValue()取值再格式化。另外注意 Excel 單元格格式本身要是「日期」類(lèi)型如果只是手動(dòng)輸入了看起來(lái)像日期的文本POI 會(huì)當(dāng)字符串處理這種反而不會(huì)出問(wèn)題。4.2 數(shù)字精度丟失或多了小數(shù)點(diǎn)現(xiàn)象ID 是10000000000000001生成后變成1.0E16或10000000000000000。原因Excel 對(duì)超過(guò) 15 位的數(shù)字本身就會(huì)丟精度POI 讀出來(lái)是 double再轉(zhuǎn)字符串就變形了。解決長(zhǎng)數(shù)字如訂單號(hào)、身份證號(hào)在 Excel 里必須存成文本格式或者讀取時(shí)用DataFormatter按顯示值取。這是 Excel 的鍋不是工具的鍋但排查時(shí)容易誤判。4.3 表頭字段名和數(shù)據(jù)庫(kù)對(duì)不上現(xiàn)象生成的 SQL 執(zhí)行報(bào)Unknown column xxx。原因Excel 表頭寫(xiě)了中文名或別名和數(shù)據(jù)庫(kù)真實(shí)字段名不一致。解決約定表頭必須寫(xiě)真實(shí)字段名或者在工具里加一層字段映射配置Excel 列名 → 數(shù)據(jù)庫(kù)字段名。我傾向于加映射表因?yàn)闃I(yè)務(wù)同學(xué)填 Excel 時(shí)更愿意寫(xiě)中文。4.4 空行和合并單元格導(dǎo)致錯(cuò)位現(xiàn)象生成的 SQL 里某些行字段值整體偏移或者多出空 INSERT。原因Excel 里有空行、合并單元格getLastRowNum會(huì)把空行也算進(jìn)去getCell遇到合并單元格只有左上角有值。解決讀取時(shí)跳過(guò)row null和所有單元格都為空的行合并單元格場(chǎng)景建議在 Excel 側(cè)拆開(kāi)工具側(cè)處理合并單元格成本高且容易出錯(cuò)。4.5 批量執(zhí)行時(shí)事務(wù)太大撐爆內(nèi)存現(xiàn)象JDBC 批量寫(xiě)入幾萬(wàn)條時(shí) OOM 或超時(shí)。原因一次性addBatch太多或者沒(méi)分批提交。解決每 500 到 1000 條執(zhí)行一次executeBatch并commit然后清空批次。這個(gè)閾值按數(shù)據(jù)庫(kù)和網(wǎng)絡(luò)情況調(diào)沒(méi)有萬(wàn)能值。5. 進(jìn)階技巧用 DataFormatter 兜底與生成前的結(jié)構(gòu)校驗(yàn)5.1 用 DataFormatter 拿到「所見(jiàn)即所得」的值前面getCellValue是按類(lèi)型手動(dòng)轉(zhuǎn)換遇到復(fù)雜格式百分比、貨幣、自定義格式還是會(huì)翻車(chē)。更穩(wěn)的做法是用 POI 的DataFormatter它按單元格顯示格式輸出字符串// 用 DataFormatter 按顯示格式取值兜底復(fù)雜格式 DataFormatter formatter new DataFormatter(); FormulaEvaluator evaluator workbook.getCreationHelper().createFormulaEvaluator(); for (int j 0; j columns.size(); j) { Cell cell row.getCell(j); // 第二個(gè)參數(shù)傳 evaluator公式單元格也能取到計(jì)算結(jié)果 String value formatter.formatCellValue(cell, evaluator); record.put(columns.get(j), value.trim()); }邏輯說(shuō)明formatCellValue會(huì)尊重單元格的數(shù)字格式日期就是日期字符串百分比就是50%不會(huì)出現(xiàn)序列號(hào)。傳evaluator是為了讓公式單元格返回計(jì)算結(jié)果而不是公式本身。代價(jià)是它輸出的是「顯示值」如果 Excel 列寬不夠顯示成####取出來(lái)也可能是異常值所以列寬要正常。我一般會(huì)把這個(gè)方法和手動(dòng)轉(zhuǎn)換結(jié)合先用DataFormatter取再對(duì)日期和長(zhǎng)數(shù)字做二次校正。純手動(dòng)轉(zhuǎn)換適合格式規(guī)整的數(shù)據(jù)DataFormatter適合格式五花八門(mén)的業(yè)務(wù)表格。5.2 生成前先校驗(yàn)表結(jié)構(gòu)與其等 SQL 執(zhí)行報(bào)錯(cuò)不如生成前先連庫(kù)查一次表結(jié)構(gòu)把 Excel 表頭和數(shù)據(jù)庫(kù)字段做比對(duì)// 查詢(xún)目標(biāo)表字段和 Excel 表頭比對(duì) public SetString getTableColumns(Connection conn, String tableName) throws SQLException { SetString columns new HashSet(); DatabaseMetaData meta conn.getMetaData(); try (ResultSet rs meta.getColumns(null, null, tableName, null)) { while (rs.next()) { columns.add(rs.getString(COLUMN_NAME).toLowerCase()); } } return columns; }拿到字段集合后遍歷 Excel 表頭凡是數(shù)據(jù)庫(kù)里沒(méi)有的字段直接報(bào)出來(lái)讓人去改 Excel而不是生成一堆注定失敗的 SQL。這一步能省掉大量「執(zhí)行到一半報(bào)錯(cuò)、回滾、改、重來(lái)」的循環(huán)。5.3 一個(gè)我踩過(guò)的坑有次給一個(gè)配置表導(dǎo)數(shù)據(jù)Excel 里有個(gè)字段叫status值是1和0我按字符串處理生成了1和0結(jié)果數(shù)據(jù)庫(kù)列是 tinyintMySQL 隱式轉(zhuǎn)換居然沒(méi)報(bào)錯(cuò)數(shù)據(jù)也進(jìn)去了。但后來(lái)另一個(gè)字段amount是 decimalExcel 里填了100.50我按字符串生成100.50插入時(shí)精度被截成了100查了半天才發(fā)現(xiàn)是引號(hào)導(dǎo)致類(lèi)型沒(méi)對(duì)上。從那以后我每次生成腳本前都會(huì)強(qiáng)制走一遍「字段類(lèi)型比對(duì)」把數(shù)據(jù)庫(kù)列類(lèi)型和 Excel 值的形態(tài)對(duì)一遍數(shù)字列不加引號(hào)字符串列加引號(hào)日期列用標(biāo)準(zhǔn)格式。這個(gè)習(xí)慣幫我省了至少三次線上排查。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取