Skip to content

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);
}

Using 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)
End Using

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";

'Add a HyperLink to the statistics sheet. 
Dim 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

Regional/culture-specific number formats

Looking for Regional/Culture-Specific Numberformats? Look here!

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🇲🇲ss AM/PM
20 h:mm
21 h🇲🇲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]🇲🇲ss
47 mmss.0
48 ##0.0E+0
49 @

Always use the number format. EPPlus will translate formats to the corresponding number format id.

See also