Friday, April 11, 2008

SQL Nested Gridview to Excel

I've been looking these days to extend the GridView native class so i can have export to Excel. So i have tried and built another class, that will make the export. The class doesn't relay on a specific control, but on a list of objects, every object also having a child list of objects (list meaning everything what IEnumerable means). In this way, for every property in a class a new column in XSL can be generated.

Here's the code (alin.berce - RONUA):

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 ExportToXls
{
public static void Export(string fileName, GridView gv, string title)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";

using (StringWriter sw = new StringWriter())
{
HtmlTextWriter htw = new HtmlTextWriter(sw);
try
{
// render the table into the htmlwriter
RenderGrid(gv).RenderControl(htw);
// render the htmlwriter into the response
HttpContext.Current.Response.Write("


" + title + "

");
HttpContext.Current.Response.Write("Generated in: " + DateTime.Now.ToString());
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
finally
{
htw.Close();
}
}
}

private static Table RenderGrid(GridView grd)
{
// Create a form to contain the grid
Table table = new Table();
table.GridLines = grd.GridLines;

// add the header row to the table
if (grd.HeaderRow != null)
{
ExportToXls.PrepareControlForExport(grd.HeaderRow);
table.Rows.Add(grd.HeaderRow);
}

// add each of the data rows to the table
foreach (GridViewRow row in grd.Rows)
{
//to allign top
row.VerticalAlign = VerticalAlign.Top;


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

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

private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < current =" control.Controls;">

No comments: