Introduction
The article "ASP.Net 2.0: Export GridView to Excel" received a very good response from our user community. Some of the excellent tips collected from the user feedback have been included in the first version of the article.
One of the most common questions from our readers is regarding the handling of a Hyperlink column in the GridView Export to Excel. This article will expand on the original article and in this version, we will include the handling the export of the Hyperlink columns in the GridView export to Excel functionality and also re-factor our original logic to use more general features of reflection, allowing for easy extension to include additional control types. This code generalization does have a performance overhead and if the controls embedded in your GridView are limited to a particular set, the original implementation may be more suitable for your requirements.
Architectural Changes
- Define a Hash Table which holds the values for the controls to be replaced before the GridView control is exported to Excel.
This HashTable will map the control types that can be potentially embedded in the GridView to the corresponding control property that will be used to represent the particular control when exported to Excel.
- The generalized "GetControlPropertyValue" method: In this version, we define a generalized method which will fetch the value of "key" property of the embedded control by using Reflection. We define the key properties for different types of embedded control using our HashTable member variable.
Our HashTable has been setup to perform the following property mappings.
| Control Type |
Corresponding Value to Represent in Excel |
| LinkButton or derived class |
Text Property value |
| HyperLink or derived class |
Text Property value |
| DropDownList or derived class |
SelectedValue Property Value |
| CheckBox or derived class |
Checked Property Value |
If you need to handle additional control types separately for the export process, these control types can be added to the Hashtable.
- New version of the PrepareGridViewForExport method: In this updated version, we get the "key" property for the control types that we have defined for replacement in our gridview, by calling GetControlPropertyValue if the control type or it's base type is included in the Hashtable for special handling. The control embedded in the GridView is then replaced by the value of the key property. After all the controls embedded in the GridView are processed recursively, the GridView is rendered into an HtmlTextWriter and output to the Excel formatted response.
Complete Code Listing
using System;
using System.Data;
using System.Configuration;
using System.Collections;
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;
using System.Text;
using System.IO;
using System.Reflection;
public partial class DeleteConfirm : System.Web.UI.Page
{
Hashtable htControls = new Hashtable();
protected void Page_Load(object sender, EventArgs e)
{
htControls.Add("LinkButton", "Text");
htControls.Add("HyperLink", "Text");
htControls.Add("DropDownList", "SelectedItem");
htControls.Add("CheckBox", "Checked");
}
protected void Button1_Click(object sender, EventArgs e)
{
PrepareGridViewForExport(GridView1);
ExportGridView();
}
private void ExportGridView()
{
string attachment = "attachment; filename=Contacts.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
private void PrepareGridViewForExport(Control gv)
{
Literal l = new Literal();
for (int i = 0; i < gv.Controls.Count; i++)
{
if ((null != htControls[gv.Controls[i].GetType().Name]) || (null != htControls[gv.Controls[i].GetType
().BaseType.Name]))
{
l.Text = GetControlPropertyValue(gv.Controls[i]);
gv.Controls.Remove(gv.Controls[i]);
gv.Controls.AddAt(i, l);
}
if (gv.Controls[i].HasControls())
{
PrepareGridViewForExport(gv.Controls[i]);
}
}
}
private string GetControlPropertyValue(Control control)
{
Type controlType = control.GetType();
string strControlType = controlType.Name;
string strReturn = "Error";
bool bReturn;
PropertyInfo[] ctrlProps = controlType.GetProperties();
string ExcelPropertyName = (string)htControls[strControlType];
if (ExcelPropertyName == null)
{
ExcelPropertyName = (string)htControls[control.GetType().BaseType.Name];
if (ExcelPropertyName == null)
return strReturn;
}
foreach (PropertyInfo ctrlProp in ctrlProps)
{
if (ctrlProp.Name == ExcelPropertyName &&
ctrlProp.PropertyType == typeof(String))
{
try
{
strReturn = (string)ctrlProp.GetValue(control, null);
break;
}
catch
{
strReturn = "";
}
}
if (ctrlProp.Name == ExcelPropertyName &&
ctrlProp.PropertyType == typeof(bool))
{
try
{
bReturn = (bool)ctrlProp.GetValue(control, null);
strReturn = bReturn ? "True" : "False";
break;
}
catch
{
strReturn = "Error";
}
}
if (ctrlProp.Name == ExcelPropertyName &&
ctrlProp.PropertyType == typeof(ListItem))
{
try
{
strReturn = ((ListItem)(ctrlProp.GetValue(control, null))).Text;
break;
}
catch
{
strReturn = "";
}
}
}
return strReturn;
}
}
Conclusion
In this article, we saw the technique for including HyperLink controls embedded in the GridView to be exported to Excel, along with other controls such as DropDownList and CheckBox. We also saw how to use Reflection to setup an extensible function to handle various control types.
Happy Coding!