Help with sending number to Excel 2007 from Delphi 2010 as a string.

Question

I'm sending a number to Excel 2007 as a string (Cell.Value := '2,5') using late binding. The actual code is more like:

var CellVal: OLEVariant;
...
CellVal := FloatToStr(2.5);  // Regionally formatted.
Cell.Value := CellVal;

On my Excel 97 version, this value will be formatted as "General" by default and will be seen as a number. A customer with Excel 2007 ends up with the cell formatted as "Standard" and Excel appears to see it as a string (it's not right aligned.) Note that I am using the regional settings to format the number and that Excel appears to be using the default regional settings as well.

If the customer just types 2,5 into a cell it accepts it as a number and if he does a copy of the string '2,5' from the clipboard into a cell, it also gets accepted as a number. Does anyone know why the string value sent though the automation interface to Excel ends up as a non-number?

Thanks for any suggestions! Edited to specify the regional decimal separator for the customer is ','.

Answer 1

Can't explain why the behaviour is different but it would appear to be down to how Excel 2007 interprets the incoming value.

How about setting the format of the cell in code?

Worksheets("Sheet1").Range("A17").NumberFormat = "General"

Answer 2

Probably because you give it a string. Have you tried passing it the float value directly?

Answer 3

Since you cannot format comments:

I just did a little test and Excel doesn't want a regional formatted float value as string, it just want a dot as decimal separator.

procedure TForm1.Button1Click(Sender: TObject);
var
  App: Variant;
  Workbook: Variant;
  Worksheet: Variant;
  DoubleValue: Double;
begin
  App := CreateOleObject('Excel.Application');
  Workbook := App.Workbooks.Add;
  Worksheet := Workbook.ActiveSheet;
  DoubleValue := 1.2;
  Worksheet.Range['A1'].Value := DoubleValue; //DoubleValue is a double, excel recognizes a double
  Worksheet.Range['A2'].Value := '1.2'; //excel recognizes a double
  Worksheet.Range['A3'].Value := '1,2'; //excel recognizes a string
  Worksheet.Range['A4'].Value := FloatToStr(1.2); //excel recognizes a string
  App.Visible := True;
end;

Keep in mind that I hava a comma as decimal separator.