How to Export Multiple Data Tables to Multiple Worksheets Inside a Single Excel File

Rahul Saxena
9y
80.1k
0
2

100

Article

ExportDataTableToExcel1.zip

This article shows how to export multiple data tables to multiple worksheets inside a single excel file in ASP.Net C#.

The following are the two data tables that I will export.

Employee

Script of Employee Table

CREATE TABLE [dbo].[Employee]
(
[ID] [int] IDENTITY(1, 1) NOT NULL,
[Name] [varchar](50) NULL,
[Email] [varchar](500) NULL,
[Country] [varchar](50) NULL
) ON [PRIMARY] GO

Data in Employee Table

OrderDetails

Script of OrderDetails Table

CREATE TABLE [dbo].[OrderDetails](
[Order_ID] [int] IDENTITY(1,1) NOT NULL,
[Customer_Name] [varchar](50) NULL,
[Unit] [int] NULL,
[Month] [varchar](50) NULL,
CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED
(
[Order_ID] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Data in OrderDetails table

Now, I will add a closedXML reference to my application.

.aspx code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ExportDataTableToExcel.Default" %>
<!DOCTYPE html>
<html
xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Export Multiple Data Tables to Multiple worksheets inside a single Excel File</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table border="0" cellpadding="5" cellspacing="5" style="border: solid 2px Red; background-color: skyblue; width: 100%;">
<tr>
<td colspan="2" style="background-color: #f00; color: white; font-weight: bold; font-size: 12pt; text-align: center; font-family: Verdana;">Export multiple Data Tables to Multiple worksheets inside a single Excel File</td>
</tr>
<tr>
<td style="text-align: center;">
<asp:Button ID="Button1" runat="server" Text="Click To Export Data " OnClick="btn_Export_Click" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>

.aspx.cs code

using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ExportDataTableToExcel {
public partial class Default: System.Web.UI.Page {
protected void Page_Load(object sender, EventArgs e) {
}
private DataTable getAllEmployeesList() {
string constr = ConfigurationManager.ConnectionStrings["RConnection"].ConnectionString;
using(SqlConnection con = new SqlConnection(constr)) {
using(SqlCommand cmd = new SqlCommand("SELECT * FROM Employee ORDER BY ID")) {
using(SqlDataAdapter da = new SqlDataAdapter()) {
DataTable dt = new DataTable();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
da.Fill(dt);
return dt;
}
}
}
}
private DataTable getAllEmployeesOrderList() {
string constr = ConfigurationManager.ConnectionStrings["RConnection"].ConnectionString;
using(SqlConnection con = new SqlConnection(constr)) {
using(SqlCommand cmd = new SqlCommand("SELECT * FROM OrderDetails ORDER BY Order_ID")) {
using(SqlDataAdapter da = new SqlDataAdapter()) {
DataTable dt = new DataTable();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
da.Fill(dt);
return dt;
}
}
}
}
public DataSet getDataSetExportToExcel() {
DataSet ds = new DataSet();
DataTable dtEmp = new DataTable("Employee");
dtEmp = getAllEmployeesList();
DataTable dtEmpOrder = new DataTable("Order List");
dtEmpOrder = getAllEmployeesOrderList();
ds.Tables.Add(dtEmp);
ds.Tables.Add(dtEmpOrder);
return ds;
}
protected void btn_Export_Click(object sender, EventArgs e) {
DataSet ds = getDataSetExportToExcel();
using(XLWorkbook wb = new XLWorkbook()) {
wb.Worksheets.Add(ds);
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.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= EmployeeAndOrderReport.xlsx");
using(MemoryStream MyMemoryStream = new MemoryStream()) {
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
}
}

As shown in the following code, I have declared the connection string in web.config file.

<?xml version="1.0"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.5" />
<httpRuntime targetFramework="4.5" />
</system.web>
<connectionStrings>
<add name="RConnection" connectionString="Server=INDIA\MSSQLServer2k8;database=TestDB;UID=sa; pwd=india;"/>
</connectionStrings>
</configuration>