GridView To DataSet To Excel

Cross Posted From Here

My weekend was a bit busy as I am working on a small project and one of the requirements that I have been doing is converting GridView data to an Excel document. I checked the resources in the internet and I can’t seem to find anything that fits my need. Things become more difficult as I am working on dynamic GridViews where the number of GridViews that would be displayed on a certain instance depends on the value that will be returned by the query as well as the text in some of the cells that it should contain.

Researching further, I found an article converting DataSets to Microsoft Excel without the use of COM objects. This would be a good headstart to keep things rolling. Though, I found no explicit API that allows me to convert GridView data to DataSets. Moreover, I do customizations on display on my GridView so I prefer to have an exported data that outputs the same content in the Excel worksheet as on my screen.

GridView objects have an event called RowDataBound and this allows the developer to access the value of the fields before it is displayed in a GridView. This is quite helpful in cases wherein if you want to customize display text in a BoundField column from a bit-typed DataField source rather than from a default true/false display to something more descriptive (which in case in an ordering system, display “Delivered/Not Yet Delivered” than a “true/false” value). Having that in mind, I played with the event and created a dynamic DataTable out of the value rendered in the GridView. In my example, I used the Northwind database and queried the Customers table with a SqlDataSource. There are 2 GridViews in my form where the first one consumes the result of the SqlDataSource and the other one that will consume the generated DataSet (I have a decided to append my generated DataTable to a DataSet so that I can consume the code indicated above) from the first GridView.

On the first GridView, I defined my RowDataBound as follows:


protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{

switch (e.Row.RowType)
{
case DataControlRowType.Header:

columnCount = e.Row.Cells.Count;
for (int x = 0; x < columnCount; x++)
{
dtData.Columns.Add(e.Row.Cells[x].Text);
}
break;

case DataControlRowType.DataRow:

DataRow drItem = dtData.NewRow();
for (int y = 0; y < columnCount; y++)
{
drItem[y] = e.Row.Cells[y].Text;
}
dtData.Rows.Add(drItem);
break;

default:
dsData.Tables.Add(dtData);
GridView2.DataSource = dsData;
GridView2.DataBind();
Session["gv"] = dsData;
break;

}
}

By default, DataSets when bound to a GridView, the DataColumn names become the GridView column names. After the DataBinding, if the current row is a HeaderType, I get the names and define them as my dynamic DataTable’s column names and if the current row is a DataRow, I make them rows of my DataTable. Since I didn’t define any paging in my GridView, I placed in the default case the steps that allows me to add the DataTable to a DataSet. After that, I stored the DataSet into session memory so that I can invoke it later for exporting to Excel.

I saw a sourcecode somewhere in the Internet (I forgot where I got the source) that allows one to export DataSets to Excel BUT they have to be bound first into a DataGrid:


using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
DataGrid dg = new DataGrid();
DataSet dsMem = new DataSet();
dsMem = (DataSet)Session["gv"];
dg.DataSource = dsMem;
dg.DataBind();
dg.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
}

If you want to get rid of the data binding to a DataGrid, you can use the project specified above. However, I made specific changes to the code as I to stream it to the client as a file download.

To see the whole working project, download the file here. The file is in ODT format as WordPress doesn’t allow zip files as an attachment. Download the file and rename it to .ZIP. For further questions, you can post your queries here.

Advertisements

0 Responses to “GridView To DataSet To Excel”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Blog Stats

  • 3,922 hits
October 2008
S M T W T F S
« Aug   Nov »
 1234
567891011
12131415161718
19202122232425
262728293031  

%d bloggers like this: