importing Excel files to datagrids

  • Thread starter Thread starter Conrad F
  • Start date Start date
C

Conrad F

Hello All,

I know how to import a specific named excel sheet into a datagrid
using ADO.NET by setting up a JET connection and then SELECTing data
from the sheet. However, for a real world application, I would
typically not know what the names of the sheets are going to be before
I open the Excel file.

I am wondering if there is a SELECT statement that would get me the
list of sheet names so that I can place them in a combobox and
selecting one will load the appropriate sheet in a datagrid. *** I am
trying to do this without resorting to using COM and the Office object
model!!!! ***. I am hoping some bright, well informed person knows
if there is some undocumented SELECT statement I can use to get
information from the Excel file.

Thanks,

Conrad
 
I have a form that has a textbox named miscinfo which is where the user is
supposed to type the name of the sheet they want to import. When they Enter
it, this code runs to provide them with a list of sheets for Excel or a list
of Tables for Access. The results are placed in a Listbox named lbTableNames
which is right next to the textbox. The user can either type the name
directly or select the entry in the listbox.


Private Sub miscinfo_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles miscinfo.Enter
Dim strConn, strFileFormat, strLocalFileName As String
Dim TableNames As DataTable
Dim objDAO As DAO

Try
Cursor.Current = Cursors.WaitCursor
strLocalFileName = Me.txtPath.Text
strFileFormat = Me.lbFileFormat.Text

If strFileFormat = "Excel" Or strFileFormat = "Access" Then
If strLocalFileName <> String.Empty Then
Me.lbTableNames.Visible = True
If strFileFormat = "Excel" Then
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strLocalFileName & ";Extended Properties=""Excel 8.0;HDR=YES"""
'get the list of tables in the Excel file
'sheets end with $ and named ranges do not.
End If
If strFileFormat = "Access" Then
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strLocalFileName
'get the list of tables in the Access mdb file
End If

objDAO = New DAO(strConn)
objDAO.GetSchema(TableNames, strSQL, "OLEDB")

With Me.lbTableNames
.DisplayMember = "TABLE_NAME"
.ValueMember = "TABLE_NAME"
.DataSource = TableNames
.SelectedIndex = -1
End With
End If
End If

Catch ex As Exception
MessageBox.Show(ex.Message, ex.Source & " - " & ex.TargetSite.Name,
MessageBoxButtons.OK, MessageBoxIcon.Information)
Finally
objDAO = Nothing
Cursor.Current = Cursors.Default
End Try
End Sub
==========================================================

Private Sub lbTableNames_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
lbTableNames.SelectedIndexChanged
Me.miscinfo.Text = CStr(Me.lbTableNames.SelectedValue)
End Sub
==========================================================

My DAO class has a GetSchema method:

Public Sub GetSchema(ByRef dt As DataTable, ByVal strSQL As String, ByVal
DBtype As String)
'retrieve structure information into a datatable using the FillSchema
method of a DataAdapter object
If DBtype = "SQL Server" Then
Dim da As SqlDataAdapter
Dim cnn As New SqlConnection(mConnStr)
Dim cmd As New SqlCommand(strSQL, cnn)
Try
cmd.CommandType = CommandType.Text
cnn.Open()
da = New SqlClient.SqlDataAdapter(cmd)
da.FillSchema(dt, SchemaType.Source)

Catch exc As Exception

Finally
cnn.Close()
End Try
ElseIf DBtype = "Oracle" Then
Dim da As OracleDataAdapter
Dim cnn As New OracleConnection(mConnStr)
Dim cmd As New OracleCommand(strSQL, cnn)
Try
cmd.CommandType = CommandType.Text
cnn.Open()
da = New OracleClient.OracleDataAdapter(cmd)
da.FillSchema(dt, SchemaType.Source)
Catch exc As Exception

Finally
cnn.Close()
End Try
ElseIf DBtype = "OLEDB" Then
Dim cnn As New OleDbConnection(mConnStr)
Try
cnn.Open()
'KB309488 - the Object array is for filtering the returned data
(only TABLEs are returned not Views, etc.)
dt = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object()
{Nothing, Nothing, Nothing, "TABLE"})
Catch exc As Exception

Finally
cnn.Close()
End Try
End If
End Sub
 
Open the excel file.. You can foreach the Sheets collection and each sheet
will have a name property. You don't want to use COM or Office, but how
else would you know what to select from? You'll need to know a sheet name
to use a select, but since you can walk through the collection of sheets in
probably 3-5 lines of code....

HTH,

Bill
 
On 18 Sep 2003 07:18:52 -0700, (e-mail address removed) (Conrad F) wrote:

¤ Hello All,
¤
¤ I know how to import a specific named excel sheet into a datagrid
¤ using ADO.NET by setting up a JET connection and then SELECTing data
¤ from the sheet. However, for a real world application, I would
¤ typically not know what the names of the sheets are going to be before
¤ I open the Excel file.
¤
¤ I am wondering if there is a SELECT statement that would get me the
¤ list of sheet names so that I can place them in a combobox and
¤ selecting one will load the appropriate sheet in a datagrid. *** I am
¤ trying to do this without resorting to using COM and the Office object
¤ model!!!! ***. I am hoping some bright, well informed person knows
¤ if there is some undocumented SELECT statement I can use to get
¤ information from the Excel file.

Public Function ListExcelTablesNET() As Boolean

Dim ExcelConnection As System.Data.OleDb.OleDbConnection
Dim ExcelTables As DataTable

Try

ExcelConnection = New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book10.xls;Extended Properties=Excel 8.0;")

ExcelConnection.Open()

ExcelTables =
ExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing,
Nothing, Nothing, "TABLE"})

frmMain.DataGrid1.DataSource = ExcelTables

Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
ExcelConnection.Close()

End Try

End Function


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top