Export DataSet or DataTable to multiple Excel file using C# and VB.Net in ASP.Net

Hi nandish,

I have created a sample which full fill your requirement you need to modify the code according to your need.

First you need to download ClosedXml DLL and add reference of it to your project. For downloading the Dll refer below link.

Download ClosedXML Library

and i have made use of Northwind Database Customers and Employees table you can get the database from below article.

Install the Northwind and Pubs Sample Databases in SQL Server Express

Refer below sample code.

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using ClosedXML.Excel;

public partial class CS : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataSet ds = new DataSet();
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        string query = @"SELECT TOP 10 CustomerId,ContactName,City,Country FROM Customers GO 
                        SELECT TOP 5 EmployeeID,(FirstName + ' ' + LastName) EmployeeName,City,Country FROM Employees";
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    sda.Fill(ds);
                    ds.Tables[0].TableName = "Customers";
                    ds.Tables[1].TableName = "Employees";
                }
            }
        }

        for (int i = 0; i < ds.Tables.Count; i++)
        {
            DataTable dt = ds.Tables[i];
            XLWorkbook wb = new XLWorkbook();
            wb.Worksheets.Add(dt, dt.TableName);
            wb.SaveAs(Server.MapPath("~/Files/") + dt.TableName + ".xlsx");
        }
    }
}

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports ClosedXML.Excel

Partial Class VB
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim ds As New DataSet()
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim query As String = "SELECT TOP 10 CustomerId,ContactName,City,Country FROM Customers GO SELECT TOP 5 EmployeeID,(FirstName + ' ' + LastName) EmployeeName,City,Country FROM Employees"
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand(query)
                Using sda As New SqlDataAdapter()
                    cmd.Connection = con
                    sda.SelectCommand = cmd
                    sda.Fill(ds)
                    ds.Tables(0).TableName = "Customers"
                    ds.Tables(1).TableName = "Employees"
                End Using
            End Using
        End Using

        For i As Integer = 0 To ds.Tables.Count - 1
            Dim dt As DataTable = ds.Tables(i)
            Dim wb As New XLWorkbook()
            wb.Worksheets.Add(dt, dt.TableName)
            wb.SaveAs(Server.MapPath("~/Files/") + dt.TableName + ".xlsx")
        Next
    End Sub
End Class