Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Export to Excel – Multiple GridView into Multiple Worksheet

, 22 Jan 2013
Rate this:
Please Sign up or sign in to vote.
Multiple GridView into multiple worksheets.

There are many articles available on the web which explain the functionality “Export gridview data into Excel“. Which is like “Exporting a single gridview data into an Excel workbook with a single worksheet“.

What-if we want to “Export multiple gridview data into multiple worksheets”? I decided to write an article which explains such a scenario.

The focus of the article is the export to Excel functionality – Gridview and its data binding are only for demonstrating the export functionality.

Here it goes, step-by-step code snippets which help us to achieve the functionality mentioned above.

Step 1: Create a simple website and add two gridviews like this:

<form id="form1" runat="server">
<div>
    <h1>Patient Data</h1>
    <br />
    <asp:GridView ID="gvPatient" runat="server">
    </asp:GridView>
    <h1>Student Data</h1>
    <br />
    <asp:GridView ID="gvStudent" runat="server">
    </asp:GridView>
    <br />
    <asp:Button ID="btnExportBoth" runat="server"
        Text="Export Both Grid Data to Excel" onclick="btnExportBoth_Click" />
</div>
</form>

Step 2: Create properties which help us to store the datatable data.

/// <summary>
/// Property to store patient datatable
/// </summary>
public DataTable PatientData
{
   get { return (DataTable)(ViewState["PatientData"] ?? null); }
   set { ViewState["PatientData"] = value; }
}

/// <summary>
/// Property to store student datatable
/// </summary>
public DataTable StudentData
{
   get { return (DataTable)(ViewState["StudentData"] ?? null); }
   set { ViewState["StudentData"] = value; }
}

Step 3: Page_Load event will load the sample data in Gridview on the page [Patient and Student data, respectively]

protected void Page_Load(object sender, System.EventArgs e)
{
    DataTable dt = new DataTable();
    //Patient Data Binding
    dt = GetPatientData();
    gvPatient.DataSource = dt;
    gvPatient.DataBind();
    PatientData = dt;
    //Student Data Binding
    dt = GetStudentData();
    gvStudent.DataSource = dt;
    gvStudent.DataBind();
    StudentData = dt;
}

/// <summary>
/// Get Patient Data - Sample Data creation
/// </summary>
/// <returns></returns>
private DataTable GetPatientData()
{
    // Here we create a DataTable with four columns.
    DataTable table = new DataTable("Patients");
    table.Columns.Add("Dosage", typeof(int));
    table.Columns.Add("Drug", typeof(string));
    table.Columns.Add("Patient", typeof(string));
    table.Columns.Add("Date", typeof(DateTime));

    // Here we add five DataRows.
    table.Rows.Add(25, "Indocin", "David", DateTime.Now);
    table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
    table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
    table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
    table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
    return table;
}

/// <summary>
/// Get Student Data - Sample Data creation
/// </summary>
/// <returns></returns>
private DataTable GetStudentData()
{
    // Here we create a DataTable with four columns.
    DataTable table = new DataTable("Students");
    table.Columns.Add("SrNo", typeof(int));
    table.Columns.Add("FirstName", typeof(string));
    table.Columns.Add("LastName", typeof(string));
    table.Columns.Add("Age", typeof(int));

    // Here we add five DataRows.
    table.Rows.Add(1, "Sandeep", "Ramani", 29);
    table.Rows.Add(2, "Kapil", "Bhaai", 28);
    table.Rows.Add(3, "Vinit", "Shah", 28);
    table.Rows.Add(4, "Samir", "Bhaai", 30);
    table.Rows.Add(5, "Umang", "Samani", 29);
    return table;
}

Step 4: Here comes the main part which we are waiting for. Export button click event logic along with methods required to create workbook and worksheets.

/// <summary>
/// Export button click event
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExportBoth_Click(object sender, EventArgs e)
{
    object[] myGridViews = new object[2];
    myGridViews[0] = PatientData;
    myGridViews[1] = StudentData;
    CreateWorkBook(myGridViews, "ExportToExcel", 80);
}

