CODEWITHSUNDEEP
asp.netexcelc#-4.0oledb
Mike
Mike

Reputation: 33

OleDb - Retrieving Excel worksheet names also retrieves Defined Names

I am trying to retrieve a list of the worksheets in an Excel workbook, but the collection I get back has both the sheet names and the data column id's, which seem to be called 'Defined Names' in the original xlsx xml. Can you tell me how to return only the worksheet names?

The code I'm using is along the lines of: 

OleDbConnection connExcel = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;"
            + @"Data Source=" + FilePath + ";"
            + @"Extended Properties=""Excel 8.0;HDR=Yes;""");

OleDbCommand cmdExcel = new OleDbCommand();
cmdExcel.Connection = connExcel;
connExcel.Open();

DataTable testTable = connExcel.GetSchema("Tables");

The contents of the resulting testTable collection contain entries under TABLE_NAME of:

They all have a TABLE_TYPE of TABLE.

The original workbook corresponding to the above would have 1 worksheet containing 5 columns, the first row would contain a header. I'm only interested in the Sheet1$ entry. The spreadsheet is created in Excel 2010, I'm trying to process it in an ASP.NET 4 app written in C#. Potentially, the worksheet name may have been changed so I can't guarrantee that it will always be Sheet1$.

Upvotes: 3

Views: 8774

Answers (3)

DieserTBZ
DieserTBZ

Reputation: 67

I had a similar issue but with the exception that it showed me Sheets that didn't exist in Excel. Even though this post is a bit old now, maybe somebody finds this and finds it helpful.

My Code:

//OpenFileDialog
try
{
    OpenFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
    OpenFileDialog.Filter = "XLSX Files(*.xlsx)|*.xlsx|All Files (*.*)|*.*";
    OpenFileDialog.ShowDialog();
}
catch (Exception ex)
{
    //some Error Message
}

//read into Combobox
try
{
    OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + OpenFileDialog.FileName + ";Extended Properties=Excel 12.0;");
    con.Open();
    DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    con.Close();
    this.Combobox.Items.Clear();
  
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        String sheetName = dt.Rows[i]["TABLE_NAME"].ToString();
        sheetName = sheetName.Substring(0, sheetName.Length - 1);

        //cheap Filter to filter out unneeded/wrong sheets
        if (sheetName.Replace("'", " ").Replace("$", " ").TrimStart().TrimEnd().Contains("#") != true)
        {
            this.Combobox.Items.Add(sheetName.Replace("'", " ").Replace("$", " ").TrimStart().TrimEnd());
        }
    }
}
catch (Exception Ex)
{
    //some Error Message
}

This might not be the best solution, but it works quite well for me.

Upvotes: 0

Sergio Santos
Sergio Santos

Reputation: 1

private static string EXCEL_CONNECTIONSTRING = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=Excel 12.0;", "#{FILENAME}");

private IEnumerable<string> GetWorksheetNames(string excelFile)
{

     var currentConnectionString = EXCEL_CONNECTIONSTRING.Replace("#{FILENAME}", excelFile);

     using (OleDbConnection connection = new OleDbConnection(currentConnectionString))
     {
        OleDbCommand cmdExcel = new OleDbCommand();


        cmdExcel.Connection = connection;
        connection.Open();

        DataTable dt = connection.GetSchema("Tables");

        IEnumerable<string> excelSheets = dt.Rows.Cast<DataRow>().Select(row => row["TABLE_NAME"].ToString());
        dt.Dispose();
        connection.Close();
        return excelSheets;

     }

  }

Upvotes: -1

David Yenglin
David Yenglin

Reputation: 685

My first thoughts were wrong so I came up with this as a workaround. The actual worksheet names returned should always end with $, so I hacked it to check for that. Messy but you get the general idea I'm sure.

OleDbConnection connExcel = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;"
        + @"Data Source=c:\test.xlsx;"
        + @"Extended Properties=""Excel 12.0 Xml;HDR=Yes;""");

        OleDbCommand cmdExcel = new OleDbCommand();
        cmdExcel.Connection = connExcel;
        connExcel.Open();

        DataTable testTable = connExcel.GetSchema("Tables");

        String[] excelSheets = new String[testTable.Rows.Count];
        int i = 0;

        foreach (DataRow row in testTable.Rows)
        {
            excelSheets[i] = row["TABLE_NAME"].ToString();

            if (excelSheets[i].EndsWith("$"))
            {
                Console.WriteLine(excelSheets[i] = row["TABLE_NAME"].ToString());
                i++;
            }
            else
            {
                i++;
            }

        }

        Console.ReadLine();

Upvotes: 3

Related Questions