Export Data table to MS-Excel CSV

Discussion in 'ASP.NET' started by naimish, Jul 2, 2009.

  1. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,043
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth

    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
        }
    }
     
  2. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    769
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    http://techiethakkar.blogspot.com
    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?
     
  3. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,043
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth
    This dll doesn't need to be registered. Simply copy the assembly "ExportUtility.dll" into r project folder and add it to references.
     
  4. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    769
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    http://techiethakkar.blogspot.com
    Can you post this assembly, ExportUtility.dll
     
  5. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,043
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth
    The above provided code is for ExportUtility.dll itself.
     
  6. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,043
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth
    Check this : namespace ExportUtility
     
  7. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    769
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    http://techiethakkar.blogspot.com
    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.
     
  8. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,043
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth
    Haha, here also you have done the same....please check my reply on another post :)
     
  9. cignusweb

    cignusweb New Member

    Joined:
    Dec 10, 2008
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Data Entry Services, Web Design Services, Web Deve
    Location:
    New Delhi, India
    Home Page:
    http://www.cignusweb.com
    Tried and get pass with it fine.

    Thanks for the info.
     
  10. Raj08

    Raj08 New Member

    Joined:
    Aug 1, 2009
    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    0
    Occupation:
    Agent 420
    Location:
    India
    Hey nice one, let me check also. If it got passed for you, I ll check for me also.
     
  11. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
  12. ice123456

    ice123456 New Member

    Joined:
    Dec 15, 2010
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Export Data~~ Spire.DataExport is a professional C#/VB.NET component specially designed for export data. Take a shot.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice