In this article I will explain with an example, how to read and import
Excel file data to
DataGridView using
ClosedXml in Windows Forms (WinForms) Application with C# and VB.Net.
Note:
ClosesXml does not support
Excel file with extension
.xls as it is older.
Install ClosedXml package
Form Design
The Form consists of following controls:
Button – For selecting the
Excel file.
DataGridView – For displaying data.
OpenFileDialog – For allowing 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.Data;
using ClosedXML.Excel;
VB.Net
Imports System.Data
Imports ClosedXML.Excel
Selecting the Excel File in C# and VB.Net
When the
Select File button is clicked, the File Dialog is opened using
ShowDialog method of OpenFileDialog control, using which you need to select the
Excel file that you want to import to DataGridView control.
C#
private void OnSelect(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
}
VB.Net
Private Sub OnSelect(sender As Object, e As EventArgs) Handles btnSelect.Click
OpenFileDialog1.ShowDialog()
End Sub
Reading and importing Excel file data to DataGridView using ClosedXml in C# and VB.Net
When the
Excel File is selected, the file path is determined.
Then, the XLWorkbook class object is created which accepts the file path as a parameter.
The IXLWorkSheet class object is created and Worksheet method is called with parameter 1 which indicates that the first sheet will be read.
After that, an object of DataTable is created a FOR EACH loop is executed over the rows of
Excel sheet.
Inside the loop, a check is performed if it is first row then again a FOR EACH loop is executed and the columns are defined using DataTable object.
If the row is not the first row or header row, then a FOR EACH loop is executed over each rows and is added as rows to the DataTable object creates earlier.
Finally, the DataTable is assigned to the DataSource property of DataGridView.
C#
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
string filePath = openFileDialog1.FileName;
//Open the Excel file usingClosedXML.
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 usingClosedXML.
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
![Read and import Excel file data to DataGridView using ClosedXml using C# and VB.Net]()
Downloads
