ASP Snippets

Categories






Alerts

Free Alerts

Your email will always be
private and will not be shared.




Follow us on twitter.




Zoom In | Zoom Out


Author is awarded Most Valuable Professional award by Microsoft ASP/ASP.Net

Exporting Multiple GridViews To Excel SpreadSheet in ASP.Net

Author:Mudassar Khan

I have already explained how to Export GridView to Excel in my following articles

Export GridView To Word/Excel/PDF/CSV in ASP.Net
Export GridView with Images from database to Word, Excel and PDF Formats
Export GridView with Images to Word, Excel and PDF Formats in ASP.Net

But this is something different and is hitting the forums quite frequently.

1. How to Export Multiple GridViews to Excel?

2. How to align GridViews in Excel?

 

And the article below is the working answer for the above questions.

 

GridViews

Below is the HTML markup of the two GridViews

<asp:GridView ID="GridView1" runat="server"

    AutoGenerateColumns = "false" Font-Names = "Arial"

    Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" 

    HeaderStyle-BackColor = "green" AllowPaging ="true"  

    OnPageIndexChanging = "OnPaging">

   <Columns>

    <asp:BoundField ItemStyle-Width = "150px" DataField = "CustomerID"

       HeaderText = "CustomerID" />

    <asp:BoundField ItemStyle-Width = "150px" DataField = "City"

       HeaderText = "City"/>

   </Columns>

</asp:GridView>

<br /><br />

<asp:GridView ID="GridView2" runat="server"

    AutoGenerateColumns = "false" Font-Names = "Arial"

    Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" 

    HeaderStyle-BackColor = "green" AllowPaging ="true"  

    OnPageIndexChanging = "OnPaging" >

   <Columns>

    <asp:BoundField ItemStyle-Width = "150px" DataField = "OrderID"

       HeaderText = "Order ID" />

    <asp:BoundField ItemStyle-Width = "150px" DataField = "CustomerID"

      HeaderText = "Customer ID"/>

   </Columns>

</asp:GridView>

 

   

    

Namespaces

You will need to import the following namespaces

C#

using System.IO;

using System.Text;

using System.Configuration;

 

 

VB.Net

Imports System.IO

Imports System.Text

Imports System.Configuration

 

Connection String

Below is the connection string used for this tutorial. As you will notice I am using the NorthWind SQL Server Data base which you can download by clicking here

 

<connectionStrings>

  <add name="conString" connectionString="Data Source=.\SQLEXPRESS;

               database=Northwind;Integrated Security=true" />

</connectionStrings>

           

 

DataBinding GridViews

  

Below is the how I bind the two GridViews in the Page Load Event of the ASP.Net Web Page. You will notice I am running a simple Select Query and binding the data to both the GridViews using the GetData function

C#

 

 

protected void Page_Load(object sender, EventArgs e)

{

    string strQuery = "select CustomerID,City,Country from customers";

    SqlCommand cmd = new SqlCommand(strQuery);

    DataTable dt = GetData(cmd);

    GridView1.DataSource = dt;

    GridView1.DataBind();

 

    strQuery = "select OrderID, CustomerID, EmployeeID from  Orders";

    cmd = new SqlCommand(strQuery);

    dt = GetData(cmd);

    GridView2.DataSource = dt;

    GridView2.DataBind();

}

 

private DataTable GetData(SqlCommand cmd)

{

    DataTable dt = new DataTable();

    String strConnString = ConfigurationManager

        .ConnectionStrings["conString"].ConnectionString;

    SqlConnection con = new SqlConnection(strConnString);

    SqlDataAdapter sda = new SqlDataAdapter();

    cmd.CommandType = CommandType.Text;

    cmd.Connection = con;

    try

    {

        con.Open();

        sda.SelectCommand = cmd;

        sda.Fill(dt);

        return dt;

    }

    catch (Exception ex)

    {

        throw ex;

    }

    finally

    {

        con.Close();

        sda.Dispose();

        con.Dispose();

    }

}

 

   

  

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

   Dim strQuery As String = "select CustomerID,City,Country from customers"

   Dim cmd As New SqlCommand(strQuery)

   Dim dt As DataTable = GetData(cmd)

   GridView1.DataSource = dt

   GridView1.DataBind()

 

   strQuery = "select OrderID, CustomerID, EmployeeID from Orders"

   cmd = New SqlCommand(strQuery)

   dt = GetData(cmd)

   GridView2.DataSource = dt

   GridView2.DataBind()

End Sub

 

