Get Excel Worksheet name Runtime

[Guest]

Hi

How can I get the Worsheet name in my SELECT statement using ADO.NET

"SELECT * FROM [mysheet$]

I do not know what mysheet$ name is

Yama

 

[William Ryan eMVP]

I'm not sure how to do it via ADO.NET, but you can open the workbook
through Interop and find it out from Worksheets("Sheet1").Name (you can
walk the collection and get a list of all of the names.

Also, although I haven't tried it yet, I think
OleDbConnection.GetOleDbSchemaTable may be able to retrieve that information
(I'm pretty sure I saw this done elsewhere but I can't be positive since I
can't find it now).

ComInterop is a pain but it will work if the GetSchemeTable won't.

HTH,

Bill

 

[Guest]

Hello William

Thanks to you I figured it out..
After doing some research

Dim ds As New DataSe
'Open a file for reading WHERE Upload is a Virtual Site mapped to a folder containing the excel fil
Dim oFile As String = "MyExcelSpreadsheet.xls
Dim m_fileName As String = Server.MapPath("/Upload/" + oFile
Dim conn As New OleDbConnectio
'Connection to uploaded fil
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" &
"Data Source=" + m_fileName + ";" + "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
Tr
conn.ConnectionString = strCon
conn.Open(
Dim dtTables As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing
Dim tableName As String = dtTables.Rows(0)(dtTables.Columns.Item(2).ColumnName()

Dim strSQL As String = "SELECT * " &
" FROM [" + tableName + "]
Dim da As New OleDbDataAdapter(strSQL, conn

da.TableMappings.Add("Table", oFile.Replace(".xls", "")
da.Fill(ds

If ds.Tables(0).Rows.Count < 1 The
Response.Write("No data!"
Exit Su
End I

For i As Integer = 0 To ds.Tables(0).Rows.Count -
Dim pn As String = ds.Tables(0).Rows(i)(0).ToStrin
Dim fn As String = ds.Tables(0).Rows(i)(3).ToStrin
Dim mn As String = ds.Tables(0).Rows(i)(4).ToStrin
Response.Write(pn + " " + fn + " " + mn
Nex
Catch eX_SQL As OleDbException
Exit Su
Catch ex As Exception
Throw New Exceptio
Finall
conn.close(
conn.Dispose(
End Tr

Yama Kamya

----- William Ryan eMVP wrote: ----

I'm not sure how to do it via ADO.NET, but you can open the workboo
through Interop and find it out from Worksheets("Sheet1").Name (you ca
walk the collection and get a list of all of the names

Also, although I haven't tried it yet, I thin
OleDbConnection.GetOleDbSchemaTable may be able to retrieve that informatio
(I'm pretty sure I saw this done elsewhere but I can't be positive since
can't find it now)

ComInterop is a pain but it will work if the GetSchemeTable won't

HTH

Bil

Hi

How can I get the Worsheet name in my SELECT statement using ADO.NET
"SELECT * FROM [mysheet$]
I do not know what mysheet$ name is
Yam

 

[William Ryan eMVP]

Yama:

I'm glad it worked and thanks for posting the code... I'm sure this question
will come up again.

Cheers,

Bill

Hello William,

Thanks to you I figured it out...
After doing some research:

Dim ds As New DataSet
'Open a file for reading WHERE Upload is a Virtual Site mapped

to a folder containing the excel file

Dim oFile As String = "MyExcelSpreadsheet.xls"
Dim m_fileName As String = Server.MapPath("/Upload/" + oFile)
Dim conn As New OleDbConnection
'Connection to uploaded file
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" + m_fileName + ";" + "Extended

Properties=""Excel 8.0;HDR=YES;IMEX=1"""

Try
conn.ConnectionString = strConn
conn.Open()
Dim dtTables As DataTable =

conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

Dim tableName As String = dtTables.Rows(0)(dtTables.Columns.Item(2).ColumnName())

Dim strSQL As String = "SELECT * " & _
" FROM [" + tableName + "]"
Dim da As New OleDbDataAdapter(strSQL, conn)

da.TableMappings.Add("Table", oFile.Replace(".xls", ""))
da.Fill(ds)

If ds.Tables(0).Rows.Count < 1 Then
Response.Write("No data!")
Exit Sub
End If

For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
Dim pn As String = ds.Tables(0).Rows(i)(0).ToString
Dim fn As String = ds.Tables(0).Rows(i)(3).ToString
Dim mn As String = ds.Tables(0).Rows(i)(4).ToString
Response.Write(pn + " " + fn + " " + mn)
Next
Catch eX_SQL As OleDbException
Exit Sub
Catch ex As Exception
Throw New Exception
Finally
conn.close()
conn.Dispose()
End Try

Yama Kamyar

----- William Ryan eMVP wrote: -----

I'm not sure how to do it via ADO.NET, but you can open the workbook
through Interop and find it out from Worksheets("Sheet1").Name (you can
walk the collection and get a list of all of the names.

Also, although I haven't tried it yet, I think
OleDbConnection.GetOleDbSchemaTable may be able to retrieve that information
(I'm pretty sure I saw this done elsewhere but I can't be positive since I
can't find it now).

ComInterop is a pain but it will work if the GetSchemeTable won't.

HTH,

Bill

Hi,

How can I get the Worsheet name in my SELECT statement using ADO.NET?
"SELECT * FROM [mysheet$]"
I do not know what mysheet$ name is.
Yama