Introduction
Exporting data from a data table to Excel or CSV is one of the most common functionality required in ASP.NET Web Application or .NET Window application. Users can download the data from the data grid into an Excel spreadsheet or CSV file for offline verification and/or computation. This article includes the source code for such functionality.
Background
This class library is fully implemented in C#.NET. This dll doesn't need to be registered. Simply copy the assembly "ExportUtility.dll" into r project folder and add it to references. You can also include the "Export Utility" project and reference it in your project.
The code
Code: CSharp
#region File Summary & Modification History
/// <summary>
/// Export Data table to MS-Excel CSV
/// </summary>
#endregion
#region Includes
using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Data;
using System.IO;
using System.Xml;
using System.Xml.Xsl;
using System.Threading;
#endregion
namespace ExportUtility
{
#region Class : Export
public class Export
{
#region Variable
System.Web.HttpResponse response;
private string appType;
#endregion
#region Constants
private static string WEB = "Web";
private static string WIN = "Win";
private static string EXPORT = "Export";
private static string VALUES = "Values";
private static string ERRORMSG1 = "Provide valid application format (Web/Win)";
private static string ERRORMSG2 = "There are no details to export.";
private static string ERRORMSG3 = "ExportColumn List should not exceed Total Columns";
private static string ERRORMSG4 = "ExportColumn Number should not exceed Total Columns Range";
private static string ERRORMSG5 = "ExportColumn List and Headers List should be of same length";
private static string SPACE = " ";
private static string SPACECODE = "_x0020_";
private static string PERCENTAGE = "%";
private static string PERCENTAGECODE = "_x0025_";
private static string HASH = "#";
private static string HASHCODE = "_x0023_";
private static string AMPERSAND = "&";
private static string AMPERSANDCODE = "_x0026_";
private static string SLASH = "/";
private static string SLASHCODE = "_x002F_";
private static string XMLNAMESPACE = "[URL]http://www.w3.org/1999/XSL/Transform[/URL]";
private static string XSL = "xsl";
private static string STYLESHEET = "stylesheet";
private static string VERSION = "version";
private static string ONEPOINTZERO = "1.0";
private static string XSLOUTPUT = "xsl:output";
private static string METHOD = "method";
private static string TEXT = "text";
private static string FOURPONTZERO = "4.0";
private static string XSLTEMPLATE = "xsl:template";
private static string MATCH = "match";
private static string XSLVALUEOF = "xsl:value-of";
private static string SELECT = "select";
private static string SINGLEQUOTE = "'";
private static string XSLFOREACH = "xsl:for-each";
private static string EXPORTVALUES = "Export/Values";
private static string COMMA = ",";
private static string DOUBLESPACE = " ";
private static string TEXTCSV = "text/csv";
private static string CONTENTDISPOSITION = "content-disposition";
private static string ATTACHMENTFILENAME = "attachment; filename=";
private static string APPLICATIONVNDMSEXCEL = "application/vnd.ms-excel";
#endregion
#region Enum
// Export format enumeration
public enum ExportFormat
{
CSV = 1,
Excel = 2
};
#endregion
#region Constructor
#region Default Constructor
/// <summary>
/// Creates Default Export Class for Web Application.
/// </summary>
public Export()
{
appType = WEB;
response = System.Web.HttpContext.Current.Response;
}
#endregion
#region Parameterized Constructor
/// <summary>
/// Creates Export Class for Web or Window Application.
/// </summary>
/// <param name="ApplicationType">
/// For Web Application ApplicationType is "Web", For Window Application ApplicationType is "Windows".
/// </param>
public Export(string ApplicationType)
{
appType = ApplicationType;
// Throw exception if ApplicationType is not valid
if (appType != WEB && appType != WIN)
{
throw new Exception(ERRORMSG1);
}
// Set Response Object if ApplicationType is Web.
if (appType == WEB)
{
response = System.Web.HttpContext.Current.Response;
}
}
#endregion
#endregion
#region Public Methods
#region ExportDetails OverLoad : Type#1
/// <summary>
/// Exports all Column and rows of DataTable
/// </summary>
/// <param name="dtDetails">DataTable to be Exported.</param>
/// <param name="FormatType">CSV or Excel</param>
/// <param name="FileName"></param>
public void ExportDataTable(DataTable dtDetails, ExportFormat FormatType, string FileName)
{
try
{
if (dtDetails.Rows.Count == 0)
throw new Exception(ERRORMSG2);
// Create Dataset
DataSet dsExport = new DataSet(EXPORT);
DataTable dtExport = dtDetails.Copy();
dtExport.TableName = VALUES;
dsExport.Tables.Add(dtExport);
// Getting Field Names
string[] sHeaders = new string[dtExport.Columns.Count];
string[] sFileds = new string[dtExport.Columns.Count];
for (int i = 0; i < dtExport.Columns.Count; i++)
{
sHeaders[i] = dtExport.Columns[i].ColumnName;
sFileds[i] = ReplaceSpclChars(dtExport.Columns[i].ColumnName);
}
if (appType == WEB)
{
ExportForWebApp(dsExport, sHeaders, sFileds, FormatType, FileName);
}
else if (appType == WIN)
{
ExportForWinApp(dsExport, sHeaders, sFileds, FormatType, FileName);
}
}
catch (Exception Ex)
{
throw Ex;
}
}
#endregion
#region ExportDetails OverLoad : Type#2
/// <summary>
/// Exports Specific Column of DataTable
/// </summary>
/// <param name="DetailsTable"></param>
/// <param name="ColumnList"></param>
/// <param name="FormatType"></param>
/// <param name="FileName"></param>
public void ExportDataTable(DataTable DetailsTable, int[] ColumnList, ExportFormat FormatType, string FileName)
{
try
{
if (DetailsTable.Rows.Count == 0)
throw new Exception(ERRORMSG2);
// Create Dataset
DataSet dsExport = new DataSet(EXPORT);
DataTable dtExport = DetailsTable.Copy();
dtExport.TableName = VALUES;
dsExport.Tables.Add(dtExport);
if (ColumnList.Length > dtExport.Columns.Count)
throw new Exception(ERRORMSG3);
// Getting Field Names
string[] sHeaders = new string[ColumnList.Length];
string[] sFileds = new string[ColumnList.Length];
for (int i = 0; i < ColumnList.Length; i++)
{
if ((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
throw new Exception(ERRORMSG4);
sHeaders[i] = dtExport.Columns[ColumnList[i]].ColumnName;
sFileds[i] = ReplaceSpclChars(dtExport.Columns[ColumnList[i]].ColumnName);
}
if (appType == WEB)
{
ExportForWebApp(dsExport, sHeaders, sFileds, FormatType, FileName);
}
else if (appType == WIN)
{
ExportForWinApp(dsExport, sHeaders, sFileds, FormatType, FileName);
}
}
catch (Exception Ex)
{
throw Ex;
}
}
#endregion
#region ExportDetails OverLoad : Type#3
/// <summary>
/// Export Specified Columns of DataTable with specified Headers.
/// </summary>
/// <param name="DetailsTable"></param>
/// <param name="ColumnList"></param>
/// <param name="Headers"></param>
/// <param name="FormatType"></param>
/// <param name="FileName"></param>
public void ExportDataTable(DataTable DetailsTable, int[] ColumnList, string[] Headers, ExportFormat FormatType, string FileName)
{
try
{
if (DetailsTable.Rows.Count == 0)
throw new Exception(ERRORMSG2);
// Create Dataset
DataSet dsExport = new DataSet(EXPORT);
DataTable dtExport = DetailsTable.Copy();
dtExport.TableName = VALUES;
dsExport.Tables.Add(dtExport);
if (ColumnList.Length != Headers.Length)
throw new Exception(ERRORMSG5);
else if (ColumnList.Length > dtExport.Columns.Count || Headers.Length > dtExport.Columns.Count)
throw new Exception(ERRORMSG3);
// Getting Field Names
string[] sFileds = new string[ColumnList.Length];
for (int i = 0; i < ColumnList.Length; i++)
{
if ((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
throw new Exception(ERRORMSG4);
sFileds[i] = ReplaceSpclChars(dtExport.Columns[ColumnList[i]].ColumnName);
}
if (appType == WEB)
{
ExportForWebApp(dsExport, Headers, sFileds, FormatType, FileName);
}
else if (appType == WIN)
{
ExportForWinApp(dsExport, Headers, sFileds, FormatType, FileName);
}
}
catch (Exception Ex)
{
throw Ex;
}
}
#endregion
#endregion
#region Private Methods
#region Method : ReplaceSpclChars
/// <summary>
/// Removes Special Characters in String.
/// </summary>
/// <param name="fieldName">String from which Special Character is to be removed.</param>
/// <returns></returns>
private string ReplaceSpclChars(string fieldName)
{
// space -> _x0020_
// % -> _x0025_
// # -> _x0023_
// & -> _x0026_
// / -> _x002F_
fieldName = fieldName.Replace(SPACE, SPACECODE);
fieldName = fieldName.Replace(PERCENTAGE, PERCENTAGECODE);
fieldName = fieldName.Replace(HASH, HASHCODE);
fieldName = fieldName.Replace(AMPERSAND, AMPERSANDCODE);
fieldName = fieldName.Replace(SLASH, SLASHCODE);
return fieldName;
}
#endregion
#region Method : CreateStyleSheet
/// <summary>
/// Creates Default Stylesheet
/// </summary>
/// <param name="writer"></param>
/// <param name="sHeaders"></param>
/// <param name="sFileds"></param>
/// <param name="FormatType"></param>
private void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, ExportFormat FormatType)
{
try
{
// xsl:stylesheet
string ns = XMLNAMESPACE;
writer.Formatting = Formatting.Indented;
writer.WriteStartDocument();
writer.WriteStartElement(XSL, STYLESHEET, ns);
writer.WriteAttributeString(VERSION, ONEPOINTZERO);
writer.WriteStartElement(XSLOUTPUT);
writer.WriteAttributeString(METHOD, TEXT);
writer.WriteAttributeString(VERSION, FOURPONTZERO);
writer.WriteEndElement();
// xsl-template
writer.WriteStartElement(XSLTEMPLATE);
writer.WriteAttributeString(MATCH, SLASH);
// xsl:value-of for headers
for (int i = 0; i < sHeaders.Length; i++)
{
writer.WriteString("\"");
writer.WriteStartElement(XSLVALUEOF);
writer.WriteAttributeString(SELECT, SINGLEQUOTE + sHeaders[i] + SINGLEQUOTE);
writer.WriteEndElement(); // xsl:value-of
writer.WriteString("\"");
if (i != sFileds.Length - 1) writer.WriteString((FormatType == ExportFormat.CSV) ? "," : " ");
}
// xsl:for-each
writer.WriteStartElement(XSLFOREACH);
writer.WriteAttributeString(SELECT, EXPORTVALUES);
writer.WriteString("\r\n");
// xsl:value-of for data fields
for (int i = 0; i < sFileds.Length; i++)
{
writer.WriteString("\"");
writer.WriteStartElement(XSLVALUEOF);
writer.WriteAttributeString(SELECT, sFileds[i]);
writer.WriteEndElement(); // xsl:value-of
writer.WriteString("\"");
if (i != sFileds.Length - 1) writer.WriteString((FormatType == ExportFormat.CSV) ? "," : " ");
}
writer.WriteEndElement(); // xsl:for-each
writer.WriteEndElement(); // xsl-template
writer.WriteEndElement(); // xsl:stylesheet
writer.WriteEndDocument();
}
catch (Exception Ex)
{
throw Ex;
}
#endregion
}
#region Method : ExportForWinApp
/// <summary>
/// Exports DataSet into CSV / Excel format
/// </summary>
/// <param name="dsExport"></param>
/// <param name="sHeaders"></param>
/// <param name="sFileds"></param>
/// <param name="FormatType"></param>
/// <param name="FileName"></param>
private void ExportForWinApp(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
{
try
{
// XSLT to use for transforming this dataset.
MemoryStream stream = new MemoryStream();
XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
CreateStylesheet(writer, sHeaders, sFileds, FormatType);
writer.Flush();
stream.Seek(0, SeekOrigin.Begin);
XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
XslTransform xslTran = new XslTransform();
xslTran.Load(new XmlTextReader(stream), null, null);
System.IO.StringWriter sw = new System.IO.StringWriter();
xslTran.Transform(xmlDoc, null, sw, null);
//Writeout the Content
StreamWriter strwriter = new StreamWriter(FileName);
strwriter.WriteLine(sw.ToString());
strwriter.Close();
sw.Close();
writer.Close();
stream.Close();
}
catch (Exception Ex)
{
throw Ex;
}
}
#endregion
#region Method : ExportForWebApp
/// <summary>
/// Exports DataSet into CSV / Excel format
/// </summary>
/// <param name="dsExport">Source DataSet</param>
/// <param name="sHeaders"></param>
/// <param name="sFileds"></param>
/// <param name="FormatType"></param>
/// <param name="FileName"></param>
private void ExportForWebApp(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
{
try
{
// Appending Headers
response.Clear();
response.Buffer = true;
if (FormatType == ExportFormat.CSV)
{
response.ContentType = TEXTCSV;
response.AppendHeader(CONTENTDISPOSITION, ATTACHMENTFILENAME + FileName);
}
else
{
response.ContentType = APPLICATIONVNDMSEXCEL;
response.AppendHeader(CONTENTDISPOSITION, ATTACHMENTFILENAME + FileName);
}
// XSLT to use for transforming this dataset.
MemoryStream stream = new MemoryStream();
XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
CreateStylesheet(writer, sHeaders, sFileds, FormatType);
writer.Flush();
stream.Seek(0, SeekOrigin.Begin);
XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
//dsExport.WriteXml("Data.xml");
XslTransform xslTran = new XslTransform();
xslTran.Load(new XmlTextReader(stream), null, null);
System.IO.StringWriter sw = new System.IO.StringWriter();
xslTran.Transform(xmlDoc, null, sw, null);
//xslTran.Transform(System.Web.HttpContext.Current.Server.MapPath("Data.xml"), null, sw, null);
//Writeout the Content
response.Write(sw.ToString());
sw.Close();
writer.Close();
stream.Close();
response.End();
}
catch (ThreadAbortException Ex)
{
string ErrMsg = Ex.Message;
}
catch (Exception Ex)
{
throw Ex;
}
#endregion
#endregion
}
#endregion
}
}
