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; }
}
public void Create(DataTable dtSource, string strFileName)
{
using (XmlTextWriter xtwWriter = new XmlTextWriter(strFileName, Encoding.UTF8))
{
xtwWriter.Formatting = Formatting.Indented;
xtwWriter.WriteStartDocument();
xtwWriter.WriteProcessingInstruction("mso-application", "progid='Excel.Sheet'");
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");
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");
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();
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.WriteStartElement("Row");
foreach (DataColumn Header in dtSource.Columns)
{
xtwWriter.WriteStartElement("Cell");
xtwWriter.WriteAttributeString("ss", "StyleID", null, "s16");
xtwWriter.WriteStartElement("Data");
xtwWriter.WriteAttributeString("ss", "Type", null, "String");
xtwWriter.WriteValue(Header.ColumnName);
xtwWriter.WriteEndElement();
xtwWriter.WriteEndElement();
}
xtwWriter.WriteEndElement();
foreach (DataRow row in dtSource.Rows)
{
xtwWriter.WriteStartElement("Row");
foreach (object cellValue in row.ItemArray)
{
xtwWriter.WriteStartElement("Cell");
xtwWriter.WriteStartElement("Data");
xtwWriter.WriteAttributeString("ss", "Type", null, "String");
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");
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");
xtwWriter.WriteElementString("Number", "1");
xtwWriter.WriteElementString("ActiveRow", "1");
xtwWriter.WriteElementString("ActiveCol", "1");
xtwWriter.WriteEndElement();
xtwWriter.WriteEndElement();
xtwWriter.WriteElementString("ProtectObjects", "False");
xtwWriter.WriteElementString("ProtectScenarios", "False");
xtwWriter.WriteEndElement();
xtwWriter.WriteEndElement();
xtwWriter.WriteEndElement();
xtwWriter.Flush();
xtwWriter.Close();
}
}
}
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.