How to access Excel Spreadsheets

[Acephalus]

I am currently using this to get data from an .xls
file:string conn =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + file + ";" +
"Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbDataAdapter adapter =
new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM
[Sheet1$]",conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
The problem is there has to be a sheet in that .xsl file, called
"Sheet1", otherwise the program generates an exception. Is there a
way to access sheets by index rather than name? Or maybe a better way
of getting data from .xsl files overall?

 

[Juan Ramon Martinez]

I have not accessed sheets by index, I have however use this code to access
single sheet excel files, regardless of the sheet's name

public String getFirstSheet(){
OleDbConnection connection=null;
try{
connection=getConnection(); //This contains your logic to obtan a
connection to the excel file
System.Data.DataTable lookup;
connection.Open();
lookup
=connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,nul
l);
connection.Close();
connection.Dispose();
System.String table;
table = (System.String)lookup.Rows[0]["TABLE_NAME"];
if(table.Length > 2 && table.StartsWith("$") && table.EndsWith("$")){
table = table.Substring(1, table.Length - 2);
}
return table;
}
catch(Exception ex){
if (connection==null){
connection.Dispose();
}
throw new ApplicationException("No se pudo abrir archivo",ex);
}
}

Lookup.Rows contains information about the sheets in the book, it is however
sorted alfabetically.

Anyways, hope this helps

JR

I am currently using this to get data from an .xls
file:string conn =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + file + ";" +
"Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbDataAdapter adapter =
new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM
[Sheet1$]",conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
The problem is there has to be a sheet in that .xsl file, called
"Sheet1", otherwise the program generates an exception. Is there a
way to access sheets by index rather than name? Or maybe a better way
of getting data from .xsl files overall?



----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption

=---

 

[Acephalus]

Thanks a lot! That .GetOleDbSchemaTable() method gives me all the
sheets in the file so I can ask the user to select one. Thanks again

 

[Guest]

Any idea on how to get the table list in default order as in Excel instead of alphabetical order

Thanks

----- Juan Ramon Martinez wrote: ----

I have not accessed sheets by index, I have however use this code to acces
single sheet excel files, regardless of the sheet's nam

public String getFirstSheet()
OleDbConnection connection=null
try
connection=getConnection(); //This contains your logic to obtan
connection to the excel fil
System.Data.DataTable lookup
connection.Open()
looku
=connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,nu
l)
connection.Close()
connection.Dispose()
System.String table
table = (System.String)lookup.Rows[0]["TABLE_NAME"]
if(table.Length > 2 && table.StartsWith("$") && table.EndsWith("$"))
table = table.Substring(1, table.Length - 2)

return table

catch(Exception ex)
if (connection==null)
connection.Dispose()

throw new ApplicationException("No se pudo abrir archivo",ex)



Lookup.Rows contains information about the sheets in the book, it is howeve
sorted alfabetically

Anyways, hope this help

J

I am currently using this to get data from an .xl
file:string conn
"Provider=Microsoft.Jet.OLEDB.4.0;"
"Data Source=" + file + ";"
"Extended Properties=Excel 8.0;"
System.Data.OleDb.OleDbDataAdapter adapter
new System.Data.OleDb.OleDbDataAdapter("SELECT * FRO
[Sheet1$]",conn)
DataSet ds = new DataSet()
adapter.Fill(ds)
The problem is there has to be a sheet in that .xsl file, calle
"Sheet1", otherwise the program generates an exception. Is there
way to access sheets by index rather than name? Or maybe a better wa
of getting data from .xsl files overall News==---
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,00 Newsgroup
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryptio

=--