1. 打开Excel,依次打开“文件”-“选项信任中心”-“信任中心设置”-“宏设置”-启用VBA宏;
2. 在“开发工具”中点击“宏”,创建一个宏并命名,编写宏代码,如下参考:
Sub ExportToMySQL()
Dim conn As Object
Dim rs As Object
Dim sql As String
Dim ws As Worksheet
Dim lastRow As Long, i As Long
' 创建连接对象
Set conn = CreateObject("ADODB.Connection")
' 连接字符串
Dim connString As String
connString = "Driver={MySQL ODBC 9.0 Unicode Driver};" & _
"Server=localhost;" & _
"Database=rfid_data;" & _
"User=root;" & _
"Password=123321;" & _
"Option=3;"
' 打开连接
conn.Open connString
' 指定数据来源工作表
Set ws = ThisWorkbook.Sheets("生产记录") ' 更改为你的工作表名称
' 获取最后一行
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' 循环遍历每一行
For i = 2 To lastRow ' 假设第1行是表头
sql = "INSERT INTO product (Odate, Id, name, spc, chip, qty, customer, Indate, remark) VALUES (" & _
"'" & ws.Cells(i, 1).Value & "', " & _
"'" & ws.Cells(i, 2).Value & "', " & _
"'" & ws.Cells(i, 3).Value & "', " & _
"'" & ws.Cells(i, 4).Value & "', " & _
"'" & ws.Cells(i, 5).Value & "', " & _
"'" & ws.Cells(i, 6).Value & "', " & _
"'" & ws.Cells(i, 7).Value & "', " & _
"'" & ws.Cells(i, 8).Value & "', " & _
"'" & ws.Cells(i, 9).Value & "')"
' 执行 SQL 语句
conn.Execute sql
Next i
' 关闭连接
conn.Close
Set conn = Nothing
MsgBox "数据导出完毕!"
End Sub
3. 能正常运行宏的前提是安装了正确版本的 MySQL ODBC 驱动,在控制面板-系统和安全-windows工具-ODBC数据源,用户DNS或系统DNS中添加安装好的驱动。