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

Hi ahmedsa,

Refer below sample.

First group your data and create separate DataTable and using those DataTable add each DataTable as separate ExcelWorksheet.

Namespaces

C#

using System.Data;
using System.IO;
using System.Linq;
using OfficeOpenXml;

VB.Net

Imports System.Data
Imports System.IO
Imports System.Linq
Imports OfficeOpenXml

Code

C#

protected void CreateExcel(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] {
        new DataColumn("PartId"), new DataColumn("Company"), new DataColumn("Files"), new DataColumn("Tab"), new DataColumn("Module") });
    dt.Rows.Add(1222, "micro", "Abc", "source", 1);
    dt.Rows.Add(1321, "silicon", "Abc", "source", 1);
    dt.Rows.Add(1444, "cd2", "Abc", "types", 2);
    dt.Rows.Add(1321, "cd3", "Abc", "types", 2);
    dt.Rows.Add(1541, "tvs", "Abc", "types", 2);
    DataSet ds = new DataSet();
    var result = from rows in dt.AsEnumerable()
                    group rows by new { Module = rows["Module"] } into grp
                    select grp;

    foreach (var item in result)
    {
        ds.Tables.Add(item.CopyToDataTable());
    }
    createExcelFile(ds, Server.MapPath("~/Test.xls"));
}

public Boolean createExcelFile(DataSet ds, String FullFilePathName)
{
    Boolean IsDone = false;
    try
    {
        FileInfo CreatedFile = new FileInfo(FullFilePathName);
        Boolean ISNew = false;
        if (!CreatedFile.Exists)
        {
            ISNew = true;
        }
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using (var pck = new ExcelPackage(CreatedFile))
        {
            ExcelWorksheet ws;
            foreach (DataTable Table in ds.Tables)
            {
                if (ISNew == true)
                {
                    ws = pck.Workbook.Worksheets.Add(Table.Rows[0]["Tab"].ToString());

                    if (System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.IsRightToLeft) // Right to Left for Arabic lang
                    {
                        ExcelWorksheetView wv = ws.View;
                        wv.RightToLeft = true;
                        ws.PrinterSettings.Orientation = eOrientation.Landscape;
                    }
                    else
                    {
                        ExcelWorksheetView wv = ws.View;
                        wv.RightToLeft = false;
                        ws.PrinterSettings.Orientation = eOrientation.Landscape;
                    }
                    ws.Cells.AutoFitColumns();
                    ws.Cells[1, 1].LoadFromDataTable(Table, ISNew, OfficeOpenXml.Table.TableStyles.Light8);
                }
                else
                {
                    ws = pck.Workbook.Worksheets.FirstOrDefault();
                    ws.Cells[2, 1].LoadFromDataTable(Table, ISNew);
                }
            }
            pck.Save();
            IsDone = true;
        }
    }
    catch (Exception ex)
    {

        throw ex;
    }
    return IsDone;
}

VB.Net

Protected Sub CreateExcel(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn() {
                        New DataColumn("PartId"), New DataColumn("Company"), New DataColumn("Files"), New DataColumn("Tab"), New DataColumn("Module")})
    dt.Rows.Add(1222, "micro", "Abc", "source", 1)
    dt.Rows.Add(1321, "silicon", "Abc", "source", 1)
    dt.Rows.Add(1444, "cd2", "Abc", "types", 2)
    dt.Rows.Add(1321, "cd3", "Abc", "types", 2)
    dt.Rows.Add(1541, "tvs", "Abc", "types", 2)
    Dim ds As DataSet = New DataSet()
    Dim result = From rows In dt.AsEnumerable()
                    Group rows By Key = New With {Key .Module = rows("Module")} Into Group
                    Select Group
    For Each item In result
        ds.Tables.Add(item.CopyToDataTable())
    Next

    createExcelFile(ds, Server.MapPath("~/Test.xls"))
End Sub

Public Function createExcelFile(ByVal ds As DataSet, ByVal FullFilePathName As String) As Boolean
    Dim IsDone As Boolean = False
    Try
        Dim CreatedFile As FileInfo = New FileInfo(FullFilePathName)
        Dim ISNew As Boolean = False

        If Not CreatedFile.Exists Then
            ISNew = True
        End If
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial
        Using pck = New ExcelPackage(CreatedFile)
            Dim ws As ExcelWorksheet
            For Each Table As DataTable In ds.Tables
                If ISNew = True Then
                    ws = pck.Workbook.Worksheets.Add(Table.Rows(0)("Tab").ToString())
                    If System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.IsRightToLeft Then
                        Dim wv As ExcelWorksheetView = ws.View
                        wv.RightToLeft = True
                        ws.PrinterSettings.Orientation = eOrientation.Landscape
                    Else
                        Dim wv As ExcelWorksheetView = ws.View
                        wv.RightToLeft = False
                        ws.PrinterSettings.Orientation = eOrientation.Landscape
                    End If

                    ws.Cells.AutoFitColumns()
                    ws.Cells(1, 1).LoadFromDataTable(Table, ISNew, OfficeOpenXml.Table.TableStyles.Light8)
                Else
                    ws = pck.Workbook.Worksheets.FirstOrDefault()
                    ws.Cells(2, 1).LoadFromDataTable(Table, ISNew)
                End If
            Next
            pck.Save()
            IsDone = True
        End Using

    Catch ex As Exception
        Throw ex
    End Try

    Return IsDone
End Function

Screenshot