Export DataSet (DataTables) to multiple Excel files with multiple sheets using EPPlus library in ASP.Net

Hi ahmedsa,

Refer below sample code.

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 Page_Load(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", "Types", 3);
    dt.Rows.Add(1444, "cd2", "AutoD", "Rev", 10);
    dt.Rows.Add(1321, "cd3", "AutoD", "source", 11);
    dt.Rows.Add(1541, "tvs", "AutoD", "Rev", 12);
    dt.Rows.Add(9811, "tvs2", "Mog", "Dal", 5);
    dt.Rows.Add(1901, "tvs3", "Mog", "Mondo", 56);
    dt.Rows.Add(2111, "toyo", "Mog", "Pingo", 57);

    string[] files = (from t in dt.AsEnumerable()
                        select t["Files"].ToString()).ToArray();

    foreach (string file in files)
    {
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        ExcelPackage excelPackage = new ExcelPackage();
        DataTable dtFiles = dt.Select("Files='" + file + "'").CopyToDataTable();
        DataSet ds = new DataSet();
        var tabs = from rows in dtFiles.AsEnumerable()
                    group rows by new { Module = rows["Tab"] } into grp
                    select grp;
        foreach (var tab in tabs)
        {
            ds.Tables.Add(tab.CopyToDataTable());
        }

        ExcelWorksheet ws;
        foreach (DataTable Table in ds.Tables)
        {
            ws = excelPackage.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, true, OfficeOpenXml.Table.TableStyles.Light8);
        }

        excelPackage.SaveAs(new FileInfo(Server.MapPath("~/") + file + ".xlsx"));
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    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", "Types", 3)
    dt.Rows.Add(1444, "cd2", "AutoD", "Rev", 10)
    dt.Rows.Add(1321, "cd3", "AutoD", "source", 11)
    dt.Rows.Add(1541, "tvs", "AutoD", "Rev", 12)
    dt.Rows.Add(9811, "tvs2", "Mog", "Dal", 5)
    dt.Rows.Add(1901, "tvs3", "Mog", "Mondo", 56)
    dt.Rows.Add(2111, "toyo", "Mog", "Pingo", 57)
    Dim files As Object() = (From t In dt.AsEnumerable() Select t("Files")).ToArray()

    For Each file As String In files
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial
        Dim exPackage As ExcelPackage = New ExcelPackage()
        Dim dtFiles As DataTable = dt.Select("Files='" & file & "'").CopyToDataTable()
        Dim ds As DataSet = New DataSet()
        Dim tabs = From rows In dt.AsEnumerable()
                    Group rows By Key = New With {Key .Module = rows("Tab")} Into Group
                    Select Group

        For Each t In tabs
            ds.Tables.Add(t.CopyToDataTable())
        Next
        Dim ws As ExcelWorksheet
        For Each Table As DataTable In ds.Tables
            ws = exPackage.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, True, OfficeOpenXml.Table.TableStyles.Light8)
        Next
        exPackage.SaveAs(New FileInfo(Server.MapPath("~/") & file & ".xlsx"))
    Next
End Sub