Export Data to Multiple Excel Sheets in ASP.Net using C# and VB.Net

Hi mahesh213,

Refer below example. Here i am using ClosedXML library for exporting the Excel.

HTML

 <asp:Button ID="btnExport" OnClick="Export" runat="server" Text="Export" />

Namespaces

C#

using System.Data;
using System.IO;
using System.Reflection;
using ClosedXML.Excel;

VB.Net

Imports System.Data
Imports System.IO
Imports System.Reflection
Imports ClosedXML.Excel

Code

C#

protected void OnExport(object sender, EventArgs e)
{
    List<Employee> employees = new List<Employee>()
    {
        new Employee() { Id = 1, Name = "A", IsActive=true },
        new Employee() { Id = 2, Name = "B", IsActive = true },
        new Employee() { Id = 3, Name = "C", IsActive = false },
        new Employee() { Id = 4, Name = "D", IsActive = true }
    };

    var isActive = from emp in employees
                    where emp.IsActive == true
                    select emp;

    var isInActive = from emp in employees
                        where emp.IsActive == false
                        select emp;

    DataTable dt1 = this.LinqToDataTable<Employee>(isActive);
    DataTable dt2 = this.LinqToDataTable<Employee>(isInActive);

    using (XLWorkbook wb = new XLWorkbook())
    {
        wb.Worksheets.Add(dt1, "Active");
        wb.Worksheets.Add(dt2, "InActive");
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;filename=Book1.xlsx");
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }
}

//Generic function to convert Linq query to DataTable.
public DataTable LinqToDataTable<T>(IEnumerable<T> items)
{
    //Createa DataTable with the Name of the Class i.e. Customer class.
    DataTable dt = new DataTable(typeof(T).Name);

    //Read all the properties of the Class i.e. Customer class.
    PropertyInfo[] propInfos = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

    //Loop through each property of the Class i.e. Customer class.
    foreach (PropertyInfo propInfo in propInfos)
    {
        //Add Columns in DataTable based on Property Name and Type.
        dt.Columns.Add(new DataColumn(propInfo.Name, propInfo.PropertyType));
    }

    //Loop through the items if the Collection.
    foreach (T item in items)
    {
        //Add a new Row to DataTable.
        DataRow dr = dt.Rows.Add();

        //Loop through each property of the Class i.e. Customer class.
        foreach (PropertyInfo propInfo in propInfos)
        {
            //Add value Column to the DataRow.
            dr[propInfo.Name] = propInfo.GetValue(item, null);
        }
    }

    return dt;
}

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
}

VB.Net

Protected Sub OnExport(ByVal sender As Object, ByVal e As EventArgs)
    Dim employees As List(Of Employee) = New List(Of Employee)() From {
        New Employee() With {
            .Id = 1,
            .Name = "A",
            .IsActive = True
        },
        New Employee() With {
            .Id = 2,
            .Name = "B",
            .IsActive = True
        },
        New Employee() With {
            .Id = 3,
            .Name = "C",
            .IsActive = False
        },
        New Employee() With {
            .Id = 4,
            .Name = "D",
            .IsActive = True
        }
    }
    Dim isActive = From emp In employees Where emp.IsActive = True Select emp
    Dim isInActive = From emp In employees Where emp.IsActive = False Select emp
    Dim dt1 As DataTable = Me.LinqToDataTable(Of Employee)(isActive)
    Dim dt2 As DataTable = Me.LinqToDataTable(Of Employee)(isInActive)

    Using wb As XLWorkbook = New XLWorkbook()
        wb.Worksheets.Add(dt1, "Active")
        wb.Worksheets.Add(dt2, "InActive")
        Response.Clear()
        Response.Buffer = True
        Response.Charset = ""
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        Response.AddHeader("content-disposition", "attachment;filename=Book1.xlsx")
        Using MyMemoryStream As MemoryStream = New MemoryStream()
            wb.SaveAs(MyMemoryStream)
            MyMemoryStream.WriteTo(Response.OutputStream)
            Response.Flush()
            Response.End()
        End Using
    End Using
End Sub

Public Function LinqToDataTable(Of T)(ByVal items As IEnumerable(Of T)) As DataTable
    Dim dt As DataTable = New DataTable(GetType(T).Name)
    Dim propInfos As PropertyInfo() = GetType(T).GetProperties(BindingFlags.[Public] Or BindingFlags.Instance)

    For Each propInfo As PropertyInfo In propInfos
        dt.Columns.Add(New DataColumn(propInfo.Name, propInfo.PropertyType))
    Next

    For Each item As T In items
        Dim dr As DataRow = dt.Rows.Add()

        For Each propInfo As PropertyInfo In propInfos
            dr(propInfo.Name) = propInfo.GetValue(item, Nothing)
        Next
    Next

    Return dt
End Function

Public Class Employee
    Public Property Id As Integer
    Public Property Name As String
    Public Property IsActive As Boolean
End Class

Screenshot