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]
  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();
  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"?>
  3. <!--
  4. For more information on how to configure your ASP.NET application, please visit
  5. http://go.microsoft.com/fwlink/?LinkId=169433
  6. -->
  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.