mysql - VB - 连接到本地 SQL 服务器并将数据从 Excel 加载到表中

Question

我想使用 VB 代码连接到本地 SQL Server 并将数据从 Excel 文件加载到表中。这是我到目前为止所得到的不完整的代码。我在 SQL 数据库中创建的表 (me_table) 的字段是 z、ad、ag、retd、to、wg，在 Excel Sheet1 中是包含反射(reflect)表中字段的数据的列。谢谢

请指教

Imports System.Data
Imports System.Data.SqlClient

Module Module1
    Dim myconnection As SqlConnection
    Dim mycommand As SqlCommand
    Dim dr As SqlDataReader
    Dim dr1 As SqlDataReader
    Dim ra As Integer

    Sub Main()

        Dim connectionString As String = "Server=DER7D;Database=testDB;User Id=DER7D\Der;Password="
        myconnection = New SqlConnection("server=DER7D;uid=root;pwd=;database=simple")
        'you need to provide password for sql server
        myconnection.Open()

    End Sub

End Module

Answer 1

不考虑好的设计，这就是你所需要的。只需更新 SqlConnection 字符串即可。如果您决定要使用多个工作表，只需将它们添加到工作表变量中即可。

Private Sub SaveDataFromSpreadsheet()
Dim filePath = "directory\me_spreadsheet.xlsx"
Dim connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", filePath)
Dim worksheets As New List(Of String)() From {
  "Sheet1"
}

For i As Integer = 0 To worksheets.Count - 1
  Dim worksheetName As String = worksheets(i)
  Dim adapter = New OleDbDataAdapter(String.Format("SELECT * FROM [{0}$]", worksheetName), connectionString)
  Dim ds = New DataSet()

  adapter.Fill(ds, "me_table")

  Dim data As DataTable = ds.Tables("me_table")

  For x As Integer = 0 To data.Rows.Count - 1
    Dim z As String = If(String.IsNullOrEmpty(data.Rows(x).ItemArray(0).ToString()), "", data.Rows(x).ItemArray(0).ToString())
    Dim ad As String = If(String.IsNullOrEmpty(data.Rows(x).ItemArray(1).ToString()), "", data.Rows(x).ItemArray(1).ToString())
    Dim ag As String = If(String.IsNullOrEmpty(data.Rows(x).ItemArray(2).ToString()), "", data.Rows(x).ItemArray(2).ToString())
    Dim retd As String = If(String.IsNullOrEmpty(data.Rows(x).ItemArray(3).ToString()), "", data.Rows(x).ItemArray(3).ToString())
    Dim wg As String = If(String.IsNullOrEmpty(data.Rows(x).ItemArray(4).ToString()), "", data.Rows(x).ItemArray(4).ToString())

    Using myconnection As New SqlConnection("Data Source=Your-Server;Initial Catalog=me_database;Integrated Security=True")
      myconnection.Open()
      Dim mycommand As New SqlCommand("INSERT INTO me_Table(z, ad, ag, retd, wg) VALUES(@z, @ad, @ag, @retd, @wg)", myconnection)

      mycommand.Parameters.Add(New SqlParameter("@z", z))
      mycommand.Parameters.Add(New SqlParameter("@ad", ad))
      mycommand.Parameters.Add(New SqlParameter("@ag", ag))
      mycommand.Parameters.Add(New SqlParameter("@retd", retd))
      mycommand.Parameters.Add(New SqlParameter("@wg", wg))

      mycommand.ExecuteNonQuery()
      myconnection.Close()
    End Using

  Next
Next
End Sub