mysql - 如何将excel文件导入mysql

Question

这是我得到的代码，仅将 excel 导入到 datagridview。如何使用vb.net将excel导入mysql数据库？

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    OpenFileDialog1.ShowDialog()
End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Try

        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim dataSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

        MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + TextBox1.Text + ";Extended Properties=Excel 12.0;")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)

        dataSet = New System.Data.DataSet
        MyCommand.Fill(dataSet)
        DataGridView1.DataSource = dataSet.Tables(0)

        MyConnection.Close()
    Catch ex As Exception
        MsgBox(ex.Message.ToString)
    End Try
End Sub

Private Sub OpenFileDialog1_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
    TextBox1.Text = OpenFileDialog1.FileName
End Sub

Answer 1

无论你使用VB还是其他语言。

• 读取文件并循环

• 只需转换excel column_title得到像 (column1, column2, column3, ...) 这样的字符串

• 然后将每个 Excel 行连接到values_variable:

(value1, value2, value3, ...), 
(value1, value2, value3, ...),
(value1, value2, value3, ...)

• 因此，您只需在循环末尾创建一个 INSERT 查询即可:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...), (value1, value2, value3, ...), (value1, value2, value3, ...), ... ;