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