In this article I will explain with an example, how to export DataTable to Excel using OpenXml and ClosedXml Excel conversion libraries in Windows Forms (WinForms) Applications using C# and VB.Net.
I making use of ClosedXml library along with OpenXml as ClosedXml is a wrapper library of OpenXml and makes it easier to use.
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.
![Export DataTable to Excel using OpenXml in Windows Forms (WinForms) Applications using C# and VB.Net]()
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using System.Reflection;
using ClosedXML.Excel;
VB.Net
Imports System.IO
Imports System.Data
Imports System.Reflection
Imports ClosedXML.Excel
Populating DataGridView
In order to populate the DataGridView, I have created a dynamic DataTable with some sample data.
C#
public Form1()
{
InitializeComponent();
this.BindDataGridView();
}
private void BindDataGridView()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Country",typeof(string)) });
dt.Rows.Add(1, "John Hammond", "United States");
dt.Rows.Add(2, "Mudassar Khan", "India");
dt.Rows.Add(3, "Suzanne Mathews", "France");
dt.Rows.Add(4, "Robert Schidner", "Russia");
this.dataGridView1.DataSource = dt;
}
VB.Net
Public Sub New()
InitializeComponent()
Me.BindDataGridView()
End Sub
Private Sub BindDataGridView()
Dim dt As New DataTable()
dt.Columns.AddRange(New DataColumn() {New DataColumn("Id", GetType(Integer)), _
New DataColumn("Name", GetType(String)), _
New DataColumn("Country", GetType(String))})
dt.Rows.Add(1, "John Hammond", "United States")
dt.Rows.Add(2, "Mudassar Khan", "India")
dt.Rows.Add(3, "Suzanne Mathews", "France")
dt.Rows.Add(4, "Robert Schidner", "Russia")
Me.dataGridView1.DataSource = dt
End Sub
![Export DataTable to Excel using OpenXml in Windows Forms (WinForms) Applications using C# and VB.Net]()
Exporting DataGridView data to Excel
Inside the Button Click event handler, I have written the code for exporting DataGridView data to Excel file.
A DataTable is created with columns same as that of the DataGridView and a loop is executed over the DataGridView rows and the data is added to the DataTable.
Then a Workbook object is created to which the DataTable is added as Worksheet using the Add method which accepts DataTable and the name of the Sheet as parameters.
Once the DataTable is added as a Worksheet to the Workbook, the WorkBook is saved to the specified location on the disk.
C#
private void btnExportExcel_Click(object sender, EventArgs e)
{
//Creating DataTable
DataTable dt = new DataTable();
//Adding the Columns
foreach (DataGridViewColumn column in dataGridView1.Columns)
{
dt.Columns.Add(column.HeaderText, column.ValueType);
}
//Adding the Rows
foreach (DataGridViewRow row in dataGridView1.Rows)
{
dt.Rows.Add();
foreach (DataGridViewCell cell in row.Cells)
{
dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
}
}
//Exporting to Excel
string folderPath = "C:\\Excel\\";
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "Customers");
wb.SaveAs(folderPath + "DataGridViewExport.xlsx");
}
}
VB.Net
Private Sub btnExportExcel_Click(sender As Object, e As EventArgs) Handles btnExportExcel.Click
'Creating DataTable
Dim dt As New DataTable()
'Adding the Columns
For Each column As DataGridViewColumn In dataGridView1.Columns
dt.Columns.Add(column.HeaderText, column.ValueType)
Next
'Adding the Rows
For Each row As DataGridViewRow In dataGridView1.Rows
dt.Rows.Add()
For Each cell As DataGridViewCell In row.Cells
dt.Rows(dt.Rows.Count - 1)(cell.ColumnIndex) = cell.Value.ToString()
Next
Next
'Exporting to Excel
Dim folderPath As String = "C:\Excel\"
If Not Directory.Exists(folderPath) Then
Directory.CreateDirectory(folderPath)
End If
Using wb As New XLWorkbook()
wb.Worksheets.Add(dt, "Customers")
wb.SaveAs(folderPath & Convert.ToString("DataGridViewExport.xlsx"))
End Using
End Sub
Screenshots
![Export DataTable to Excel using OpenXml in Windows Forms (WinForms) Applications using C# and VB.Net]()
Downloads
