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 opened and read using OLEDB.
 
 

Download System.Data.OleDb DLL

There are two ways you can download the System.Data.OleDb DLL.
MSI Installer: Download Microsoft.ACE.OLEDB.12.0 provider for 32bit and 64bit.
Nuget: Install System.Data.OleDb Nuget Package.
 
 

Connection Strings

The first thing is to build connection strings to Excel files and Excel files are broadly divided into two types and Excel 97-2003 and Excel 2007 and higher.
Excel 97 – 2003 format which uses Microsoft Jet driver and the Excel version is set to 8.0 and Header is set to YES i.e. first row in Excel sheet will be Header Row.
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'" />
 
Excel 2007 and higher format which uses Microsoft Ace driver and the Excel version is set to 12.0 and Header is set to YES i.e. first row in Excel sheet will be Header Row.
<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 following controls:
FileUpload – For selecting file.
Button – For uploading selected file.
The Button has been assigned with an OnClick event handler.
GridView – For displaying the records.
<asp:FileUpload ID="fuUpload" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="Upload" />
<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.OleDb;
using System.Configuration;
 
VB.Net
Imports System.IO
Imports System.Data.OleDb
Imports System.Configuration
 
 

Reading Sheet (WorkSheet) Names of Excel file using C# and VB.Net

When Upload Button is clicked, the selected file is saved in the Files Folder (Directory).
Then, using the switch case statement, the Excel file extension is determined and the connection string is built.
Finally, a connection is established with the Excel file using OLEDB driver and the list of Sheet (WorkSheet) names of Excel file is fetched using GetOleDbSchemaTable method of the OleDbConnection class.
C#
protected void Upload(object sender, EventArgs e)
{
    //Upload and save the File.
    string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(fuUpload.PostedFile.FileName);
    fuUpload.SaveAs(excelPath);
 
    string conString = string.Empty;
    string extension = Path.GetExtension(fuUpload.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(fuUpload.PostedFile.FileName)
    fuUpload.SaveAs(excelPath)
    Dim conString As String = String.Empty
    Dim extension As String = Path.GetExtension(fuUpload.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