Click here to Skip to main content
11,410,750 members (63,549 online)
Click here to Skip to main content

Tagged as

Create Excel file from DataTable

, 4 Sep 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
Here is a simplified class to create Excel file from DataTable just Pass DataTable and Preferred location on server to generate. Call the

Editorial Note

This articles was originally at wiki.asp.net but has now been given a new home on CodeProject. Editing rights for this article has been set at Bronze or above, so please go in and edit and update this article to keep it fresh and relevant.

Here is a simplified class to create Excel file from DataTable

 just Pass DataTable and Preferred location on server to generate.

Call the method in your Page by

ExcelHelper.Instance.Create(Dt,StrPath);

 use this class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;


    public class ExcelHelper
    {
        private static readonly ExcelHelper _instance = new ExcelHelper();

        public static ExcelHelper Instance
        {
            get { return _instance; }
        }
        /// 
        /// Create one Excel-XML-Document with SpreadsheetML from a DataTable
        /// 
        /// Datasource which would be exported in Excel
        /// Name of exported file
        public void Create(DataTable dtSource, string strFileName)
        {
            // Create XMLWriter
            using (XmlTextWriter xtwWriter = new XmlTextWriter(strFileName, Encoding.UTF8))
            {

                //Format the output file for reading easier
                xtwWriter.Formatting = Formatting.Indented;

                // 
                xtwWriter.WriteStartDocument();

                // Adding processing information 
                xtwWriter.WriteProcessingInstruction("mso-application", "progid='Excel.Sheet'");

                // Adding root element
                xtwWriter.WriteStartElement("Workbook");
                xtwWriter.WriteAttributeString("xmlns", "urn:schemas-microsoft-com:office:spreadsheet");
                xtwWriter.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
                xtwWriter.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
                xtwWriter.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:spreadsheet");
                xtwWriter.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40");

                // 
                xtwWriter.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");

                // 
                xtwWriter.WriteStartElement("DocumentProperties", "urn:schemas-microsoft-com:office:office");

                // Write document properties
                xtwWriter.WriteElementString("Author", "Sandeep Prajapati");
                xtwWriter.WriteElementString("LastAuthor", "Sandeep Prajapati");
                xtwWriter.WriteElementString("Created", DateTime.Now.ToString("u") + "Z");
                xtwWriter.WriteElementString("Company", "XXXXXXXXXX");
                xtwWriter.WriteElementString("Version", "12");

                // 
                xtwWriter.WriteEndElement();

                // 
                xtwWriter.WriteStartElement("ExcelWorkbook", "urn:schemas-microsoft-com:office:excel");

                // Write settings of workbook
                xtwWriter.WriteElementString("WindowHeight", "8010");
                xtwWriter.WriteElementString("WindowWidth", "14805");
                xtwWriter.WriteElementString("WindowTopX", "240");
                xtwWriter.WriteElementString("WindowTopY", "105");
                xtwWriter.WriteElementString("ProtectStructure", "False");
                xtwWriter.WriteElementString("ProtectWindows", "False");

                // 
                xtwWriter.WriteEndElement();

                // 
                xtwWriter.WriteStartElement("Styles");

                // 
                xtwWriter.WriteStartElement("Style");
                xtwWriter.WriteAttributeString("ss", "ID", null, "Default");
                xtwWriter.WriteAttributeString("ss", "Name", null, "Normal");

                // 
                xtwWriter.WriteStartElement("Alignment");
                xtwWriter.WriteAttributeString("ss", "Vertical", null, "Bottom");
                xtwWriter.WriteEndElement();

                // Write null on the other properties
                xtwWriter.WriteElementString("Borders", null);
                xtwWriter.WriteElementString("Font", null);
                xtwWriter.WriteElementString("Interior", null);
                xtwWriter.WriteElementString("NumberFormat", null);
                xtwWriter.WriteElementString("Protection", null);


                // 
                xtwWriter.WriteEndElement();

                //
                xtwWriter.WriteStartElement("Style");
                xtwWriter.WriteAttributeString("ss", "ID", null, "s16");
                xtwWriter.WriteStartElement("Font");
                xtwWriter.WriteAttributeString("ss", "Bold", null, "1");
                xtwWriter.WriteAttributeString("ss", "Size", null, "11");
                xtwWriter.WriteAttributeString("ss", "Underline", null, "Single");
                xtwWriter.WriteEndElement();

                // 
                xtwWriter.WriteEndElement();


                // 
                xtwWriter.WriteEndElement();

                // 
                xtwWriter.WriteStartElement("Worksheet");
                xtwWriter.WriteAttributeString("ss", "Name", null, dtSource.TableName);

                // 
                xtwWriter.WriteStartElement("Table");
                xtwWriter.WriteAttributeString("ss", "ExpandedColumnCount", null, dtSource.Columns.Count.ToString());
                xtwWriter.WriteAttributeString("ss", "ExpandedRowCount", null, (dtSource.Rows.Count + 1).ToString());
                xtwWriter.WriteAttributeString("x", "FullColumns", null, "1");
                xtwWriter.WriteAttributeString("x", "FullRows", null, "1");
                //xtwWriter.WriteAttributeString("ss", "DefaultColumnWidth", null, "60");

                // Run through all rows of data source


                // 
                xtwWriter.WriteStartElement("Row");
                foreach (DataColumn Header in dtSource.Columns)
                {
                    // 
                    xtwWriter.WriteStartElement("Cell");
                    xtwWriter.WriteAttributeString("ss", "StyleID", null, "s16");

                    // xxx
                    xtwWriter.WriteStartElement("Data");
                    xtwWriter.WriteAttributeString("ss", "Type", null, "String");
                    // Write content of cell
                    xtwWriter.WriteValue(Header.ColumnName);

                    // 
                    xtwWriter.WriteEndElement();

                    // 
                    xtwWriter.WriteEndElement();
                }

                xtwWriter.WriteEndElement();


                foreach (DataRow row in dtSource.Rows)
                {
                    // 
                    xtwWriter.WriteStartElement("Row");

                    // Run through all cell of current rows
                    foreach (object cellValue in row.ItemArray)
                    {
                        // 
                        xtwWriter.WriteStartElement("Cell");
                        //if (cnt == 0)
                        //    xtwWriter.WriteAttributeString("ss", "StyleID", null, "s16");

                        // 
                        xtwWriter.WriteStartElement("Data");
                        xtwWriter.WriteAttributeString("ss", "Type", null, "String");
                        // Write content of cell
                        string strcellValue = (cellValue == System.DBNull.Value ? string.Empty : (string)cellValue);
                        xtwWriter.WriteValue(strcellValue);

                        // 
                        xtwWriter.WriteEndElement();

                        // 
                        xtwWriter.WriteEndElement();

                        xtwWriter.WriteEndElement();
                    }
                    // 
                    xtwWriter.WriteEndElement();
                }

                xtwWriter.WriteEndElement();

                // 
                xtwWriter.WriteStartElement("WorksheetOptions", "urn:schemas-microsoft-com:office:excel");

                // Write settings of page
                xtwWriter.WriteStartElement("PageSetup");
                xtwWriter.WriteStartElement("Header");
                xtwWriter.WriteAttributeString("x", "Margin", null, "0.4921259845");
                xtwWriter.WriteEndElement();
                xtwWriter.WriteStartElement("Footer");
                xtwWriter.WriteAttributeString("x", "Margin", null, "0.4921259845");
                xtwWriter.WriteEndElement();
                xtwWriter.WriteStartElement("PageMargins");
                xtwWriter.WriteAttributeString("x", "Bottom", null, "0.984251969");
                xtwWriter.WriteAttributeString("x", "Left", null, "0.78740157499999996");
                xtwWriter.WriteAttributeString("x", "Right", null, "0.78740157499999996");
                xtwWriter.WriteAttributeString("x", "Top", null, "0.984251969");
                xtwWriter.WriteEndElement();
                xtwWriter.WriteEndElement();

                // 
                xtwWriter.WriteElementString("Selected", null);

                // 
                xtwWriter.WriteStartElement("Panes");

                // 
                xtwWriter.WriteStartElement("Pane");

                // Write settings of active field
                xtwWriter.WriteElementString("Number", "1");
                xtwWriter.WriteElementString("ActiveRow", "1");
                xtwWriter.WriteElementString("ActiveCol", "1");

                // 
                xtwWriter.WriteEndElement();

                // 
                xtwWriter.WriteEndElement();

                // False
                xtwWriter.WriteElementString("ProtectObjects", "False");

                // False
                xtwWriter.WriteElementString("ProtectScenarios", "False");

                // 
                xtwWriter.WriteEndElement();

                // 
                xtwWriter.WriteEndElement();

                // 
                xtwWriter.WriteEndElement();

                // Write file on hard disk
                xtwWriter.Flush();
                xtwWriter.Close();
            }
        }
    }

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

ASP.NET Community

United States United States
The ASP.NET Wiki was started by Scott Hanselman in February of 2008. The idea is that folks spend a lot of time trolling the blogs, googlinglive-searching for answers to common "How To" questions. There's piles of fantastic community-created and MSFT-created content out there, but if it's not found by a search engine and the right combination of keywords, it's often lost.

The ASP.NET Wiki articles moved to CodeProject in October 2013 and will live on, loved, protected and updated by the community.
Group type: Collaborative Group

490 members


Comments and Discussions

 
BugError: Token StartElement in state Epilog would result in an invalid XML document PinmemberMember 19853257-Jul-14 5:34 
SuggestionRe: Error: Token StartElement in state Epilog would result in an invalid XML document PinmemberIstván Kovács (HU)4-Sep-14 6:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150414.5 | Last Updated 4 Sep 2014
Article Copyright 2013 by ASP.NET Community
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid