How to get sheetname of the uploaded excel file using C#?

Question

I would like to get the sheet name of the uploaded excel file using C# code. The file may be in .xls or .xlsx format. The Code I have used is as follows:

protected void btnGenerateCSV_Click(object sender, EventArgs e)
{            
    string sourceFile = ExcelFileUpload.PostedFile.FileName;
    string worksheetName = ?? //(How to get the first sheetname of the uploaded file)                
    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sourceFile + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

    if (sourceFile.Contains(".xlsx"))
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sourceFile + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";

    try
    {
        conn = new OleDbConnection(strConn);
        conn.Open();

        cmd = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", conn);
        cmd.CommandType = CommandType.Text;
        wrtr = new StreamWriter(targetFile);

        da = new OleDbDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);

        for (int x = 0; x < dt.Rows.Count; x++)
            {
                string rowString = "";
                for (int y = 0; y < dt.Columns.Count; y++)
                {
                    rowString += "\"" + dt.Rows[x][y].ToString() + "\",";
                }
                wrtr.WriteLine(rowString);
            }
    }
    catch (Exception exp)
    {
    }
    finally
    {
        if (conn.State == ConnectionState.Open)
        conn.Close();
        conn.Dispose();
        cmd.Dispose();
        da.Dispose();
        wrtr.Close();
        wrtr.Dispose();
    }
}

• (How to get the first sheetname of the uploaded file) string worksheetName = ??

Answer 1

I use this to get sheet names from a .xlsx file and loop through all the names to read sheets one by one.

OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0 xml;HDR=YES;'");
connection.Open();
DataTable Sheets = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

foreach(DataRow dr in Sheets.Rows)
{
    string sht = dr[2].ToString().Replace("'", "");
    OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select * from [" + sht + "]", connection);
}

Answer 2

DataTable Sheets = oleConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

for(int i=0;i<Sheets.Rows.Count;i++)
{
   string worksheets= Sheets.Rows[i]["TABLE_NAME"].ToString();
   string sqlQuery = String.Format("SELECT * FROM [{0}]", worksheets);
}