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

20. November 2015 Tutorial 15

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.

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:

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.

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:

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:

Finally we initialize the Stylesheet object:

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

Complete Stylesheet code:

Step 2: Add the style to the workbook

Add a WorkbookStylePart to the WorkbookPart and initialize its Stylesheet:

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:

 

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:

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.

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

Download

Download the sample code


15 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!

Leave a Reply

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