user define function

[kjteng]

Is it possible to create user defined function for fpSpread?  If so, whare can I get any sample code or help document on this subject?

[wp]

Read this: http://wiki.lazarus.freepascal.org/FPSpreadsheet#Extending_FPSpreadsheet_by_user-defined_formulas. And there is a sample project in example/other, project "demo_formula_func".

[kjteng]

Thanks for the pointer.  I have managed to create a simple formula and it seems working.  Now I have three more question relating to user defined formula:-

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 ?

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 ?

Thanks in advance.
 

[wp]

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.

[kjteng]

^{... 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?}

BasicaIly what I am trying to make a link to other worksheet in the same workbook.  So I created a formula call LINK(address).For example if a cell A1 in Sheet1 contain the formula Link('Sheet2!A2) + Link('Sheet3!B3), the call back function will sum up the two values from Sheet2!A2 and Sheet3!B3 and then store the result in Sheet1!A1.
The function seems to work but I need know the effect of those parameters (AExcelCode, boReadFormulas...).
 

[wp]

The latest addition to fpspreadsheet trunk will make this obsolete. Now you can enter in formulas references to single cells in other sheets of the same workbook, such as '=Sheet2!A1+Sheet3!B3. Cell ranges are not yet supported. Reading and writing to xlsx and ods are working, You can also write to xls (Excel 8 only) , but you cannot read xls at the moment.

See this demo:

Code: Pascal  [Select][+][-]

1. program project1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. uses
6.   fpspreadsheet, fpstypes, fpsutils, fpsallformats;
7.  
8. const
9.   FILE_NAME_ODS = 'test.ods';
10.   FILE_NAME_XLSX = 'test.xlsx';
11.   FILE_NAME_XLS = 'test.xls';
12. var
13.   book: TsWorkbook;
14.   sheet: TsWorksheet;
15.   cell: PCell;
16.   i: Integer;
17. begin
18.   book := TsWorkbook.Create;
19.   try
20.     book.Options := [boCalcBeforeSaving];
21.  
22.     sheet := book.AddWorksheet('Sheet1');
23.     sheet.WriteFormula(1, 1, 'Sheet2!A3+Sheet3!C1');
24.     sheet := book.AddWorksheet('Sheet2');
25.     sheet.WriteNumber(2, 0, 1.0);
26.     sheet := book.AddWorksheet('Sheet3');
27.     sheet.WriteNumber(0, 2, 2.0);
28.  
29.     book.WriteToFile(FILE_NAME_XLS, true);
30.     book.WriteToFile(FILE_NAME_XLSX, true);
31.     book.WriteToFile(FILE_NAME_ODS, true);
32.   finally
33.     book.Free;
34.   end;
35.  
36.   book := TsWorkbook.Create;
37.   try
38.     book.Options := [boReadFormulas];
39.     book.ReadFromFile(FILE_NAME_XLSX);  // xls not yet working for reading
40.     for i:=0 to book.GetWorksheetCount-1 do
41.     begin
42.       sheet := book.GetWorksheetByIndex(i);
43.       WriteLn('Sheet "' + sheet.Name + '":');
44.       for cell in sheet.Cells do begin
45.         Write('  ', GetCellString(cell^.Row, cell^.Col), ': Value ', sheet.ReadAsText(cell));
46.         if HasFormula(cell) then
47.           Write(', Formula: ', cell^.Formulavalue);
48.         WriteLn;
49.       end;
50.     end;
51.   finally
52.     book.Free;
53.   end;
54.  
55.   ReadLn;
56. end.

[kjteng]

Thanks for the new feature. The following problems were encounter after I download and install the SVN:

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;)
Have I missed out something?

2. I managed to start a simple application from scratch without problem. However 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);

3. 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 ?

[kjteng]

OK the first question in my previous post is solved as follows:
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.

[wp]

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:
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.

What is your Lazarus/FPC version?

[kjteng]

1.8.2

[wp]

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?

[kjteng]

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.

Yes, fpSpread1.pas is just my file.   In this file I have some test procedure which traverse through all the nodes of worksheet.hyperlinks  (i.e. findfirst,  findsuccessor ...)

Please help me on the second and third issues.

[wp]

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:

Code: Pascal  [Select][+][-]

1. var
2.   hyperlink: TsHyperlink;
3.   comment: TsComment;
4. ...
5.   for hyperlink in Worksheet.Hyperlinks do ...
6.   for comment in Worksheet.Comments do ...

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.

But let me finish the file-related stuff first, i.e. reader/writer for xls/Excel 5, support of 3d cell ranges (Sheet1!A1:B3, Sheet1!A1:Sheet2!B3).
Mo

[kjteng]

Thanks. I wasnt aware that
'for... in' is available in free pascal (i came from delphi 5).
Btw please let me know once you have completed the amendment.

[kjteng]

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:

Code: Pascal  [Select][+][-]

1. var
2.   hyperlink: TsHyperlink;
3.   comment: TsComment;
4. ...
5.   for hyperlink in Worksheet.Hyperlinks do ...
6.   for comment in Worksheet.Comments do ...

should the above code be ?
var
  hyperlink: psHyperlink;
  comment: psComment;
 ...