美国留学

Imports System.Data.SqlClient

Imports System.Data.OleDb

Public Class Form 1

Private DBconnString As String = "Server=CW.shscth.com.cn;Database=SCTH;Integrated Security=True;"

Private Sub Button导入_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button导入.Click

Try

Dim openFileDialog As New OpenFileDialog()

openFileDialog.Filter = "Excel Files|*.xls;*.xlsx"

openFileDialog.Title = "选择Excel文件"

If openFileDialog.ShowDialog() = DialogResult.OK Then

Dim filePath As String = openFileDialog.FileName

Dim connectionString As String = ""

If filePath.EndsWith(".xlsx") Then

connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties='Excel 12.0 xml;HDR=YES';"

Else

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath & ";Extended Properties='Excel 8.0;HDR=YES';"

End If

Using oleDbConn As New OleDbConnection(connectionString)

oleDbConn.Open()

Dim dtSchema As DataTable = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

Dim sheetName As String = dtSchema.Rows(0)("TABLE_NAME").ToString()

Dim query As String = "SELECT * FROM [" & sheetName & "]"

Dim adapter As New OleDbDataAdapter(query, oleDbConn)

Dim dt As New DataTable()

adapter.Fill(dt)

Using sqlConn As New SqlConnection(DBconnString)

sqlConn.Open()

For Each row As DataRow In dt.Rows

Dim sql As String = "INSERT INTO 进口清单(部品番号,数量,净重,毛重,金额,原产地) VALUES (@部品番号,@数量,@净重,@毛重,@金额,@原产地)"

Using cmd As New SqlCommand(sql, sqlConn)

'根据EXCEL列名映射到数据库表列名,添加参数

cmd.Parameters.AddWithValue("@部品番号", row("部品番号"))

cmd.Parameters.AddWithValue("@数量", row("数量"))

cmd.Parameters.AddWithValue("@净重", row("净重"))

cmd.Parameters.AddWithValue("@毛重", row("毛重"))

cmd.Parameters.AddWithValue("@金额", row("金额"))

cmd.Parameters.AddWithValue("@原产地", row("原产地"))

cmd.ExecuteNonQuery()

End Using

Next

End Using

End Using

MessageBox.Show("导入完成!")

Dim dt2 As New DataTable()

Dim sql2 As String = "SELECT * FROM 进口清单"

Dim da As New SqlDataAdapter(sql2, DBconnString)

da.Fill(dt2)

DataGridView1.DataSource = dt2

Else

LoadData()

End If

Catch ex As Exception

MessageBox.Show("导入失败：" & ex.Message)

End Try

End Sub

版权声明：本文转载于今日头条，版权归作者所有，如果侵权，请联系本站编辑删除