/// <summary>
/// Method to create workbook
/// </summary>
/// <param name="cList"></param>
/// <param name="wbName"></param>
/// <param name="CellWidth"></param>
private void CreateWorkBook(object[] cList, string wbName, int CellWidth)
{
   string attachment = "attachment; filename=\"" + wbName + ".xml\"";
   HttpContext.Current.Response.ClearContent();
   HttpContext.Current.Response.AddHeader("content-disposition", attachment);
   HttpContext.Current.Response.ContentType = "application/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>2013-01-09T19:14:19Z</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>");
   foreach (DataTable myTable in cList)
   {
      CreateWorkSheet(myTable.TableName, sw, myTable, CellWidth);
   }
   sw.WriteLine("</Workbook>");
   HttpContext.Current.Response.Write(sw.ToString());
   HttpContext.Current.Response.End();
}

/// <summary>
/// Method to create worksheet
/// </summary>
/// <param name="wsName"></param>
/// <param name="sw"></param>
/// <param name="dt"></param>
/// <param name="cellwidth"></param>
private void CreateWorkSheet(string wsName, System.IO.StringWriter sw, DataTable dt, int cellwidth)
{
    if (dt.Columns.Count > 0)
    {
       sw.WriteLine("<Worksheet ss:Name=\"" + wsName + "\">");
       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>");
   }
}

/// <summary>
/// Method to create rows by iterating thru datatable rows
/// </summary>
/// <param name="dt"></param>
/// <param name="sw"></param>
private void DataTableRowIteration(DataTable dt, System.IO.StringWriter sw)
{
     sw.WriteLine("");
     foreach (DataColumn dc in dt.Columns)
     {
        string tcText = dc.ColumnName;
        sw.WriteLine("<data>" + tcText + "</data>");
     }
     sw.WriteLine("");
     foreach (DataRow dr in dt.Rows)
     {
        sw.WriteLine("");
        foreach (DataColumn tc in dt.Columns)
       {
          string gcText = dr[tc].ToString();
          sw.WriteLine("<data>" + gcText + "</data>");
       }
       sw.WriteLine("");
     }
}

The above code helps us to create worksheets as per requirements. You just need to pass the object list [DataTable objects]. So it will iterate thru each one and create worksheets for each of them.

Please make sure you create Datatable with TableName so that we can create a workbook with different worksheet names. I have used the datatable name as the worksheet name.

Hope this will help!!!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sandeep Ramani
Software Developer (Senior) Cognizant
India India
Sandeep is a passionate .NET developer.

He is also certified as Microsoft Certified Technologies Specialist - Web Applications Development with Microsoft .NET Framework 4.
 
He is also awarded as Microsoft Community Contributor of the year 2011.
 
He has also received several awards at various forums and his various articles got listed as "Article of the day" at ASP.NET Microsoft Official Website www.asp.net.

He has done MCA from Gujarat University.
 
Visit his Blog:
http://ramanisandeep.wordpress.com/


Area of Expertise:
C#, ASP.NET , AJAX, Java script, JQuery, JSON, XML, Web Services, WCF, SSIS 2005, SQL Server 2005/2008, ASP.NET MVC
 
He is fond of movies, music, cricket, hockey and boxing.
Follow on   Twitter

Comments and Discussions

 
SuggestionThe format of forming multiple sheets in excel via. xml PinmemberMember 80771973-May-14 15:20 
BugDoes not work [modified] Pinmembersledhead4-Dec-13 8:35 
GeneralMy vote of 1 [modified] Pinmembersledhead26-Nov-13 11:31 
GeneralMy vote of 1 PinmemberDaNeo019-May-13 12:02 
QuestionCode is not working, Pinmemberpuneetky25-Apr-13 2:45 
QuestionERRORE DI ANALISI XML Pinmembereljhonb21-Mar-13 4:59 
AnswerRe: ERRORE DI ANALISI XML PinmemberUnervous2-Oct-13 10:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140926.1 | Last Updated 22 Jan 2013
Article Copyright 2013 by Sandeep Ramani
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid