none
Finding the name of a worksheet so I can SELECT from it? RRS feed

  • Question

  • I am considering using ADO.NET to read the contents of an Excel sheet. One thing has me stumped though, how do I know what the name of the worksheet is? This code works successfully...

    string.Format("SELECT * FROM [{0}$]", worksheet);

    Where "worksheet" is a string containing the name to read from. However, this name changes all the time. I have seen snippets of code here that use the Excel.Application object model to get at things like this, but this is EXTREMELY slow compared to the read.

    So how do you all deal with this problem?

    Maury
    Monday, October 19, 2009 3:30 PM

Answers

  • You can achieve this goal by using GetOleDbSchemaTable method of OledbConnection.

    Here is a sample

            public string GetExcelConnectionString(string fileName)
            {
                if (fileName.EndsWith("xls"))
                {
                    return @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + @";Extended Properties=""Excel 8.0;IMEX=1""";
                }
                else
                {
                    return @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + @";Extended Properties=""Excel 12.0 Xml;HDR=YES;""";
                }
            }
            private DataSet LoadExcel(string fileName)
            {
                DataSet ds = new DataSet();
                OleDbConnection conn = new OleDbConnection(GetExcelConnectionString(fileName));
                conn.Open();
                DataTable tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                conn.Close();
                foreach (DataRow dr in tables.Rows)
                {
                    string tableName = dr["TABLE_NAME"].ToString();
                    if (!tableName.EndsWith("Print_Area"))
                    {
                        OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + tableName + "]", conn);
                        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        ds.Tables.Add(dt);
                    }
                }
                
                return ds;
            }
    • Proposed as answer by Raymond Tang Tuesday, October 20, 2009 12:15 AM
    • Marked as answer by Zhipeng Lee Monday, October 26, 2009 1:53 AM
    Monday, October 19, 2009 3:52 PM