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'

Answer 1

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);
    }