Skip to content

The ExcelRange.Text property

The ExcelRange.Text method returns the cells 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 spread sheet application. To get the correct format in all cases you can add your own handling for this.

Using the ExcelPackageSettings.CultureSpecificBuildInNumberFormats

Some of the build 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"}
   });

ExcelPackageSettings.CultureSpecificBuildInNumberFormats.Add("de-DE",
  New Dictionary(Of Integer, String)() From {
    {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.

ExcelWorkbook.NumberFormatToTextHandler

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 call back function to our own TextHandler.

package.Workbook.NumberFormatToTextHandler = AddressOf TextHandler 'Set the new call back 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;
}

Public Function TextHandler(options As NumberFormatToTextArgs) As String
    Select Case options.NumberFormat.NumFmtId
        Case 15
            If TypeOf options.Value Is DateTime Then
                Dim dt = CType(options.Value, DateTime)
                Return dt.ToString("dd. mmm yy") 'Return your own formatted text.
            End If
    End Select
    Return options.Text
End Function

The NumberFormatToTextArgs class

This class returns information about the cell to be formatted.

Property Data type Description
Worksheet ExcelWorksheet The worksheet of the cell to be formatted
Row int The row of cell to be formatted
Column int The column of the cell to be formatted
NumberFormat ExcelNumberFormatXml The number format settings for the cell to be formatted
Value object The value to be formatted
Text string The text string formatted by EPPlus

See also