anyoneis
asked on
Results from OleDbConnection.GetSchema("Tables")
I am using C# and ADO.NET to read an Excel workbook with multiple worksheets. When I use the code below, I get multiple entries for each sheet.
For example, I have a sheet named "Plate_2". When I do the GetSchema call, I see entries for "Plate_2" and one for "Plate_2$".
More confusing, for the sheet named "Plate_3", I have "Plate_3", "Plate_3$", and "Plate_3$_".
Which of these is the worksheet? Why are there threee different entries? What do the differences indicate? Are there any other funky things like "Print_Area" which I should expect to see?
Thanks,
David
[Code]
public static string LoadWorkbookSchema(string workbookPath, ref DataTable dtWorksheets)
{
try
{
using (OleDbConnection connection = GetWorkbookConnection(work bookPath))
{
dtWorksheets = connection.GetSchema("Tabl es");
}
foreach (DataRow dr in dtWorksheets.Rows)
{
if (dr["TABLE_NAME"].ToString ().EndsWit h("Print_A rea"))
dr.Delete();
}
}
catch (Exception exception)
{
return exception.Message;
}
return "";
}
public static OleDbConnection GetWorkbookConnection(stri ng WorkBookPath)
{
// Note that IMEX=1 was added to eliminate problems with data in the "9th-plus line"
// having a different data format than the first 8. dwr
string connectionString =
string.Format(@"Provider=M icrosoft.J et.OLEDB.4 .0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""",
WorkBookPath);
OleDbConnection connection = new OleDbConnection(connection String);
connection.Open();
return connection;
}
[/Code]
For example, I have a sheet named "Plate_2". When I do the GetSchema call, I see entries for "Plate_2" and one for "Plate_2$".
More confusing, for the sheet named "Plate_3", I have "Plate_3", "Plate_3$", and "Plate_3$_".
Which of these is the worksheet? Why are there threee different entries? What do the differences indicate? Are there any other funky things like "Print_Area" which I should expect to see?
Thanks,
David
[Code]
public static string LoadWorkbookSchema(string workbookPath, ref DataTable dtWorksheets)
{
try
{
using (OleDbConnection connection = GetWorkbookConnection(work
{
dtWorksheets = connection.GetSchema("Tabl
}
foreach (DataRow dr in dtWorksheets.Rows)
{
if (dr["TABLE_NAME"].ToString
dr.Delete();
}
}
catch (Exception exception)
{
return exception.Message;
}
return "";
}
public static OleDbConnection GetWorkbookConnection(stri
{
// Note that IMEX=1 was added to eliminate problems with data in the "9th-plus line"
// having a different data format than the first 8. dwr
string connectionString =
string.Format(@"Provider=M
WorkBookPath);
OleDbConnection connection = new OleDbConnection(connection
connection.Open();
return connection;
}
[/Code]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
David,
The normal worksheet name is Plate_3$, all the others are not normal table names. OleDb doesn't distinguish between the table types, as far as I know.
Bob
The normal worksheet name is Plate_3$, all the others are not normal table names. OleDb doesn't distinguish between the table types, as far as I know.
Bob
ASKER
I'm going to leave this open for a bit longer to see if someone knows what "Plate_3$_" might be....
I still don't know what the ones ending in an '_' are, but I can ignore them for now and just crunch the worksheets. Thanks, Bob.
David
I still don't know what the ones ending in an '_' are, but I can ignore them for now and just crunch the worksheets. Thanks, Bob.
David
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent! It was obvious in the xml. I discovered:
<Names>
<NamedRange ss:Name="_FilterDatabase" ss:RefersTo="=Plate_3!R1C1 :R97C13"
ss:Hidden="1"/>
</Names>
I changed the ss:Hidden attribute value to "0", save the file and reopened it, and the named range showed up.
grazie!
David
<Names>
<NamedRange ss:Name="_FilterDatabase" ss:RefersTo="=Plate_3!R1C1
ss:Hidden="1"/>
</Names>
I changed the ss:Hidden attribute value to "0", save the file and reopened it, and the named range showed up.
grazie!
David
ASKER
:-David