The ExcelRange.Text property

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

The ExcelRange.Text method returns the cell's formatted value as a string. In most cases EPPlus converts the Value property to the same Text value as your spreadsheet application, but some number formats are converted to a localized format that differs between .NET and the spreadsheet application. To get the correct format in all cases you can add your own handling for this.

Some of the built-in number formats are formatted differently depending on the regional setting. By default EPPlus uses the standard OOXML number formats as specified in ECMA-376, section 18.8.30. To support regional formats you can add your own formats like this:

ExcelPackageSettings.CultureSpecificBuildInNumberFormats.Add("de-DE",
  new Dictionary<int, string>()
  {
    {14, "dd.mm.yyyy"}, 
    {15, "dd. mmm yy"}, 
    {16, "dd. mmm"},
    {17, "mmm yy"}, 
    {18, "hh:mm AM/PM" }, 
    {22, "dd.mm.yyyy hh:mm"},
    {39, "#,##0.00;-#,##0.00"}, 
    {47, "mm:ss,f"}
   });

To override EPPlus behaviour for other number formats you can also use the ExcelWorkbook.NumberFormatToTextHandler callback function.

Using the ExcelWorkbook.NumberFormatToTextHandler property you can add a callback function used to format the Text property yourself.

package.Workbook.NumberFormatToTextHandler = TextHandler; // Set the new callback function to our own TextHandler.

The signature for this method looks like this: string TextHandler(NumberFormatToTextArgs options). Here is a simple example:

public string TextHandler(NumberFormatToTextArgs options)
{
    switch (options.NumberFormat.NumFmtId)
    {
        case 15:
            if (options.Value is DateTime dt)
            {
                return dt.ToString("dd. mmm yy"); // Return your own formatted text.
            }
            break;
    }
    return options.Text;
}

This class returns information about the cell to be formatted.

• Formatting and Styling (Contains Number Format syntax)