How to get sheet name of the uploaded excel file in ASP.NET?

using System.IO;

using System.Data.OleDb;

using System.Data;

protected void btnUpload_Click(object sender, EventArgs e)

{

string ext = string.Empty;

string filePath = string.Empty;

string strConnectionString = string.Empty;

OleDbConnection con = null;

OleDbCommand cmd = null;

OleDbDataAdapter da = null;

DataSet ds = null;

if (!FileUpload1.HasFile)

{

DisplayMessage("Please select file to upload.");

return;

}

else

{

ext = Path.GetExtension(FileUpload1.PostedFile.FileName);

ext = ext.ToLower();

if (!(ext.Equals(".xls") || ext.Equals(".xlsx")))

{

DisplayMessage("Please upload .xls / .xlsx files only.");

return;

}

}

filePath = FileUpload1.PostedFile.FileName;

if (ext.Equals(".xls"))

{

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";

}

else

{

if (ext.Equals(".xlsx"))

{

strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0";

}

}

con = new OleDbConnection(strConnectionString);

con.Open();

// Get sheet name

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

string worksheets = Sheets.Rows[0]["TABLE_NAME"].ToString();

string query = string.Format("SELECT * FROM [{0}]", worksheets);

cmd = new OleDbCommand(query, con);

da = new OleDbDataAdapter(cmd);

ds = new DataSet();

da.Fill(ds);

}

Note:-

In above example selecting first row sheet only.

If you have multiple sheets in excel sheet and all sheets need to process, use following to get all sheet names

foreach (DataRow row in dt.Rows)

{

string worksheets = row["TABLE_NAME"].ToString();

// Your code here

}