Bookstore

Recent

Author Topic: [SOLVED]sheet1!A4 was changed from SUM(A1:A3) to SUM(A1:A2) after delete ...  (Read 550 times)

abcthing

  • Newbie
  • Posts: 4
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.
« Last Edit: March 27, 2020, 01:38:49 am by abcthing »

wp

  • Hero Member
  • *****
  • Posts: 6824
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).
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

abcthing

  • Newbie
  • Posts: 4
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!
« Last Edit: March 26, 2020, 09:40:52 am by abcthing »

abcthing

  • Newbie
  • Posts: 4
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

  • Hero Member
  • *****
  • Posts: 6824
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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

abcthing

  • Newbie
  • Posts: 4
The 3D formula is so complicated that I've never used it.
It's perfect now.

Thank you again!