Thursday, January 19, 2012

Exporting gridview to excel with multiple headers

In the code behind declare GridViewRow globally for the page.
Refer the link merging-gridview-with-multiple-headers
Add the ImageButton in the aspx page.

<asp:ImageButton ToolTip="Csv" ID="btnCsvDown" runat="server" ImageUrl="~/Images/csv.jpg" OnClick="ibtnDownload_Click" CommandName="Csv"/>

<asp:ImageButton ToolTip="Word" ID="btnWordDown" runat="server" ImageUrl="~/Images/word-icon.png" OnClick="ibtnDownload_Click" CommandName="Word" />

<asp:ImageButton ToolTip="Excel" ID="btnExcelDown" runat="server" ImageUrl="~/Images/excel-icon.png" OnClick="ibtnDownload_Click" CommandName="Excel"/>

<asp:ImageButton ToolTip="Pdf" ID="btnPdfDown" runat="server" ImageUrl="~/Images/pdf-icon.png" OnClick="ibtnDownload_Click" CommandName="Pdf"/>

Click events for all the download Image buttons.
protected void ibtnDownload_Click(object sender, ImageClickEventArgs e)
    {
        ImageButton imgButton = sender as ImageButton;
        GridViewExportUtil.Export("Report.xls", this.gvStylish, HeaderRow,
            DateTime.Now.AddDays(-20).ToShortDateString(),
            DateTime.Now.ToShortDateString(), imgButton.CommandName.ToUpper());
    }
The below source code is developed by Mattberseth.
I have updated this, when we are exporting a gridview with multiple headers. mattberseth
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;

