VB.Net code to read data from excel 2003, 2007 and 2010

參考引用來源:VB.Net code to read data from excel 2003, 2007 and 2010
--

' Get Data from Excel 2003


Public Function GetExcel2003Data(ByVal PrmPathExcelFile As String, ByVal DataGrid1 As DataGrid)
 Dim MyConnection As System.Data.OleDb.OleDbConnection
 Try
  '' Fetch Data from Excel
  Dim DtSet As System.Data.DataSet
  Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

  MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
  "data source='" & PrmPathExcelFile & " '; " & "Extended Properties=Excel 8.0;")
 
  ' Select the data from Sheet1 of the workbook.
  MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection)

  MyCommand.TableMappings.Add("Table", "Attendence")
  DtSet = New System.Data.DataSet
  MyCommand.Fill(DtSet)
 
  DataGrid1.DataSource = DtSet.Tables(0)

 Catch ex As Exception
  Throw ex
 Finally
  MyConnection.Close()
 End Try
End Function




' Get Data from Excel 2007 or Excel 2010 ()


Public Function GetExcel2003Data(ByVal PrmPathExcelFile As String, ByVal DataGrid1 As DataGrid)
 Dim MyConnection As System.Data.OleDb.OleDbConnection
 Try
  '' Fetch Data from Excel
  Dim DtSet As System.Data.DataSet
  Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

  MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; " & _
  "data source='" & PrmPathExcelFile & " '; " & "Extended Properties=Excel 12.0;")
 
  ' Select the data from Sheet1 of the workbook.
  MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection)

  MyCommand.TableMappings.Add("Table", "Attendence")
  DtSet = New System.Data.DataSet
  MyCommand.Fill(DtSet)
 
  DataGrid1.DataSource = DtSet.Tables(0)

 Catch ex As Exception
  Throw ex
 Finally
  MyConnection.Close()
 End Try
End Function