-
Notifications
You must be signed in to change notification settings - Fork 275
Formatting and styling
Cell styling is accessed by the Style property of a range. You can easily style your spreadsheets by using the indexer of the Cells property decribed above. Lets say you want to set the numberformat of a range...
worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";
...or you want to set the header row to bold and dark background and white font...
using (var range = worksheet.Cells[1, 1, 5, 1]) //Address "A1:A5"
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
range.Style.Font.Color.SetColor(Color.White);
}
To see the some real code showing cell access and styling, look into the sample project, for example Sample 1.1-C# or Sample 1.1-VB
You can also create your own named styles, using the Workbook.Style.CreateNamedStyle method and the ExcelRange.StyleName property. As shown in Sample 3.4-C# or Sample 3.4-VB...
//Add a HyperLink to the statistics sheet.
var namedStyle = pck.Workbook.Styles.CreateNamedStyle("HyperLink");
namedStyle.Style.Font.UnderLine = true;
namedStyle.Style.Font.Color.SetColor(Color.Blue);
ws.Cells["K13"].Hyperlink = new ExcelHyperLink("Statistics!A1", "Statistics");
ws.Cells["K13"].StyleName = "HyperLink";
Number formats in EPPlus use the ooxml number format style. If you are unsure what format to use, please try this to get a specifict format:
- Open a new Excel document and format a cell to the number format you want to apply.
- Save the workbook.
From here you can either open the workbook using EPPlus to see the number format for the cell or exctract the package as described here:
- Rename the workbook to *.zip
- Extract the zip package.
- Open the file xl\styles.xml in the folder where you extracted the package.
- Number formats can be found under the numFmts element in the xml file.
OOXml has several build in number formats:
Id | Format |
---|---|
0 | General |
1 | 0 |
2 | 0.00 |
3 | #,##0 |
4 | #,##0.00 |
9 | 0% |
10 | 0.00% |
11 | 0.00E+00 |
12 | # ?/? |
13 | # ??/?? |
14 | mm-dd-yy |
15 | d-mmm-yy |
16 | d-mmm |
17 | mmm-yy |
18 | h:mm AM/PM |
19 | h:mm:ss AM/PM |
20 | h:mm |
21 | h:mm:ss |
22 | m/d/yy h:mm |
37 | #,##0 ;(#,##0) |
38 | #,##0 ;[Red](#,##0) |
39 | #,##0.00;(#,##0.00) |
40 | #,##0.00;[Red](#,##0.00) |
45 | mm:ss |
46 | [h]:mm:ss |
47 | mmss.0 |
48 | ##0.0E+0 |
49 | @ |
Always use the number format. EPPlus will translate formats to the corresponding number format id.
EPPlus Software AB - https://epplussoftware.com
- Getting Started
- The Sample Project
- EPPlus and Docker
- EPPlus and Blazor
- EPPlus and Linux
- Security
- Configuration
- Releases/versioning
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles