C# xls & xlsx query selecting from a sheet regardless of sheet name

Question

This is my GetData method:

    private DataTable GetData(string userFileName)
    {
        string dirName = Path.GetDirectoryName(userFileName);
        string fileName = Path.GetFileName(userFileName);
        string fileExtension = Path.GetExtension(userFileName);
        string connection = string.Empty;
        string query = string.Empty;

        switch (fileExtension)
        {
            case ".xls":
                connection = $@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={userFileName};" +
                               "Extended Properties=\"Excel 8.0; HDR=Yes; IMEX=1\"";
                query = "SELECT * FROM [Sheet1$]";
                break;

            case ".xlsx":
                connection = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={userFileName};" +
                               "Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=1\"";
                query = "SELECT * FROM [Sheet1$]";
                break;

            case ".csv":
                connection = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dirName};" +
                               "Extended Properties=\"text; HDR=Yes; IMEX=1; FMT=Delimited\"";
                query = $"SELECT * FROM [{fileName}]";
                break;
        }

        return FillData(connection, query);
    }

It works for .csv files as it uses the file name rather than the sheet name.

It works for .xls and .xlsx files that have a worksheet called Sheet1.

When I try and use a .xls/.xlsx file with a different sheet name, I get the following error:

System.Data.OleDb.OleDbException: ''Sheet1$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.'

An answer from another question:

using (OleDbConnection conn = new OleDbConnection(connString))
{
    conn.Open();
    dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    Sheet1= dtSchema.Rows[0].Field<string>("TABLE_NAME");
}

Did not help as I don't know where this should go in my code nor is there any indication in the answer as to where this should go.

Do i add it in like such?

string Sheet1 = dataGridView1.Rows[0].Field<string>("TABLE_NAME");

This gives me an error:

Error CS1929 'DataGridViewRow' does not contain a definition for 'Field' and the best extension method overload 'DataRowExtensions.Field(DataRow, string)' requires a receiver of type 'DataRow'

Possible duplicate https://stackoverflow.com/questions/1438083/getting-the-first-sheet-from-an-excel-document-regardless-of-sheet-name-with-ole — Woldemar89, Feb 04 '19 at 14:09
Possible duplicate of [Getting the first sheet from an Excel document regardless of sheet name with OleDb](https://stackoverflow.com/questions/1438083/getting-the-first-sheet-from-an-excel-document-regardless-of-sheet-name-with-ole) — Renatas M., Feb 04 '19 at 14:16
@woldemar That link didn't provide me with the explanation I need to resolve the issue I am having. — Matt, Feb 04 '19 at 14:20
@Matt `string Sheet1 = **dataGridView1**.......` wtf are you doing? :) there is no any grid views in answer. I think you are opening connection in `FillData` method, open your connection and call `var dtSchema = conn.GetOleDbSchemaTable` etc on it — Woldemar89, Feb 04 '19 at 14:28
Just to add as @woldemar already wrote most of an idea...the duplicate answer just points you that you need to fetch table schema and the first row with column name "TABLE_NAME" will contain Excel Sheet1 name, which you can use in your queries. — Renatas M., Feb 04 '19 at 14:37

Woldemar89 · Answer 1 · 2019-02-04T14:54:05.937

Try to call this method instead FillData method in the end of your GetData method, passing of query is not required as you can see, because this method gets sheetname self from document schema.

    private DataTable GetDataFromFirstSheet(string connection)
    {
        using (OleDbConnection conn = new OleDbConnection(connection))
        {
            using (DataTable dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }))
            {
                string firstSheet = dtSchema.Rows[0].Field<string>("TABLE_NAME");

                //try to remove $ from sheetname if it will be not working
                using (OleDbCommand cmd = new OleDbCommand($"SELECT * FROM [{firstSheet}$]", conn))
                {
                    using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
                    {
                        conn.Open();

                        DataTable dt = new DataTable();
                        adp.Fill(dt);

                        return dt;
                    }                            
                }
            }
        }
    }

score 3 · Accepted Answer · answered Feb 04 '19 at 15:06

I quickly added some fixes to your code, but this solution is far away from beeing clean. You should consider the solution from @woldemar and get a bit deeper into unterstanding the code. Some excelent sources can be found here: https://github.com/EbookFoundation/free-programming-books

Back to your code. To find out the name of the first sheet you have to open a connection to the xlsx file at first. Then query the meta data with some code like in the example:

using (OleDbConnection conn = new OleDbConnection(connString))
{
    conn.Open();
    dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    Sheet1= dtSchema.Rows[0].Field<string>("TABLE_NAME");
}

After that you can insert the received sheet name into your query.

Quick and dirty, your code schould look like this to get it to work with xlsx files:

private static DataTable GetData(string userFileName)
    {
        string dirName = Path.GetDirectoryName(userFileName);
        string fileName = Path.GetFileName(userFileName);
        string fileExtension = Path.GetExtension(userFileName);
        string connection = string.Empty;
        string query = string.Empty;

        switch (fileExtension)
        {
            case ".xls":
                connection = $@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={userFileName};" +
                             "Extended Properties=\"Excel 8.0; HDR=Yes; IMEX=1\"";
                query = "SELECT * FROM [Sheet1$]";
                break;

            case ".xlsx":
                connection = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={userFileName};" +
                             "Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=1\"";
                string sheetName;
                using (OleDbConnection con = new OleDbConnection(connection))
                {
                    con.Open();
                    var dtSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    sheetName = dtSchema.Rows[0].Field<string>("TABLE_NAME");
                }

                if (sheetName.Length <= 0) throw new InvalidDataException("No sheet found."); // abort if no sheet name was returned

                query = $"SELECT * FROM [{sheetName}]";
                break;

            case ".csv":
                connection = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dirName};" +
                             "Extended Properties=\"text; HDR=Yes; IMEX=1; FMT=Delimited\"";
                query = $"SELECT * FROM [{fileName}]";
                break;
        }

        return FillData(connection, query);
    }

:D @woldemar welcome to SO. At least this answer is posted in one go. There are "technique" when SO experts posts answer with one sentence/ line of code and then starts to edit it to be fully acceptable answer. And if you are like a normal person you write fully acceptable answer and then post it, but then your answer is very late so the experts will be accepted :) — Renatas M., Feb 04 '19 at 15:26
Not all of us can start as pros. He was not able to use the given hints so I think showing him how to use them in his code will help him more than telling him how stupid his code is. And I think I made it clear that he has to get deeper into the theroy of programming. And no one of us has the time to explain him how to code "perfect" in detail, by starting at the basics. — soulflyman, Feb 04 '19 at 15:28
@woldemar the issue and subsequent fix are hardly groundbreaking nor are they large hulks of code, if there is a more efficient way of doing it then great, but for this instance, just bringing in excel sheets irregardless of sheet name, its a working and useful solution. — Matt, Feb 04 '19 at 21:47

C# xls & xlsx query selecting from a sheet regardless of sheet name

2 Answers2