In this article I will explain with an example, how to read and import Excel file (Excel sheet) data to GridView using ClosedXml library 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 System.Collections.Generic;
using ClosedXML.Excel;
VB.Net
Imports System.IO
Imports System.Data
Imports System.Collections.Generic
Imports ClosedXML.Excel
Read and Import Excel data to DataTable using ClosedXml
When the Import Button is clicked, the following event handler is executed. First the uploaded Excel file is saved to a folder named Files and then it is opened and read using ClosedXml WorkBook class object.
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)
{
//Save the uploaded Excel file.
string filePath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(filePath);
//Open the Excel file using ClosedXML.
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
//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)
'Save the uploaded Excel file.
Dim filePath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
FileUpload1.SaveAs(filePath)
'Open the Excel file using ClosedXML.
Using workBook As New XLWorkbook(filePath)
'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 and Import Excel data to DataTable using ClosedXml in ASP.Net with C# and VB.Net]()
GridView displaying Excel data
![Read and Import Excel data to DataTable using ClosedXml in ASP.Net with C# and VB.Net]()
Downloads
