Click here to Skip to main content
Click here to Skip to main content

Using C# and Open XML SDK 2.0 for Microsoft Office to Create an Excel 2007 Document

, 30 Jul 2010
Rate this:
Please Sign up or sign in to vote.
Shows how to create an Excel 2007 document using C# and Open XML SDK 2.0 for Microsoft Office.

Spreadsheet.jpg

Introduction

Many business users want to export their business objects to a Microsoft Excel Spreadsheet because it offers the ability to present and manipulate data to business executives in a way that makes sense to them.

In this article, we are going to learn how easy it is to create an Excel Spreadsheet document from a list of objects using C# and Open XML SDK 2.0 for Microsoft Office.

Background

Open XML Formats Developer Group was formed to provide a technical forum for developers who are interested in using the ECMA International-developed Office Open XML file formats. Office 2007 used the format to create Word, Excel, etc. The default web site of Open XML Formats Developer Group is http://openxmldeveloper.org/default.aspx

The Open XML SDK 2.0 provided by Microsoft simplifies the task of manipulating Open XML packages and the underlying Open XML schema elements within a package. The Open XML Application Programming Interface (API) encapsulates many common tasks that developers perform on Open XML packages. The download link for Open XML SDK 2.0 is http://www.microsoft.com/downloads/details.aspx?FamilyID=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&displaylang=en.

In the past, for us to export data in our C# application to an Excel spreadsheet, we had to either user XSLT transform or COM interop. Open XML SDK is a .NET object we can use without having to learn all the XSLT, and it easy to use and reliable. Working with COM Interop is also not that easy, and we will have to deal with different versions of Excel objects for Excel 2007 and Excel 2010. Sometimes we just want to build a service to export a spreadsheet from a server. Using COM interop means we have to install part of the Excel COM objects into our server to make it work. The Open XML SDK 2.0 does need any of the Excel COM objects in our server.

Purposes

The purpose to export a business objects into spreadsheet for business users, an example is when we display a data list in the grid and users want to have in a spreadsheet to be certain scenario of what if. This is very useful for Financial Analyst who just wants these data in a spreadsheet.

Using the Code

First, you have to create a Console Application using Visual Studio 2010.

In order to use the classes, you must add as a reference the DocumentFormat.OpenXml library from Open XML SDK 2.0, and WindowsBase in your project; adding in Visual Studio is straightforward. First, you have install Open XML SDK 2.0 provided by Microsoft, and it will install into C:\Program Files\Open XML SDK.

Right click on your project on Solution Explorer, and you should see the Add Reference option. Once selected, click on the Browser tab and browser the folder C:\Program Files\Open XML SDK\V2.0\lib and select DocumentFormat.OpenXml.dll. You also need to select WindowBase and System.Drawing from the .NET tab.

You can copy the respective classes to the files created. Finally copy void Main and Package class into Program.cs.

Let's Go Over the Class

We have created CustomStylesheet.cs, CustomColumn.cs, CustomCell.cs, and ExcelHelper.cs.

Custom Stylesheet

We will try to create a custom stylesheet that has most of the commonly used formats, as follows:

  • It has two types of fonts: Normal and Heading that has two index.
  • It has four types of Fills including empty and grey.
  • It has three types of borders: empty, all sides, top and bottom.
  • Finally, it has twelve types of cell formats, for numbers, date and text, and each StyleIndex in a cell uses the index to format correctly.

You can add a lot more styles for formatting the cells. The cell style index has to assigned with the correct index. As you can see, the custom cells in the projects use the index to format numbers and date.

//CustomStylesheet.cs
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
namespace CreateExcelSpreadsheet
{
    public class CustomStylesheet : Stylesheet
    {
        public CustomStylesheet()
        {
            var fonts = new Fonts();
            var font = new DocumentFormat.OpenXml.Spreadsheet.Font();
            var fontName = new FontName {Val = StringValue.FromString("Arial")};
            var fontSize = new FontSize {Val = DoubleValue.FromDouble(11)};
            font.FontName = fontName;
            font.FontSize = fontSize;
            fonts.Append(font);
            //Font Index 1
            font = new DocumentFormat.OpenXml.Spreadsheet.Font();
            fontName = new FontName {Val = StringValue.FromString("Arial")};
            fontSize = new FontSize {Val = DoubleValue.FromDouble(12)};
            font.FontName = fontName;
            font.FontSize = fontSize;
            font.Bold = new Bold();
            fonts.Append(font);
            fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
            var fills = new Fills();
            var fill = new Fill();
            var patternFill = new PatternFill {PatternType = PatternValues.None};
            fill.PatternFill = patternFill;
            fills.Append(fill);
            fill = new Fill();
            patternFill = new PatternFill {PatternType = PatternValues.Gray125};
            fill.PatternFill = patternFill;
            fills.Append(fill);
            //Fill index  2
            fill = new Fill();
            patternFill = new PatternFill {PatternType = PatternValues.Solid, 
                                           ForegroundColor = new ForegroundColor()};
            patternFill.ForegroundColor = 
               TranslateForeground(System.Drawing.Color.LightBlue);
            patternFill.BackgroundColor = 
                new BackgroundColor {Rgb = patternFill.ForegroundColor.Rgb};
            fill.PatternFill = patternFill;
            fills.Append(fill);
            //Fill index  3
            fill = new Fill();
            patternFill = new PatternFill {PatternType = PatternValues.Solid, 
                              ForegroundColor = new ForegroundColor()};
            patternFill.ForegroundColor = 
               TranslateForeground(System.Drawing.Color.DodgerBlue);
            patternFill.BackgroundColor = 
               new BackgroundColor {Rgb = patternFill.ForegroundColor.Rgb};
            fill.PatternFill = patternFill;
            fills.Append(fill);
            fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
            var borders = new Borders();
            var border = new Border
                        {
                            LeftBorder = new LeftBorder(),
                            RightBorder = new RightBorder(),
                            TopBorder = new TopBorder(),
                            BottomBorder = new BottomBorder(),
                            DiagonalBorder = new DiagonalBorder()
                        };
            borders.Append(border);
            //All Boarder Index 1
            border = new Border
                         {
                             LeftBorder = new LeftBorder {Style = BorderStyleValues.Thin},
                             RightBorder = new RightBorder {Style = BorderStyleValues.Thin},
                             TopBorder = new TopBorder {Style = BorderStyleValues.Thin},
                             BottomBorder = new BottomBorder {Style = BorderStyleValues.Thin},
                             DiagonalBorder = new DiagonalBorder()
                         };
            borders.Append(border);
            //Top and Bottom Boarder Index 2
            border = new Border
            {
                LeftBorder = new LeftBorder(),
                RightBorder = new RightBorder (),
                TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
                BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
                DiagonalBorder = new DiagonalBorder()
            };
            borders.Append(border);
            borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
            var cellStyleFormats = new CellStyleFormats();
            var cellFormat = new CellFormat {NumberFormatId = 0, 
                                 FontId = 0, FillId = 0, BorderId = 0};
            cellStyleFormats.Append(cellFormat);
            cellStyleFormats.Count = 
               UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
            uint iExcelIndex = 164;
            var numberingFormats = new NumberingFormats();
            var cellFormats = new CellFormats();
            cellFormat = new CellFormat {NumberFormatId = 0, FontId = 0, 
                             FillId = 0, BorderId = 0, FormatId = 0};
            cellFormats.Append(cellFormat);
            var nformatDateTime = new NumberingFormat
                     {
                         NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
                         FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
                     };
            numberingFormats.Append(nformatDateTime);
            var nformat4Decimal = new NumberingFormat
                     {
                         NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
                         FormatCode = StringValue.FromString("#,##0.0000")
                     };
            numberingFormats.Append(nformat4Decimal);
            var nformat2Decimal = new NumberingFormat
                      {
                          NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
                          FormatCode = StringValue.FromString("#,##0.00")
                      };
            numberingFormats.Append(nformat2Decimal);
            var nformatForcedText = new NumberingFormat
                       {
                           NumberFormatId = UInt32Value.FromUInt32(iExcelIndex),
                           FormatCode = StringValue.FromString("@")
                       };
            numberingFormats.Append(nformatForcedText);
            // index 1
            // Cell Standard Date format 
            cellFormat = new CellFormat
                 {
                     NumberFormatId = 14,
                     FontId = 0,
                     FillId = 0,
                     BorderId = 0,
                     FormatId = 0,
                     ApplyNumberFormat = BooleanValue.FromBoolean(true)
                 };
            cellFormats.Append(cellFormat);
            // Index 2
            // Cell Standard Number format with 2 decimal placing
            cellFormat = new CellFormat
                 {
                     NumberFormatId = 4,
                     FontId = 0,
                     FillId = 0,
                     BorderId = 0,
                     FormatId = 0,
                     ApplyNumberFormat = BooleanValue.FromBoolean(true)
                 };
            cellFormats.Append(cellFormat);
            // Index 3
            // Cell Date time custom format
            cellFormat = new CellFormat
                 {
                     NumberFormatId = nformatDateTime.NumberFormatId,
                     FontId = 0,
                     FillId = 0,
                     BorderId = 0,
                     FormatId = 0,
                     ApplyNumberFormat = BooleanValue.FromBoolean(true)
                 };
            cellFormats.Append(cellFormat);
            // Index 4
            // Cell 4 decimal custom format
            cellFormat = new CellFormat
                 {
                     NumberFormatId = nformat4Decimal.NumberFormatId,
                     FontId = 0,
                     FillId = 0,
                     BorderId = 0,
                     FormatId = 0,
                     ApplyNumberFormat = BooleanValue.FromBoolean(true)
                 };
            cellFormats.Append(cellFormat);
            // Index 5
            // Cell 2 decimal custom format
            cellFormat = new CellFormat
                 {
                     NumberFormatId = nformat2Decimal.NumberFormatId,
                     FontId = 0,
                     FillId = 0,
                     BorderId = 0,
                     FormatId = 0,
                     ApplyNumberFormat = BooleanValue.FromBoolean(true)
                 };
            cellFormats.Append(cellFormat);
            // Index 6
            // Cell forced number text custom format
            cellFormat = new CellFormat
                 {
                     NumberFormatId = nformatForcedText.NumberFormatId,
                     FontId = 0,
                     FillId = 0,
                     BorderId = 0,
                     FormatId = 0,
                     ApplyNumberFormat = BooleanValue.FromBoolean(true)
                 };
            cellFormats.Append(cellFormat);
            // Index 7
            // Cell text with font 12 
            cellFormat = new CellFormat
                 {
                     NumberFormatId = nformatForcedText.NumberFormatId,
                     FontId = 1,
                     FillId = 0,
                     BorderId = 0,
                     FormatId = 0,
                     ApplyNumberFormat = BooleanValue.FromBoolean(true)
                 };
            cellFormats.Append(cellFormat);
            // Index 8
            // Cell text
            cellFormat = new CellFormat
                 {
                     NumberFormatId = nformatForcedText.NumberFormatId,
                     FontId = 0,
                     FillId = 0,
                     BorderId = 1,
                     FormatId = 0,
                     ApplyNumberFormat = BooleanValue.FromBoolean(true)
                 };
            cellFormats.Append(cellFormat);
            // Index 9
            // Coloured 2 decimal cell text
            cellFormat = new CellFormat
                     {
                         NumberFormatId = nformat2Decimal.NumberFormatId,
                         FontId = 0,
                         FillId = 2,
                         BorderId = 2,
                         FormatId = 0,
                         ApplyNumberFormat = BooleanValue.FromBoolean(true)
                     };
            cellFormats.Append(cellFormat);
            // Index 10
            // Coloured cell text
            cellFormat = new CellFormat
                     {
                         NumberFormatId = nformatForcedText.NumberFormatId,
                         FontId = 0,
                         FillId = 2,
                         BorderId = 2,
                         FormatId = 0,
                         ApplyNumberFormat = BooleanValue.FromBoolean(true)
                     };
            cellFormats.Append(cellFormat);
            // Index 11
            // Coloured cell text
            cellFormat = new CellFormat
                 {
                     NumberFormatId = nformatForcedText.NumberFormatId,
                     FontId = 1,
                     FillId = 3,
                     BorderId = 2,
                     FormatId = 0,
                     ApplyNumberFormat = BooleanValue.FromBoolean(true)
                 };
            cellFormats.Append(cellFormat);
            numberingFormats.Count = 
              UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
            cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
            this.Append(numberingFormats);
            this.Append(fonts);
            this.Append(fills);
            this.Append(borders);
            this.Append(cellStyleFormats);
            this.Append(cellFormats);
            var css = new CellStyles();
            var cs = new CellStyle {Name = StringValue.FromString("Normal"), 
                                    FormatId = 0, BuiltinId = 0};
            css.Append(cs);
            css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
            this.Append(css);
            var dfs = new DifferentialFormats {Count = 0};
            this.Append(dfs);
            var tss = new TableStyles
                  {
                      Count = 0,
                      DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
                      DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
                  };
            this.Append(tss);
        }
        private static ForegroundColor TranslateForeground(System.Drawing.Color fillColor)
        {
           return new ForegroundColor()
           {
               Rgb = new HexBinaryValue()
                     {
                         Value =
                             System.Drawing.ColorTranslator.ToHtml(
                             System.Drawing.Color.FromArgb(
                                 fillColor.A,
                                 fillColor.R,
                                 fillColor.G,
                                 fillColor.B)).Replace("#", "")
                     }
           };
        }
    }
}

Custom Column Class

We create a custom column that allows us to resize to fit the heading, as sometimes the standard width does not show all the text in the cells.

//CustomColumn.cs
using System;
using DocumentFormat.OpenXml.Spreadsheet;
namespace CreateExcelSpreadsheet
{
    public class CustomColumn : Column
    {
        public CustomColumn(UInt32 startColumnIndex, 
               UInt32 endColumnIndex, double columnWidth)
        {
            this.Min = startColumnIndex;
            this.Max = endColumnIndex;
            this.Width = columnWidth;
            this.CustomWidth = true;
        }
    }
}

Custom Cell Class

We create custom cells to format the cell based on the type as follows:

  • TextCell - to be used for text fields.
  • NumberCell - to be used for integer fields.
  • FormatedNumberCell - to be used for decimal or double and format into 2 decimal placing.
  • DateCell - to be used for date, and format into dd/mm/yyyy, as in the spreadsheet it is a number value for the date and relies on the formatting.
  • FormulaCell - to used as a formula, and the example used as sum of the value of the column.
  • HeaderCell - to be used for text fields that is the header.

Another option you can further enhance your code is by allowing two different formats: for formatted number cells with two a digits decimal place and four digits decimal place.

//CustomCells

using System;
using DocumentFormat.OpenXml.Spreadsheet;
namespace CreateExcelSpreadsheet
{
    public class TextCell : Cell
    {
        public TextCell(string header, string text, int index)
        {
            this.DataType = CellValues.InlineString;
            this.CellReference = header + index;
            //Add text to the text cell.
            this.InlineString = new InlineString { Text = new Text { Text = text } };
        }
    }
    public class NumberCell : Cell
    {
        public NumberCell(string header, string text, int index)
        {
            this.DataType = CellValues.Number;
            this.CellReference = header + index;
            this.CellValue = new CellValue(text);
        }
    }
    public class FormatedNumberCell : NumberCell
    {
        public FormatedNumberCell(string header, string text, int index)
            : base(header, text, index)
        {
            this.StyleIndex = 2;
        }
    }
    public class DateCell : Cell
    {
        public DateCell(string header, DateTime dateTime, int index)
        {
            this.DataType = CellValues.Date;
            this.CellReference = header + index;
            this.StyleIndex = 1;
            this.CellValue = new CellValue { Text = dateTime.ToOADate().ToString() }; ;
        }
    }
    public class FomulaCell : Cell
    {
        public FomulaCell(string header, string text, int index)
        {
            this.CellFormula = new CellFormula { CalculateCell = true, Text = text };
            this.DataType = CellValues.Number;
            this.CellReference = header + index;
            this.StyleIndex = 2;
        }
    }
    public class HeaderCell : TextCell
    {
        public HeaderCell(string header, string text, int index) : 
               base(header, text, index)
        {
            this.StyleIndex = 11;
        }
    }
}

Excel Helper Class

We created a main helper method where we can pass the filename with the full path for the spreadsheet that we want to create, the list of the objects for rows in the spreadsheet, the sheet name (changing sheet 1), and the list of header names on the first row.

//ExcelHelper.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace CreateExcelSpreadsheet
{
    class ExcelHelper
    {
        /// <summary>
        /// Write excel file of a list of object as T
        /// Assume that maximum of 24 columns 
        /// </summary>
        /// <typeparam name="T">Object type to pass in</typeparam>
        /// <param name="fileName">Full path of the file name of excel spreadsheet</param>
        /// <param name="objects">list of the object type</param>
        /// <param name="sheetName">Sheet names of Excel File</param>
        /// <param name="headerNames">Header names of the object</param>
        public void Create<T>(
            string fileName,
            List<T> objects,
            string sheetName,
            List<string> headerNames)
        {
            //Open the copied template workbook. 
            using (SpreadsheetDocument myWorkbook = 
                   SpreadsheetDocument.Create(fileName, 
                   SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
                var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                // Create Styles and Insert into Workbook
                var stylesPart = 
                    myWorkbook.WorkbookPart.AddNewPart<WorkbookStylesPart>();
                Stylesheet styles = new CustomStylesheet();
                styles.Save(stylesPart);
                string relId = workbookPart.GetIdOfPart(worksheetPart);
                var workbook = new Workbook();
                var fileVersion = 
                    new FileVersion { ApplicationName = 
                    "Microsoft Office Excel" };
                var worksheet = new Worksheet();
                int numCols = headerNames.Count;
                var columns = new Columns();
                for (int col = 0; col < numCols; col++)
                {
                    int width = headerNames[col].Length + 5;
                    Column c = new CustomColumn((UInt32)col + 1, 
                                  (UInt32)numCols + 1, width);
                    columns.Append(c);
                }
                worksheet.Append(columns);
                var sheets = new Sheets();
                var sheet = new Sheet { Name = sheetName, SheetId = 1, Id = relId };
                sheets.Append(sheet);
                workbook.Append(fileVersion);
                workbook.Append(sheets);
                SheetData sheetData = CreateSheetData(objects, headerNames);
                worksheet.Append(sheetData);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();
                myWorkbook.WorkbookPart.Workbook = workbook;
                myWorkbook.WorkbookPart.Workbook.Save();
                myWorkbook.Close();
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T">Object type to pass in</typeparam>
        /// <param name="objects">list of the object type</param>
        /// <param name="headerNames">Header names of the object</param>
        /// <returns></returns>
        private static SheetData CreateSheetData<T>(List<T> objects, 
                       List<string> headerNames)
        {
            var sheetData = new SheetData();
            if (objects != null)
            {
                //Get fields names of object
                List<string> fields = GetPropertyInfo<T>();
                //Get a list of A to Z
                var az = new List<Char>(Enumerable.Range('A', 'Z' - 
                                      'A' + 1).Select(i => (Char)i).ToArray());
                //A to E number of columns 
                List<Char> headers = az.GetRange(0, fields.Count);
                int numRows = objects.Count;
                int numCols = fields.Count;
                var header = new Row();
                int index = 1;
                header.RowIndex = (uint)index;
                for (int col = 0; col < numCols; col++)
                {
                    var c = new HeaderCell(headers[col].ToString(), 
                                           headerNames[col], index);
                    header.Append(c);
                }
                sheetData.Append(header);
                for (int i = 0; i < numRows; i++)
                {
                    index++;
                    var obj1 = objects[i];
                    var r = new Row { RowIndex = (uint)index };
                    for (int col = 0; col < numCols; col++)
                    {
                        string fieldName = fields[col];
                        PropertyInfo myf = obj1.GetType().GetProperty(fieldName);
                        if (myf != null)
                        {
                            object obj = myf.GetValue(obj1, null);
                            if (obj != null)
                            {
                                if (obj.GetType() == typeof(string))
                                {
                                    var c = new TextCell(headers[col].ToString(), 
                                                obj.ToString(), index);
                                    r.Append(c);
                                }
                                else if (obj.GetType() == typeof(bool))
                                {
                                    string value = 
                                      (bool)obj ? "Yes" : "No";
                                    var c = new TextCell(headers[col].ToString(), 
                                                         value, index);
                                    r.Append(c);
                                }
                                else if (obj.GetType() == typeof(DateTime))
                                {
                                    var c = new DateCell(headers[col].ToString(), 
                                               (DateTime)obj, index);
                                    r.Append(c);
                                }
                                else if (obj.GetType() == typeof(decimal) || 
                                         obj.GetType() == typeof(double))
                                {
                                    var c = new FormatedNumberCell(
                                                 headers[col].ToString(), 
                                                 obj.ToString(), index);
                                    r.Append(c);
                                }
                                else
                                {
                                    long value;
                                    if (long.TryParse(obj.ToString(), out value))
                                    {
                                        var c = new NumberCell(headers[col].ToString(), 
                                                    obj.ToString(), index);
                                        r.Append(c);
                                    }
                                    else
                                    {
                                        var c = new TextCell(headers[col].ToString(), 
                                                    obj.ToString(), index);
                                        r.Append(c);
                                    }
                                }
                            }
                        }
                    }
                    sheetData.Append(r);
                }
                index++;
                Row total = new Row();
                total.RowIndex = (uint)index;
                for (int col = 0; col < numCols; col++)
                {
                    var obj1 = objects[0];
                    string fieldName = fields[col];
                    PropertyInfo myf = obj1.GetType().GetProperty(fieldName);
                    if (myf != null)
                    {
                        object obj = myf.GetValue(obj1, null);
                        if (obj != null)
                        {
                            if (col == 0)
                            {
                                var c = new TextCell(headers[col].ToString(), 
                                                     "Total", index);
                                c.StyleIndex = 10;
                                total.Append(c);
                            }
                            else if (obj.GetType() == typeof(decimal) || 
                                     obj.GetType() == typeof(double))
                            {
                                string headerCol = headers[col].ToString();
                                string firstRow = headerCol + "2";
                                string lastRow = headerCol + (numRows + 1);
                                string formula = "=SUM(" + firstRow + " : " + lastRow + ")";
                                //Console.WriteLine(formula);
                                var c = new FomulaCell(headers[col].ToString(), 
                                                       formula, index);
                                c.StyleIndex = 9;
                                total.Append(c);
                            }
                            else
                            {
                                var c = new TextCell(headers[col].ToString(), 
                                                     string.Empty, index);
                                c.StyleIndex = 10;
                                total.Append(c);
                            }
                        }
                    }
                }
                sheetData.Append(total);
            }
            return sheetData;
        }
        private static List<string> GetPropertyInfo<T>()
        {
            PropertyInfo[] propertyInfos = typeof(T).GetProperties();
            // write property names
            return propertyInfos.Select(propertyInfo => propertyInfo.Name).ToList();
        }
    }
}

How to Use the Class

Below is an example of how to use the class and you should see the Excel 2007 spreadsheet. Create a list for the packages object and pass the object into the Excel helper class; create a method along with the file name with the full path, sheet name, and list of header names.

Compile your project, run the application, and be amazed. You've just created an Excel 2007 spreadsheet.

using System;
using System.Collections.Generic;

namespace CreateExcelSpreadsheet
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                List<package> packages =
                    new List<package>
                        { new Package { Company = "Coho Vineyard", Weight = 25.2, 
                              TrackingNumber = 89453312L, 
                              DateOrder = DateTime.Today, HasCompleted = false },
                          new Package { Company = "Lucerne Publishing", Weight = 18.7, 
                              TrackingNumber = 89112755L, 
                              DateOrder = DateTime.Today, HasCompleted = false },
                          new Package { Company = "Wingtip Toys", Weight = 6.0, 
                              TrackingNumber = 299456122L, 
                              DateOrder = DateTime.Today, HasCompleted = false },
                          new Package { Company = "Adventure Works", Weight = 33.8, 
                              TrackingNumber = 4665518773L, 
                              DateOrder =  DateTime.Today.AddDays(-4), 
                              HasCompleted = true },
                          new Package { Company = "Test Works", Weight = 35.8, 
                              TrackingNumber = 4665518774L, 
                              DateOrder =  DateTime.Today.AddDays(-2), 
                              HasCompleted = true },
                          new Package { Company = "Good Works", Weight = 48.8, 
                              TrackingNumber = 4665518775L, 
                              DateOrder =  DateTime.Today.AddDays(-1), HasCompleted = true },

                        };

                List<string> headerNames = 
                   new List<string> { "Company", 
                   "Weight", "Tracking Number", 
                   "Date Order", "Completed" };

                ExcelHelper excelFacade = new ExcelHelper();
                excelFacade.Create<package>(@"C:\temp\output1.xlsx", 
                            packages,"Packages", headerNames);

                Console.WriteLine("Completed");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.Read();
        }
    }

    public class Package
    {
        public string Company { get; set; }
        public double Weight { get; set; }
        public long TrackingNumber { get; set; }
        public DateTime DateOrder { get; set; }
        public bool HasCompleted { get; set; }
    }
}

