ASP.NET – How to format and style a Excel XLSX Worksheet with EPPlus A brief tutorial explaining how to apply styles and format rules to a Worksheet created with EPPlus: bold, colors, auto-filter, auto-fit and more!

ASP.NET - Generate MS Excel files programmatically with EPPlus

We already talked about EPPlus in this post, where we described it as one of the best open-source libraries to programmatically read, create and edit MS Excel files in XLS and XLSX format: a great alternative to the Microsoft.Office.Interop.Excel official package, at least for web applications.

In this follow-up post we’ll show how to apply styles and format rules to a Worksheet created with EPPlus. To be more specific, we’ll deal with the following:

  • Foreground and background color for cells and rows, for example to create the typical striped effect (white/gray alternate rows) to increase readability.
  • Freeze panes, for example the top row.
  • Auto-filter the worksheet columns.
  • Auto-fit the columns to have their contents fully visible, without having to manually expand them.
  • Bold, Italic, Underline and so on.

Are you ready? Then let’s go straight to the source code!

For the sake of simplicity, let’s suppose that we already wrote the following lines of code, thus ending up with a valid ExcelWorksheet object (ws). Here are some basic hints explaining how we can style and format it.

Freeze row(s)

Here’s how we can freeze the first row:

Auto-Filter and Auto-Fit columns

Select the header cells

NOTE: the headerCells object resulting from this selection will be used for the next tasks.

Set the Font as Bold-Italic-Underline

Here’s how we can set the header cells text to bold, italic and underline:

Set the Foreground Color

Here’s how to set the header cells foreground color (i.e., the text color) to White:

Set the Background Color

Here’s how we can set the header cells background color to Dark Blue.

Notice that we’ll have to select a PatternType first, otherwise EPPlus will throw an error because it needs to know what kind of pattern he would apply the background color to.

Color Stripes

Here’s how we can use the “Set Background Color” technique explained above to apply a striping effect to our worksheet:

Conclusion

That’s it for the time being: feel free to use the above code to better style your MS Excel document using EPPlus!

UPDATE: if you want to know how you can programmatically set the proper format for numeric, date-time and currency data types on MS Excel cells and/ columns with EPPlus, read this other post!

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

0 Comments on “ASP.NET – How to format and style a Excel XLSX Worksheet with EPPlus A brief tutorial explaining how to apply styles and format rules to a Worksheet created with EPPlus: bold, colors, auto-filter, auto-fit and more!

  1. Pingback: ASP.NET How to set the proper Data Type of MS Excel cells with EPPlus

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.