Preventing Undesired Format Conversion in CellValueFormat

Environment

Version	Product	Author
2025.4.1104	RadSpreadProcessing	Desislava Yordanova

Description

When using the CellValueFormat in RadSpreadProcessing, format strings may experience an undesired exchange of symbols, such as , to . and . to ,.

    CellValueFormat textFormat = new CellValueFormat("[$-de-DE,101]#.##0,00;-#.##0,00"); 
    CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
    cellSelectionA1.SetValue(1234.56789); 
    cellSelectionA1.SetFormat(textFormat);
    CellSelection cellSelectionA1 = worksheet.Cells[new CellIndex(0, 0)]; 
    CellValueFormat cellSelectioA1Format = cellSelectionA1.GetFormat().Value;

The returned Value is:

[$-de-DE,101]#,##0.00;-#,##0.00

The , and . are exchanged. For better visualization:

Input:

[$-de-DE,101]#.##0,00;-#.##0,00

Output:

[$-de-DE,101]#,##0.00;-#,##0.00

You can see that 0,00 has been changed to 0.00 and #.## has been changed to #,##.

This happens due to culture settings. For instance, using the German culture (de-DE) where the decimal separator is a comma and the group separator is a period, versus the English culture (en-US) where these separators are reversed, can lead to a different output format.

This knowledge base article also answers the following questions:

• How to prevent undesired format conversion in Telerik Spreadsheet?
• How to set custom culture settings for CellValueFormat?
• How to retain original format string in CellValueFormat?

Solution

To prevent undesired format symbol exchanges, explicitly set the desired culture in your application using the SpreadsheetCultureHelper. Follow these steps:

1. Import the required namespaces:

   using System.Globalization;
   using Telerik.Windows.Documents.Spreadsheet.Formatting;
   

2. Set the desired culture by creating a new SpreadsheetCultureHelper instance:

   FormatHelper.CultureHelper = new SpreadsheetCultureHelper(new CultureInfo("de-DE"));
   

   Replace "de-DE" with the appropriate culture code for your scenario.

3. Apply the culture settings before working with the CellValueFormat and related operations.

This approach ensures consistent handling of format strings regardless of the user's default culture settings.

See Also

• Number Formats in Telerik Document Processing
• Setting the Culture for Telerik Spreadsheet