Post
Ask Question

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

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

  1. CREATE TABLE [dbo].[Employee]  
  2. (  
  3. [ID] [int] IDENTITY(1, 1) NOT NULL,  
  4. [Name] [varchar](50) NULL,  
  5. [Email] [varchar](500) NULL,  
  6. [Country] [varchar](50) NULL  
  7. ) ON [PRIMARY] GO
Data in Employee Table


 
OrderDetails



Script of OrderDetails Table
  1. CREATE TABLE [dbo].[OrderDetails](  
  2. [Order_ID] [int] IDENTITY(1,1) NOT NULL,  
  3. [Customer_Name] [varchar](50) NULL,  
  4. [Unit] [int] NULL,  
  5. [Month] [varchar](50) NULL,  
  6. CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED   
  7. (  
  8. [Order_ID] ASC  
  9. )WITH (PAD_INDEX = OFF,   
  10. STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,   
  11. ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  12. ) ON [PRIMARY]  
  13.   
  14. GO
Data in OrderDetails table



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



.aspx code
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ExportDataTableToExcel.Default" %>  
  2. <!DOCTYPE html>  
  3. <html  
  4.     xmlns="http://www.w3.org/1999/xhtml">  
  5.     <head runat="server">  
  6.         <title>Export Multiple Data Tables to Multiple worksheets inside a single Excel File</title>  
  7.     </head>  
  8.     <body>  
  9.         <form id="form1" runat="server">  
  10.             <div>  
  11.                 <table border="0" cellpadding="5" cellspacing="5" style="border: solid 2px Red; background-color: skyblue; width: 100%;">  
  12.                     <tr>  
  13.                         <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>  
  14.                     </tr>  
  15.                     <tr>  
  16.                         <td style="text-align: center;">  
  17.                             <asp:Button ID="Button1" runat="server" Text="Click To Export Data " OnClick="btn_Export_Click" />  
  18.                         </td>  
  19.                     </tr>  
  20.                 </table>  
  21.             </div>  
  22.         </form>  
  23.     </body>  
  24. </html>  

.aspx.cs code

  1. using ClosedXML.Excel;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Configuration;  
  5. using System.Data;  
  6. using System.Data.SqlClient;  
  7. using System.IO;  
  8. using System.Linq;  
  9. using System.Web;  
  10. using System.Web.UI;  
  11. using System.Web.UI.WebControls;  
  12. namespace ExportDataTableToExcel {  
  13.     public partial class Default: System.Web.UI.Page {  
  14.         protected void Page_Load(object sender, EventArgs e) {  
  15.         }  
  16.         private DataTable getAllEmployeesList() {  
  17.             string constr = ConfigurationManager.ConnectionStrings["RConnection"].ConnectionString;  
  18.             using(SqlConnection con = new SqlConnection(constr)) {  
  19.                 using(SqlCommand cmd = new SqlCommand("SELECT * FROM Employee ORDER BY ID")) {  
  20.                     using(SqlDataAdapter da = new SqlDataAdapter()) {  
  21.                         DataTable dt = new DataTable();  
  22.                         cmd.CommandType = CommandType.Text;  
  23.                         cmd.Connection = con;  
  24.                         da.SelectCommand = cmd;  
  25.                         da.Fill(dt);  
  26.                         return dt;  
  27.                     }  
  28.                 }  
  29.             }  
  30.         }  
  31.         private DataTable getAllEmployeesOrderList() {  
  32.             string constr = ConfigurationManager.ConnectionStrings["RConnection"].ConnectionString;  
  33.             using(SqlConnection con = new SqlConnection(constr)) {  
  34.                 using(SqlCommand cmd = new SqlCommand("SELECT * FROM OrderDetails ORDER BY Order_ID")) {  
  35.                     using(SqlDataAdapter da = new SqlDataAdapter()) {  
  36.                         DataTable dt = new DataTable();  
  37.                         cmd.CommandType = CommandType.Text;  
  38.                         cmd.Connection = con;  
  39.                         da.SelectCommand = cmd;  
  40.                         da.Fill(dt);  
  41.                         return dt;  
  42.                     }  
  43.                 }  
  44.             }  
  45.         }  
  46.         public DataSet getDataSetExportToExcel() {  
  47.             DataSet ds = new DataSet();  
  48.             DataTable dtEmp = new DataTable("Employee");  
  49.             dtEmp = getAllEmployeesList();  
  50.   
  51.             DataTable dtEmpOrder = new DataTable("Order List");  
  52.             dtEmpOrder = getAllEmployeesOrderList();  
  53.             ds.Tables.Add(dtEmp);  
  54.             ds.Tables.Add(dtEmpOrder);  
  55.             return ds;  
  56.         }  
  57.         protected void btn_Export_Click(object sender, EventArgs e) {  
  58.             DataSet ds = getDataSetExportToExcel();  
  59.             using(XLWorkbook wb = new XLWorkbook()) {  
  60.                 wb.Worksheets.Add(ds);  
  61.                 wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;  
  62.                 wb.Style.Font.Bold = true;  
  63.                 Response.Clear();  
  64.                 Response.Buffer = true;  
  65.                 Response.Charset = "";  
  66.                 Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
  67.                 Response.AddHeader("content-disposition", "attachment;filename= EmployeeAndOrderReport.xlsx");  
  68.                 using(MemoryStream MyMemoryStream = new MemoryStream()) {  
  69.                     wb.SaveAs(MyMemoryStream);  
  70.                     MyMemoryStream.WriteTo(Response.OutputStream);  
  71.                     Response.Flush();  
  72.                     Response.End();  
  73.                 }  
  74.             }  
  75.         }  
  76.     }  
  77. }  
As shown in the following code, I have declared the connection string in web.config file.
  1. <?xml version="1.0"?>  
  2.   
  3. <!--  
  4. For more information on how to configure your ASP.NET application, please visit  
  5. http://go.microsoft.com/fwlink/?LinkId=169433  
  6. -->  
  7.   
  8. <configuration>  
  9. <system.web>  
  10. <compilation debug="true" targetFramework="4.5" />  
  11. <httpRuntime targetFramework="4.5" />  
  12. </system.web>  
  13. <connectionStrings>  
  14. <add name="RConnection" connectionString="Server=INDIA\MSSQLServer2k8;database=TestDB;UID=sa; pwd=india;"/>  
  15. </connectionStrings>  
  16. </configuration>  
Next, run your application.









Now you can see both tables in separate worksheets in a single Excel sheet.


Similar Articles