History

  • 28 July 2010: Initial post.
  • 30 July 2010: Updated with explanation of the classes.

License

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

About the Author

No Biography provided

Comments and Discussions

 
QuestionWhere's the XML? PinmemberMember 822789019-Feb-14 15:06 
AnswerRe: Where's the XML? PinmemberEdenCerberus22hrs 2mins ago 
QuestionSupport for Multiline text in an Excel cell Pinmemberprichintha18-Feb-14 2:16 
QuestionA well-built plug and play excel document creation program! But slight problem with datetime format PinmemberMember 104006912-Dec-13 21:29 
QuestionMulti Sheets PinmemberMember 959588413-Nov-12 6:18 
QuestionFill definitions do not work Pinmembermelvers1-Nov-12 11:44 
QuestionExcel found unreadable content in 'output1.xlsx' PinmembersumB31-May-12 20:42 
AnswerRe: Excel found unreadable content in 'output1.xlsx' PinmemberChristoph Ammann3-Sep-13 22:30 
QuestionNice PinmemberJohnny Glenn11-Apr-12 21:47 
QuestionAttach excel to Outlook Pinmembergargvijay27-Mar-12 19:35 
QuestionDate column not working with office 2010 PinmemberMember 284774210-Feb-12 1:29 
AnswerRe: Date column not working with office 2010 PinmemberDanny Siew from Wellington, New Zealand12-Feb-12 10:03 
GeneralRe: Date column not working with office 2010 Pinmembersammyjny17-Nov-12 4:37 
GeneralMy vote of 1 PinmemberTamusRoyce21-Jun-11 11:52 
GeneralUnreadable content PinmemberChris Ferrie13-Apr-11 1:04 
GeneralRe: Unreadable content PinmemberDanny Siew from Wellington, New Zealand13-Apr-11 11:38 
GeneralRe: Unreadable content Pinmemberanjelone227-Apr-11 14:35 
GeneralRe: Unreadable content PinmemberMartin Brenden14-Jun-11 21:11 
GeneralRe: Unreadable content PinmemberClaudio Masieri22-Jun-11 6:10 
Questioncan this code support more than 26 columns ?? Pinmemberakantro21-Mar-11 12:16 
AnswerRe: can this code support more than 26 columns ?? PinmemberDanny Siew from Wellington, New Zealand21-Mar-11 14:33 
GeneralJust what I needed. Pinmemberjds274-Mar-11 5:42 
GeneralMy vote is 5 PinmemberRadim Köhler27-Feb-11 3:59 
GeneralDelete the columns PingroupSudhagar.Gg3-Jan-11 2:17 
GeneralApply grouping PinmemberThabet Tech29-Nov-10 6:54 

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 | Mobile
Web01 | 2.8.140721.1 | Last Updated 30 Jul 2010
Article Copyright 2010 by Danny Siew from Wellington, New Zealand
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid