'Load Excel to Datagridview VB Net
Private Sub ButtonLoadExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonLoadExcel.Click
' ket noi toi Excel system
Dim Excel03ConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"
Dim Excel07ConString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"
' load excel file to DATAGRIDVIEW
Dim dialog As OpenFileDialog = New OpenFileDialog
dialog.ShowDialog()
Dim filePath As String = dialog.FileName
Dim extension As String = Path.GetExtension(filePath)
Dim header As String = If(rbHeaderYES.Checked, "YES", "NO") ' Set Header by RadioButton (rbHeaderYes)
Dim conStr As String, sheetName As String
conStr = String.Empty
Select Case extension
Case ".xls"
'Excel 97-03
conStr = String.Format(Excel03ConString, filePath, Header)
Exit Select
Case ".xlsx"
'Excel 07
conStr = String.Format(Excel07ConString, filePath, Header)
Exit Select
End Select
'Get the name of the First Sheet.
Try
Using con As New OleDbConnection(conStr)
Using cmd As New OleDbCommand()
cmd.Connection = con
con.Open()
Dim dtExcelSchema As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
sheetName = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
con.Close()
End Using
End Using
'Read Data from the First Sheet.
Using con As New OleDbConnection(conStr)
Using cmd As New OleDbCommand()
Using oda As New OleDbDataAdapter()
Dim dt As New DataTable()
cmd.CommandText = (Convert.ToString("SELECT * From [") & sheetName) + "]"
cmd.Connection = con
con.Open()
oda.SelectCommand = cmd
oda.Fill(dt)
con.Close()
'Populate DataGridView.
DataGridView1.DataSource = dt
End Using
End Using
End Using
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Nguồn: Sharecode.vn