470,831 Members | 1,407 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,831 developers. It's quick & easy.

importing Excel files to datagrids

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
Nov 20 '05 #1
3 8779
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
--
Joe Fallon

"Conrad F" <co*********@hotmail.com> wrote in message
news:6a**************************@posting.google.c om...
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

Nov 20 '05 #2
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
"Conrad F" <co*********@hotmail.com> wrote in message
news:6a**************************@posting.google.c om...
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

Nov 20 '05 #3
On 18 Sep 2003 07:18:52 -0700, co*********@hotmail.com (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=Micros oft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book10.xls;Extended Properties=Excel 8.0;")

ExcelConnection.Open()

ExcelTables =
ExcelConnection.GetOleDbSchemaTable(System.Data.Ol eDb.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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Steve Chatham | last post: by
reply views Thread by Steve Chatham | last post: by
9 posts views Thread by | last post: by
5 posts views Thread by hharriel | last post: by
1 post views Thread by =?Utf-8?B?Sm9obiBXYWxrZXI=?= | last post: by
3 posts views Thread by scoots987 | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.