Export multiple Windows Form ListBox to multiple Excel Sheets (Worksheets) using C# and VB.Net

Last Reply 5 months ago By pandeyism

Posted 5 months ago

How to import the listbox values to two excel sheets in windows application?

The first list  box values to sheet 1 and second list box values to sheet 2.

Posted 5 months ago

Hi SUJAYS,

Refer below sample.

Namespaces

C#

using ClosedXML.Excel;
using System.IO;

VB.Net

Imports System.IO
Imports ClosedXML.Excel

Code

C#

private void btnExport_Click(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] { new DataColumn("Name") });
    dt.TableName = "listBox1";
    foreach (string item in listBox1.Items)
    {
        dt.Rows.Add(item);
    }

    DataTable dt1 = dt.Clone();
    dt.TableName = "listBox2";
    foreach (string item in listBox2.Items)
    {
        dt1.Rows.Add(item);
    }

    List<DataTable> dts = new List<DataTable>();
    dts.Add(dt);
    dts.Add(dt1);

    string path = "D:\\Excel\\";
    if (!Directory.Exists(path))
    {
        Directory.CreateDirectory(path);
    }
    using (XLWorkbook wb = new XLWorkbook())
    {
        for (int i = 0; i < dts.Count; i++)
        {
            wb.Worksheets.Add(dts[i], dts[i].TableName);
        }
        wb.SaveAs(path + "Test.xlsx");
    }
}

VB.Net

Private Sub btnExport_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn() {New DataColumn("Name")})
    dt.TableName = "listBox1"

    For Each item As String In listBox1.Items
        dt.Rows.Add(item)
    Next

    Dim dt1 As DataTable = dt.Clone()
    dt.TableName = "listBox2"

    For Each item As String In listBox2.Items
        dt1.Rows.Add(item)
    Next

    Dim dts As List(Of DataTable) = New List(Of DataTable)()
    dts.Add(dt)
    dts.Add(dt1)
    Dim path As String = "D:\Excel\"

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

    Using wb As XLWorkbook = New XLWorkbook()

        For i As Integer = 0 To dts.Count - 1
            wb.Worksheets.Add(dts(i), dts(i).TableName)
        Next

        wb.SaveAs(path & "Test.xlsx")
    End Using
End Sub

Screenshot

Listbox Data

Excel Data