Export each group Windows Form DataGridView data into separate Sheets (Worksheets) of Excel fileusing C# and VB.Net

Hi Appache,

Insert each group data to separate DataTable and use ClosedXml to insert sheets in excel file.

Please refer below sample.

Windows Form

Namespaces

C#

using System.IO;
using System.Data;
using ClosedXML.Excel;
using System.Data.SqlClient;

VB.Net

Imports System.IO
Imports System.Data
Imports ClosedXML.Excel
Imports System.Data.SqlClient

Code

C#

private void Form1_Load(object sender, EventArgs e)
{
    dataGridView1.AllowUserToAddRows = false;
    BindDataGrid();
}

private void btnExportExcel_Click(object sender, EventArgs e)
{
    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();
        }
    }

    var result = from rows in dt.AsEnumerable()
                    group rows by new { Country = rows["Country"] } into grp
                    select grp;

    // Exporting to Excel
    string folderPath = @"C:\Users\Dharmendra\Desktop\";
    if (!Directory.Exists(folderPath))
    {
        Directory.CreateDirectory(folderPath);
    }

    using (XLWorkbook wb = new XLWorkbook())
    {
        foreach (var item in result)
        {
            wb.Worksheets.Add(item.CopyToDataTable(), item.CopyToDataTable().Rows[0]["Country"].ToString());
        }

        wb.SaveAs(folderPath + "Export.xlsx");
        MessageBox.Show("Export Sucessfully.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}

private void BindDataGrid()
{
    string constring = @"Data Source=192.168.0.100\SQL2019;DataBase=Northwind;UID=sa;PWD=pass@123";
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT TOP 10 CustomerId, ContactName, Country FROM Customers", con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);
                dataGridView1.DataSource = dt;
            }
        }
    }
}

VB.Net

Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
    DataGridView1.AllowUserToAddRows = False
    BindDataGrid()
End Sub

Private Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
    Dim dt As DataTable = New DataTable()
    For Each column As DataGridViewColumn In DataGridView1.Columns
        dt.Columns.Add(column.HeaderText, column.ValueType)
    Next

    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

    Dim result = From rows In dt.AsEnumerable()
                    Group rows By groupByKey = New With {Key .Country = rows("Country")} Into Group
                    Select Group
    Dim folderPath As String = "C:\Users\Dharmendra\Desktop\"

    If Not Directory.Exists(folderPath) Then
        Directory.CreateDirectory(folderPath)
    End If

    Using wb As XLWorkbook = New XLWorkbook()
        For Each item In result
            wb.Worksheets.Add(item.CopyToDataTable(), item.CopyToDataTable().Rows(0)("Country").ToString())
        Next

        wb.SaveAs(folderPath & "Export.xlsx")
        MessageBox.Show("Export Sucessfully.", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Using
End Sub

Private Sub BindDataGrid()
    Dim constring As String = "Data Source=192.168.0.100\SQL2019;DataBase=Northwind;UID=sa;PWD=pass@123"
    Using con As SqlConnection = New SqlConnection(constring)
        Using cmd As SqlCommand = New SqlCommand("SELECT TOP 10 CustomerId, ContactName, Country FROM Customers", con)
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                DataGridView1.DataSource = dt
            End Using
        End Using
    End Using
End Sub

Screenshots

DataGridView

Exported Group by Excel sheet