Export Data from SQL Server to multiple Excel sheets in ASP.Net using C# and VB.Net

Hi mirshad,

You need to get the distinct DepartmentNames and loop through each department and fetch record from database to Datatable.

then add the DataTable to the XLWorkbook.

Please refer below sample. I have used country to group the records in each sheet.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML 

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

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using ClosedXML.Excel;

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO
Imports ClosedXML.Excel

Code

C#

protected void Export(object sender, EventArgs e)
{
    DataTable countries = GetData("SELECT DISTINCT Country FROM Employees");
    string folderPath = Server.MapPath("~/Data/");
    if (!Directory.Exists(folderPath))
    {
        Directory.CreateDirectory(folderPath);
    }
    XLWorkbook wb = new XLWorkbook();
    foreach (DataRow dr in countries.Rows)
    {
        string country = dr[0].ToString();
        DataTable dt = GetData(string.Format("SELECT EmployeeID, FirstName, Country FROM Employees WHERE Country = '{0}'", country));
        wb.Worksheets.Add(dt, country);
    }
    wb.SaveAs(folderPath + "DueCorrespondences.xlsx");
}

private DataTable GetData(string query)
{
    string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);

                    return dt;
                }
            }
        }
    }
}

VB.Net

Protected Sub Export(ByVal sender As Object, ByVal e As EventArgs)
    Dim countries As DataTable = GetData("SELECT DISTINCT Country FROM Employees")
    Dim folderPath As String = Server.MapPath("~/Data/")
    If Not Directory.Exists(folderPath) Then
        Directory.CreateDirectory(folderPath)
    End If
    Dim wb As XLWorkbook = New XLWorkbook()
    For Each dr As DataRow In countries.Rows
        Dim country As String = dr(0).ToString()
        Dim dt As DataTable = GetData(String.Format("SELECT EmployeeID, FirstName, Country FROM Employees WHERE Country = '{0}'", country))
        wb.Worksheets.Add(dt, country)
    Next
    wb.SaveAs(folderPath & "DueCorrespondences.xlsx")
End Sub

Private Function GetData(ByVal query As String) As DataTable
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(strConnString)
        Using cmd As SqlCommand = New SqlCommand(query)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Using
End Function

Screenshot