Recent

Author Topic: user define function  (Read 29542 times)

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: user define function
« Reply #30 on: June 02, 2018, 10:54:04 am »
Thanks for the detailed information. Still unclear: In the test case "sum(H1:J10)" you delete rows and columns. In which sheet are they deleted?

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user define function
« Reply #31 on: June 02, 2018, 11:56:58 am »
Thanks for the detailed information. Still unclear: In the test case "sum(H1:J10)" you delete rows and columns. In which sheet are they deleted?

the formula is in sheet5. Deleted row 10 of sheet5 - program crash. 

Deleted column J of the same sheet - Not crash but formula changed to sum(H1:I10) ..sorry for the wrong information.

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: user define function
« Reply #32 on: June 02, 2018, 02:44:36 pm »
The first failed test case (delete sheet1) is fixed in trunk.

But I cannot reproduce the other two (delete columns/rows in sheet5) - see attached test program. Could you provide your test program?

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user define function
« Reply #33 on: June 02, 2018, 04:20:54 pm »
The first failed test case (delete sheet1) is fixed in trunk.

But I cannot reproduce the other two (delete columns/rows in sheet5) - see attached test program. Could you provide your test program?
attached as per your  request
« Last Edit: June 02, 2018, 06:08:27 pm by kjteng »

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: user define function
« Reply #34 on: June 02, 2018, 06:35:30 pm »
Should be fixed now.

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user define function
« Reply #35 on: June 03, 2018, 05:03:27 am »
Should be fixed now.
Almost there.

1. Refer to the attached PNG, the results for sum(Sheet1!A1:C5) (when row/col are inserted/deleted in sheet1)  are not quite consisitent with that of  sum(H1:J10) <sum within same sheet) and also different from ms-Office. (You can test this with the test program in my previous post.)

2. Reference to invalid sheetname in formula are removed in most cases but not in the case of  sum(Sheet1!A1:C5).  The program crash when I set the formula to sum(Sheet11!A1:C5)  (if Sheet11 does not exist).  See attached test program.

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user define function
« Reply #36 on: June 03, 2018, 11:08:20 am »
CopyCellsToClipboard does not work now (if it is formula cell), I think this is because formulas are stored in a separate tree now.
PasteCellsFromClipboard may be affected too but I am unable to test it now.
« Last Edit: June 03, 2018, 04:56:38 pm by kjteng »

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: user define function
« Reply #37 on: June 04, 2018, 09:08:24 pm »
Yes, getting closer...
- Fixed clipboard issue
- Fixed update of formulas when rows/cols are inserted/deleted
- Fixed crashes when reading Excel files which contain shared formulas (i.e. formula written into one cell and then dragged into the other cells of a range).

What is left?

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user define function
« Reply #38 on: June 05, 2018, 08:31:48 am »
Yes, getting closer...
- Fixed clipboard issue
- Fixed update of formulas when rows/cols are inserted/deleted
- Fixed crashes when reading Excel files which contain shared formulas (i.e. formula written into one cell and then dragged into the other cells of a range).

What is left?
Copycells function has some problem. Please see attached images.

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user define function
« Reply #39 on: June 06, 2018, 06:11:26 am »
Test results: 1. CutCellsToClipboard  2. Installing desiggtime package

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: user define function
« Reply #40 on: June 06, 2018, 12:45:43 pm »
The compilation error is fixed

I'm not sure about the other one. My concern is
  • this feature has never been implemented in fpspreadsheet and I fear to open another box of Pandora...
  • the behavior of the office applications is inconsistent
    • Excel changes the cell addresses referenced by a formula when the referemced cells are dragged to another place. But this seems to be true only within the current sheet, Excel does not do this in a 3d-Formula, i.e. when the referenced and moved cell is in another sheet, not in the sheet with the formula.
    • Libre/OpenOffice Calc never changes the cell addresses when referenced cells are dragged
    • Excel does not change the cell address when a referenced cell is cut and pasted somewhere else (Your observation in line 18 says: "Formula in B18 became Sheet1!A1+Sheet2°A1", but this is exactly the initial formula - no change).
I don't like the behavior of Excel here, sometimes changing, sometimes not. This is confusing. Calc is clear: Never change.

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user define function
« Reply #41 on: June 06, 2018, 01:40:25 pm »
The compilation error is fixed

I'm not sure about the other one. My concern is
  • this feature has never been implemented in fpspreadsheet and I fear to open another box of Pandora...
  • the behavior of the office applications is inconsistent
    • Excel changes the cell addresses referenced by a formula when the referemced cells are dragged to another place. But this seems to be true only within the current sheet, Excel does not do this in a 3d-Formula, i.e. when the referenced and moved cell is in another sheet, not in the sheet with the formula.
    • Libre/OpenOffice Calc never changes the cell addresses when referenced cells are dragged
    • Excel does not change the cell address when a referenced cell is cut and pasted somewhere else (Your observation in line 18 says: "Formula in B18 became Sheet1!A1+Sheet2°A1", but this is exactly the initial formula - no change).
I don't like the behavior of Excel here, sometimes changing, sometimes not. This is confusing. Calc is clear: Never change.
Should be Ok so long as the user of the component know about this behaviour.

I've just tested Libre office/Calc  (v5.2.7.2) does changed the formula when we move/cut paste the reference cell.
« Last Edit: June 06, 2018, 02:08:55 pm by kjteng »

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: user define function
« Reply #42 on: June 06, 2018, 03:11:38 pm »
LibreOffice v5.4.1.2 (Windows) does not, OpenOffice v4.1.3 neither.

What exactly are you doing?

I do this:
** DRAG AND DROP **
- Cell B3: type value 1
- Cell B5: type vlaue 2
- Cell D3: type formula "=SUM(B3:B5)". ENTER --> value 3 is displayed
- with the mouse I select cells B5 and B6, click somewhere in any of these two cells and drag both cells to a different location
- The sum value in D3 changes to 1. And when I select D3 I still see the old formula

** CUT AND PASTE **
- repeat the first 3 steps of above
- Select cell B5.
- Press CTRL+X --> sum cell value jumps to 1 (because value in B5 is erased)
- Click some other cell
- Press CTRL+V to paste the value back in --> sum cell value remains at 1. The formula in D3 is still SUM(B3:B5)

[EDIT]
if the formula cell C3 contains the expression "=B3+B5" then the behavior is like you describe. But this is even worse: Even within the same application the behavior is inconsistent. Why should "SUM(B3:B5)" behave differently from "B3+B5"?
« Last Edit: June 06, 2018, 03:25:30 pm by wp »

kjteng

  • Sr. Member
  • ****
  • Posts: 259
Re: user define function
« Reply #43 on: June 06, 2018, 05:50:13 pm »
LibreOffice v5.4.1.2 (Windows) does not, OpenOffice v4.1.3 neither.

What exactly are you doing?

I do this:
** DRAG AND DROP **
- Cell B3: type value 1
- Cell B5: type vlaue 2
- Cell D3: type formula "=SUM(B3:B5)". ENTER --> value 3 is displayed
- with the mouse I select cells B5 and B6, click somewhere in any of these two cells and drag both cells to a different location
- The sum value in D3 changes to 1. And when I select D3 I still see the old formula

** CUT AND PASTE **
- repeat the first 3 steps of above
- Select cell B5.
- Press CTRL+X --> sum cell value jumps to 1 (because value in B5 is erased)
- Click some other cell
- Press CTRL+V to paste the value back in --> sum cell value remains at 1. The formula in D3 is still SUM(B3:B5)

[EDIT]
if the formula cell C3 contains the expression "=B3+B5" then the behavior is like you describe. But this is even worse: Even within the same application the behavior is inconsistent. Why should "SUM(B3:B5)" behave differently from "B3+B5"?

OIC you were talking about formula like sum(B2:B5) but I was testing A3+B3 and sheet1.A1+Sheet1.A3 (where both cut+paste  and drag move operation would result in change in the formula).
I understand and accept your reasoning for this. For me it's just a matter of getting use to it (because I have been using Excel for decades :-)

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: user define function
« Reply #44 on: June 06, 2018, 06:18:44 pm »
I would not have thought that the Excel formula engine would make a difference whether it gets a sum as a sequence of binary operations or as a function call. This is really hard to understand and very confusing. Therefore, I'll go the easy way and will not implement this behavior, at least for the time being. So, the rule is for fpspreadsheet:
  • If a cell with a formula is dragged or copied/pasted or cut/pasted to another location the formula is updated according to the type of the reference: absolute --> no change, relative --> change depending on the distance between the origin and final location of the cell (in agreement with Excel/Calc)
  • If a cell referenced by the formula is dragged/copied/cut/pasted to another location the formula is NEVER changed. This contradicts Excel/Calc where the formula is SOMETIMES changed.
  • With "formula" I mean ever expression which begins with "=".
BTW, thank you for the help in debugging.

 

TinyPortal © 2005-2018