-->

22 February 2020

Home » » Export to Excel – Multiple GridView into Multiple Worksheet

Export to Excel – Multiple GridView into Multiple Worksheet

  Asp.Net CS By Example       22 February 2020
 Export to Excel – Multiple GridView into Multiple Worksheet 

 In this article we are learning how to export Multiple GridView into Multiple Worksheet of excel file. There are many article available on web in which explain us functionality "Export gridview data into excel". Which is like "Exporting single gridview data into excel workbook with single worksheet". and there are also many article in which we can export data into multiple sheet but using third party library. What if we want to export multiple gridview into multiple sheet without using any dll. Here is the example.

  Below examples to illustrate the How to Multiple GridView into Multiple Worksheet of excel.


  Code: GridViewDownloadAsExcel.aspx 
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridViewDownloadAsExcel.aspx.cs"
    Inherits="LearnAsp.Net.ControlDemo.ViewFile.GridViewDownloadAsExcel" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>GridView Download as Excel File</title>
</head>
<body>
    <form id="form1" runat="server">
    <div class="form-group">
        <div class="col-md-6">
            <asp:ScriptManager ID="ScriptManager1" runat="server">
            </asp:ScriptManager>
            <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="conditional">
                <Triggers>
                    <asp:PostBackTrigger ControlID="btnExportExcel" />
                </Triggers>
                <ContentTemplate>
                    <asp:Button ID="btnExportExcel" CssClass="btn btn-primary" runat="server" Text="Export To Excel"
                        ValidationGroup="A" Style="margin-top: 0px;" OnClick="btnExportExcel_Click" />
                </ContentTemplate>
            </asp:UpdatePanel>
        </div>
        <br />
        <asp:GridView ID="grdEmp" runat="server" AlternatingRowStyle-CssClass="GrdAltRow"            
            CssClass="table table-striped table-bordered table-hover dataTables1-example">
            <RowStyle CssClass="GrdAltRow" />
        </asp:GridView>
        <br />
         <asp:GridView ID="grdDept" runat="server" AlternatingRowStyle-CssClass="GrdAltRow"            
            CssClass="table table-striped table-bordered table-hover dataTables1-example">
            <RowStyle CssClass="GrdAltRow" />
        </asp:GridView>
    </div>
    </form>
</body>
</html>

  Code: GridViewDownloadAsExcel.aspx.cs 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;

