How do you create a format string to make negative numbers show as red that works in all languages?
#,##00;[Color3]-#,##00 : works in english
However in French locale - it becomes #,##00;[Couleur3]-#,##00 and does not work.
Is there any standard way to achieve this without havign to translate colors into 35 different languages?
thanks!
I am not sure if this can be possible via custom formatting string, if you know let us know with sample Excel file (in which you have specified some custom string and it works in all versions of Excel for different languages).
One thing that you may try is use setNumber() attribute instead of setCustom(). Doing this, it will change the numbers format accordingly in different locales/regional settings. For example, you may try the line of code:
style.setNumber(38); //–> this basically refers to (Accounting format), i.e., “#,##0_);[Red](#,##0)”
Also, see the API reference page where you may choose any other suitable format (number) for setNumber() attribute.
@redred,
And for the formatting with color, we think you can just set the same custom string with the one corresponding to en_US locale by Style.setCustom(), then the formatting will work for all locales in ms excel and may be adjusted automatically by ms excel according to different locales.