How to get sheet name of downloaded excel file using C #?

Question

I would like to get the sheet name of the downloaded excel file using C # code. The file can be in .xls or .xlsx format. The code I 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(); } } 

string worksheetName = ??? (How to get the first sheet name of the downloaded file)

Someone please help ...

Answer 1

I use this to get sheet names from a .xlsx file and skip all 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); } 

Answer 3

If Excel is too big, this code will spend a lot of time in (conn.open ()). Using Openxml will be better (use less time), but if Excel is open, using openxml for reading will have an exception, but oldbhelper wile has no exception. My English is a pool, sorry .----- Chinese boy

Answer 4

I am using Microsoft excel library Microsoft.Office.Interop.Excel. You can then use the index to get the name of the worksheet as follows.

  string path = @"C\Desktop\MyExcel.xlsx" //Path for excel using Excel = Microsoft.Office.Interop.Excel; xlAPP = new Excel.Application(); xlAPP.Visible = false; xlWbk = xlAPP.Workbooks.Open(path); string worksheetName = xlWbk.Worksheets.get_Item(1).Name //pass Index here. Reemember that index starts from 1. xlAPP.Quit(); releaseObject(xlWbk); releaseObject(xlAPP); //Always handle unmanaged code. private void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { obj = null; MessageBox.Show("Unable to release the Object " + ex.ToString()); } finally { GC.Collect(); } }