* * *

Author Topic: user define function  (Read 9131 times)

wp

  • Hero Member
  • *****
  • Posts: 4838
Re: user define function
« Reply #15 on: May 10, 2018, 09:36:52 am »
Your are right.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 126
Re: user define function
« Reply #16 on: May 11, 2018, 02:13:51 pm »
I have installed the latest version of fpSpread. Encountered the following problem for single cell linking. eg Sheet2!B2  Specifically when rows/cols are inserted/deleted in sheet2 and when the new B2 cell is empty, the formula in sheet1 would not be recalculated.  Attached is my test code.

 
« Last Edit: May 11, 2018, 04:37:01 pm by kjteng »

kjteng

  • Full Member
  • ***
  • Posts: 126
Re: user define function
« Reply #17 on: May 14, 2018, 06:03:52 pm »
Hi, is  revision 6411 the latest version?

In this revision, additional sheets would be automatically created when I try to open my  xlsx file.

For example, in my book1.xlsx file (uploaded in my previous post) it consists of Sheet1, Sheet2  and Sheet3 with some formulas. When open the file with:-
 
Code: Pascal  [Select]
  1.    wbSrc1.Workbook.Options := wbSrc1.Workbook.Options + [boReadFormulas];    
  2.    wbSrc1.Workbook.ReadFromFile(OpenDialog1.FileName);  

I get 5 sheets i.e. Sheet1, Sheet2(blank, new sheet), sheet3 (blank, new sheet), sheet21  (which is the original sheet2) and Sheet 31 (which is the original sheet3).

Any thing wrong with my code or did I miss out something?
« Last Edit: May 15, 2018, 04:20:24 am by kjteng »

wp

  • Hero Member
  • *****
  • Posts: 4838
Re: user define function
« Reply #18 on: May 15, 2018, 06:45:47 pm »
Fixed.

fpspreadsheet now supports "3d references", i.e. cell addresses an other sheets of the same workbook. Cell ranges can be in a single sheet or in a range of sheets.

