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