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: #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 } }
You mean save this code as a .dll file and then import it into your project? When we can save the gridview as excel with just a few more lines of code, what is the need of this library?
This dll doesn't need to be registered. Simply copy the assembly "ExportUtility.dll" into r project folder and add it to references.
yes, but I'm unable to reference to this file. unless I add a reference, I cannot use it's properties. I tried saving the file as dll also and as cs also. Both didn't help.
Export Data~~ Spire.DataExport is a professional C#/VB.NET component specially designed for export data. Take a shot.