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]


Really Nice Looking Export to Excel using Table Formatting

Inevitably, someone is going to want your data exportable to an Excel format, no matter how many different ways you let them look at right on your site–and they’re going to want it to look really nice.

If you’re just going to a CSV format, it’s pretty easy to do, but if it opens in Excel, none of the cells are formatted initially.? It can look good eventually, but you have to manually format it after the export.? It would sure be nice if you could format it better from the code itself.? For me, I thought it would be too tedious and time-consuming to actually create the Excel Spreadsheet.? I wanted something quicker.

Using a DataGrid for the bulk of the actual data works well as it renders as a table, and Excel seems to work well with table formats.? I used an HtmlTextWriter for this purpose.

[sourcecode lang="csharp"]
HtmlTextWriter hw
dg.RenderControl(hw)
[/sourcecode]

However, the DataGrid wasn’t the only thing I wanted to export.? What I wanted was a nice big heading that was bolded, nice subheadings, nice spacing,and a bunch of things like that.? What I found is that if I just wrote table structure syntax,I could do basically everything I wanted.

What I did here was to write a large, centered heading that spanned 8 columns (the entire width of my report).? Both the “th” and the “h1″ tags impact the way it renders in Excel.? I added the colspan and alignment attributes on the th tag, and it worked great.? Then I added a blank link by simply writing a br tag.? Here’s the code:

[sourcecode lang="csharp"]
hw.WriteLine(“

My Nice Looking Report

“);
hw.WriteLine(“
“);
[/sourcecode]

The rendering just follows down the page pretty nicely.? I added subheaders with h2 tags, and I played around with color and other attributes as well.? Then, after you export to Excel, the moment you open it up, all the formatting is there.? It looks pretty nice–particularly so in product demos.? Even if clients opt for csv format so they can put it into reporting software, the look that this provides gives great curb appeal.

Here’s the code for the sub heading and the legend for the report.? The term takes up two cells, and the description is confined to the other 6 cells (just because the total width was 8 cells/td’s).

[sourcecode lang="csharp"]
hw.WriteLine(“

“);
hw.WriteLine(“
“);

hw.WriteLine(“

“);
hw.WriteLine(“

“);
hw.WriteLine(“

“);
hw.WriteLine(“

“);
hw.WriteLine(“

“);

hw.WriteLine(“

### Report Legend

Incomplete EvaluationsEvaluations assigned but not been completed.
Completed EvaluationsEvaluations assigned that have been completed.
Request ReviewsTotal number of requests for reviews.
Unfulfilled Request ReviewsTotal number of requests not yet reviewed.
Request Responses/td>Total number of request review responses.
“);
hw.WriteLine(“
“);
[/sourcecode]

Again, this works really well for me. It allows me to easily control the format without actually creating an Excel Spreadsheet in the code itself.