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 HTML Markup consists of:
FileUpload – For selecting file.
Button – For import selected file.
GridView – For displaying data.
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" />
<hr />
<asp:GridView ID="gvCustomers" 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
Inside the
ImportExcel event handler. First the
import Button is clicked and then 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
GridViewidView.
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++;
}
}
gvCustomers.DataSource = dt;
gvCustomers.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
gvCustomers.DataSource = dt
gvCustomers.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
