1. What number should I use for the AExcelCode (parameter) in the RegisterFunction if there is no similar function in excel. I have tried using 0 and which seems ok but I wish to know what is the correct number to use ?I fear I don't understand... fpspreadsheet is a library mainly for reading and writing spreadsheet files. If you implement a completely new function then Excel and certainly also Calc will not be able to open the file any more. Or maybe you are talking of a "macro"? Macros are not supported by fpspreadsheet. Or if you just want to make a calculation without saving the formula: Why don't you make the calculation in Pascal within your program?
2. The wiki page refers to sample project demo_ignore_formula. The code in the sample project add boIgnoreFormula to worksheet options. However boIgnoreFormula is not a valid identifier. Shall boReadFormulas be used instead ?This is a feature of the development version on svn. The fpspreadsheet wiki refers to this version. If you need the wiki for the version which you actually are using refer to the fpspreadsheet-wiki.chm file in the folder docs of you fpspreashseet installtion.
1. My existing codes couldn't complie any more. (Error message:
fpspread1.pas(173,43) Error: Incompatible type for arg no. 1: Got "AVL_TREE.TAVLTreeNode", expected "LAZ_AVL_TREE.TAVLTreeNode"
laz_avl_tree.pp(1067,19) Hint: Found declaration: FindSuccessor (TAVLTreeNode): TAVLTreeNode;)
OK the first question in my previous post is solved as follows:What is your Lazarus/FPC version?
i. change avl_tree (in uses clause) to avglvltree as the trunk version is using the latter;
ii. change TAVLTreeNode to TAVGLVLTreeNode in all the variable declaration.
Strange, fpspreadsheet does compile with Laz 1.8.2 without any problems. What is this file fpspread1.pas? I guess this is your file, isn't it? This would explain the issue. The AvlTree units have been rearranged in Lazarus recently. Why do you need them at all?As I have mentioned above, problem 1 has been resolved. My code could not compile becos I used avl_tree unit (also useds in the fpSpreadsheet.pas which comes with lazarus 1.8.1). The problem is solved by changing all avl_tree to avglvltree in all my *.pas files.
In this file I have some test procedure which traverse through all the nodes of worksheet.hyperlinks (i.e. findfirst, findsuccessor ...)The Hyperlinks already have an enumerator in the WorkSheet, like all cell-based lists (Cells, Hyperlinks, Comments, MergedCells); there's no need to dive into the AVLTree:
I notice that the link formula does not get the correct value if it only refers to single cell in another sheet eg. Sheet2!A1 .However Sheet2!A1+0 works! (see attached screen shot);Should be fixes along with some other bugs in the current svn version. Now fpspreadsheet can also read xls files (Excel 8 only) with 3d-formulas.
It would be nice if the formula can automatically update if the position of the linked cells changed. As it is now the linked formula would remains if we insert or delete row/col in the reference sheet. Perhaps to implement such feature would take time ?Updating of links when rows/columns are inserted/deleted is already contained, however, operates only on the active sheet. But I think it should be feasible to adjust the routine for any other sheet.
The Hyperlinks already have an enumerator in the WorkSheet, like all cell-based lists (Cells, Hyperlinks, Comments, MergedCells); there's no need to dive into the AVLTree:
should the above code be ?
var
hyperlink: psHyperlink;
comment: psComment;
...
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.
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.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?
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).
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.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.
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).
Thanks for the good work.Please give more complete test conditions. Besides the tested formula you should provide this information:
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
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.
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 first failed test case (delete sheet1) is fixed in trunk.attached as per your request
But I cannot reproduce the other two (delete columns/rows in sheet5) - see attached test program. Could you provide your test program?
Should be fixed now.Almost there.
Yes, getting closer...Copycells function has some problem. Please see attached images.
- 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?
The compilation error is fixedShould be Ok so long as the user of the component know about this behaviour.
I'm not sure about the other one. My concern isI don't like the behavior of Excel here, sometimes changing, sometimes not. This is confusing. Calc is clear: Never change.
- 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).
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"?
I accidentally keyed in an invalid formula (=BB) into a cell say A1 of sheet3, an error message pop up (see e1.png attached) and the invlaid formula would not be shown in the worksheet.This is hard stuff. I tried several approaches, but none of them worked out, mostly things are even getting worse...
After that, whenever I key in something to the worksheet (in any cell), I will get the same error message.
However, if i key in a valid 3D formula in cell A1 eg Sheet1!B12, the worksheet would be back to normal (error message wont popup anymore).
This is hard stuff. I tried several approaches, but none of them worked out, mostly things are even getting worse...Noted your comment above.
There are two elemental issues:
- While TCustomGrid has a ValidateEntry method which can be used to prevent leaving an erroneous cell, TEdit does not have such a thing. It does have OnEditingDone but this is too late, it is called when the other control is already focused. Does anybody know a cross-platform way to hook into the TEdit method flow to call some kind of "ValidateEntry" method before the control loses focus?
- Even if this can be solved there is the fundamental question how an error message can be displayed in a messagebox if the error control is not allowed to become unfocused. Or how the application can be closed in the error case by a click on a TButton which also requires a focus change.
Is it ok to ignore this message?This indicates a lot of memory leaks. Please report the EXACT steps how to reproduce.
I fixed a lot of the navigation issues in the error case. This is what should work:This is hard stuff. I tried several approaches, but none of them worked out, mostly things are even getting worse...Noted your comment above.
There are two elemental issues:
- While TCustomGrid has a ValidateEntry method which can be used to prevent leaving an erroneous cell, TEdit does not have such a thing. It does have OnEditingDone but this is too late, it is called when the other control is already focused. Does anybody know a cross-platform way to hook into the TEdit method flow to call some kind of "ValidateEntry" method before the control loses focus?
- Even if this can be solved there is the fundamental question how an error message can be displayed in a messagebox if the error control is not allowed to become unfocused. Or how the application can be closed in the error case by a click on a TButton which also requires a focus change.
I also noticed that you have amended the code in fpspreadsheetgrid.pas and the test result are follows:
Now if I entered =AA in TsCellEdit box, I will get 'Unknown identifier' error message, and I have two choices: OK or Abort. If I choose OK, the cursor will be back to the tsCellEdit box for me to make changes----this seems to be ok.
However, if I key in =AA directly in the wsGrid (editor on the cell), I will get 'Unknown identifier' error message with an OK button. I am stuck in this error message window because the same message will be display if I click ok button. The only way to get out is to kill the program from task manager. Please look into this. TQ
- Use the in-place editor of the grid to type an erroneous formula (e.g. '=A')
- Navigate to another cell by click, arrow keys, or complete editing by ENTER key --> the incorrect formula is detected, the parser's error message is displayed, and navigation is reverted, the grid is in the erroneous cell in edit mode again.
- Use the in-place editor of the grid, type an erroneous formula
- Click into the TsCellEdit box. The error message appears, but the cell is erased - this is not good.
- Use the TsCellEdit box to type an erroneous formula
- When the error message comes up, press ENTER to close it (don't click "OK") -> the box comes up again and again (However, if you click OK with the mouse, the message box does not appear again.
- Use the TsCellEdit box to type an erroneous formulaSame result but see my comment in (1) above
- After typiing don't press ENTER, but click into the cell. The formula is erased. The error message box comes up. Press ENTER or click OK to close the error message. A selection rectangle appears in the grid which follows the mouse. You must click anywhere to return to normal operation.
At times, heaptrc shows there is unfreed memory block, but I am yet to find the way to reproduce this.I fixed a memory leak related to the new handling of formulas.
Maybe I've got an operating solution now - it based on the old Borland article https://community.embarcadero.com/article/technical-articles/149-tools/12766-validating-input-in-tedit-components and looks ok to me. Now it is no longer possible to exit the CellEdit or edited WorksheetGrid cell while an entered formula is not valid.I accidentally keyed in an invalid formula (=BB) into a cell say A1 of sheet3, an error message pop up (see e1.png attached) and the invlaid formula would not be shown in the worksheet.This is hard stuff. I tried several approaches, but none of them worked out, mostly things are even getting worse...
After that, whenever I key in something to the worksheet (in any cell), I will get the same error message.
However, if i key in a valid 3D formula in cell A1 eg Sheet1!B12, the worksheet would be back to normal (error message wont popup anymore).
Maybe I've got an operating solution now - it based on the old Borland article https://community.embarcadero.com/article/technical-articles/149-tools/12766-validating-input-in-tedit-components and looks ok to me. Now it is no longer possible to exit the CellEdit or edited WorksheetGrid cell while an entered formula is not valid.
ROUND(1.2345,2) returns 1.23 butFixed along with some similar ones.
ROUND(a1,b1) returns 1.00 where a1=1.2345 and b1=2
FACT(n) returns correct result if n is a number constant
FACT(A1) returns #VALUE!
ISBLANK(" ") returns TRUE butThe correct result must be FALSE, only if the cell is not allocated or contains only formatting the result is TRUE. Fixed the cases ISBLANK(" ") and ISBLANK("") which both return FALSE in Excel/Calc.
ISBLANK(A1) returns FALSE even if A1 contains only space/spaces
countif(sheet2!A1: A5,">10") return same result as countif(A1: A5,">10")Fixed. It did not find an exact specification, but my experiments with Excel/Calc show that this type of formulas does not support a sheet range here, i.e. '=COUNTIF(Sheet1:Sheet2!A1:B10, ">10")' is not allowed. Therefore I am returning an error in such a case. A single sheet like in your example, however, is correct.
I never understood what this partial calculation mode would be good for. It will display inconsistent results somewhere in the end after all.
No, I will not do this. But your post gave me the idea to display the formula in the cells when AutoCalc is off.
When autorecalc is off with goAlwayShowEditor option, the inplace editor will always show blank (or the value displayed in cell) . To edit the formula, I have to press ESC follwed by F2 key?I don't understand. Did you already try the new version? I never use goAlwaysShowEditor, even without it you can start typing immediately once the grid is focused. The only important option is goEditing which must be active to allow editing at all. The annoying feature of the LCL grids, however, is, that the left/right arrow keys end edit mode and move to the previous/next cell if the cursor is at the first/last character of the edited cell.
Just install SVN6542. Works as expected. Thanks.When autorecalc is off with goAlwayShowEditor option, the inplace editor will always show blank (or the value displayed in cell) . To edit the formula, I have to press ESC follwed by F2 key?I don't understand. Did you already try the new version? I never use goAlwaysShowEditor, even without it you can start typing immediately once the grid is focused. The only important option is goEditing which must be active to allow editing at all. The annoying feature of the LCL grids, however, is, that the left/right arrow keys end edit mode and move to the previous/next cell if the cursor is at the first/last character of the edited cell.
I cannot WriteNumber(r1, c1, 123.45) if cell R1C1 already contain a formula.Should be fixed in current trunk.
I also tried to clear the cell content first by EraseCell method but found that erasecell only take effect after I clicked on another cell. Is this the normal behaviour?EraseCell is a low-level function and does not perform notification. Use DeleteCell instead. I know it is hard to tell which one is the correct method...
EraseCell is a low-level function and does not perform notification. Use DeleteCell instead. I know it is hard to tell which one is the correct method...Thank you master WP for the fix.
The easiest way is to install dcpcrypt via Online Package Manager.Thanks for the link and useful info. After reading and with better understanding of the package, I think I do not need to install crypto.
You only need the crypto package if you want to read Excel 2007 files (exactly this version, not just xlsx in general) in which passwords to remove workbook/worksheet protection are used (http://wiki.lazarus.freepascal.org/FPSpreadsheet#Passwords). It does not open generally encrypted files! So, its use is rather limited.
You may want to read https://forum.lazarus.freepascal.org/index.php/topic,36075.msg240803.html to learn about the background why it is there. I did not put any further work into the encryption thing, thus I am not 100% sure if the information provided is correct.