Private Function GetData(ByVal cmd As SqlCommand) As DataTable

   Dim dt As New DataTable()

   Dim strConnString As String = ConfigurationManager _

             .ConnectionStrings("conString").ConnectionString()

   Dim con As New SqlConnection(strConnString)

   Dim sda As New SqlDataAdapter()

   cmd.CommandType = CommandType.Text

   cmd.Connection = con

   Try

     con.Open()

     sda.SelectCommand = cmd

     sda.Fill(dt)

     Return dt

   Catch ex As Exception

     Throw ex

   Finally

     con.Close()

     sda.Dispose()

     con.Dispose()

   End Try

End Function

 

The figure below displays the two GridViews we just populated using the NorthWind Database.


Multiple GridViews displayed on ASP.Net Webpage



Concept

Now in order to export multiple GridViews you will need to wrap the GridViews in a Control that will act as a container and then export that control instead of the GridViews. Hence here I am creating a fake dynamic table and exporting the table instead of GridViews.

   

 

Option of Paging Enable Disable and GridView Alignment

  

Here I provided the user facility whether user wants complete records or only the records for the current page. Secondly I also provided the choice whether the two GridViews should be aligned horizontally next to each other or vertically one after another using the two RadioButtonLists

<br />Paging Enabled?

<asp:RadioButtonList ID="rbPaging" runat="server">

<asp:ListItem  Text = "Yes" Value = "True" Selected = "True">

</asp:ListItem>

<asp:ListItem  Text = "No" Value = "False"></asp:ListItem>

</asp:RadioButtonList>

<br />

Export Preference

<asp:RadioButtonList ID="rbPreference" runat="server">

      <asp:ListItem  Text = "Vertical" Value = "1" Selected = "True">

</asp:ListItem>

      <asp:ListItem  Text = "Horizontal" Value = "2"></asp:ListItem>

</asp:RadioButtonList>

 

In the front end they look like below. As you can see user can choose how he wants the output in the Exported Excel file.


Option to Enable Disable Paging and also the Orientation while exporting



Exporting the Multiple GridViews

Now comes the final part that is exporting the two GridViews to Excel on the click of the Export Button

Refer the code below

   

         

C#

protected void btnExportExcel_Click(object sender, EventArgs e)

{

    Response.Clear();

    Response.Buffer = true;

 

    Response.AddHeader("content-disposition",

     "attachment;filename=GridViewExport.xls");

    Response.Charset = "";

    Response.ContentType = "application/vnd.ms-excel";

    StringWriter sw = new StringWriter();

    HtmlTextWriter hw = new HtmlTextWriter(sw);

 

    PrepareForExport(GridView1);

    PrepareForExport(GridView2);

 

    Table tb = new Table();

    TableRow tr1 = new TableRow();

    TableCell cell1 = new TableCell();

    cell1.Controls.Add(GridView1);

    tr1.Cells.Add(cell1);

    TableCell cell3 = new TableCell();

    cell3.Controls.Add(GridView2);

    TableCell cell2 = new TableCell();

    cell2.Text = "&nbsp;";

    if (rbPreference.SelectedValue == "2")

    {

        tr1.Cells.Add(cell2);

        tr1.Cells.Add(cell3);

        tb.Rows.Add(tr1);

    }

    else

    {

        TableRow tr2 = new TableRow();

        tr2.Cells.Add(cell2);  

        TableRow tr3 = new TableRow();

        tr3.Cells.Add(cell3);

        tb.Rows.Add(tr1);

        tb.Rows.Add(tr2);

        tb.Rows.Add(tr3);

    }

    tb.RenderControl(hw);

 

    //style to format numbers to string

    string style = @"<style> .textmode { mso-number-format:\@; } </style>";

    Response.Write(style);

    Response.Output.Write(sw.ToString());

    Response.Flush();

    Response.End();

}

 

        

 

VB.Net

