Saturday, November 19, 2011

Create Excel File From DataTable

Hello Friends,
Many times we need to create Excel files in our application using existing Data.
I have figured out a class which will help to create excel file by just passing DataTable and preferred location to generate on server.
please download the cs file from here .. and use it :)

here is full code

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; }
        }
        /// <summary>
        /// Create one Excel-XML-Document with SpreadsheetML from a DataTable
        /// </summary>
        /// <param name="dataSource">Datasource which would be exported in Excel</param>
        /// <param name="fileName">Name of exported file</param>
        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;

                // <?xml version="1.0"?>
                xtwWriter.WriteStartDocument();

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

                // <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet >"
                xtwWriter.WriteStartElement("Workbook", "urn:schemas-microsoft-com:office:spreadsheet");

                //Write definition of namespace
                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");

                // <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
                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");

                // </DocumentProperties>
                xtwWriter.WriteEndElement();

                // <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
                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");

                // </ExcelWorkbook>
                xtwWriter.WriteEndElement();

                // <Styles>
                xtwWriter.WriteStartElement("Styles");

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

                // <Alignment ss:Vertical="Bottom"/>
                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);


                // </Style>
                xtwWriter.WriteEndElement();

                //<Style ss:ID="s16">
                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();

                // </Style>
                xtwWriter.WriteEndElement();


                // </Styles>
                xtwWriter.WriteEndElement();

                // <Worksheet ss:Name="xxx">
                xtwWriter.WriteStartElement("Worksheet");
                xtwWriter.WriteAttributeString("ss", "Name", null, dtSource.TableName);

                // <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="3" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60">
                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


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

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

                    // </Data>
                    xtwWriter.WriteEndElement();

                    // </Cell>
                    xtwWriter.WriteEndElement();
                }

                xtwWriter.WriteEndElement();


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

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

                        // <Data ss:Type="String">xxx</Data>
                        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);

                        // </Data>
                        xtwWriter.WriteEndElement();

                        // </Cell>
                        xtwWriter.WriteEndElement();
                    }
                    // </Row>
                    xtwWriter.WriteEndElement();
                }
                // </Table>
                xtwWriter.WriteEndElement();

                // <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                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();

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

                // <Panes>
                xtwWriter.WriteStartElement("Panes");

                // <Pane>
                xtwWriter.WriteStartElement("Pane");

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

                // </Pane>
                xtwWriter.WriteEndElement();

                // </Panes>
                xtwWriter.WriteEndElement();

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

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

                // </WorksheetOptions>
                xtwWriter.WriteEndElement();

                // </Worksheet>
                xtwWriter.WriteEndElement();

                // </Workbook>
                xtwWriter.WriteEndElement();

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


Write me if help full
Happy Coding..

1 comment:

Angel said...

It's Very HelpFul...