How to create an Excel file in .NET using OpenXML – Part 3: Add Stylesheet to the Spreadsheet

20. November 2015 Tutorial 39

Overview

This is the 3rd part of OpenXML Spreadsheet Tutorial Series:

In this tutorial we are going work with our sample in the Part 2, and we are going to add style and do some customization on that.

Step 1: Add Stylesheet

Stylesheet class is used to add custom style to a spreadsheet. Stylesheet can accept different elements such as, Borders, Colors, Fills, and etc. as its child elements which specifies the look of a spreadsheet. The CellFormats stores the combination of different Styles which later can be applied on a cell.

In order to add a Stylesheet to our workbook, we need to add a WorkbookStylePart to the Workbook part and initialize its StyleSheet property.

We are going to make our header bold and white with dark background as well as adding border to all other cells.

Create a new method GenerateStylesheet() which returns a StyleSheet object to the Report class.

private Stylesheet GenerateStylesheet()
{
    Stylesheet styleSheet = null;
    
    return styleSheet;
}

Fonts

Fonts can have one or more Font children which each have different properties like FontSize, Bold, Color, and etc.

Add the following block of code in GenerateStyleSheet method:

Fonts fonts = new Fonts(
    new Font( // Index 0 - default
        new FontSize() { Val = 10 }

    ),
    new Font ( // Index 1 - header
        new FontSize() { Val = 10 },
        new Bold(),
        new Color() { Rgb = "FFFFFF" }

    ));

Take note that we add two Font children to the Fonts object. The first one is the default font use by all cells, and the second one is specific to header.

Fills

Fills can have one or more Fill children which you can set its ForegroundColor.

Fills fills = new Fills(
         new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default
         new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default
         new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "66666666" } })
         { PatternType = PatternValues.Solid }) // Index 2 - header
     );

Excel needs to have the first two as the default. The 3rd one is the style we want to have for our header cells; a gray sold background.

Borders

Borders can have one or more Border children which each defines how the border should look like:

Borders borders = new Borders(
        new Border(), // index 0 default
        new Border( // index 1 black border
            new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
            new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
            new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
            new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
            new DiagonalBorder())
    );

The 1st is the default border and the 2nd one is our customized border.

CellFormats

Now that we have defied our custom formats elements we can create CellFormats which has one or many CellFormat children. Each CellFormat gets the index of Font, Border, Fill, or etc. which it will associate with:

CellFormats cellFormats = new CellFormats(
        new CellFormat(), // default
        new CellFormat { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }, // body
        new CellFormat { FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true } // header
    );

Finally we initialize the Stylesheet object:

styleSheet = new Stylesheet(fonts, fills, borders, cellFormats);

Complete Stylesheet code:

private Stylesheet GenerateStylesheet()
{
    Stylesheet styleSheet = null;

    Fonts fonts = new Fonts(
        new Font( // Index 0 - default
            new FontSize() { Val = 10 }

        ),
        new Font( // Index 1 - header
            new FontSize() { Val = 10 },
            new Bold(),
            new Color() { Rgb = "FFFFFF" }

        ));

    Fills fills = new Fills(
            new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default
            new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default
            new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "66666666" } })
            { PatternType = PatternValues.Solid }) // Index 2 - header
        );

    Borders borders = new Borders(
            new Border(), // index 0 default
            new Border( // index 1 black border
                new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                new DiagonalBorder())
        );

    CellFormats cellFormats = new CellFormats(
            new CellFormat(), // default
            new CellFormat { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }, // body
            new CellFormat { FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true } // header
        );

    styleSheet = new Stylesheet(fonts, fills, borders, cellFormats);

    return styleSheet;
}

Step 2: Add the style to the workbook

Add a WorkbookStylePart to the WorkbookPart and initialize its Stylesheet:

// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart<WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();

Step 3: Add style to Cells

Now that we have our stylesheet link to workbook we can specify what style each cell need to follow.

Modify the content of ConstructCell method in Report.cs to get the style index:

private Cell ConstructCell(string value, CellValues dataType, uint styleIndex = 0)
{
    return new Cell()
    {
        CellValue = new CellValue(value),
        DataType = new EnumValue<CellValues>(dataType),
        StyleIndex = styleIndex
    };
}

 

As you can see, Cell have a property called StyleIndex which gets the index of the style we want to apply to this cell. The index here refers to index of CellFormats.

Modifies the construction of the header and cell in CreateExcelDoc and pass the desirable style index:

// Constructing header
Row row = new Row();

row.Append(
    ConstructCell("Id", CellValues.String, 2),
    ConstructCell("Name", CellValues.String, 2),
    ConstructCell("Birth Date", CellValues.String, 2),
    ConstructCell("Salary", CellValues.String, 2));

// Insert the header row to the Sheet Data
sheetData.AppendChild(row);

// Inserting each employee
foreach (var employee in employees)
{
    row = new Row();

    row.Append(
        ConstructCell(employee.Id.ToString(), CellValues.Number, 1),
        ConstructCell(employee.Name, CellValues.String, 1),
        ConstructCell(employee.DOB.ToString("yyyy/MM/dd"), CellValues.String, 1),
        ConstructCell(employee.Salary.ToString(), CellValues.Number, 1));

    sheetData.AppendChild(row);
}

Step 4: Add custom width for columns

Adding custom width to specific columns is very easy. First we need to create a Columns object and then add one or more Column as its children which each will define the custom width of a range of columns in the spreadsheet. You can explore the properties of the column to specify more customization on columns. Here we are only interested in specifying the width of the columns.

// Setting up columns
Columns columns = new Columns(
        new Column // Id column
        {
            Min = 1,
            Max = 1,
            Width = 4,
            CustomWidth = true
        },
        new Column // Name and Birthday columns
        {
            Min = 2,
            Max = 3,
            Width = 15,
            CustomWidth = true
        },
        new Column // Salary column
        {
            Min = 4,
            Max = 4,
            Width = 8,
            CustomWidth = true
        });

Lastly we need to append the columns to work worksheet.

worksheetPart.Worksheet.AppendChild(columns);

Result

Run the application and check the generated Excel

Complete Code

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace OpenXMLSample
{
    public class Report
    {
        public void CreateExcelDoc(string fileName)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();
 
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet();
 
                // Adding style
                WorkbookStylesPart stylePart = workbookPart.AddNewPart<WorkbookStylesPart>();
                stylePart.Stylesheet = GenerateStylesheet();
                stylePart.Stylesheet.Save();
 
                // Setting up columns
                Columns columns = new Columns(
                        new Column // Id column
                        {
                            Min = 1,
                            Max = 1,
                            Width = 4,
                            CustomWidth = true
                        },
                        new Column // Name and Birthday columns
                        {
                            Min = 2,
                            Max = 3,
                            Width = 15,
                            CustomWidth = true
                        },
                        new Column // Salary column
                        {
                            Min = 4,
                            Max = 4,
                            Width = 8,
                            CustomWidth = true
                        });
 
                worksheetPart.Worksheet.AppendChild(columns);
 
                Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
 
                Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Employees" };
 
                sheets.Append(sheet);
 
                workbookPart.Workbook.Save();
 
                List<Employee> employees = Employees.EmployeesList;
 
                SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
 
                // Constructing header
                Row row = new Row();
 
                row.Append(
                    ConstructCell("Id", CellValues.String, 2),
                    ConstructCell("Name", CellValues.String, 2),
                    ConstructCell("Birth Date", CellValues.String, 2),
                    ConstructCell("Salary", CellValues.String, 2));
 
                // Insert the header row to the Sheet Data
                sheetData.AppendChild(row);
 
                // Inserting each employee
                foreach (var employee in employees)
                {
                    row = new Row();
 
                    row.Append(
                        ConstructCell(employee.Id.ToString(), CellValues.Number, 1),
                        ConstructCell(employee.Name, CellValues.String, 1),
                        ConstructCell(employee.DOB.ToString("yyyy/MM/dd"), CellValues.String, 1),
                        ConstructCell(employee.Salary.ToString(), CellValues.Number, 1));
 
                    sheetData.AppendChild(row);
                }
 
                worksheetPart.Worksheet.Save();
            }
        }
 
        private Cell ConstructCell(string value, CellValues dataType, uint styleIndex = 0)
        {
            return new Cell()
            {
                CellValue = new CellValue(value),
                DataType = new EnumValue<CellValues>(dataType),
                StyleIndex = styleIndex
            };
        }
 
        private Stylesheet GenerateStylesheet()
        {
            Stylesheet styleSheet = null;
 
            Fonts fonts = new Fonts(
                new Font( // Index 0 - default
                    new FontSize() { Val = 10 }
 
                ),
                new Font( // Index 1 - header
                    new FontSize() { Val = 10 },
                    new Bold(),
                    new Color() { Rgb = "FFFFFF" }
 
                ));
 
            Fills fills = new Fills(
                    new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default
                    new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default
                    new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "66666666" } })
                    { PatternType = PatternValues.Solid }) // Index 2 - header
                );
 
            Borders borders = new Borders(
                    new Border(), // index 0 default
                    new Border( // index 1 black border
                        new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new DiagonalBorder())
                );
 
            CellFormats cellFormats = new CellFormats(
                    new CellFormat(), // default
                    new CellFormat { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }, // body
                    new CellFormat { FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true } // header
                );
 
            styleSheet = new Stylesheet(fonts, fills, borders, cellFormats);
 
            return styleSheet;
        }
    }
}

