Export multiple query result to one excel sheet using C# and VB.Net in ASP.Net

Hi itsme,

Save the query resutl in DataTable. Add the Rows from each DataTable to one and export it to excel. 

Check this example. Now please take its reference and correct your code.

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="OnExport" />

Namespaces

C#

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

VB.Net

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

Code

C#

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

        return dt;
    }
}

protected void OnExport(object sender, EventArgs e)
{
    SqlCommand cmd = new SqlCommand("SELECT TOP 5 CustomerID,ContactName,City,Country FROM Customers");
    DataTable dt1 = GetData(cmd);

    cmd = new SqlCommand("SELECT TOP 5 EmployeeID,FirstName + ' ' + LastName AS 'Name',City,Country FROM Employees");
    DataTable dt2 = GetData(cmd);

    DataTable dt = new DataTable("Data");
    dt.Columns.AddRange(new DataColumn[]
    {
        new DataColumn("Id"),
        new DataColumn("Name"),
        new DataColumn("City"),
        new DataColumn("Country")
    });
    using (XLWorkbook wb = new XLWorkbook())
    {
        foreach (DataRow row in dt1.Rows)
        {
            dt.Rows.Add();
            for (int j = 0; j < row.ItemArray.Length; j++)
            {
                dt.Rows[dt.Rows.Count - 1][j] = row.ItemArray[j].ToString().Trim();
            }
        }

        foreach (DataRow row in dt2.Rows)
        {
            dt.Rows.Add();
            for (int j = 0; j < row.ItemArray.Length; j++)
            {
                dt.Rows[dt.Rows.Count - 1][j] = row.ItemArray[j].ToString().Trim();
            }
        }

        var ws = wb.Worksheets.Add(dt);
        ws.Table(0).ShowAutoFilter = false;
        ws.Table(0).Theme = XLTableTheme.None;
        ws.Columns().AdjustToContents();
        ws.Row(1).Style.Font.Bold = true;
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;filename=Data.xlsx");

        using (MemoryStream memoryStream = new MemoryStream())
        {
            wb.SaveAs(memoryStream);
            memoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }
}

VB.Net

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

Protected Sub OnExport(ByVal sender As Object, ByVal e As EventArgs)
    Dim cmd As SqlCommand = New SqlCommand("SELECT TOP 5 CustomerID,ContactName,City,Country FROM Customers")
    Dim dt1 As DataTable = GetData(cmd)
    cmd = New SqlCommand("SELECT TOP 5 EmployeeID,FirstName + ' ' + LastName AS 'Name',City,Country FROM Employees")
    Dim dt2 As DataTable = GetData(cmd)
    Dim dt As DataTable = New DataTable("Data")
    dt.Columns.AddRange(New DataColumn() {
                        New DataColumn("Id"),
                        New DataColumn("Name"),
                        New DataColumn("City"),
                        New DataColumn("Country")})

    Using wb As XLWorkbook = New XLWorkbook()
        For Each row As DataRow In dt1.Rows
            dt.Rows.Add()
            For j As Integer = 0 To row.ItemArray.Length - 1
                dt.Rows(dt.Rows.Count - 1)(j) = row.ItemArray(j).ToString().Trim()
            Next
        Next

        For Each row As DataRow In dt2.Rows
            dt.Rows.Add()
            For j As Integer = 0 To row.ItemArray.Length - 1
                dt.Rows(dt.Rows.Count - 1)(j) = row.ItemArray(j).ToString().Trim()
            Next
        Next

        Dim ws = wb.Worksheets.Add(dt)
        ws.Table(0).ShowAutoFilter = False
        ws.Table(0).Theme = XLTableTheme.None
        ws.Columns().AdjustToContents()
        ws.Row(1).Style.Font.Bold = True
        Response.Clear()
        Response.Buffer = True
        Response.Charset = ""
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        Response.AddHeader("content-disposition", "attachment;filename=Data.xlsx")

        Using memoryStream As MemoryStream = New MemoryStream()
            wb.SaveAs(memoryStream)
            memoryStream.WriteTo(Response.OutputStream)
            Response.Flush()
            Response.End()
        End Using
    End Using
End Sub

Screenshot