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

Last Reply one month ago By pandeyism

Posted one month 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 one month 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