public class GridViewMultipleHeadersExportUtil
{
    public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "utf-8");
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.AddHeader("oCodepage", "65001");

        HttpContext.Current.Response.Charset = "utf-8";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1253");

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a table to contain the grid
                Table table = new Table();

                //  include the gridline settings
                table.GridLines = gv.GridLines;

                table.Rows.Add(CreateTableRow("Report Generated on: " +
                 DateTime.Now.ToString("MM/dd/yyyy"), 5));

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewMultipleHeadersExportUtil.PrepareControlForExport(gv.HeaderRow);

                    foreach(TableCell tcHeader in gv.HeaderRow.Cells )  
                        tcHeader.BackColor = System.Drawing.Color.LightGray;  
                    //gv.HeaderRow.BackColor = System.Drawing.Color.LightGray; 
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    //Set the default color  
                    System.Drawing.Color color = row.BackColor;
                    if (color != System.Drawing.Color.White)
                    {
                        row.BackColor = System.Drawing.Color.White;
                        foreach (TableCell tcRow in row.Cells)
                            tcRow.BackColor = color;
                    }

                    GridViewMultipleHeadersExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                //   if (gv.FooterRow != null)
                //   {
                //       GridViewMultipleHeadersExportUtil.PrepareControlForExport(gv.FooterRow);
                //       table.Rows.Add(gv.FooterRow);
                //   }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //render the htmlwriter into the response

                String s = sw.ToString();

                HttpContext.Current.Response.Write(s);
                HttpContext.Current.Response.End();
            }
        }
    }


    public static void Export(string fileName, GridView gv, GridViewRow HeaderRow, 
                              string fdate, string tdate, string strExportType)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);

        switch (strExportType)
        {
            case "WORD":
                HttpContext.Current.Response.ContentType = "application/ms-word";
                fileName = fileName.Replace(Path.GetExtension(fileName), ".doc");
                break;
            case "EXCEL":
                HttpContext.Current.Response.ContentType = "application/ms-excel";
                fileName = fileName.Replace(Path.GetExtension(fileName), ".xls");
                break;
            case "PDF":
                HttpContext.Current.Response.ContentType = "application/pdf";
                fileName = fileName.Replace(Path.GetExtension(fileName), ".pdf");
                break;
            case "CSV":
                HttpContext.Current.Response.ContentType = "application/text";
                fileName = fileName.Replace(Path.GetExtension(fileName), ".csv");
                break;
        }

        HttpContext.Current.Response.AddHeader(
    "content-disposition", string.Format("attachment; filename={0}", fileName));

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a table to contain the grid
                Table table = new Table();

                //  include the gridline settings
                table.GridLines = gv.GridLines;

                table.Rows.Add(CreateTableRow("Report from " + fdate + " to " + tdate, 5));
                table.Rows.Add(CreateTableRow("Report Generated on: " +
                 DateTime.Now.ToString("MM/dd/yyyy"), 5));

                //  add the new header row to the table
                if (HeaderRow != null)
                {
                    PrepareControlForExport(HeaderRow);

                    //This set entire excel row as LightGray
                    //HeaderRow.BackColor = System.Drawing.Color.LightGray;

                    //This set only columns we are exporting
                    foreach (TableCell tcHeader in HeaderRow.Cells)
                        tcHeader.BackColor = System.Drawing.Color.LightGray;

                    table.Rows.Add(HeaderRow);
                }

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    //Since we are merging the header cell. We have to remove the cell which are not needed
                    /**/
                    GridViewRow gridViewHeader = gv.HeaderRow;
                    for (int removeCell = 2; removeCell >= 0; removeCell--)
                        gridViewHeader.Cells.RemoveAt(removeCell);
                    
                    //This set only columns we are exporting
                    foreach (TableCell tcHeader in gridViewHeader.Cells)
                        tcHeader.BackColor = System.Drawing.Color.LightGray;
                    //gridViewHeader.BackColor = System.Drawing.Color.LightGray;
                    /**/

                    //GridViGridViewMultipleHeadersExportUtilewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    //table.Rows.Add(gv.HeaderRow);
                    GridViewMultipleHeadersExportUtil.PrepareControlForExport(gridViewHeader);
                    table.Rows.Add(gridViewHeader);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewMultipleHeadersExportUtil.PrepareControlForExport(row);

                    /**/
                    //Set the default color
                    System.Drawing.Color color = row.BackColor;

                    //Set the entire excel row as white
                    if (color != System.Drawing.Color.White)
                        row.BackColor = System.Drawing.Color.White;

                    if (row.Cells[0].Text.ToUpper().Contains("TOTAL") ||
                        row.Cells[0].Text.ToUpper().Contains("GRAND"))
                    {
                        color = System.Drawing.Color.LightGray;
                        row.Font.Bold = true;
                    }

                    //Set the color only for the Total and Grand rows
                    if (color != System.Drawing.Color.White)
                        foreach (TableCell tcRow in row.Cells)
                            tcRow.BackColor = color;
                    /**/

                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewMultipleHeadersExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //  render the htmlwriter into the response
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }
    /*
    private static void ExportToPdf()
    {
        // Create instance of a new document
        Document doc = new Document(PageSize.A4, 10, 10, 50, 50);
        // Change   the content type to application/pdf !Important
        HttpContext.Current.Response.ContentType = "application/pdf";
        // Get   Instance of pdfWriter to be able to create 
        // the document in the OutputStream
        PdfWriter.GetInstance(doc, HttpContext.Current.Response.OutputStream);
        // Create   Style Sheet
        StyleSheet styles = new iTextSharp.text.html.simpleparser.StyleSheet();
        //--styles.LoadTagStyle("ol", "leading", "16,0");
        doc.Add(new Header(iTextSharp.text.html.Markup.HTML_ATTR_STYLESHEET, "Style.css"));
        // Open the   document to be able to write to it
        doc.Open();
        //--styles.LoadTagStyle("li", "face", "garamond");
        //--styles.LoadTagStyle("span", "size", "8px");
        //--styles.LoadTagStyle("body", "font-family", "times new roman");
        //--styles.LoadTagStyle("body", "font-size", "10px");
        // Parse   html to PDF understandable objects
        var objects = iTextSharp.text.html.simpleparser.HTMLWorker.ParseToList(new StreamReader(Server.MapPath("Order.aspx"), Encoding.Default), styles);
        for (int k = 0; k < objects.Count; k++)
        {
            doc.Add((IElement)objects[k]);
        }
        // Close   the document, rendering it to the browser
        doc.Close();
    }
     */
   
    /// Replace any of the contained controls with literals
    private static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            //ctl.GetType().FullName == "System.Web.UI.WebControls.DataControlLinkButton")
            Control current = control.Controls[i];
            switch (current.GetType().Name)
            {
                case "LinkButton":
                    //ReplaceControl(control, i, (current as LinkButton).Text);
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
                    break;
                case "ImageButton":
                    //ReplaceControl(control, i, (current as ImageButton).AlternateText);
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
                    break;
                case "HyperLink":
                    //ReplaceControl(control, i, (current as HyperLink).Text);
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
                    break;
                case "DropDownList":
                    //ReplaceControl(control, i, (current as DropDownList).SelectedItem.Text);
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
                    break;
                case "CheckBox":
                    //ReplaceControl(control, i, (current as CheckBox).Checked ? "True" : "False");
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
                    break;
            }
           
            if (current.HasControls())
            {
                GridViewMultipleHeadersExportUtil.PrepareControlForExport(current);
            }
        }
    }

    //private static void ReplaceControl(Control control, int i, string text)
    //{
    //    control.Controls.Remove(text);
    //    control.Controls.AddAt(i, new LiteralControl(text));
    //}

    public static TableRow CreateTableRow(string text, int iColSpan)
    {
        TableRow objTableRow = new TableRow();
        TableCell tc = new TableCell();
        tc.Text = text;
        tc.HorizontalAlign = HorizontalAlign.Left;
        tc.Font.Bold = true;
        tc.ColumnSpan = iColSpan;
        objTableRow.Cells.Add(tc);
        return objTableRow;
   }
}
Export GridView with Images to Word Excel and PDF Formats in ASP.Net Soure Code References

2 comments:

Lisa Green said...

Hello- I trying your code to export gridview with Multiple header, but when I do the export, no header is shown in the excel. And I am not sure what to put for HeaderRow.
Any help is greatly appreciated. Thanks

Anonymous said...
This comment has been removed by a blog administrator.