The syntax is that of Excel, e.g. Sheet3!A1 (single cell), Sheet3!A1:B3 (cell range in single sheet), Sheet3:Sheet6!A1 (the same cell in a range of sheets, Sheet3:Sheet6!A1:B3 (the same cell range in a range of sheets). The syntax of LibreOffice Calc (e.g. Sheet3.A1:Sheet6.B3 is supported internally for reading and writing .ods files, but is not allowed in the TsWorksheet's method WriteFormula. Maybe this will change - I don't know...

The readers and writers of the formats Excel 95, 97 (BIFF5 and BIFF8, .xls) and 2007+ (.xlsx), as well as OpenDocument (.ods) are supporting the new feature. Excel 2 is not supported because it serves only a single worksheet anyway.

References to other files still are not allowed. I don't know whether I will implement this. Use the workaround with the workbook option [boIgnoreFormulas] if you must read such files and write them back -- but you must be aware that the formula engine is disabled with this option.

All provided unit tests are passed, but of course I cannot exclude that some bugs are still hidden somewhere. So please test.

The new feature has consequences on inserting/deleteing of rows and columns - the required adaptions are have not been implemented yet. Moreover I expect some speed pentaly for large workbooks with many sheets and formulas when the workbook option boAutoCalc is set because now the entire workbook has to be recalculated with every change of any cell - the old version only had to recalculate the sheet containing the changed cell.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 126
Re: user define function
« Reply #19 on: May 16, 2018, 08:31:07 am »
SVN 6419 - test :
1. Reference to single cell in another sheet wont recalculate when the cell is deleted (with DEL key) or moved - same issue as previous version;
2. sum(B3:B1) does not work;
3. formula SUM(Sheet1:Sheet2!A1) causes access violation error.
Please refer  to attached screen shot
« Last Edit: May 16, 2018, 12:11:54 pm by kjteng »

kjteng

  • Full Member
  • ***
  • Posts: 126
Re: user define function
« Reply #20 on: May 17, 2018, 03:35:19 pm »
I have problems when rename worksheet. I have sheet1, sheet2 ...sheet5 in a workbook and sheet5 contains several cells with 3d reference.  (see attached image)
If I rename sheet1 to sht1, or sheet5 to sht5, some formulas will changed and my program crash if I try to key in data into the worksheet.
Please advise.

wp

  • Hero Member
  • *****
  • Posts: 4838
Re: user define function
« Reply #21 on: May 21, 2018, 10:20:44 am »
If I rename sheet1 to sht1, or sheet5 to sht5, some formulas will changed and my program crash if I try to key in data into the worksheet.
fpspreadsheet, at the moment, stores the string formulas in the cell records. The formulas are parsed whenever needed, the parsed formulas are not stored.

When a sheet is renamed all formulas are parsed to find out the sheetname, and this is where the program crashes because the old sheet in the formula does not exist any more.

Of course the crash could be avoided by some changes in the formula engine. But I think the real problem is that the formulas are not stored in the parsed state. This slows down formula calculation, and adding columns, renaming sheets, copying cells etc. becomes unnecessarily complicated.

Therefore, I decided not to fix this issue at the moment. Instead, I plan to redo the formula handling by fpspreadsheet. I want to set up another tree to store the parsed formulas outside of the cell records. This will speed up calculation and make correction of formulas easier.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 126
Re: user define function
« Reply #22 on: May 26, 2018, 12:00:27 pm »
Noted and thanks for you reply.

wp

  • Hero Member
  • *****
  • Posts: 4838
Re: user define function
« Reply #23 on: June 02, 2018, 12:02:05 am »
Done. I put a lot of effort into restructuring the formula handling. The formulas now are longer stored as strings in the cells (the string format caused successive parsing of the formula), but they are put into a separate tree (Formulas) as string plus parser tree. This makes corrective actions on changes in the workbook structure much easier. The cases "rename worksheet" and "delete worksheet" should work now. Moving of worksheets would be another candidate but this is not yet implemented at all, so I'll postpone this a bit until dust has settled.

Inserting and deleting rows and columns was another issue that you mentioned. But I tested with Excel: If sheet1 contains a formula "SUM(Sheet2:Sheet3!C3)" and I insert a row in sheet2 before the summed cell C3, then the formula in sheet1 does not change (the sum, of course, does). So, I think there's nothing to do here with the formula. Already earlier, recalculation has been changed to operate on the full workbook instead of the worksheet only. Therefore, the formula result should update upon changes in the referenced cells of another sheet.

Please test.

Side note: Since the formula is no longer part of the cell record reading a formula by "cell^.FormulaValue" is no longer valid now. Instead, use the function ReadFormula(cell) or ReadFormulaAsString(cell...) of the worksheet containing the formula (the latter variant can be used to return a localized formula).
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

taazz

  • Hero Member
  • *****
  • Posts: 5257
Re: user define function
« Reply #24 on: June 02, 2018, 02:44:34 am »
Done. I put a lot of effort into restructuring the formula handling. The formulas now are longer stored as strings in the cells (the string format caused successive parsing of the formula), but they are put into a separate tree (Formulas) as string plus parser tree. This makes corrective actions on changes in the workbook structure much easier. The cases "rename worksheet" and "delete worksheet" should work now. Moving of worksheets would be another candidate but this is not yet implemented at all, so I'll postpone this a bit until dust has settled.

Inserting and deleting rows and columns was another issue that you mentioned. But I tested with Excel: If sheet1 contains a formula "SUM(Sheet2:Sheet3!C3)" and I insert a row in sheet2 before the summed cell C3, then the formula in sheet1 does not change (the sum, of course, does). So, I think there's nothing to do here with the formula. Already earlier, recalculation has been changed to operate on the full workbook instead of the worksheet only. Therefore, the formula result should update upon changes in the referenced cells of another sheet.

Please test.

Side note: Since the formula is no longer part of the cell record reading a formula by "cell^.FormulaValue" is no longer valid now. Instead, use the function ReadFormula(cell) or ReadFormulaAsString(cell...) of the worksheet containing the formula (the latter variant can be used to return a localized formula).
wow, that must have been at least a moths effort, grats! One stupid question though, is it hard to call readformula(cell) from the cell^.FormulaValue getter?
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

kjteng

  • Full Member
  • ***
  • Posts: 126
Re: user define function
« Reply #25 on: June 02, 2018, 05:17:48 am »
Thanks for the good work.
Append below are my preliminary test results:

1. formula=SUM(B1:B7)
delete first row: Crash (except when B1 is empty)
delete end row (row 7):  formula changed to SUM(B1:B6) - ok
2. formula=Sheet1!A1   
   formula=Sheet1!A1+Sheet4!A2   
   formula=Sheet4!A2+Sheet1!A1

Rename Sheet1 to sht1 - Formula changed correctly

Delete Sheet1, formula changed to #REF
formula=Sheet1!A1    became #REF
formula=Sheet1!A1+Sheet4!A2    became #REF+Sheet4!A2
formula=Sheet4!A2+Sheet1!A1    became Sheet4!A2+Sheet2!A1

Delete sheet4
formula=Sheet1!A1+Sheet4!A2    became Sheet1!A1+ #REF
formula=Sheet4!A2+Sheet1!A1    became #REF +Sheet1!A1

kjteng

  • Full Member
  • ***
  • Posts: 126
Re: user define function
« Reply #26 on: June 02, 2018, 08:34:43 am »
Done. I put a lot of effort into restructuring the formula handling. The formulas now are longer stored as strings in the cells (the string format caused successive parsing of the formula), but they are put into a separate tree (Formulas) as string plus parser tree. This makes corrective actions on changes in the workbook structure much easier. The cases "rename worksheet" and "delete worksheet" should work now. Moving of worksheets would be another candidate but this is not yet implemented at all, so I'll postpone this a bit until dust has settled.

Inserting and deleting rows and columns was another issue that you mentioned. But I tested with Excel: If sheet1 contains a formula "SUM(Sheet2:Sheet3!C3)" and I insert a row in sheet2 before the summed cell C3, then the formula in sheet1 does not change (the sum, of course, does). So, I think there's nothing to do here with the formula. Already earlier, recalculation has been changed to operate on the full workbook instead of the worksheet only. Therefore, the formula result should update upon changes in the referenced cells of another sheet.

Please test.

Side note: Since the formula is no longer part of the cell record reading a formula by "cell^.FormulaValue" is no longer valid now. Instead, use the function ReadFormula(cell) or ReadFormulaAsString(cell...) of the worksheet containing the formula (the latter variant can be used to return a localized formula).
You are right: for 3D formula like "SUM(Sheet2:Sheet3!C3)", C3 should remain even if new row are inserted in the linked sheet. However, for  non 3D formula "SUM(Sheet2!C1:Sheet2!C3)"  insertion/deletion between the range from C1 to C3 would affect on the formula.

wp

  • Hero Member
  • *****
  • Posts: 4838
Re: user define function
« Reply #27 on: June 02, 2018, 09:38:47 am »
Thanks for the good work.
Append below are my preliminary test results:

1. formula=SUM(B1:B7)
delete first row: Crash (except when B1 is empty)
delete end row (row 7):  formula changed to SUM(B1:B6) - ok
2. formula=Sheet1!A1   
   formula=Sheet1!A1+Sheet4!A2   
   formula=Sheet4!A2+Sheet1!A1

Rename Sheet1 to sht1 - Formula changed correctly

Delete Sheet1, formula changed to #REF
formula=Sheet1!A1    became #REF
formula=Sheet1!A1+Sheet4!A2    became #REF+Sheet4!A2
formula=Sheet4!A2+Sheet1!A1    became Sheet4!A2+Sheet2!A1

Delete sheet4
formula=Sheet1!A1+Sheet4!A2    became Sheet1!A1+ #REF
formula=Sheet4!A2+Sheet1!A1    became #REF +Sheet1!A1
Please give more complete test conditions. Besides the tested formula you should provide this information:
- In which cell is the formula, and in which sheet?
- If a row/column is deleted, in which sheet is it?
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

wp

  • Hero Member
  • *****
  • Posts: 4838
Re: user define function
« Reply #28 on: June 02, 2018, 09:45:39 am »
For 3D formula like "SUM(Sheet2:Sheet3!C3)", C3 should remain even if new row are inserted in the linked sheet. However, for  non 3D formula "SUM(Sheet2!C1:Sheet2!C3)"  insertion/deletion between the range from C1 to C3 would affect on the formula.
Again, I don't know in which sheet these formulas are.

Did you test this? I think fpspreadsheet should reject these formulas because they are no valid Excel syntax. Excel always has "Range of sheets!Range of cells", in this case "SUM(Sheet2:Sheet3!C2:C3)"
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 126
Re: user define function
« Reply #29 on: June 02, 2018, 10:39:58 am »
Sorry, there are typo in the formula. Please see attached excel file and screen shot for your reference.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus