Click here to Skip to main content
13,147,956 members (73,786 online)
Click here to Skip to main content

Stats

40.1K views
658 downloads
63 bookmarked
Posted 17 Mar 2009

Reusable Class to Generate Excel Files

, 20 Mar 2009
A class that generates an XML string as an Excel file
ExcelGeneratingClass
ExcelGeneratingClass
ExcelGeneratingClass.csproj.user
Properties
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ExcelGeneratingClass
{
    class ExcelDatasetDemo
    {
        #region - Private Constants -
        private const string TABLE_HEADER_FORMAT_NAME = "greyBackground";
        private const string SMALL_FONT_FORMAT = "smallFont";
        #endregion

        #region - Create Excel File - 
        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();
        }
        #endregion

        #region - Create DataTable -
        private static DataTable CreateDataTable()
        {
            DataTable companies = new DataTable("Companies");
            DataColumn column;

            column = new DataColumn("CompanyName");
            companies.Columns.Add(column);

            column = new DataColumn("CompanyCountry");
            companies.Columns.Add(column);

            column = new DataColumn("YearSales");
            companies.Columns.Add(column);

            column = new DataColumn("GrossProfit");
            companies.Columns.Add(column);

            column = new DataColumn("CreationDate");
            companies.Columns.Add(column);

            companies.Rows.Add("Mexican Tequila", "Mexico", "10875600", "4975200", "03/24/1995 2:35:00PM");
            companies.Rows.Add("Canadian Food", "Canada", "308450870", "103476200", "08/12/1983");
            companies.Rows.Add("French Wines", "France", "285309567", "81650000", "11/21/1990 17:40:00");
            companies.Rows.Add("German Beer", "Germany", "285309567", "81650000", "11/21/1990 17:40:00");
            companies.Rows.Add("Russian Vodka", "Russia", "285309567", "81650000", "11/21/1990 17:40:00");
            companies.Rows.Add("Spanish Paella", "Spain", "285309567", "81650000", "11/21/1990 17:40:00");
            companies.Rows.Add("Argentinian Beef", "Argentina", "285309567", "81650000", "11/21/1990 17:40:00");
            companies.Rows.Add("Italian Pasta", "Italy", "285309567", "81650000", "11/21/1990 17:40:00");

            return companies;
        }
        #endregion
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

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

Share

About the Author

Sergio Romero
Software Developer (Senior)
Mexico Mexico
Sergio has been working as a software developer since 2002 in several industries such as Insurance, Health, and Oil. He is an MCAD and most likely will be looking to upgrade his certification soon.

He currently lives in Montreal, Canada.

You may also be interested in...

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170915.1 | Last Updated 20 Mar 2009
Article Copyright 2009 by Sergio Romero
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid