Error #VALUE! on opening a file created with TXLSReadWriteII

[Y.AMILIN]

Hello,

my company is currently testing the TXLSReadWriteII component. During our test, we found the following bug:

1- Execute this code

procedure TForm1.Button1Click(Sender: TObject);
var XLS: TXLSReadWriteII4;
begin
XLS := TXLSReadWriteII4.Create(nil);
try
XLS.Sheet[0].AsFloatRef['A2'] := 1;
XLS.Sheet[0].AsFloatRef['B2'] := 1;
XLS.Sheet[0].AsFloatRef['C2'] := 1;
XLS.Sheet[0].AsFormulaRef['D2'] := 'IF(SUM(A2:C2)=0,"na",SUM(A2:C2))';
XLS.Filename := 'd:\Test.xls';
XLS.Write;
finally
XLS.Free;
end;
end;

2- Open the file with Excel (tested with 2003/2007) => value of cell D2 is #VALUE!

3- Enter cell D2 and validate (ie F2 + ENTER) => value of cell D2 is 3

It happens on other formulas as well.


PS:
I apologize for my bad English.

[larsa]

Hello

This shall be fixed in the last update. Please download it and try again.

[bcrooker]

Which version was this fixed in? I downloaded 4.0.22 today and it seems to have this problem.

[larsa]

Hello

This is fixed again now, and hopefully better this time.

[jets]

I have similar error with latest version 4.00.25 when trying to make excel 2007 file.
Modified example code:

procedure TForm1.Button1Click(Sender: TObject);
var XLS: TXLSReadWriteII4;
begin
XLS := TXLSReadWriteII4.Create(nil);
XLS.Version := xvExcel2007;
try
XLS.Sheet[0].AsFloatRef['A2'] := 1;
XLS.Sheet[0].AsFloatRef['B2'] := 1;
XLS.Sheet[0].AsFloatRef['C2'] := 1;
XLS.Sheet[0].AsFormulaRef['D2'] := 'SUM(A2:C2)';
XLS.Filename := 'd:\Test2.xlsx';
XLS.Write;
finally
XLS.Free;
end;
end;

When opening Test2.xlsx, excel states that found unreadable content and D2 cell states #VALUE!(the value is '=A2:C2'), not what is needed - '=SUM(A2:C2)'
Is this bug or am I doing something wrong here? Unfortunately I cannot use excel 97 compatible format because need for more than 256 columns.

[larsa]

Hello

Yes, there was a problem with some formulas in Excel 2007 files.
This is fixed in update 4.00.26

[jets]

Hey, thanks for updated version, the formulas work now up to column 'IV'.
But after that there are still problems, at least with AsFloatRef, AsFormulaRef and AsFormula.
The issues are best illustrated by this example:

XLS3 := TXLSReadWriteII4.Create(nil);
XLS3.Version := xvExcel2007;

XLS3.Sheet[0].AsFloatRef['IQ2'] := 1.2;
XLS3.Sheet[0].AsFloat[250,2] := 1.3;
XLS3.Sheet[0].AsFloatRef['IQ4'] := 1.4;
XLS3.Sheet[0].AsFormulaRef['IQ5'] := 'SUM(IQ2:IQ3)';
XLS3.Sheet[0].AsFormula[250,5] := 'SUM(IQ3:IQ4)';

XLS3.Sheet[0].AsFloatRef['IX12'] := 2.2;
XLS3.Sheet[0].AsFloat[257,12] := 2.3;
XLS3.Sheet[0].AsFloatRef['IX14'] := 2.4;
XLS3.Sheet[0].AsFormulaRef['IX15'] := 'SUM(IX12:IX13)';
XLS3.Sheet[0].AsFormula[257,15] := 'SUM(IX13:IX14)';

XLS3.Sheet[0].AsFloatRef['JC22'] := 3.2;
XLS3.Sheet[0].AsFloat[262,22] := 3.3;
XLS3.Sheet[0].AsFloatRef['JC24'] := 3.4;
XLS3.Sheet[0].AsFormulaRef['JC26'] := 'SUM(JC22:JC23)';
XLS3.Sheet[0].AsFormula[262,26] := 'SUM(JC23:JC24)';

Here should be three columns with some summing.
Column IQ is OK but IX and JC are messed up - some values/formulas moving to IV or not appearing correctly.

Another problem is when opening excel document, it states that it found unreadable content and repair log is:
Removed Records: Document Theme from /xl/workbook.xml part (Workbook)