Foliotek Developer Blog

Export to Excel Class

Summary

It took me awhile, but I eventually got tired of writing export to Excel methods on each report page. So, I created a class that will handle all the heavy lifting for me. I just call the methods I want with parameters

Methods

  • Initiate Export (file name)
  • Finalize Report (StringWriter)
  • Render Control(Control, HtmlTextWriter)
  • Print Report Title (HtmlTextWriter, report title, columns to span, align center, add space after)
  • Print Report Line (HtmlTextWriter, line of data, columns to span, align center, add space after)
  • Print Report Filter Header (HtmlTextWriter, title of filter, columns to span)
  • *Print Report Filter Item *(HtmlTextWriter, item header, columns to span for header, item text, columns to span for item
  • Print Report Filter Footer (HtmlTextWriter)
  • Export Control to Excel (Control, name of file)
  • Prep Export (Control) -* replaces links and other things that can’t be exported to text*

C# Code

[sourcecode lang="csharp"]

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Xml;
using System.IO;
using zip = ICSharpCode.SharpZipLib.Zip;
using System.Text.RegularExpressions;
using System.Linq;
using System.Web.UI;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;

namespace Foliotek.DataAccess
{

///

/// Provides various methods for exporting data to excel.
///

public partial class ExportToExcel : DBAccess
{
private static bool IsIE
{
get
{
return System.Web.HttpContext.Current.Request.Browser.Browser.ToLower() == “ie”;
}
}

private const string ContentType = “text/csv”;

///

/// Title Sizes
///

public enum ReportTitleSize
{
XLarge = 1,
Large = 2,
Medium = 3,
Small = 4
}

public static void InitiateExport(string fileName)
{
fileName = fileName.REPLACE(cast(cast(” ” as nvarchar(max)) as nvarchar(max)),cast(cast( “_”).Replace(“&” as nvarchar(max)) as nvarchar(max)),cast(cast( ” and ” as nvarchar(max as nvarchar(max))))).REPLACE(cast(cast(“/” as nvarchar(max)) as nvarchar(max)),cast(cast( ” “).Replace(“&” as nvarchar(max)) as nvarchar(max)),cast(cast( ” and ” as nvarchar(max as nvarchar(max))))); // remove spaces — Firefox seems to not like spaces in the filename.

System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.AddHeader(“content-disposition”, “attachment;filename=” + fileName);
System.Web.HttpContext.Current.Response.Charset = “”;
Response.Cache.SetCacheability(HttpCacheability.Private);
Response.Cache.SetMaxAge(new TimeSpan(0));
System.Web.HttpContext.Current.Response.ContentType = ContentType;
}

public static void FinalizeReport(System.IO.StringWriter stringWrite)
{
System.Web.HttpContext.Current.Response.Write(stringWrite.ToString());
System.Web.HttpContext.Current.Response.End();
}

public static void RenderControl(Control c, HtmlTextWriter hw)
{
c.RenderControl(hw);
}

///

/// Print a report title
///

/// /// /// /// /// /// public static void PrintReportTitle(HtmlTextWriter hw, string reportTitle, ReportTitleSize size, int columnsToSpan, bool alignCenter, bool addSpaceAfter)
{
if (IsIE)
{
hw.WriteLine(reportTitle);
if (addSpaceAfter)
hw.WriteLine();
}
else
{
string header = “

if (alignCenter)
header += "align=\"center\"";
else
header += "align=\"left\""; header += ">” + reportTitle + “" + (int)size + ">

“;

hw.WriteLine(header);

if (addSpaceAfter)
hw.WriteLine(“
“);
}
}

///

/// Print a report title
///

/// /// /// /// /// /// public static void PrintReportLine(HtmlTextWriter hw, string data, int columnsToSpan, bool alignCenter, bool addSpaceAfter)
{
string header = “

if (alignCenter)
header += "align=\"center\"";
else
header += "align=\"left\""; header += ">” + data + “

“;

hw.WriteLine(header);

if (addSpaceAfter)
hw.WriteLine(“
“);

}

///

/// Print the header information for the filtered paramaters for a report. NOTE: Must close with PrintReportfilterFooter()
///

/// /// /// public static void PrintReportFilterHeader(HtmlTextWriter hw, string FilterTitle, int columnsToSpan)
{
if (IsIE)
hw.WriteLine(FilterTitle);
else
{
hw.WriteLine(“

“);
hw.WriteLine(“
“);
}
}

///

/// Print an item that is being filtered
///

/// /// /// /// /// public static void PrintReportFilterItem(HtmlTextWriter hw, string itemHeader, int itemHeaderColSpan, string itemText, int itemTextColSpan)
{
if (IsIE)
hw.WriteLine(itemHeader + “,” + itemText);
else
hw.WriteLine(“

“);
}

///

/// Print the ending information for the Filter
///

/// public static void PrintReportFilterFooter(HtmlTextWriter hw)
{
if (!IsIE)
hw.WriteLine(“

### ” + FilterTitle + “

” + itemHeader + “” + itemText + “
“);

hw.WriteLine(“
“);
}

public static void ExportControlToExcel(Control control, string fileName)
{
InitiateExport(fileName);
Response.ContentType = ContentType;// “application/vnd.ms-excel”;
var writer = new StringWriter();
var hw = new HtmlTextWriter(writer);
PrepExport(control);
control.RenderControl(hw);
Response.Write(writer.ToString());
Response.End();
}

private static void PrepExport(Control control)
{
Dictionary controlsToAddAfter = new Dictionary();

foreach (Control c in control.Controls)
{
if (c.Visible)
{
PrepExport(c);

Literal litText = new Literal();

// replace links with just their text
if (c is LinkButton)
{
litText.Text = ((LinkButton)c).Text;
c.Visible = false;
}
else if (c is HtmlAnchor)
{
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
c.RenderControl(hw);
litText.Text = sw.ToString();
c.Visible = false;
}
// replace checkboxes with their checked value
else if (c is CheckBox)
{
litText.Text = ( (CheckBox)c ).Checked.ToString();
c.Visible = false;
}
else if (c is HtmlInputCheckBox)
{
litText.Text = ((HtmlInputCheckBox)c).Checked.ToString();
c.Visible = false;
}

// images don’t export well – replace them with their alt-text
else if (c is HtmlImage)
{
litText.Text = ( (HtmlImage)c ).Alt;
c.Visible = false;
}
else if (c is Image)
{
litText.Text = ( (Image)c ).AlternateText;
}

// various form controls that don’t export well
else if (c is DropDownList)
c.Visible = false;
else if (c is HiddenField)
c.Visible = false;
else if (c is GridView)
c.Visible = false;
else if (c is RequiredFieldValidator)
c.Visible = false;
else if (c is TextBox)
c.Visible = false;
else if (c is CustomValidator)
c.Visible = false;
else if (c is CompareValidator)
c.Visible = false;

if (litText.Text != String.Empty)
controlsToAddAfter.Add(c.Parent.Controls.IndexOf(c), litText);
}
}

foreach (var kvp in controlsToAddAfter)
{
control.Controls.AddAt(kvp.Key, kvp.Value);
}
}
}
}

[/sourcecode]