BorlandTalk.com

nisbus · Guest

Hi,

I'm trying to export data from a TStringGrid to an Excel Worksheet.
I get the data to Excel but it's all formatted as text but most of the data
is floating point numbers.

I've tried to change the format of the Excel cells like this:

Format := '#.##0,00';
Worksheet.Range['A1',Worksheet.Cells.Item[R+2,C+2]].NumberFormat := Format;

This seems to work for the cell format because when I look at the format in
Excel it is specified as number.
The problem is that the data from my application uses '.' as a decimal
seperator but excel seems to use ','.
I've tried to replace all occurences of '.' with ',' but it still isn't
perceived as a number by Excel.
Only when I edit the cell itself and delete the comma and put it in again
does the number format kick in.

Not the first person to battle the locale settings in windows Smile

How come there isn't an easy way to do this since a lot of people must be
developing for more than one locale?

thank you,
nisbus

nisbus · Guest

At first I tried using the String representation for all of the data from
the TStringGrid cells but then I tried using the double itself.
This is how I'm doing it now.

GetLocaleFormatSettings(0,fs);
fs.DecimalSeparator := '.';
fs.ThousandSeparator := #0;
GetLocaleFormatSettings(0,ELEAfs);
repeat
for J := 0 to (C - 1) do
begin
InsertValue := StrGrdData.Cells[J,I];
try
TestValue := StrToFloat(InsertValue,fs);
TabGrid[I,J] := FormatFloat('##,##0.##',TestValue,ELEAfs);
except
TabGrid[I,J] := InsertValue;
end;
end;
Inc(I,1);
until
I > (R - 1);

This gives me double values inside the OLEVariant (TabGrid) but Excel
doesn't seem to recognize it as such.
When I open the Excel document all of the number cells have this green
triangle in the upper-left corner, when I select the cell I get a small
yellow diamond with ! in it and a menu that offers the 'convert to number'
which fixes the problem?

thank you,
nisbus

"Chris Cheney" <cjc1@nospam%ucs.cam.ac.uk%no%spam%please> wrote

nisbus · Guest

Thanks for the tip.

An oversight on my behalf. But the problem perstists.

The floating point number I'm gettin is in a text format from an XML.
The locale settings on the server I'm receiving it from are different from
mine so it uses '.' as decimalseparator.
My system uses comma.

This is an example:

The number I'm getting is 1.6

I convert it to float:
Value := StrToFloat(number);

Now when I look at it in delphi (while running with breakpoints) it looks
like this: 1,6

Then I add Value to the Matrix (TabGrid)
TabGrid[I,J] := Value;

then I open Excel and the cell contains the value: 1.6 and can therefore not
be understood as a number by Excel.

I tried various ways with TFormatSettings and I managed to get the value as
a text with the right decimalseparator but Excel always has those darn green
triangles in the cell.
I've worked my way around these locale problems within my application but I
can't get Excel to get it.

Anyone know a way to access the 'Convert to number' selection mentioned in
my previous post?

Thanks,
nisbus

"Chris Cheney" <XXChris.CheneyXX (AT) tesco (DOT) net> wrote

George Birbilis · Guest