Gridview to Excel in zip format

gagansharma7 · October 16th, 2007, 09:51 PM

Hi,

I am using C#.net 2.0 with Webforms and displaying data from dataset in the GridView. On button click, I need to Export the Gridview data to Excel but save as a zip file. Please let me know how to do this.

Thanks
gs

samjudson · October 17th, 2007, 04:32 AM

The 'trick' with writing excel files to the browser for download is the fact that excel will quite happily open an HTML table into excel if you give it the .xls file extension.

You'd have to write the HTML to a file, or a MemoryStream and then use a Zip library to stream that to the browser Response.BinaryWrite.

/- Sam Judson : Wrox Technical Editor -/

gagansharma7 · October 17th, 2007, 08:06 AM

Hi Sam,

          I am able to get the Excel file with the following code but getting a zip from it is giving me a tough time:
///////////////////////////////////////////////////////////////////////////////////////////
using System;
using System.Data;
using System.Configuration;
using System.IO;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public class GridViewExportUtil
{
    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.ContentType = "application/ms-excel";
       //HttpContext.Current.Response.ContentType = "application/x-zip-compressed";

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

                // add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.Head erRow);
                    table.Rows.Add(gv.HeaderRow);
                }

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

                // add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.Foot erRow);
                    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();
            }
        }
    }

        /// Replace any of the contained controls with literals
        public static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            Control current = control.Controls[i];
            if (current is LinkButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
            }
            else if (current is ImageButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
            }
            else if (current is HyperLink)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
            }
            else if (current is DropDownList)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
            }
            else if (current is CheckBox)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
            }

            if (current.HasControls())
            {
                GridViewExportUtil.PrepareControlForExport(current );
            }
        }
    }

    public static void setGrid(GridView gv)
    {

        int i, j;

        if (gv.Rows.Count > 0)
        {
            for (i = 0; i < gv.Rows.Count; i++)
                for (j = 0; j < gv.Rows[i].Cells.Count; j++)
                    gv.Rows[i].Cells[j].Wrap = false;

            gv.HeaderStyle.Wrap = false;

            if (gv.HeaderRow.Cells.Count > 0)
            {
                for (i = 0; i < gv.HeaderRow.Cells.Count; i++)
                    gv.HeaderRow.Cells[i].Wrap = false;
            }
        }
    }

}
/////////////////////////////////////////////////////////

Anyone please point out here how get the zip format.

Thanks
gs

samjudson · October 17th, 2007, 08:24 AM

Get a copy of SharpZipLib.

Do something like the following:

* Create a ZipOutputStream:
* Write the output stream directly to the response stream
* Create an 'entry' for your excel file, with whatever filename you want.
* Stream the grid to the zip stream via the StreamWriter/HtmlTextWriter
* Finish and close the zip stream.
* End the response.

Code:

ZipOutputStream outputStream = new ZipOutputStream(Response.OutputStream);

ZipEntry entry = new ZipEntry("Worksheet.xls");
entry.DateTime = DateTime.Now;
outputStream.PutEntry(entry);

using(StreamWriter sw = new StreamWriter(outputStream))
{
// ... etc - the rest of your code, except the following two lines:
// HttpContext.Current.Response.Write(sw.ToString());
// HttpContext.Current.Response.End();

}
// Close the zip file:
outputStream.Finish();
outputStream.Close();  

// Now end the response.
HttpContext.Current.Response.End();

/- Sam Judson : Wrox Technical Editor -/

dparsons · October 17th, 2007, 08:25 AM

Google has the answer:
http://www.google.com/search?hl=en&q...eate+zip+files

=]

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Beginning Visual C# 2008
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========

gagansharma7 · October 18th, 2007, 07:09 AM

Sam,

It is working after following the way you told. Thanks a lot !

Thanks
gs