Export Windows Form DataGridView from TabPages to Excel sheet using C# and VB.Net

Hi luckydead,

I have created this sample using below articles.

Export-DataGridView-to-Excel-with-Column-Headers-Column-Names-in-C-and-VBNet.aspx

Export-DataSet-DataTables-to-multiple-Excel-Sheets-Worksheets-in-ASPNet-using-C-and-VBNet.aspx

Namespaces

C#

using System.IO;
using ClosedXML.Excel;

VB.Net

Imports System.IO
Imports ClosedXML.Excel

Code

C#

private void Form1_Load(object sender, EventArgs e)
{
    string[] Lines = System.IO.File.ReadAllLines(Application.StartupPath + @"\Lines.txt");
    foreach (string line in Lines)
    {
        string[] LineParts = line.Split('|');
        if (LineParts.Count() < 2)
            continue;
        else
        {
            string Key = LineParts[0];
            string Value = LineParts[1];
            TabPage dynamicTab = new TabPage(Key);
            DataGridView btn = new DataGridView();
            btn.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            btn.AlternatingRowsDefaultCellStyle.BackColor = Color.White;
            btn.AlternatingRowsDefaultCellStyle.ForeColor = Color.Black;
            btn.AlternatingRowsDefaultCellStyle.SelectionBackColor = Color.FromArgb(0, 177, 89);
            btn.AlternatingRowsDefaultCellStyle.SelectionForeColor = Color.White;

            btn.ColumnHeadersDefaultCellStyle.BackColor = Color.White;
            btn.ColumnHeadersDefaultCellStyle.ForeColor = Color.Black;
            btn.ColumnHeadersDefaultCellStyle.SelectionBackColor = Color.FromArgb(0, 177, 89);
            btn.ColumnHeadersDefaultCellStyle.SelectionForeColor = Color.White;

            btn.DefaultCellStyle.BackColor = Color.White;
            btn.DefaultCellStyle.ForeColor = Color.Black;
            btn.DefaultCellStyle.SelectionBackColor = Color.FromArgb(0, 177, 89);
            btn.DefaultCellStyle.SelectionForeColor = Color.White;
            btn.RowHeadersVisible = true;
            btn.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize;
            btn.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            btn.Anchor = AnchorStyles.Top & AnchorStyles.Left & AnchorStyles.Right;
            btn.ScrollBars = ScrollBars.Both;
            btn.Dock = DockStyle.Fill;

            btn.Columns.Add("SAPCenter", "WorkCenter");
            btn.Columns.Add("GetLine", "Line");
            btn.Columns["GetLine"].DefaultCellStyle.NullValue = Value;
            btn.Columns["GetLine"].ReadOnly = true;
            TabControl1.TabPages.Add(dynamicTab);
            dynamicTab.Controls.Add(btn);
        }
    }
}

private void Button1_Click(System.Object sender, System.EventArgs e)
{
    DataSet ds = new DataSet();
    foreach (TabPage tabPage in TabControl1.TabPages)
    {
        DataGridView dgv = tabPage.Controls.OfType<DataGridView>().FirstOrDefault();
        DataTable dt = new DataTable();
        dt.TableName = tabPage.Text;
        dt.Columns.AddRange(new DataColumn[] { new DataColumn("WorkCenter"), new DataColumn("Line") });
        dgv.AllowUserToAddRows = false;
        foreach (DataGridViewRow row in dgv.Rows)
            dt.Rows.Add(row.Cells[0].FormattedValue.ToString(), row.Cells[1].FormattedValue.ToString());
        ds.Tables.Add(dt);
    }
    string folderPath = @"C:\Excel\";
    if (!Directory.Exists(folderPath))
        Directory.CreateDirectory(folderPath);
    using (XLWorkbook wb = new XLWorkbook())
    {
        foreach (DataTable dt in ds.Tables)
            wb.Worksheets.Add(dt, dt.TableName);
        wb.SaveAs(folderPath + Convert.ToString("DataGridViewExport.xlsx"));
    }
}

VB.Net

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Dim Lines() As String = IO.File.ReadAllLines(My.Application.Info.DirectoryPath & "\Lines.txt")
    For Each line As String In Lines
        Dim LineParts() As String = line.Split("|")
        If LineParts.Count < 2 Then
            Continue For
        Else
            Dim Key As String = LineParts(0)
            Dim Value As String = LineParts(1)
            Dim dynamicTab As New TabPage(Key)
            Dim btn As New DataGridView()
            btn.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
            btn.AlternatingRowsDefaultCellStyle.BackColor = Color.White
            btn.AlternatingRowsDefaultCellStyle.ForeColor = Color.Black
            btn.AlternatingRowsDefaultCellStyle.SelectionBackColor = Color.FromArgb(0, 177, 89)
            btn.AlternatingRowsDefaultCellStyle.SelectionForeColor = Color.White

            btn.ColumnHeadersDefaultCellStyle.BackColor = Color.White
            btn.ColumnHeadersDefaultCellStyle.ForeColor = Color.Black
            btn.ColumnHeadersDefaultCellStyle.SelectionBackColor = Color.FromArgb(0, 177, 89)
            btn.ColumnHeadersDefaultCellStyle.SelectionForeColor = Color.White

            btn.DefaultCellStyle.BackColor = Color.White
            btn.DefaultCellStyle.ForeColor = Color.Black
            btn.DefaultCellStyle.SelectionBackColor = Color.FromArgb(0, 177, 89)
            btn.DefaultCellStyle.SelectionForeColor = Color.White
            btn.RowHeadersVisible = True
            btn.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
            btn.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
            btn.Anchor = AnchorStyles.Top And AnchorStyles.Left And AnchorStyles.Right
            btn.ScrollBars = ScrollBars.Both
            btn.Dock = DockStyle.Fill

            btn.Columns.Add("SAPCenter", "WorkCenter")
            btn.Columns.Add("GetLine", "Line")
            btn.Columns("GetLine").DefaultCellStyle.NullValue = Value
            btn.Columns("GetLine").ReadOnly = True
            TabControl1.TabPages.Add(dynamicTab)
            dynamicTab.Controls.Add(btn)
        End If
    Next
End Sub

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Dim ds As New DataSet()
    For Each tabPage As TabPage In TabControl1.TabPages
        Dim dgv As DataGridView = tabPage.Controls.OfType(Of DataGridView)().FirstOrDefault()
        Dim dt As New DataTable()
        dt.TableName = tabPage.Text
        dt.Columns.AddRange(New DataColumn() {
                            New DataColumn("WorkCenter"),
                            New DataColumn("Line")})
        dgv.AllowUserToAddRows = False
        For Each row As DataGridViewRow In dgv.Rows
            dt.Rows.Add(row.Cells(0).FormattedValue.ToString(), row.Cells(1).FormattedValue.ToString())
        Next
        ds.Tables.Add(dt)
    Next
    Dim folderPath As String = "C:\Excel\"
    If Not Directory.Exists(folderPath) Then
        Directory.CreateDirectory(folderPath)
    End If
    Using wb As New XLWorkbook()
        For Each dt As DataTable In ds.Tables
            wb.Worksheets.Add(dt, dt.TableName)
        Next
        wb.SaveAs(folderPath & Convert.ToString("DataGridViewExport.xlsx"))
    End Using
End Sub

Screenshot