namespace LearnAsp.Net.ControlDemo.ViewFile
{
    public partial class GridViewDownloadAsExcel : System.Web.UI.Page
    {
        #region "ShowMessage" Show Alert Popup Message
        public void ShowMessage(String Msg, String WinLoc)
        {
            String str = "";
            str = "alert('|| " + Msg + " ||');";

            if (WinLoc != "")
                str += "window.location = '" + WinLoc + "';";

            ScriptManager.RegisterStartupScript(this, typeof(Page), UniqueID, str, true);
            return;
        }
        #endregion

        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                if (!IsPostBack)
                {
                    grdEmp.DataSource = GetEmpData();
                    grdEmp.DataBind();
                    grdDept.DataSource = GetDeptData();
                    grdDept.DataBind();
                }
            }
            catch (Exception ex)
            {
                ShowMessage(ex.ToString(), "");
            }
        }

        public override void VerifyRenderingInServerForm(Control control)
        { /* Do nothing */ }


        private DataTable GetEmpData()
        {
            // Here we create a DataTable with four columns.
            DataTable table = new DataTable("Employee");
            table.Columns.Add("EmpNo", typeof(int));
            table.Columns.Add("EmpName", typeof(string));
            table.Columns.Add("Design", typeof(string));
            table.Columns.Add("Salary", typeof(int));

            // Here we add four DataRows.
            table.Rows.Add(7369, "SMITH", "CLERK", 8000);
            table.Rows.Add(7499, "ALLEN", "SALESMAN", 1600);
            table.Rows.Add(7521, "WARD ", "SALESMAN", 1250);
            table.Rows.Add(7566, "JONES", "MANAGER ", 2975);
            return table;
        }

        private DataTable GetDeptData()
        {
            // Here we create a DataTable with four columns.
            DataTable table = new DataTable("Department");
            table.Columns.Add("DeptNo", typeof(int));
            table.Columns.Add("DeptName", typeof(string));
            table.Columns.Add("Location", typeof(string));

            // Here we add five DataRows.
            table.Rows.Add(10, "ACCOUNTING", "NEW YORK");
            table.Rows.Add(20, "RESEARCH", "DALLAS");
            table.Rows.Add(30, "SALES", "CHICAGO");
            table.Rows.Add(40, "OPERATIONS", "BOSTON");
            table.Rows.Add(50, "IT", "Mumbai");
            return table;
        }

        protected void btnExportExcel_Click(object sender, EventArgs e)
        {
            object[] myGridViews = new object[2];
            myGridViews[0] = GetEmpData();
            myGridViews[1] = GetDeptData();
            CreateExcelFile(myGridViews, "Company detials.xls", 100);
        }

        private void CreateExcelFile(object[] cList, string FileName, int CellWidth)
        {
            string attachment = "attachment; filename=\"" + FileName;
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.AddHeader("content-disposition", attachment);
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            System.IO.StringWriter sw = new System.IO.StringWriter();
            sw.WriteLine("<?xml version=\"1.0\"?>");
            sw.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
            sw.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
            sw.WriteLine("xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
            sw.WriteLine("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
            sw.WriteLine("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
            sw.WriteLine("xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
            sw.WriteLine("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
            sw.WriteLine("<LastAuthor>Try Not Catch</LastAuthor>");
            sw.WriteLine("<Created>2020-02-22T21:31:20</Created>");
            sw.WriteLine("<Version>11.9999</Version>");
            sw.WriteLine("</DocumentProperties>");
            sw.WriteLine("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
            sw.WriteLine("<WindowHeight>9210</WindowHeight>");
            sw.WriteLine("<WindowWidth>19035</WindowWidth>");
            sw.WriteLine("<WindowTopX>0</WindowTopX>");
            sw.WriteLine("<WindowTopY>90</WindowTopY>");
            sw.WriteLine("<ProtectStructure>False</ProtectStructure>");
            sw.WriteLine("<ProtectWindows>False</ProtectWindows>");
            sw.WriteLine("</ExcelWorkbook>");
            sw.WriteLine("<Styles>");
            sw.WriteLine("<Style ss:ID=\"Default\" ss:Name=\"Normal\">");
            sw.WriteLine("<Alignment ss:Vertical=\"Bottom\"/>");
            sw.WriteLine("<Borders/>");
            sw.WriteLine("<Font/>");
            sw.WriteLine("<Interior/>");
            sw.WriteLine("<NumberFormat/>");
            sw.WriteLine("<Protection/>");
            sw.WriteLine("</Style>");
            sw.WriteLine("<Style ss:ID=\"s22\">");
            sw.WriteLine("<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
            sw.WriteLine("<Borders>");
            sw.WriteLine("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
            sw.WriteLine("ss:Color=\"#000000\"/>");
            sw.WriteLine("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
            sw.WriteLine("ss:Color=\"#000000\"/>");
            sw.WriteLine("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
            sw.WriteLine("ss:Color=\"#000000\"/>");
            sw.WriteLine("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
            sw.WriteLine("ss:Color=\"#000000\"/>");
            sw.WriteLine("</Borders>");
            sw.WriteLine("<Font ss:Bold=\"1\"/>");
            sw.WriteLine("</Style>");
            sw.WriteLine("<Style ss:ID=\"s23\">");
            sw.WriteLine("<Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
            sw.WriteLine("<Borders>");
            sw.WriteLine("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
            sw.WriteLine("ss:Color=\"#000000\"/>");
            sw.WriteLine("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
            sw.WriteLine("ss:Color=\"#000000\"/>");
            sw.WriteLine("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
            sw.WriteLine("ss:Color=\"#000000\"/>");
            sw.WriteLine("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
            sw.WriteLine("ss:Color=\"#000000\"/>");
            sw.WriteLine("</Borders>");
            sw.WriteLine("</Style>");
            sw.WriteLine("<Style ss:ID=\"s24\">");
            sw.WriteLine("<Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
            sw.WriteLine("<Borders>");
            sw.WriteLine("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
            sw.WriteLine("ss:Color=\"#000000\"/>");
            sw.WriteLine("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
            sw.WriteLine("ss:Color=\"#000000\"/>");
            sw.WriteLine("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
            sw.WriteLine("ss:Color=\"#000000\"/>");
            sw.WriteLine("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
            sw.WriteLine("ss:Color=\"#000000\"/>");
            sw.WriteLine("</Borders>");
            sw.WriteLine("<Font ss:Color=\"#FFFFFF\"/>");
            sw.WriteLine("<Interior ss:Color=\"#FF6A6A\" ss:Pattern=\"Solid\"/>");
            
            //set header colour here
            sw.WriteLine("</Style>");
            sw.WriteLine("</Styles>");
            int i = 1;
            foreach (DataTable myTable in cList)
            {
                CreateWorkSheet(myTable.TableName, sw, myTable, CellWidth);
                i++;
            }
            sw.WriteLine("</Workbook>");
            HttpContext.Current.Response.Write(sw.ToString());
            HttpContext.Current.Response.End();
        }
        private void CreateWorkSheet(string SheetName, StringWriter sw, DataTable dt, int cellwidth)
        {
            if (dt.Columns.Count > 0)
            {
                sw.WriteLine("<Worksheet ss:Name=\"" + SheetName + "\">");
                int cCount = dt.Columns.Count;
                long rCount = dt.Rows.Count + 1; 
                sw.WriteLine("<Table ss:ExpandedColumnCount=\"" + cCount + "\" ss:ExpandedRowCount=\"" + rCount + "\" x:FullColumns=\"1\"");
                sw.WriteLine("x:FullRows=\"1\">");
                for (int i = (cCount - cCount); i <= (cCount - 1); i++)
                {
                    sw.WriteLine(" <Column ss:AutoFitWidth=\"1\" ss:Width=\"" + cellwidth + "\"/> ");
                }
                DataTableRowIteration(dt, sw);
                sw.WriteLine("</Table>");
                sw.WriteLine("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
                sw.WriteLine("<Selected/>");
                sw.WriteLine("<DoNotDisplayGridlines/>");
                sw.WriteLine("<ProtectObjects>False</ProtectObjects>");
                sw.WriteLine("<ProtectScenarios>False</ProtectScenarios>");
                sw.WriteLine("</WorksheetOptions>");
                sw.WriteLine("</Worksheet>");
            }
        }

        private void DataTableRowIteration(DataTable dt, StringWriter sw)
        {
            sw.WriteLine("");
            sw.WriteLine("<Row>");
            foreach (DataColumn dc in dt.Columns)
            {
                string tcText = dc.ColumnName;
                sw.WriteLine("<Cell><Data ss:Type=\"String\">" + tcText + "</Data></Cell>");
            }
            sw.WriteLine("</Row>");
            sw.WriteLine("");
            foreach (DataRow dr in dt.Rows)
            {
                sw.WriteLine("");
                sw.WriteLine("<Row>");
                foreach (DataColumn tc in dt.Columns)
                {
                    string gcText = dr[tc].ToString();
                    sw.WriteLine("<Cell><Data ss:Type=\"String\">" + gcText + "</Data></Cell>");
                }
                sw.WriteLine("</Row>");
                sw.WriteLine("");
            }
        }
    }
}

  Output:
logoblog

Thanks for reading Export to Excel – Multiple GridView into Multiple Worksheet

Previous
« Prev Post
Next
Next Post »
By at
Labels:

1 comment:

Please do not enter any spam link in the comment box.

Copyright © Asp.Net C# Examples. All rights reserved. Blog Templates by CB Blogger.