Link to home
Start Free TrialLog in
Avatar of anyoneis
anyoneisFlag for United States of America

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(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 =
                string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""",
                    WorkBookPath);

            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            return connection;
        }


[/Code]
ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of anyoneis

ASKER

Correct! The "unadorned" names (no dollah, no quote, no...) appear to be named ranges. That's part of it. "Excel schema anomalies for 200, Alex."

:-David
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
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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