I have just started with Flexcel and maybe this question has a simple answer.
I'm using German settings under Windows and wanted to write real values into cells.
The problem is, that real values are written with "." and not with ","?
What can I do to change according to user's settings?
Hi,
FlexCel should automatically pick the user settings of the machine where it is running. You can change the default by using the class TFlexCelFormatSettings, but by default we use the settings in the machine. Just for completeness, there are the following possibilities, in increasing order of priority:
1)You don't do anything: We use the locale in the machine. (The global FormatSettings variable in delphi).
2)You call something like:
var
fmt: TFormatSettings;
begin
fmt := TFormatSettings.Create('de-DE');
TFlexCelFormatSettings.SetGlobalFormat('de-DE', fmt);
This will set a global locale for FlexCel which can be different from the locale of your app, but it normaly has little use in GUI apps: You are likely to want the same locale everywhere. It might be useful in a webserver if you want to create different files for users accessing the webpage in different locales.
3)Finally, the highest priority is to set the thread locale with TFlexCelFormatSettings.SetThreadLocale and/or TFlexCelFormatSettings.PushThreadLocale/PopThreadLocale. This allows you to have different threads with different locales: again this might be useful in a web server.
Now, all of this being said, I believe we might have a different problem here. The thing is: if you write a number into an Excel cell, it will show with a "." decimal separator if you open that file in an English Excel, and with a "," if you open the exact same file in a German Excel. What is used for decimal separator in xls or xlsx files is up to the version of Excel and locale of windows opening the file, and you can't change it from the file itself. So a german user will see "," and and english user will see "." but the file is exactly the same. Also the english user will see english formulas like "=SUM(A1:A2)" and the german user will see german formulas like "=SUMME(A1:A2)" (what happens under the hood for xls files is that in the file we save the id of the formula, for example "6" for sum, and then the localized Excel which opens the file will show "sum" or "summe" for the "6", depending on the locale of Excel. For xlsx all formulas are always saved in English, and Excel will translate them in order to show them to you)
FlexCelFormatSettings and the locales are useful for when exporting to pdf or html, or CSV. For xls/x it really doesn't matter and all that matters is how the Excel which is opening the file is configured.
If you are seeing "." everywhere, one thing that might be happening is that you are entering a string and not a number. If you do:
xls.SetCellValue(1, 1, '1.2');
This will enter the string "1.2" into cell a1. This is a string, not different from the string "potato", and won't change with the user locale: it will always be a string with the characters "1", "." and "2"
If you do
xls.SetCellValue(1, 1, 1.2);
This will enter the number 1.2 and it will show different in different versions of Excel.
The other thing that might be happening is that you aren't setting the format of the cell well. Format strings are always in English for FlexCel, so you should always enter for example "0.00" as format string, and never "0,00" when setting the format in FlexCel. The easiest way of course to find out the format is to set it in Excel, and then open the file with APIMate to know the format you need to write from FlexCel. That format will always be in english, but it will show different depending in the Excel version you have installed.
Sorry, It was my fault.
I have used a text with "." and copied into excel cells. So it will be shown as text. Therefore I have converted this into a real value and copied later to cell. Than it works.