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

.net, c#, excel, file-upload, oledb

Solution

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

Problem

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 = ??`

Original source