A4 of sheet1 was changed from SUM(A1:A3) to SUM(A1:A2) after delete row 2 in sheet2.
Example:
procedure TForm1.Button1Click(Sender: TObject);
var
fWorkbook: TsWorkbook;
fWorksheet1,fWorksheet2: TsWorksheet;
fResult,fResult2: integer;
begin
fWorkbook := TsWorkbook.Create;
try
fWorkbook.Options:=fWorkbook.Options+[boAutoCalc];
fWorksheet1 := fWorkbook.AddWorksheet('sheet1');
fWorksheet2 := fWorkbook.AddWorksheet('sheet2');
fWorksheet1.WriteNumber(0, 0, 10); // A1
fWorksheet1.WriteNumber(1, 0, 20); // A2
fWorksheet1.WriteNumber(2, 0, 30); // A3
fWorksheet1.WriteFormula(3,0, 'SUM(A1:A3)'); // A4
fResult := trunc(fWorksheet1.ReadAsNumber(3,0));
fWorksheet2.DeleteRow(1);
fResult2 := trunc(fWorksheet1.ReadAsNumber(3,0));
if fResult<>fResult2 then
ShowMessage('Error!')
else
ShowMessage('OK!');
finally
fWorkbook.Free;
end;
end;
Running this test will show 'Error'.
My solution is to modify the fixdeletedrow procedure of the fpsclases.pas, as well as fixdeletedcol, fixinsertedcol, and fixinsertedrow.
......
if AExprNode is TsCellExprNode then
begin
if TsCellExprNode(AExprNode).Error <> errOK then
exit;
referencedSheet := TsCellExprNode(AExprNode).GetSheet;
if TsCellExprNode(AExprNode).Has3dLink and (referencedSheet <> changedSheet) then
exit;
if referencedSheet <> changedSheet then exit; //add
......
else
if AExprNode is TsCellRangeExprNode then
begin
if TsCellRangeExprNode(AExprNode).Error <> errOK then
exit;
referencedSheet := TsCellRangeExprNode(AExprNode).GetSheet(1);
referencedSheet2 := TsCellRangeExprNode(AExprNode).GetSheet(2);
if TsCellRangeExprNode(AExprNode).Has3dLink and
(referencedSheet <> changedSheet) and
(referencedSheet2 <> changedSheet)
then
exit;
if (referencedSheet = nil) and (TsCellRangeExprNode(AExprNode).Worksheet <> changedSheet) then exit; //add
......
I'm not sure that's right. Please fix this bug.