Table of Contents
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:
|
1 2 |
// Freeze the first row ws.View.FreezePanes(2, 1); |
Auto-Filter and Auto-Fit columns
|
1 2 3 4 5 6 |
// Apply the auto-filter to all the columns var allCells = ws.Cells[ws.Dimension.Address]; allCells.AutoFilter = true; // Auto-fit all the columns allCells.AutoFitColumns(); |
Select the header cells
NOTE: the headerCells object resulting from this selection will be used for the next tasks.
|
1 2 |
// Select only the header cells var headerCells = ws.Cells[1, 1, 1, ws.Dimension.Columns]; |
Set the Font as Bold-Italic-Underline
Here's how we can set the header cells text to bold, italic and underline:
|
1 2 3 4 |
// Set their text to bold, italic and underline. headerCells.Style.Font.Bold = true; headerCells.Style.Font.Italic = true; headerCells.Style.Font.Underline = true; |
Set the Foreground Color
Here's how to set the header cells foreground color (i.e., the text color) to White:
|
1 2 |
// Set their foreground color (text color) to White. headerCells.Style.Font.Color.SetColor(System.Drawing.Color.White); |
Set the Background Color
Here's how we can set the header cells background color to Dark Blue.
|
1 2 3 |
// Set their background color to DarkBlue. headerCells.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; headerCells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DarkBlue); |
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:
|
1 2 3 4 5 6 7 8 9 |
// Apply a striping effect to all rows by alternating white & gray background color for (var row = 1; row <= ws.Dimension.End.Row; row++) { if (row % 2 != 0) { ws.Row(row).Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; ws.Row(row).Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray); } } |
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!
One Comment 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!”