Voorbeeld class voor cre-eren Excel file

 using System; using System.Linq; using System.Xml.Linq; namespace ExcelGeneratingClass { ///  /// Creates an Xml document with the format that generates an Excel document. /// public class XmlExcelHelper { #region - Private Constants - private const string DEFAULT_FILE_NAME = @"c:\GeneratedExcel.xml"; private const string DEFAULT_FONT_NAME = "Arial"; private const int DEFAULT_FONT_SIZE = 10; private const string XML_DECLARATION_STRING = ""; #endregion #region - Enumerations - public enum CellType { String, Number } ///  /// Styles included by default for rows and cells. /// ///  /// BoldColumn sets the text as Bold using the default Font and Font Size. /// StringLiteral sets the text as the default Font and Font Size. /// DateLiteral sets the date format to mm/dd/yyyy /// public enum DefaultStyles { BoldColumn, StringLiteral, Decimal, Integer, DateLiteral } #endregion #region - Private Attributes - private readonly XDocument _XExport; private XElement _Workbook; private readonly XNamespace _MainNamespace = XNamespace.Get("urn:schemas-microsoft-com:office:spreadsheet"); private readonly XNamespace _o = XNamespace.Get("urn:schemas-microsoft-com:office:office"); private readonly XNamespace _x = XNamespace.Get("urn:schemas-microsoft-com:office:excel"); private readonly XNamespace _ss = XNamespace.Get("urn:schemas-microsoft-com:office:spreadsheet"); private readonly XNamespace _html = XNamespace.Get("http://www.w3.org/TR/REC-html40"); private string _ExcelFileXml; #endregion #region - Properties - ///  /// Gets or sets the name in which the file will be saved. /// /// If the path is relative the file will be saved where the application is running. public string FileName { get; set; } ///  /// Gets the Font Name that is used by default on the Excel document. /// public string DefaultFontName{ get; private set; } ///  /// Gets the Font Size that is used by default on the Excel document. /// public int DefaultFontSize { get; private set; } ///  /// Gets the generated xml string to create an Excel document. /// public string ExcelFileXml { get { System.Text.StringBuilder sb = new System.Text.StringBuilder(); _ExcelFileXml = _XExport.ToString(); //During testing, even though during the creation of the _XExport document the Declaration element //was added, it seems that the ToString method of the XElement object removes it so this code was //added to ensure that the declaration element always exists in the returned xml string. //If the declaration is missing the Excel document will not be properly rendered. if (!_ExcelFileXml.Contains(XML_DECLARATION_STRING)) { sb.Append(XML_DECLARATION_STRING); sb.Append(_ExcelFileXml); _ExcelFileXml = sb.ToString(); } return _ExcelFileXml; } private set { _ExcelFileXml = value; } } ///  /// Gets or sets the name of the file's author. This value will be included in the Excel file's properties. /// public string Author { get; set; } ///  /// Gets or sets the name of the file's last author. This value will be included in the Excel file's properties. /// public string LastAuthor { get; set; } #endregion #region - Constructors - ///  /// Creates a new instance of the XmlExcelHelper class. /// /// The FileName property is set to the default value "c:\GeneratedExcel.xml" public XmlExcelHelper() : this(DEFAULT_FILE_NAME, DEFAULT_FONT_NAME, DEFAULT_FONT_SIZE) { } ///  /// Creates a new instance of the XmlExcelHelper class. /// /// A string that sets the name in which the file will be saved. public XmlExcelHelper(string fileName) : this(fileName, DEFAULT_FONT_NAME, DEFAULT_FONT_SIZE) { } ///  /// Creates a new instance of the XmlExcelHelper class. /// /// A string that sets the name in which the file will be saved. /// A string that sets the Font Name that will be used as default for the Excel document. /// An integer that sets the Font Size that will be used as default for the Excel document. public XmlExcelHelper(string fileName, string fontName, int fontSize) { FileName = fileName; DefaultFontName = fontName; DefaultFontSize = fontSize; _XExport = new XDocument(new XDeclaration("1.0", "utf-8", "yes"), null); _XExport.Add(new XProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"")); CreateExcelHeader(); _XExport.Add(_Workbook); ExcelFileXml = _XExport.ToString(); //Xml for an empty excel document. } #endregion #region - Public Methods - ///  /// Saves the document to disk using the value of the FileName property. /// public void SaveDocument() { _XExport.Save(FileName); } ///  /// Adds a new worksheet to the Excel document. /// /// A string with the name for the new worksheet. public void CreateSheet(string sheetName) { var worksheet = (new XElement(_MainNamespace + "Worksheet", new XAttribute(_ss + "Name", sheetName), new XElement(_MainNamespace + "Table"))); _Workbook.Add(worksheet); ExcelFileXml = _XExport.ToString(); } ///  /// Adds a new row to the current worksheet with the Default style. /// public void AddRow() { AddRow("Default"); } ///  /// Adds a new row to the current worksheet with the Default style. /// /// A DefaultStyle value that sets the style of the new row. public void AddRow(DefaultStyles style) { AddRow(style.ToString()); } ///  /// Adds a new row to the current worksheet with the received style. /// /// A string with a style that will be applied to the new row. /// The style string must exist in the styles collection of the xml document. Custom styles /// can be added by using the AddStringStyle method. A style only needs to be added once. When a style /// has been added it can be used repeatedly for both rows and cells. /// If the received style does not exist Excel will not be able to open the document. /// See the AddStringStyle method for details. public void AddRow(string style) { var table = _Workbook.Elements().Where(w => w.Name == _MainNamespace + "Worksheet").LastOrDefault() .Elements().Where(e => e.Name == _MainNamespace + "Table").LastOrDefault(); var row = new XElement(_MainNamespace + "Row", new XAttribute(_ss + "StyleID", style)); table.Add(row); ExcelFileXml = _XExport.ToString(); } ///  /// Adds a cell to the current row in the current worksheet. /// /// A CellType enumeration. The values can be String and Number. /// A string with the contents for the cell. public void AddCell(CellType type, string value) { AddCell(type, string.Empty, value, 0); } ///  /// Adds a cell to the current row in the current worksheet. /// /// A CellType enumeration. The values can be String and Number. /// A string with the contents for the cell. ///  public void AddCell(CellType type, string value, int mergeAcrossCells) { AddCell(type, string.Empty, value, mergeAcrossCells); } ///  /// Adds a cell to the current row in the current worksheet. /// /// A CellType enumeration. The values can be String and Number. /// A DefaultStyle value that sets the style of the new row. /// A string with the contents for the cell. public void AddCell(CellType type, DefaultStyles style, string value) { AddCell(type, style.ToString(), value, 0); } ///  /// Adds a cell to the current row in the current worksheet. /// /// A CellType enumeration. The values can be String and Number. /// A DefaultStyle value that sets the style of the new row. /// A string with the contents for the cell. ///  public void AddCell(CellType type, DefaultStyles style, string value, int mergeAcrossCells) { AddCell(type, style.ToString(), value, mergeAcrossCells); } ///  /// Adds a cell to the current row in the current worksheet. /// /// A CellType enumeration. The values can be String and Number. /// A DefaultStyle value that sets the style of the new row. /// A string with the contents for the cell. public void AddCell(CellType type, string style, string value) { AddCell(type, style, value, 0); } ///  /// Adds a cell to the current row in the current worksheet. /// /// A CellType enumeration. The values can be String and Number. /// A string with a style that will be applied to the new cell. /// A string with the contents for the cell. ///  /// The style string must exist in the styles collection of the xml document. Custom styles /// can be added by using the AddStringStyle method. A style only needs to be added once. When a style /// has been added it can be used repeatedly for both rows and cells. /// If the received style does not exist Excel will not be able to open the document. /// See the AddStringStyle method for details. public void AddCell(CellType type, string style, string value, int mergeAcrossCells) { var row = _Workbook.Elements().Where(w => w.Name == _MainNamespace + "Worksheet").LastOrDefault() .Elements().Where(t => t.Name == _MainNamespace + "Table").LastOrDefault() .Elements().Where(r => r.Name == _MainNamespace + "Row").LastOrDefault(); XElement cell; cell = new XElement(_MainNamespace + "Cell", new XElement(_MainNamespace + "Data", new XAttribute(_ss + "Type", type.ToString()), value) ); if(mergeAcrossCells != 0) { XAttribute mergeAttribute = new XAttribute(_ss + "MergeAcross", mergeAcrossCells); cell.Add(mergeAttribute); } if(!string.IsNullOrEmpty(style)) { XAttribute styleAttribute = new XAttribute(_ss + "StyleID", style); cell.Add(styleAttribute); } row.Add(cell); ExcelFileXml = _XExport.ToString(); } ///  /// Creates a new style for rows or cells with text content. /// /// A string with a unique id from which the style will be referenced in order for it to be applied. /// A string with the name of the font to be used in the style. /// An integer with the size for the font to be used in the style. /// A string with the hexadecimal value that represents the color in which the text will be displayed. /// The value must be preceeded by the pound symbol i.e. "#000000" for black. /// A boolean to indicate if the text should be bold. /// Once this method is executed, a new "style" element will be added to the "Styles" node in the xml /// document and will be identified by the styleId parameter. /// The styleId is the string that must be used in the AddRow and AddCell "style" parameter public void AddStringStyle(string styleId, string fontName, int size, string color, bool bold) { AddStringStyle(styleId, fontName, "Swiss", size, color, string.Empty, bold); } ///  /// Creates a new style for rows or cells with text content. /// /// A string with a unique id from which the style will be referenced in order for it to be applied. /// A string with the name of the font to be used in the style. /// An integer with the size for the font to be used in the style. /// A string with the hexadecimal value that represents the color in which the text will be displayed. /// The value must be preceeded by the pound symbol i.e. "#000000" for black. /// A string with the hexadecimal value that represents the color in which the cell will be displayed. /// The value must be preceeded by the pound symbol i.e. "#000000" for black. /// A boolean to indicate if the text should be bold. /// Once this method is executed, a new "style" element will be added to the "Styles" node in the xml /// document and will be identified by the styleId parameter. /// The styleId is the string that must be used in the AddRow and AddCell "style" parameter public void AddStringStyle(string styleId, string fontName, int size, string color, string backgroundColor, bool bold) { AddStringStyle(styleId, fontName, "Swiss", size, color, backgroundColor, bold); } ///  /// Creates a new style for rows or cells with text content. /// /// A string with a unique id from which the style will be referenced in order for it to be applied. /// A string with the name of the font to be used in the style. /// A string with the name of the font family to be used in the style. /// An integer with the size for the font to be used in the style. /// A string with the hexadecimal value that represents the color in which the text will be displayed. /// The value must be preceeded by the pound symbol i.e. "#000000" for black. /// A string with the hexadecimal value that represents the color in which the cell will be displayed. /// The value must be preceeded by the pound symbol i.e. "#000000" for black. /// A boolean to indicate if the text should be bold. /// Once this method is executed, a new "style" element will be added to the "Styles" node in the xml /// document and will be identified by the styleId parameter. /// The styleId is the string that must be used in the AddRow and AddCell "style" parameter public void AddStringStyle(string styleId, string fontName, string fontFamily, int size, string color, string backgroundColor, bool bold) { var styles = _Workbook.Elements().Where(s => s.Name == _MainNamespace + "Styles").FirstOrDefault(); XElement interior; if(!string.IsNullOrEmpty(backgroundColor)) { interior = new XElement(_MainNamespace + "Interior", new XAttribute(_ss + "Color", backgroundColor), new XAttribute(_ss + "Pattern", "Solid")); } else { interior = new XElement(_MainNamespace + "Interior"); } var style = new XElement(_MainNamespace + "Style", new XAttribute(_ss + "ID", styleId), new XElement(_MainNamespace + "Font", new XAttribute(_ss + "FontName", fontName), new XAttribute(_x + "Family", fontFamily), new XAttribute(_ss + "Size", size.ToString()), new XAttribute(_ss + "Color", color), new XAttribute(_ss + "Bold", Convert.ToInt16(bold).ToString()) ), interior ); styles.Add(style); ExcelFileXml = _XExport.ToString(); } #endregion #region - Excel File Header Methods - ///  /// Generates the Xml header with the necessary namespaces and schema for an Excel document. /// /// Within the Xml header created in this method a Workbook element is created. /// All subsequent elements will be created as children of the Workbook one. private void CreateExcelHeader() { _Workbook = new XElement(_MainNamespace + "Workbook", new XAttribute(XNamespace.Xmlns + "html", _html), CreateNamespaceAttribute(XName.Get("ss", "http://www.w3.org/2000/xmlns/"), _ss), CreateNamespaceAttribute(XName.Get("o", "http://www.w3.org/2000/xmlns/"), _o), CreateNamespaceAttribute(XName.Get("x", "http://www.w3.org/2000/xmlns/"), _x), CreateNamespaceAttribute(_MainNamespace), new XElement(_o + "DocumentProperties", CreateNamespaceAttribute(_o), new XElement(_o + "Author", Author), new XElement(_o + "LastAuthor", LastAuthor), new XElement(_o + "Created", DateTime.Now.ToString()) ), //end document properties) new XElement(_x + "ExcelWorkbook", CreateNamespaceAttribute(_x), new XElement(_x + "WindowHeight", 12750), new XElement(_x + "WindowWidth", 24855), new XElement(_x + "WindowTopX", 240), new XElement(_x + "WindowTopY", 75), new XElement(_x + "ProtectStructure", "False"), new XElement(_x + "ProtectWindows", "False") ), //end ExcelWorkbook new XElement(_MainNamespace + "Styles", new XElement(_MainNamespace + "Style", new XAttribute(_ss + "ID", "Default"), new XAttribute(_ss + "Name", "Normal"), new XElement(_MainNamespace + "Alignment", new XAttribute(_ss + "Vertical", "Bottom") ), new XElement(_MainNamespace + "Borders"), new XElement(_MainNamespace + "Font", new XAttribute(_ss + "FontName", DefaultFontName), new XAttribute(_x + "Family", "Swiss"), new XAttribute(_ss + "Size", DefaultFontSize.ToString()), new XAttribute(_ss + "Color", "#000000") ), new XElement(_MainNamespace + "Interior"), new XElement(_MainNamespace + "NumberFormat"), new XElement(_MainNamespace + "Protection") ), new XElement(_MainNamespace + "Style", new XAttribute(_ss + "ID", "BoldColumn"), new XElement(_MainNamespace + "Font", new XAttribute(_ss + "FontName", DefaultFontName), new XAttribute(_x + "Family", "Swiss"), new XAttribute(_ss + "Size", DefaultFontSize), new XAttribute(_ss + "Color", "#000000"), new XAttribute(_ss + "Bold", "1") ) ), new XElement(_MainNamespace + "Style", new XAttribute(_ss + "ID", "StringLiteral"), new XElement(_MainNamespace + "NumberFormat", new XAttribute(_ss + "Format", "@") ) ), new XElement(_MainNamespace + "Style", new XAttribute(_ss + "ID", "Decimal"), new XElement(_MainNamespace + "NumberFormat", new XAttribute(_ss + "Format", "#,##0") ) ), new XElement(_MainNamespace + "Style", new XAttribute(_ss + "ID", "Integer"), new XElement(_MainNamespace + "NumberFormat", new XAttribute(_ss + "Format", "0") ) ), new XElement(_MainNamespace + "Style", new XAttribute(_ss + "ID", "DateLiteral"), new XElement(_MainNamespace + "NumberFormat", new XAttribute(_ss + "Format", "mm/dd/yyyy;@") ) ) ) // close styles ); } private static XAttribute CreateNamespaceAttribute(XNamespace ns) { return CreateNamespaceAttribute(XName.Get("xmlns", ""), ns); } private static XAttribute CreateNamespaceAttribute(XName name, XNamespace ns) { var ssAtt = new XAttribute(name, ns.NamespaceName); ssAtt.AddAnnotation(ns); return ssAtt; } #endregion } }

 

Gebruik als volgt:

 public static void CreateReport() { DataTable table = CreateDataTable(); XmlExcelHelper helper = new XmlExcelHelper(@"c:\MyExcelTest.xml", "Times New Roman", 11); helper.AddStringStyle(TABLE_HEADER_FORMAT_NAME, "Arial", 10, "#FFFFFF", "#C0C0C0", false); helper.AddStringStyle(SMALL_FONT_FORMAT, "Arial", 8, "#000000", false); helper.CreateSheet("International Foods"); helper.AddRow(); helper.AddCell(XmlExcelHelper.CellType.String, "Report Generated with LINQ to XML"); helper.AddRow(); helper.AddCell(XmlExcelHelper.CellType.String, SMALL_FONT_FORMAT, "Report generated on " + DateTime.Now.ToLongDateString()); helper.AddRow(); foreach (DataColumn column in table.Columns) { helper.AddCell(XmlExcelHelper.CellType.String, TABLE_HEADER_FORMAT_NAME, column.ColumnName); } foreach (DataRow row in table.Rows) { helper.AddRow(); helper.AddCell(XmlExcelHelper.CellType.String, row[0].ToString()); helper.AddCell(XmlExcelHelper.CellType.String, row[1].ToString()); helper.AddCell(XmlExcelHelper.CellType.Number, XmlExcelHelper.DefaultStyles.Decimal, row[2].ToString()); helper.AddCell(XmlExcelHelper.CellType.Number, XmlExcelHelper.DefaultStyles.Decimal, row[3].ToString()); helper.AddCell(XmlExcelHelper.CellType.String, row[4].ToString()); } helper.SaveDocument(); }

{{opmerking.Naam}}:

{{opmerking.OpmerkingText}}

            

Saving your comment....

Naam is verplicht!
Email is verplicht!
Opmerking is verplicht!