Using C# and Open XML SDK 2.0 for Microsoft Office to Create an Excel 2007 Document
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 selectDocumentFormat.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 intoProgram.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
StyleIndexin 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)
