In this article I will explain with an example, how to read and import Excel file data to DataGridView using ClosedXml using C# and VB.Net in Windows Forms (Winforms) Application.
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.
Form Controls
I have added a DataGridView and a Button to the Windows Form. I have also added an OpenFileDialog to the Form which allow us to select the excel file.
![Read and import Excel file data to DataGridView using ClosedXml using C# and VB.Net]()
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
Selecting the Excel File
When the Select File button is clicked, it shows the Open File Dialog, using which we need to select the Excel file we want to import to DataGridView control.
C#
private void btnSelect_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
}
VB.Net
Private Sub btnSelect_Click(sender As Object, e As EventArgs) Handles btnSelect.Click
OpenFileDialog1.ShowDialog()
End Sub
![Read and import Excel file data to DataGridView using ClosedXml using C# and VB.Net]()
Importing the Excel File’s Sheet to DataGridView control
When the Open Button is clicked, the following event handler is executed. First the path of the selected Excel file is read and then the Excel file is read using the 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 used to populate the DataGridView.
C#
private void openFileDialog1_FileOk(object sender, System.ComponentModel.CancelEventArgs e)
{
string filePath = openFileDialog1.FileName;
//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++;
}
}
dataGridView1.DataSource = dt;
}
}
}
VB.Net
Private Sub openFileDialog1_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
Dim filePath As String = OpenFileDialog1.FileName
'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
dataGridView1.DataSource = dt
Next
End Using
End Sub
Screenshots
Excel File
![Read and import Excel file data to DataGridView using ClosedXml using C# and VB.Net]()
DataGridView with Excel data
![Read and import Excel file data to DataGridView using ClosedXml using C# and VB.Net]()
Downloads
