JuanPa_EC

Ok, I know you've read questions like this before, but I'm sure this is an special case.

I'm trying to read some data from an excel worksheet, I use the next code:

OleDbConnection cnx = new OleDbConnection();

string cnxStr = string.Empty;

OleDbDataAdapter da;

cnxStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\"" + file_name + "\"; Extended Properties=\"Excel 8.0; HDR=No; IMEX=1\"";

cnx.ConnectionString = cnxStr;

System.Data.DataTable dt;

string strComm = "select * from [" + sheet_name + "$" + first_cell + ":" + last_cell + "]";

da = new OleDbDataAdapter(@strComm, cnx);

dt = new System.Data.DataTable(nombre + " " + b);

try

{

da.FillSchema(dt, SchemaType.Source);

da.Fill(dt);

return dt;

}

catch (Exception ex)

{

Console.WriteLine("* * * * *ERROR: " + ex.ToString());

return null;

}

This code works fine if the variable "sheet_name" doesn't have spaces, like SHEET1. But I have a case in which it does contains spaces like "MEDIDAS DIARIAS EMPRESA", and I get a message than says than it caní»t find that sheet. Changing the name of the sheet is not an option because is a file that is downloaded and it must stay as is.

Can you help me with this



Re: Visual Studio Tools for Office Getting data from an Excel WorkSheet

Dennis Wallentin

Hi,

Will it make any change if You separate the sheet names into two categories and where sheetnames with spaces are handled as the following:

Const stCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Dt.xls;" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Const stSQL As String = "SELECT * FROM [Data Source$]"
Dim cn As New OleDbConnection(stCon)
cn.Open()
Dim da As New OleDbDataAdapter(stSQL, cn)
Dim ds As New DataSet
da.Fill(ds, "XLData")
MessageBox.Show(cn.ConnectionString)
cn.Close()
'Other stuff
ds = Nothing
da.Dispose()
cn = Nothing






Re: Visual Studio Tools for Office Getting data from an Excel WorkSheet

thorkia

I have no problem running the example you gave with the sheet name "MEDIDAS DIARIAS EMPRESA", using the code you provided as well.  Are you sure the sheet name doesn't have some strange characters

Try using:

tblExcel = conExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

foreach (TableRow tr in tblExcel.Rows)

{

Console.WriteLine(tr[2].ToString()); //Sheet Name is stored in Column 3.

}

To get the name of every sheet in the in the Excel Document, that way you can verif the proper name.





Re: Visual Studio Tools for Office Getting data from an Excel WorkSheet

Cindy Meister

This question is off-topic for this forum, which is targeted at the VSTO technology, not general Office support. Your best bet is to ask this in the data.oledb newsgroup, I think. That's the most likely place to get help on how to address an Excel worksheet properly using OLE DB. The other likely place to get help on the syntax would be the Excel.programming newsgroup. And the third possibility the ADO.NET forum.




Re: Visual Studio Tools for Office Getting data from an Excel WorkSheet

JuanPa_EC

Now I can get all the data in the sheet, but I need the data that is contained between the cells A1 and E96.

Using your example I can get the name of the sheet that is 'Lectura medidor Hidroabanico 15$', son how can I filter the query to get only the data I need

Thanks in advance!





Re: Visual Studio Tools for Office Getting data from an Excel WorkSheet

thorkia

Once you get the proper table name that is in the spreadsheet, you can fall back on this query:

string strComm = "select * from [" + sheet_name + "$" + first_cell + ":" + last_cell + "]";

Just remember, if the sheet_name variable already has the $ to remove it.