Download

Download the sample code


39 thoughts on “How to create an Excel file in .NET using OpenXML – Part 3: Add Stylesheet to the Spreadsheet”

  • 1
    Anonymous on May 21, 2016 Reply

    This is very helpful, thank you very much!

    • 2
      Behnam on May 21, 2016 Reply

      You are welcome, I am glad it helps.

  • 3
    G on August 5, 2016 Reply

    Is it possible to autowidth columns to fit the max length of a cell ?

  • 5
    Duong Thai on September 7, 2016 Reply

    The great tutorial.
    It would be nice if you write an example code about Condition Formatting >> Color Scale.
    Thank you!

  • 6
    Dev on October 21, 2016 Reply

    Hey Behnam, Really helpful post. Wanna ask you one thing: if I want to add text in cells having 50-60 different fonts and colours , Then i have to mention each and every font and colour in my workbook stylesheet ??

    • 7
      Behnam on October 30, 2016 Reply

      Thanks, I am happy it is useful. About your question, I haven’t done that and I am not sure. This was the way I have found. I am not sure if there is any possibilities on define classes, as in CSS, and apply to multiple cells. I will search and will let you know if I came across any solution.

  • 8
    Renee on January 25, 2017 Reply

    Thanks! Extremely Helpful!

  • 9
    JFco. Ibarra on June 5, 2017 Reply

    Great article
    How could you use the NumberingFormats to format the salary? We would have to do another style?

    Thank you for article

  • 11
    Andreas Pircher on July 13, 2017 Reply

    Many Thanks. I like this tutorial very much.

  • 12
    Jorys on August 24, 2017 Reply

    Helped me a lot, thank you!

    • 13
      Behnam on August 27, 2017 Reply

      You are welcome. I am happy it helped.

  • 14
    ehrn.A on November 5, 2017 Reply

    Helped me a lot to understand! Thanks! nifty piece of work

    • 15
      Behnam on November 6, 2017 Reply

      You are welcome! Happy it helped you!

  • 16
    kasun on December 20, 2017 Reply

    hey this is a great tutorial, can u suggest a way to set thousand separator in salary column in stylesheet?

  • 17
    Roberto Milone on January 13, 2018 Reply

    Thank you!!

  • 18
    Aaron on January 27, 2018 Reply

    Can you show how to format a column as currency? I just can’t get it to work without a stylesheet error.

  • 19
    Karen Payne on February 25, 2018 Reply

    Well done!!!

    • 20
      Behnam on April 6, 2018 Reply

      Thanks!

  • 21
    Nimisha on April 12, 2018 Reply

    Hi Behnam,

    Thanks for your post. It really helped me to understand the OpenXML better.
    I have a requirement. I need to find a particular row of an excel sheet using one column value and edit the other columns of that row.[Similar to editing an SQL Table item using primary key]. Can you please suggest the best approach? My excel can have 500 + rows.

    Thank you

    Regards
    Nimisha

  • 22
    Brian Bergh on June 2, 2018 Reply

    This was very useful, thank you very much. By following you guide, i finally got my code to work – so many other examples out there, that just doesn’t work (missing something) – This is a complete and working example, thank you 🙂

    • 23
      Behnam on June 4, 2018 Reply

      Welcome, happy it helps 🙂

  • 24
    Jeremy Vincent on June 21, 2018 Reply

    Hi Benham
    Thank you very much for this post and it was really helpful. I have an excel template with 4 tabs with all necessary headings. When I try to add data into it, it start from first cell, not after the heading rows. Can you please guide me how to achieve this?

  • 25
    Richard Evans on October 4, 2018 Reply

    For some reason only the borders are working when I use style index 2. That’s frustrating, because I really need the headings to be bold. Any suggestions?

  • 26
    Ekvinder Singh on December 15, 2018 Reply

    My sincere thanks to the detailed tutorials.
    I was stuck struggling with OpenXML doing column width, different font & fill for header and rest of the content, since 5 days.
    And in few hours (using this tutorial) I have completed the work.
    Though only alignment part remaining. 🙂
    KUDOS to the tutorial & the contributor.

    • 27
      Behnam on April 9, 2019 Reply

      Thanks 🙂 happy it is helpful

  • 28
    David I. McIntosh on April 9, 2019 Reply

    I spent hours trying to determine why my custom Fills were not working. I eventually realized, through some reverse engineering, that the Fills array, unlike the other array elements, requires the _two_ default fills in positions [0] and [1]. This is not made clear in any of the examples I looked at, _except_ yours (which I only found after figuring it, sadly). Kudos to you for attention to detail that is lacking in all other examples I looked at. (“The devil is in the details”, but I see you know that by your choice of colours in your Fills example… 😉

    • 29
      Behnam on April 9, 2019 Reply

      Welcome. Happy it is helpful 🙂

  • 30
    Robert on June 25, 2019 Reply

    I created the “columns ” as follows and could get the column definitions in the output.

    var columns = new Columns();
    var column1 = new Column { Min = 1, Max = 1, Width = 4, CustomWidth = true };
    var column2 = new Column { Min = 1, Max = 1, Width = 20, CustomWidth = true };
    var column3 = new Column { Min = 1, Max = 1, Width = 30, CustomWidth = true };
    var column4 = new Column { Min = 1, Max = 1, Width = 10, CustomWidth = true };

    columns.Append(column1);
    columns.Append(column2);
    columns.Append(column3);
    columns.Append(column4);

    worksheetPart.Worksheet.AppendChild(columns);

    Everthing else ist just the same as yours.
    Any suggestions?

  • 31
    Deepali on May 24, 2020 Reply

    very nice article to start with. thanks 🙂

    • 32
      Behnam on June 21, 2020 Reply

      Thanks

  • 33
    paolo on May 26, 2020 Reply

    Thank you, may be the best tutorial for openXML in the web!

    • 34
      Behnam on June 21, 2020 Reply

      You are welcome. Happy it’s helpful.

  • 35
    Bhumika on September 21, 2020 Reply

    Really helped me. Simple and precise explanation. Thanks a lot!

  • 36
    Abdul Kalam on February 11, 2021 Reply

    Hi benham,

    Your post was quite helpful to start working with OpenXML but i am having issue while using Inlinstring type.

    Issues:
    Added inlinestring data (i.e. Bold, Italic etc…) its popping up an error message when we open Excel sheet. Its bit annoying for the user although purpose is achieved to provide formatted text in a cell in Excel file.

    Error message:
    We found a problem with some content in test.xlsx file. Do you want us to try to recover as much as we can? If you trust the source of this workbook. Click yes

    And we click on Yes again information message like recovered file:
    Excel was able to open the file by repairing or removing the unreadable content
    Repaired Records: String properties from /xl/worksheets/sheet1.xml part

    Need your expertise to solve this issue.

    Thanks in advance

  • 37
    IronRod on March 9, 2021 Reply

    Thanks much for this.

  • 38
    Michael on October 16, 2021 Reply

    Verry Nice TUTORIAL .
    Thanks Behnam, you save my day 🙂

  • 39
    Muhammad Haris on December 9, 2021 Reply

    Hi, extremely helpful article.
    Is it possible to wrap text in the entire sheet?
    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.