OleDbConnection.GetSchema() and Excel
If needed, I am experiencing this sort of problem and I would be happy to work with support to provide information which might lead to an understanding or a resolution of this problem.
Briefly,
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(workbookPath))
{
dtWorksheets = connection.GetSchema("Tables");
}
foreach (DataRow dr in dtWorksheets.Rows)
{
if (dr["TABLE_NAME"].ToString().EndsWith("Print_Area"))
dr.Delete();
}
}
catch (Exception exception)
{
return exception.Message;
}
return "";
}
public static OleDbConnection GetWorkbookConnection(string 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 =
;D ata Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""",
WorkBookPath);
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
return connection;
}
EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com
|