In this article I will explain with an example, how to export DataGridView to Excel file without using Microsoft Office or Interop Library in C# and VB.Net.
This article will illustrate how to make use of the free and open source ClosedXml library which is wrapper of OpenXml library for exporting DataGridView to Excel in C# and VB.Net.
Download DocumentFormat.OpenXml and ClosedXML Libraries
You can download the libraries using the following download locations.
Note: You will need to install the OpenXml SDK 2.0 in your Windows Operating System.
Form Controls
I have added a DataGridView and a Button to the Windows Form.
![Export DataGridView to Excel without using Interop in 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 DataGridView to Excel without using Interop in C# and VB.Net]()
Exporting DataGridView 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
Screenshot
![Export DataGridView to Excel without using Interop in C# and VB.Net]()
Downloads
