Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

Export multiple Data Tables to Multiple worksheets inside a single Excel File

Article:
Viewed:  5034 
Posted On:  20/05/2015 20:48:45 
How we can export multiple Data Tables to Multiple worksheets inside a single Excel File ? 

In this article I am going to show how we can export multiple Data Tables to Multiple worksheets inside a single Excel File in asp.net c#.

Below are my 2 Data Table Which I will export.

Employee

1.png

Image 1.

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:

2.png

Image 2.

My Second Table is:  OrderDetails

3.png

Image 3.

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:

 

4.png

Image 4.

Now I add closedXML reference into my application:

5.png

Image 5.

Below is my 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>
 

My aspx.cs code is:

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();
                }
            }
        }
    }
}

I declared my connection string in my 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>

Now run your application:

6.png

Image 6.

7.png

Image 7.

8.png

Image 8.

9.png

Image 9.

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

  Comment:
 
By gyan  On  09/08/2016 01:36:10
Hi according to my requirement I want to get both the datatables in a single worksheet under different headers. How can I do that ?
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us