In this article I will explain with an example, how to read Excel File (xls & xlsx) data in ASP.Net using C# and VB.Net.
When the Excel file is uploaded, the Excel File data is available in the InputStream property of the FileUpload control.
Using ClosedXml library, the Excel file data can be easily read from the InputStream property and displayed on Web Page using GridView in ASP.Net using C# and VB.Net.
 
 
Download DocumentFormat.OpenXml and ClosedXml Libraries
You can download the libraries using the following download locations.
Note: The DLL files of both OpenXml and ClosedXml are present in the attached sample.
 
 

HTML Markup
The following HTML Markup consists of an ASP.Net FileUpload control, a Button and a GridView.
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" />
<hr />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
 
 
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using ClosedXML.Excel;
 
VB.Net
Imports System.IO
Imports System.Data
Imports ClosedXML.Excel
 
 
Read Excel File data in ASP.Net
When the Import Button is clicked, the Excel file is read into the ClosedXml WorkBook class object directly from the PostedFile.InputStream property of the FileUpload control without saving the Excel file in Folder (Directory) on Server’s Disk.
Then the instance of the first Sheet is determined and all the rows present in the Sheet are fetched.
Finally a loop is executed over the fetched rows and a DataTable is populated which is then bound to the GridView.
C#
protected void ImportExcel(object sender, EventArgs e)
{
    //Open the Excel file using ClosedXML.
    using (XLWorkbook workBook = new XLWorkbook(FileUpload1.PostedFile.InputStream))
    {
        //Read the first Sheet from Excel file.
        IXLWorksheet workSheet = workBook.Worksheet(1);
 
        //Create a new DataTable.
        DataTable dt = new DataTable();
 
        //Loop through the Worksheet rows.
        bool firstRow = true;
        foreach (IXLRow row in workSheet.Rows())
        {
            //Use the first row to add columns to DataTable.
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                //Add rows to DataTable.
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    i++;
                }
            }
 
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
    }
}
 
VB.Net
Protected Sub ImportExcel(sender As Object, e As EventArgs)
    'Open the Excel file using ClosedXML.
    Using workBook As New XLWorkbook(FileUpload1.PostedFile.InputStream)
        'Read the first Sheet from Excel file.
        Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
 
        'Create a new DataTable.
        Dim dt As New DataTable()
 
        'Loop through the Worksheet rows.
        Dim firstRow As Boolean = True
        For Each row As IXLRow In workSheet.Rows()
            'Use the first row to add columns to DataTable.
            If firstRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                Next
                firstRow = False
            Else
                'Add rows to DataTable.
                dt.Rows.Add()
                Dim i As Integer = 0
                For Each cell As IXLCell In row.Cells()
                    dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                    i += 1
                Next
            End If
 
            GridView1.DataSource = dt
            GridView1.DataBind()
        Next
    End Using
End Sub
 
 
Screenshots
The Excel File
Read Excel File data in ASP.Net
 
GridView displaying Excel data
Read Excel File data in ASP.Net
 
 
Downloads