如何读取Excel文件并赋值给DataGridView对象呢
这里有两种方法
第一种(推荐)
Private Sub listExcel(ByVal dir)
Dim connectionString As String = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = " & dir & ";Extended Properties = 'Excel 12.0;HDR=YES';"
Dim strSQL As String = "SELECT * FROM [Sheet1$]"
Dim excelConnection As OleDbConnection = New OleDbConnection(connectionString)
Dim dbCommand As OleDbCommand = New OleDbCommand(strSQL, excelConnection)
Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter(dbCommand)
Try
excelConnection.Open()
dataAdapter.Fill(ds, "Excel")
DataGridView1.DataSource = ds.Tables("Excel").DefaultView
Catch sqlException As Exception
MsgBox(sqlException.ToString() & Chr(13) & "出现异常,Excel文件打开失败")
Exit Sub
Finally
dataAdapter.Dispose()
dbCommand.Dispose()
excelConnection.Close()
excelConnection.Dispose()
End Try
End Sub
第二种
Private Sub listExcel(ByVal dir)
Dim xlApp, xlBook, xlsheet
Dim y As Integer = 1
Dim i As Integer
Dim rows(18) As String
xlApp = CreateObject("Excel.Application")
Try
xlBook = xlApp.Workbooks.Open(dir)
Catch ioException As Exception
MsgBox(ioException.ToString & Chr(13) & "出现异常,Excel文件打开失败")
Exit Sub
End Try
xlsheet = xlBook.Worksheets(1)
While (Convert.ToString(xlsheet.Cells(l, 1).Value)) <> ""
y += 1
End While
With DataGridView1
.EditMode = DataGridViewEditMode.EditOnEnter
.ColumnCount = 19
.RowHeadersVisible = False
.Columns(0).Name = "计费号码"
.Columns(1).Name = "其它费"
.Columns(2).Name = "合计"
End With
For i = 2 To y
rows(0) = Convert.ToString(xlsheet.Cells(i, 1).Value)
rows(1) = Convert.ToString(xlsheet.Cells(i, 15).Value)
rows(2) = Convert.ToString(xlsheet.Cells(i, 16).Value)
Me.DataGridView1.Rows.Add(rows)
Next
End Sub
使用第一种方法速度非常快 *^_^*
这里有两种方法
第一种(推荐)
Private Sub listExcel(ByVal dir)
Dim connectionString As String = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = " & dir & ";Extended Properties = 'Excel 12.0;HDR=YES';"
Dim strSQL As String = "SELECT * FROM [Sheet1$]"
Dim excelConnection As OleDbConnection = New OleDbConnection(connectionString)
Dim dbCommand As OleDbCommand = New OleDbCommand(strSQL, excelConnection)
Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter(dbCommand)
Try
excelConnection.Open()
dataAdapter.Fill(ds, "Excel")
DataGridView1.DataSource = ds.Tables("Excel").DefaultView
Catch sqlException As Exception
MsgBox(sqlException.ToString() & Chr(13) & "出现异常,Excel文件打开失败")
Exit Sub
Finally
dataAdapter.Dispose()
dbCommand.Dispose()
excelConnection.Close()
excelConnection.Dispose()
End Try
End Sub
第二种
Private Sub listExcel(ByVal dir)
Dim xlApp, xlBook, xlsheet
Dim y As Integer = 1
Dim i As Integer
Dim rows(18) As String
xlApp = CreateObject("Excel.Application")
Try
xlBook = xlApp.Workbooks.Open(dir)
Catch ioException As Exception
MsgBox(ioException.ToString & Chr(13) & "出现异常,Excel文件打开失败")
Exit Sub
End Try
xlsheet = xlBook.Worksheets(1)
While (Convert.ToString(xlsheet.Cells(l, 1).Value)) <> ""
y += 1
End While
With DataGridView1
.EditMode = DataGridViewEditMode.EditOnEnter
.ColumnCount = 19
.RowHeadersVisible = False
.Columns(0).Name = "计费号码"
.Columns(1).Name = "其它费"
.Columns(2).Name = "合计"
End With
For i = 2 To y
rows(0) = Convert.ToString(xlsheet.Cells(i, 1).Value)
rows(1) = Convert.ToString(xlsheet.Cells(i, 15).Value)
rows(2) = Convert.ToString(xlsheet.Cells(i, 16).Value)
Me.DataGridView1.Rows.Add(rows)
Next
End Sub
使用第一种方法速度非常快 *^_^*