Formatting and styling

This page has moved. The current version of this page is at https://epplussoftware.com/docs/styling/formatting-and-styling/. The EPPlus documentation is no longer updated in this wiki; please use the EPPlus guides and update your bookmarks.

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 described above. Let's 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 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.Styles.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";

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 specific 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 extract 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 built-in number formats:

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

• The Text Property (Regional Number Formats)
• Styling Sample-C# or Styling Sample-VB