XLSRWII5: Calculate seems not working properly (5.20.32)

[jsfkcz]

Hello,
I have 2 formulas in my "template.xlsx":
1. Cell B5 has a formula = TARGET * 100
2. Cell B6 has a formula = A3 * 100
and
Cell A3 is a named cell "TARGET" (its default value is blank).

When I write integer value 10 to the cell A3 by XLSRW and open the result file, the cell B5 value is stil zero and the cell B6 value is ok = 1000 . The formula with named cell was not calculated properly.

If I press Ctrl+Alt+F9 manually, the value of cell B5 will become ok.

(
The calculation is set "automatically" all the time.
I created my template.xlsx in Excel 2013 and I tested it with Excel 2007 and 2010 too. The behavior is the same.
I tested it at the last version of XLSRWII5 - 5.20.32
)

For this sample I used this code:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
const
  cOrigFile = 'template.xlsx';
  cTestFile = 'result.xlsx';
var
  ASheet: TXLSWorksheet;
begin
  CopyFile(cOrigFile,cTestFile,False);
  xlsRW.Filename:= cTestFile;
  xlsRW.Read;
  ASheet:= xlsRW.SheetByName('DATA');
  asheet.asinteger[0,2]:=10; // cell A3
  xlsRW.calculate;
  xlsRW.Write;
end;
end.

The template.xlsx and result.xlsx are at http://www.genet.cz/kohout/xlsrw/exampl ... culate.zip for download.

Josef Kohout

[Shovey]

Just to confirm. I have a very similar issue. using version 5.20.30

I created an excel file using excel with some formulas AVERAGEIF(I7:I30,">1") and others with SUM(E7:E30)
I then use XLSReadWrite to insert data into the file, run Calculate() and then save the file as a new name.


I then open the new file with excel.
The cells with the sum formula are all correct.
However the AverageIf formula are all showing the wrong values.
If I press Ctrl+Alt+F9 to manually calculate the sheet all the values update with the correct values.

I also noticed that the data displayed in the cells with the averageif formula actually had the summed value not average value.
example.
20 and 10 in the data cells showed 30 in the average cell instead of 15

[larsa]

Hello

This is fixed in update 5.20.33

[jsfkcz]

At version 5.20.33, I have the same problem with calculation formula with named cell as well as at the version 5.20.32 .

Josef Kohout

[larsa]

Hello

Sorry, I missed the named cells issue. This is fixed in update 5.20.34

[jsfkcz]

Hello,
there is a new problem with calculation at formlulas with named cells.
Since 5.20.34 version the formulas with named cells are calculated wrong.

(screenshot of result)


The value of named cell is taking from the sheet where is the formula, not from the right sheet where the named cell was defined.
After Ctrl+Alt+F9 every results become correct.

When the named cell is defined at the same sheet, where is the formula, everything looks fine.

The template.xlsx and result.xlsx are at http://www.genet.cz/kohout/xlsrw/exampl ... .20.34.zip for download.

Josef Kohout

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
const
  cOrigFile = 'template.xlsx';
  cTestFile = 'result.xlsx';
var
  BSheet: TXLSWorksheet;
begin
  CopyFile(cOrigFile,cTestFile,False);
  xlsRW.Filename:= cTestFile;
  xlsRW.Read;
  BSheet:= xlsRW.SheetByName('SOURCE');
  bsheet.asinteger[0,2]:=3;
  BSheet:= xlsRW.SheetByName('SOURCE');
  bsheet.asinteger[0,3]:=4;
  xlsRW.calculate;
  xlsRW.Write;
end;

[larsa]

Hello

This is fixed in update 5.20.35