Easy Reporting with GridView Control

Asp.Net GridView control has a RenderControl method that can be used to generated Excel report. The idea is creating a GridView control as report template and render Excel sheet with the control.

Following code gives an example:

GridView1.DataSource = someDataView;

GridView1.DataBind();
GridView1.Visible = true;
PrepareGridViewForExport(GridView1);

Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=anyNameString.xls");
Response.Charset = "";

// If you want the option to open the Excel file without saving then
// comment out the line below
// Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
GridView1.RenderControl(htmlWrite);

// combine two Tablular data into one Excel sheet.
GridView2.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();

A few notes regarding above code snippet:
1. The GridView control instances, such as GridView1 and GridView2 in above code, have to be created from within the page where to product Excel report. In reality, they may not be required to show on the page. In such case, we can create a invisible panel to hold the GridView controls as following:

<asp:Panel runat="server" ID="PanelDetails" Visible="false">
<%-- hidden GridView controls --%>
</asp:Panel>

2. In case that the GridView control is required to show on the page, if we use the same instance to render Excel report, the control may be not quite meet the report needs, because it is customized for the page, e.g. it has buttons or check boxes and so forth, we have to "Prepare" it before rendering a report. In above code, it calls PrepareGridViewForExport method shown below to get rid of all the link buttons.

void PrepareGridViewForExport(Control gv)
{
LinkButton lb = new LinkButton();

for (int i = 0; i < gv.Controls.Count; i++)
{
if (gv.Controls[i].GetType() == typeof(LinkButton))
{
gv.Controls.Remove(gv.Controls[i]);
}

if (gv.Controls[i].HasControls())
{
PrepareGridViewForExport(gv.Controls[i]);
}

}
}

The problem roots from that we use one GridView control for two different uses. It is cleaner to create a specific reporting GridView instance rather than modifying the instance that is for the page.

3. It is viable to combine multiple GridView control rendering into one Excel sheet. In above code, we saw both GridView1 and GridView2 render into one html string. Excel understands the combined rendering.

Word File Data Merge

For a number of times, I was asked for a suggestion to Mail-Merge like Word file data merge problem. In stead of using Active Controls to open a Word template file, I always suggest to save the template file in text style, then it can be read and manipulated through placeholders defined in it and replaced with real data.

This way is simple and avoids the complexity and effort with ActiveX control. But the problem is that it loses images in the template word file when saved as html file format.

Images are important to produce formal and decent documents or reports.

Fortunately, xml file format works. When Word saves file as xml format, it embeds images inside the file as encoded binary data, while text data and font style are maintained in css and html style message.

It is the right format to use!

Asp.Net reporting with Crystal Report

Developed by Business Object, Crystal Report has long been widely vowed as a leading product for generating different kind of reports. .Net framework has full API support for developing Crystal Report. This artical will walk through the main features of creating Crystal reports with Asp.Net.

The development API

List 1 demonstrates the mostly used namespaces:

[list 1: Namespaces]
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;
using CrystalDecisions.ReportSource;
using CrystalDecisions.Shared;

These namespaces are accomodated within the following assemblies:

CrystalDescisions.CrystalReports.Engin.dll
CrystalDecisions.ReportSource.dll
CrystalDecisions.Shared.dll
CrystalDecisions.Web.dll


The process

CrystalReportViewer rptViewer = new CrystalReportViewer();
ReportDocument repDoc = new ReportDocument();

// show report on a page
string rptFilePath = "c:\reports\xxx.rpt";
rptDoc.Load(rptFilePath);
rptViewer.ReportSource = rptDoc;

Page.Form.Controls.Add(rptViewer);


Export Report

If we need just show the report onto a web page, CrystalReportViewer, a web user control, is just doing that. if we need to export report to a file, in PDF, Excel or other supported format, use Export method of ReportClass:

ReportClass.Export();

ReportClass rpt = new ReportClass();
rpt.RecordSet = recordset;
rpt.GroupOn = string[]{columns};
rpt.SubTotal = string[]{columns}

Deployment
When install Visual Studio, Crystal Report assemblies are installed into the GAC. You also can manually install it from:

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\CrystalReports\CRRedist2005_x86.msi

within VS2005 for example.

When deploy to production, instead of install Visual studio, you can download and install Crystal Report's redistribution package from Business Object.