[SOLVED]sheet1!A4 was changed from SUM(A1:A3) to SUM(A1:A2) after
delete ...
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
abcthing · 2020-03-23 08:42

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.

----------------------------------------------------------------------
wp · 2020-03-25 23:23

Thanks for reporting. Fixed in fpspreadsheet trunk.

In the second part, where AExprNode is TsCellRangeExprNode, I only
checked for "if referencedsheet = nil then exit". Do you have a
special case in mind why you added
"TsCellRangeExprNode(AExprNode).Worksheet <> changedSheet"? (last line
of your code in the first post).

----------------------------------------------------------------------
abcthing · 2020-03-26 02:11

The special case is  to delete row in the same sheet.

I downloaded and installed the latest package (7342).
A4 of sheet1 is #REF! when delete row 2 in sheet1.
So should keep it.

There are four test cases,as follows:
1.sheet1!A4=SUM(A1:A3) and sheet2.DeleteRow(1).
2.sheet1!A4=SUM(A1:A3) and sheet1.DeleteRow(2).
3.sheet1!A7=A5+A6 and sheet2.DeleteRow(1).
4.sheet1!A7=A5+A6 and sheet1.DeleteRow(1).

This is a very good component, to my work a lot of help, thank you
very much!

----------------------------------------------------------------------
abcthing · 2020-03-26 12:18

The testing results for 7342  look as follow：

Sheet1!A4=SUM(A1:A3) and sheet2.DeleteRow(1): OK!
Sheet1!A4=SUM(A1:A3) and sheet1.DeleteRow(2): Expect is SUM(A1:A2),
but real is SUM(A1:A3)!
Sheet1!A7=A5+A6 and sheet2.DeleteRow(1): Expect is A5+A6, but real is
A4+A5!
Sheet1!A7=A5+A6 and sheet1.DeleteRow(1): OK!

----------------------------------------------------------------------
wp · 2020-03-26 17:21

Fixed. I included your (slightly modified) test cases in the
fpspreadsheet test suite.

I thought that I already had checked the effect of adding/deleting
columns/rows on formulas some time ago. Obviously, I had forgot some
cases.

Now I also looked at a 3D formula "SUM(Sheet1:Sheet2!A1:A3)". And this
is where things get painful. When this formula is in cell Sheet1!A4
and I add a row between A2 and A3 inside this range in Excel, the
formula cell is moved to A5, but the formula remains the same.
fpspreadsheet moves the formula cell as well, but fpspreadsheet
extends the formula to "SUM(Sheet1:Sheet2!A1:A4".

I don't know what's correct... If it were a single sheet extending the
formula range would be expected, and this is what Excel does too in
this case. But in a 3D block? In analogy to the single sheet, it would
be consistent to extend the range, but is does not look correct for
the other sheets of the 3d block where the range is extended too so
that cells may be included in the calculation which should not be
there. On the other hand the Excel solution does not look correct for
the 1st sheet which I would expect to be extended.

----------------------------------------------------------------------
abcthing · 2020-03-27 00:37

The 3D formula is so complicated that I've never used it.
It's perfect now.

Thank you again！