Imports System.Data.OleDb
Public Class ImportOrderForm
Private Sub BrowseButt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BrowseButt.Click
Try
DataGridView1.Rows.Clear()
Dim anydata As String()
Dim objDataAdapter As New OleDbDataAdapter()
objDataAdapter.SelectCommand = ExcelConnection()
Dim objDataSet As New DataSet()
objDataAdapter.Fill(objDataSet)
For i As Integer = 0 To objDataSet.Tables(0).Rows.Count - 1
'MsgBox(objDataSet.Tables(0).Rows(i).Item(6).ToString)
anydata = New String() {(DateTimePicker1.Value.Date), _
objDataSet.Tables(0).Rows(i).Item(0).ToString, _
objDataSet.Tables(0).Rows(i).Item(1).ToString, _
objDataSet.Tables(0).Rows(i).Item(2).ToString, _
objDataSet.Tables(0).Rows(i).Item(3).ToString, _
objDataSet.Tables(0).Rows(i).Item(4).ToString, _
objDataSet.Tables(0).Rows(i).Item(5).ToString, _
objDataSet.Tables(0).Rows(i).Item(6).ToString, _
objDataSet.Tables(0).Rows(i).Item(7).ToString, _
objDataSet.Tables(0).Rows(i).Item(8).ToString} ', _
DataGridView1.Rows.Add(anydata)
Next
objDataSet.Clear()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Protected Function ExcelConnection() As OleDbCommand
Dim OpenFileDialog1 As New OpenFileDialog
Dim ss As String
OpenFileDialog1.InitialDirectory = "c:\"
OpenFileDialog1.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*"
OpenFileDialog1.FilterIndex = 1
OpenFileDialog1.RestoreDirectory = True
If (OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK) Then
ss = OpenFileDialog1.FileName
txtBrowse.Text = ss
End If
Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ss & ";Extended Properties=Excel 8.0;"
Dim objXConn As New OleDbConnection(xConnStr)
objXConn.Open()
Dim objCommand As New OleDbCommand("SELECT * FROM [ItemMaster$]", objXConn)
Return objCommand
End Function
End Class