Go4Expert

Go4Expert (http://www.go4expert.com/)
-   ASP.NET (http://www.go4expert.com/articles/asp-net-tutorials/)
-   -   Export Data table to MS-Excel CSV (http://www.go4expert.com/articles/export-data-table-ms-excel-csv-t18293/)

naimish 2Jul2009 11:58

Export Data table to MS-Excel CSV
 

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
    }
}


nimesh 3Jul2009 23:21

Re: Export Data table to MS-Excel CSV
 
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?

naimish 6Jul2009 10:17

Re: Export Data table to MS-Excel CSV
 
This dll doesn't need to be registered. Simply copy the assembly "ExportUtility.dll" into r project folder and add it to references.

nimesh 6Jul2009 15:14

Re: Export Data table to MS-Excel CSV
 
Can you post this assembly, ExportUtility.dll

naimish 6Jul2009 15:16

Re: Export Data table to MS-Excel CSV
 
The above provided code is for ExportUtility.dll itself.

naimish 6Jul2009 15:17

Re: Export Data table to MS-Excel CSV
 
Check this : namespace ExportUtility

nimesh 6Jul2009 15:28

Re: Export Data table to MS-Excel CSV
 
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.

naimish 6Jul2009 15:30

Re: Export Data table to MS-Excel CSV
 
Haha, here also you have done the same....please check my reply on another post :)

cignusweb 3Aug2009 11:23

Re: Export Data table to MS-Excel CSV
 
Tried and get pass with it fine.

Thanks for the info.

Raj08 3Aug2009 11:43

Re: Export Data table to MS-Excel CSV
 
Hey nice one, let me check also. If it got passed for you, I ll check for me also.


All times are GMT +5.5. The time now is 15:16.