CODEWITHSUNDEEP
c#excelspreadsheetgear
Rakesh Devarasetti
Rakesh Devarasetti

Reputation: 1429

How to change Excel format when thousand and decimal seperator is changed

I have an option allowing user to select thousand seperator and decimal seperator in my windows application.How can i change the worksheet-> Cell's Number format based on selected seperators.

Upvotes: 0

Views: 1617

Answers (1)

Tim Andersen
Tim Andersen

Reputation: 3184

The only way to specify the thousands separator and decimal separator used by a workbook in SpreadsheetGear is indirectly--by passing in a particular CultureInfo object when you initially create/open the workbook via Factory.GetWorkbook(...) or Factory.GetWorkookSet(...). SpreadsheetGear uses the various options for that CultureInfo object, such as CultureInfo.NumberFormat.NumberGroupSeparator / NumberDecimalSeparator, to format cells in the workbook.

Problem is, you cannot tweak a workbook's CultureInfo object's individual properties to use different formatting options nor can you "hot swap" one overall CultureInfo object for another while a workbook is opened.

So the only way to change things like the thousands and decimal separator characters is to:

  1. Close any currently-open workbooks using the "old" separator characters.
  2. Re-open or re-create these workbooks, specifying some other CultureInfo object into Factory.GetWorkbook(...) or Factory.GetWorkookSet(...) that uses the desired thousand or decimal separator characters.

As a result, you may need to alter your application to specify a particular region instead of individual separator characters, i.e., your user provides a region which you can use to get a particular CultureInfo object via the CultureInfo.GetCultureInfo(name) method, where name might be "en-US", "en-GB", "de-DE", "hi-IN", etc. If it helps, here is one page that lists a number of predefined CultureInfo codes.

Upvotes: 2

Related Questions