Protected Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As EventArgs)

   Response.Clear()

   Response.Buffer = True

 

   Response.AddHeader("content-disposition", _

          "attachment;filename=GridViewExport.xls")

   Response.Charset = ""

   Response.ContentType = "application/vnd.ms-excel"

   Dim sw As New StringWriter()

   Dim hw As New HtmlTextWriter(sw)

 

   PrepareForExport(GridView1)

   PrepareForExport(GridView2)

 

   Dim tb As New Table()

   Dim tr1 As New TableRow()

   Dim cell1 As New TableCell()

   cell1.Controls.Add(GridView1)

   tr1.Cells.Add(cell1)

   Dim cell3 As New TableCell()

   cell3.Controls.Add(GridView2)

   Dim cell2 As New TableCell()

   cell2.Text = "&nbsp;"

   If rbPreference.SelectedValue = "2" Then

      tr1.Cells.Add(cell2)

      tr1.Cells.Add(cell3)

      tb.Rows.Add(tr1)

   Else

      Dim tr2 As New TableRow()

      tr2.Cells.Add(cell2)

      Dim tr3 As New TableRow()

      tr3.Cells.Add(cell3)

      tb.Rows.Add(tr1)

      tb.Rows.Add(tr2)

      tb.Rows.Add(tr3)

   End If

   tb.RenderControl(hw)

 

  'style to format numbers to string

   Dim style As String = "<style> .textmode { mso-number-format:\@; } </style>"

   Response.Write(style)

   Response.Output.Write(sw.ToString())

   Response.Flush()

   Response.End()

End Sub

 

As you will notice I am creating a Dynamic Table and adding the GridViews to the Dynamic Table Cells and then rendering the Table instead of the GridViews this is is the trick that makes it possible to export two or multiple GridViews to a Single Excel Sheet

You will notice the PrepareForExport Function which basically gives some formatting and styling to the GridView refer below.

   

C#

protected void PrepareForExport(GridView Gridview)

{

    Gridview.AllowPaging = Convert.ToBoolean(rbPaging.SelectedItem.Value);   

    Gridview.DataBind();

 

    //Change the Header Row back to white color

    Gridview.HeaderRow.Style.Add("background-color", "#FFFFFF");

 

    //Apply style to Individual Cells

    for (int k = 0; k < Gridview.HeaderRow.Cells.Count; k++)

    {

        Gridview.HeaderRow.Cells[k].Style.Add("background-color", "green");

    }

 

    for (int i = 0; i < Gridview.Rows.Count; i++)

    {

        GridViewRow row = Gridview.Rows[i];

 

        //Change Color back to white

        row.BackColor = System.Drawing.Color.White;

 

        //Apply text style to each Row

        row.Attributes.Add("class", "textmode");

 

        //Apply style to Individual Cells of Alternating Row

        if (i % 2 != 0)

        {

            for (int j = 0; j < Gridview.Rows[i].Cells.Count; j++)

            {

                row.Cells[j].Style.Add("background-color", "#C2D69B");

            }

        }

    }

}

  

          

  

VB.Net

 

Protected Sub PrepareForExport(ByVal Gridview As GridView)

   Gridview.AllowPaging = Convert.ToBoolean(rbPaging.SelectedItem.Value)

   Gridview.DataBind()

 

   'Change the Header Row back to white color

   Gridview.HeaderRow.Style.Add("background-color", "#FFFFFF")

 

   'Apply style to Individual Cells

   For k As Integer = 0 To Gridview.HeaderRow.Cells.Count - 1

       Gridview.HeaderRow.Cells(k).Style.Add("background-color", "green")

   Next

 

   For i As Integer = 0 To Gridview.Rows.Count - 1

       Dim row As GridViewRow = Gridview.Rows(i)

 

       'Change Color back to white

       row.BackColor = System.Drawing.Color.White

 

       'Apply text style to each Row

       row.Attributes.Add("class", "textmode")

 

       'Apply style to Individual Cells of Alternating Row

       If i Mod 2 <> 0 Then

          For j As Integer = 0 To Gridview.Rows(i).Cells.Count - 1

            row.Cells(j).Style.Add("background-color", "#C2D69B")

          Next

      End If

   Next

End Sub

 

The Screenshots below display the GridViews in the Horizontal and the Vertical Alignments

 

Vertical Alignment


Multiple GridViews aligned Vertically in the exported Excel File


Horizontal Alignment


Multiple GridViews aligned Horizontally in the exported Excel File


You might get the following errors. Click on the links below for the solution to resolve them

RegisterForEventValidation can only be called during Render()

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server


To View the Live demo Click here

 

You can download the complete source code in VB.Net and C# using the link below.

ExportMultipleGridViewsToExcel.zip (6.07 kb)

Posted: Jun 19 2009, 07:55 by Mudassar Khan | Comments (0) RSS comment feed |
Filed under: ASP.Net | C# | Excel | GridView | VB.Net

Views: 6444
Page copy protected against web site content infringement by Copyscape


If you like this article, help us grow by bookmarking this page on any social bookmarking site.
Bookmark and Share













Community News





Web Hosting SpotLight


Consulting


For consulting and work related queries click here.



Advertise


Advertise with us. For more details click here.


Suggestions


Please provide your valuable suggesstions here.

This Site is hosted on

Lunarpages.com Web Hosting