Prev: UI Automation with MSAA
Next: Sending and returning objects to/from web service
From: tshad on 7 Jan 2010 20:33
I am trying to read some excel sheets and am getting what seems to be 2
sheets but I have only one sheet in one of my tables ( I do want to be able
to read multiple sheets).

My code is:
**********************************************************
string strSQL = "SELECT * FROM [{0}$]";
string mstrConnectionXLS = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0};Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
mstrConnectionXLS = string.Format(mstrConnectionXLS, strFile);
connection = new OleDbConnection(mstrConnectionXLS);
connection.Open();

DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null);
if (dt == null)
{
return null;
}
foreach (DataRow row in dt.Rows)
{
tableName = row["TABLE_NAME"].ToString().Replace("$", "");
da = new OleDbDataAdapter(String.Format(strSQL,
Regex.Replace(tableName, "'", "")), connection);
da.Fill(ds, tableName);
}
**************************************************************

This works pretty well but in my loop I am getting 2 table names:
Query1
Query1$

My program thinks there are 2 sheets so it ends up running it with the same
TableName.

If I look at my debugger, it shows the same data for both dt.Rows[0] and
dt.Rows[1].

Why is that and how do I tell how many sheets I have?

I thought this was the way?

Thanks,

Tom


From: Alvin Bruney - ASP.NET MVP on 11 Jan 2010 12:33
There's a bug in your code. The DataTable that the GetOleDbSchemaTable
method returns has a row for each object that meets the OleDbSchemaGuid type
and the restriction criteria. The DataTable has a column for each of the
restriction columns, which is followed by additional schema information
based on the OleDbSchemaGuid field. You'll likely get duplicate tables
depending on the schema data. Why don't you just get the dataset and extract
the table from it. I do not see where you actually use schema information in
the code.

So, replace this line DataTable dt =
connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
with a ds.Fill(newdataset, string.empty)
then extract the datable like so
if(ds != null && ds.Tables.Rows.Count > 0)
DataTable dt = ds.Tables[0];
then call your loop.

--
Vapordan
Shameless Author Plug
ASP.NET 4 by Example only $20
OWC Blackbook www.lulu.com/owc

"tshad" <toms(a)pdsa.com> wrote in message
news:eDirTKAkKHA.5568(a)TK2MSFTNGP02.phx.gbl...
> I am trying to read some excel sheets and am getting what seems to be 2
> sheets but I have only one sheet in one of my tables ( I do want to be
> able to read multiple sheets).
>
> My code is:
> **********************************************************
> string strSQL = "SELECT * FROM [{0}$]";
> string mstrConnectionXLS = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source={0};Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
> mstrConnectionXLS = string.Format(mstrConnectionXLS, strFile);
> connection = new OleDbConnection(mstrConnectionXLS);
> connection.Open();
>
> DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
> null);
> if (dt == null)
> {
> return null;
> }
> foreach (DataRow row in dt.Rows)
> {
> tableName = row["TABLE_NAME"].ToString().Replace("$", "");
> da = new OleDbDataAdapter(String.Format(strSQL,
> Regex.Replace(tableName, "'", "")), connection);
> da.Fill(ds, tableName);
> }
> **************************************************************
>
> This works pretty well but in my loop I am getting 2 table names:
> Query1
> Query1$
>
> My program thinks there are 2 sheets so it ends up running it with the
> same TableName.
>
> If I look at my debugger, it shows the same data for both dt.Rows[0] and
> dt.Rows[1].
>
> Why is that and how do I tell how many sheets I have?
>
> I thought this was the way?
>
> Thanks,
>
> Tom
>
From: tshad on 12 Jan 2010 20:38
Not sure what you mean.

How do I do a ds.Fill ???

Also, I do the Schema stuff to get the name of the table to do the
connection string (one for each possible sheet in the file).

Not sure how you mean to set it up?

Thanks,

Tom
"Alvin Bruney - ASP.NET MVP" <vapordan(a)hotmail.com> wrote in message
news:eLsvCRukKHA.5608(a)TK2MSFTNGP05.phx.gbl...
> There's a bug in your code. The DataTable that the GetOleDbSchemaTable
> method returns has a row for each object that meets the OleDbSchemaGuid
> type and the restriction criteria. The DataTable has a column for each of
> the restriction columns, which is followed by additional schema
> information based on the OleDbSchemaGuid field. You'll likely get
> duplicate tables depending on the schema data. Why don't you just get the
> dataset and extract the table from it. I do not see where you actually use
> schema information in the code.
>
> So, replace this line DataTable dt =
> connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
> with a ds.Fill(newdataset, string.empty)
> then extract the datable like so
> if(ds != null && ds.Tables.Rows.Count > 0)
> DataTable dt = ds.Tables[0];
> then call your loop.
>
> --
> Vapordan
> Shameless Author Plug
> ASP.NET 4 by Example only $20
> OWC Blackbook www.lulu.com/owc
>
> "tshad" <toms(a)pdsa.com> wrote in message
> news:eDirTKAkKHA.5568(a)TK2MSFTNGP02.phx.gbl...
>> I am trying to read some excel sheets and am getting what seems to be 2
>> sheets but I have only one sheet in one of my tables ( I do want to be
>> able to read multiple sheets).
>>
>> My code is:
>> **********************************************************
>> string strSQL = "SELECT * FROM [{0}$]";
>> string mstrConnectionXLS = "Provider=Microsoft.Jet.OLEDB.4.0;Data
>> Source={0};Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
>> mstrConnectionXLS = string.Format(mstrConnectionXLS, strFile);
>> connection = new OleDbConnection(mstrConnectionXLS);
>> connection.Open();
>>
>> DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
>> null);
>> if (dt == null)
>> {
>> return null;
>> }
>> foreach (DataRow row in dt.Rows)
>> {
>> tableName = row["TABLE_NAME"].ToString().Replace("$", "");
>> da = new OleDbDataAdapter(String.Format(strSQL,
>> Regex.Replace(tableName, "'", "")), connection);
>> da.Fill(ds, tableName);
>> }
>> **************************************************************
>>
>> This works pretty well but in my loop I am getting 2 table names:
>> Query1
>> Query1$
>>
>> My program thinks there are 2 sheets so it ends up running it with the
>> same TableName.
>>
>> If I look at my debugger, it shows the same data for both dt.Rows[0] and
>> dt.Rows[1].
>>
>> Why is that and how do I tell how many sheets I have?
>>
>> I thought this was the way?
>>
>> Thanks,
>>
>> Tom
>>


 | 
Pages: 1
Prev: UI Automation with MSAA
Next: Sending and returning objects to/from web service