Ejemplo: DataGridView con Excel 2000-2003, Excel 2007

Artikel
06/09/2008

Cuando actualice my Office a versión 2007, me di cuenta que algunas de las herramientas que había diseñado para mi equipo que manejaban datos de Excel ya no funcionaban. La razón es porque los archivos de Excel .xls ahora se guardan como .xlsx en Excel 2007. Entonces tuve que modificar mi código un poco. Antes que me olvide, añadí un fragmento de código en este ejemplo para deshabilitar la orden de columnas en la DataGridView.

Aquí estan ambas versiones…

Primero incluyo Imports System.Data.OleDb

Para Excel 2007 (.xlsx)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim fname As String = "C:\Book1.xlsx"

Dim ds As DataSet = New DataSet

Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fname & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

Dim strSQL As String = "SELECT * FROM [Sheet1$]"

Dim excelConnection As New OleDbConnection(connectionString)

Dim dbCommand As New OleDbCommand(strSQL, excelConnection)

Dim dataAdapter As New OleDbDataAdapter(dbCommand)

Dim dataTable As New DataTable()

Try

excelConnection.Open()

dataAdapter.Fill(dataTable)

BindingSource1.DataSource = dataTable

DataGridView1.DataSource = BindingSource1

'Deshabilitar orden de coulumna

Dim i As DataGridViewColumn

For Each i In DataGridView1.Columns

i.SortMode = DataGridViewColumnSortMode.NotSortable

Next i

Catch ex As Exception

MsgBox(ex.Message)

End Try

dataTable.Dispose()

dataAdapter.Dispose()

dbCommand.Dispose()

excelConnection.Close()

excelConnection.Dispose()

End Sub

Para Excel 97-2003 (.xls)

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Dim fname As String = "C:\Book.xls"

Dim da As New System.Data.Odbc.OdbcDataAdapter

Dim ds As DataSet = New DataSet

Dim connString As String = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & fname

Dim myRecords As Integer

Dim connection As New System.Data.Odbc.OdbcConnection(connString)

Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fname & ";Extended Properties=""Excel 12.0;HDR=YES;"""

Dim strSQL As String = "SELECT * FROM [Sheet1$]"

Dim excelConnection As New OleDbConnection(connectionString)

Dim dbCommand As New OleDbCommand(strSQL, excelConnection)

Dim dataAdapter As New OleDbDataAdapter(dbCommand)

Dim dTable As New DataTable()

Try

connection.Open()

da.SelectCommand = New System.Data.Odbc.OdbcCommand("Select * from [Sheet1$]", connection)

myRecords = da.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)