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

20. November 2015 Tutorial 24

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


24 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?

Leave a Reply

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