In this article I will explain with an example, how to get list of Sheet (WorkSheet) names of Excel file in ASP.Net using C# and VB.Net.
The Excel file will be first uploaded and copied to a Folder (Directory) on Server and then it will be read and opened using OLEDB and the Sheet (WorkSheet) Names of the Excel file will be fetched.
 
 
Connection String for Excel 2003 and Excel 2007 or higher formats
The Connection Strings for the Excel files of both 2003 and 2007 or higher formats have been specified in the Web.Config file.
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
<add name="Excel07+ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
 
 
HTML Markup
The HTML Markup consists of ASP.Net FileUpload control and a Button to trigger the file upload process. There is also a GridView control which will be used to display the list of Sheet (WorkSheet) names of Excel file.
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" Text="Upload" OnClick="Upload" runat="server" />
<hr />
<asp:GridView ID="gvSheets" runat="server" AutoGenerateColumns = "false">
    <Columns>
        <asp:BoundField DataField = "TABLE_NAME" HeaderText = "Sheet Name" />
    </Columns>
</asp:GridView>
 
 
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
 
VB.Net
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration
 
 
Reading Sheet (WorkSheet) Names of Excel file in ASP.Net
When the Upload Button is clicked, the Excel file is first uploaded and then saved inside a Folder (Directory) named Files.
Then based on the extension of the Excel file i.e. xls or xlsx, the appropriate connection sting is fetched from the Web.Config file.
Then a connection is established with the Excel file using OLEDB and the list of Sheet (WorkSheet) names of Excel file is fetched.
C#
protected void Upload(object sender, EventArgs e)
{
    //Upload and save the File.
    string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(excelPath);
 
    string conString = string.Empty;
    string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
    switch (extension)
    {
        case ".xls": //Excel 97-03.
            conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            break;
        case ".xlsx": //Excel 07 or higher.
            conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
            break;
    }
 
    //Read the Sheet Names.
    conString = string.Format(conString, excelPath);
    using (OleDbConnection excel_con = new OleDbConnection(conString))
    {
        excel_con.Open();
        gvSheets.DataSource = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        gvSheets.DataBind();
        excel_con.Close();
    }
}
 
VB.Net
Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs)
    'Upload and save the File.
    Dim excelPath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
    FileUpload1.SaveAs(excelPath)
    Dim conString As String = String.Empty
    Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
 
    Select Case extension
        Case ".xls" 'Excel 97-03.
            conString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
        Case ".xlsx" 'Excel 07 or higher.
            conString = ConfigurationManager.ConnectionStrings("Excel07+ConString").ConnectionString
    End Select
 
    'Read the Sheet Names.
    conString = String.Format(conString, excelPath)
    Using excel_con As OleDbConnection = New OleDbConnection(conString)
        excel_con.Open()
        gvSheets.DataSource = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        gvSheets.DataBind()
        excel_con.Close()
    End Using
End Sub
 
 
Screenshot
Get list of Sheet (WorkSheet) Names of Excel file in ASP.Net using C# and VB.Net
 
 
Downloads
Download Code