Export Generic List of DataTable to multiple Excel Sheets (Worksheets) using ClosedXML in C# and VB.Net

Last Reply 4 months ago By pandeyism

Posted 4 months ago

How to create multiple worksheet based on datatable column ?

I am getting the data as Pongal and Diwali for these compnaies but it creating only for diwali sheet alone.

As its overwriting the  First sheet. I want two sheet to be created.

               using (XLWorkbook wb = new XLWorkbook())
               { 
                   if (!string.IsNullOrEmpty(dts[i].Rows[0][0].ToString()))
                   {
                       foreach(DataRow dr in dts[i].Rows)
                       { 
                           //wb.Worksheets.Add(dts[i], dts[i].Rows[0][0].ToString()); 
                           wb.Worksheets.Add(dts[i], dr[i].ToString()); 
                           //wb.SaveAs(folderPath + dts[i].Rows[0][0].ToString() + ".xlsx"); 
                           wb.SaveAs(folderPath + i + ".xlsx");
                       }
                   }
               }

 

Posted 4 months ago

Hi SUJAYS,

Check the updated code sample.

Refer below article for more details.

Export DataSet DataTables to multiple Excel Sheets (Worksheets) in ASP.Net using C# and VB.Net

Namespaces

C#

using ClosedXML.Excel;
using System.IO;

VB.Net

Imports System.IO
Imports ClosedXML.Excel

Code

C#

private void button1_Click(object sender, EventArgs e)
{
    DataTable dt1 = new DataTable();
    dt1.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                    new DataColumn("Name", typeof(string)),
                    new DataColumn("Country",typeof(string)) });
    dt1.Rows.Add(1, "John Hammond", "United States");
    dt1.Rows.Add(2, "Mudassar Khan", "India");

    DataTable dt2 = dt1.Clone();
    dt2.Rows.Add(3, "Suzanne Mathews", "France");
    dt2.Rows.Add(4, "Robert Schidner", "Russia");

    // Get the list as per your code.
    List<DataTable> dts = new List<DataTable>();
    dts.Add(dt1);
    dts.Add(dt2);

    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], i.ToString());
        }
        wb.SaveAs(path + "Test.xlsx");
    }
}

VB.Net

Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click
    Dim dt1 As DataTable = New DataTable()
    dt1.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Country", GetType(String))})
    dt1.Rows.Add(1, "John Hammond", "United States")
    dt1.Rows.Add(2, "Mudassar Khan", "India")
    Dim dt2 As DataTable = dt1.Clone()
    dt2.Rows.Add(3, "Suzanne Mathews", "France")
    dt2.Rows.Add(4, "Robert Schidner", "Russia")
    Dim dts As List(Of DataTable) = New List(Of DataTable)()
    dts.Add(dt1)
    dts.Add(dt2)
    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), i.ToString())
        Next
        wb.SaveAs(path & "Test.xlsx")
    End Using
End Sub

Screenshot