Dim fileName As String
Me.OpenFileDialog1.Filter = "Excle文件(*.xls)|*.xls"
If Me.OpenFileDialog1.ShowDialog() = DialogResult.OK Then
fileName = Me.OpenFileDialog1.FileName
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties=Excel 8.0;"
Dim myDataset As New DataSet
Dim da As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
Try
da.Fill(myDataset)
Me.DataGrid1.DataSource = myDataset.Tables(0)
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
End If
读EXCEL:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Dim myStream As System.IO.Stream
OpenFileDialog1.InitialDirectory = My.Computer.FileSystem.CurrentDirectory
OpenFileDialog1.Filter = "excel files (*.xls)|*.xls|All files (*.*)|*.*"
OpenFileDialog1.FilterIndex = 2
OpenFileDialog1.RestoreDirectory = True
If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
Dim fileName As String
fileName = Me.OpenFileDialog1.FileName
'建立EXCEL连接,读入数据
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties=Excel 8.0;"
'Dim myDataset As New DataSet
Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
Try
da.Fill(myDataset)
Me.DataGridView1.DataSource = myDataset.Tables(0)
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
End If
End Sub
写入SQL:
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'建立SQL连接,写入数据
Dim sqlconnection1 As New SqlConnection
Dim sconnstring As String = "server=su;uid=sa;pwd=;database=XXX"
sqlconnection1.ConnectionString = (sconnstring)
sqlconnection1.Open()
myDataset.Tables(0).Columns.Add("结果")
Dim i, j As New Integer
i = myDataset.Tables(0).Rows.Count - 1
j = 0
'i = Me.DataGridView1.RowCount
'j = Me.DataGridView1.ColumnCount
Me.ProgressBar1.Visible = True
ProgressBar1.Maximum = i
For i = 0 To i
Try
Dim insertstr As String = "insert into customer (ccuscode,ccusname,ccccode,cCusExch_name,dCusDevDate,cCusAbbName) values ('" + myDataset.Tables(0).Rows(i).Item(0).ToString + "','" + myDataset.Tables(0).Rows(i).Item(1).ToString + "','" + myDataset.Tables(0).Rows(i).Item(2).ToString + "','" + myDataset.Tables(0).Rows(i).Item(3).ToString + "','" + myDataset.Tables(0).Rows(i).Item(4).ToString + "','" + myDataset.Tables(0).Rows(i).Item(5).ToString + "')"
Dim cmd As SqlCommand = New SqlCommand(insertstr, sqlconnection1)
cmd.ExecuteNonQuery()
myDataset.Tables(0).Rows(i).Item("结果") = "写入成功"
j = j + 1
Catch ex As Exception
myDataset.Tables(0).Rows(i).Item("结果") = ex.Message.ToString
'MsgBox(ex.Message.ToString)
End Try
ProgressBar1.Value = i
Next
ProgressBar1.Visible = False
MsgBox("成功导入" & j & "条记录")
sqlconnection1.Close()
End Sub