- 在 Excel 中，按
Alt + F11打开 VBA 编辑器。 - 在菜单栏选择
插入->模块，在新模块中粘贴以下代码。
VBA 代码
Sub GenerateSQLInsertStatementsToFile()Dim ws As WorksheetDim lastRow As Long, lastCol As Long, i As Long, j As LongDim sql As String, colNames As String, values As StringDim tableName As StringDim cellValue As StringDim filePath As StringDim fileNum As Integer' 设置工作表Set ws = ActiveSheet ' 使用当前活动的工作表tableName = "your_table_name" ' 设置你的数据库表名filePath = "D:\file.txt" ' 设置文件路径' 打开文件fileNum = FreeFile()Open filePath For Output As #fileNum' 获取数据范围lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).RowlastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column' 构建列名字符串For j = 1 To lastColIf j > 1 Then colNames = colNames & ", "colNames = colNames & "[" & ws.Cells(1, j).Value & "]"Next j' 遍历每一行For i = 2 To lastRowvalues = ""' 遍历每一列For j = 1 To lastColIf j > 1 Then values = values & ", "' 处理单引号以适应 SQL 语法cellValue = Replace(ws.Cells(i, j).Value, "'", "''")' 值If IsNumeric(cellValue) Thenvalues = values & cellValueElsevalues = values & "'" & cellValue & "'"End IfNext j' 构建 SQL 语句sql = "INSERT INTO " & tableName & " (" & colNames & ") VALUES (" & values & ");"' 写入文件Print #fileNum, sqlNext i' 关闭文件Close #fileNum
End Sub
Excel 并按 Alt + F8，选择 GenerateSQLInsertStatementsToFile，然后点击 运行
