Bug 50587

Summary: User Defined Function Documentation/Example
Product: POI Reporter: Jon Svede <jsvede>
Component: HSSFAssignee: POI Developers List <dev>
Severity: enhancement    
Priority: P2    
Version: 3.8-dev   
Target Milestone: ---   
Hardware: All   
OS: All   
Attachments: A patch containing the documentation, images and code samples.
archive that contains the new files
updated patch
new patch file with changes to documentation text and code samples
the actual patch for the previous submission

Description Jon Svede 2011-01-14 13:32:52 UTC
Created attachment 26490 [details]
A patch containing the documentation, images and code samples.

Attached you will find some documentation and a small sample of a User Defined Function.  The documentation follows the sample code.
Comment 1 Yegor Kozlov 2011-01-16 14:06:58 UTC
The images and .xls files are missing. Please re-attach.

Comment 2 Jon Svede 2011-01-17 01:09:04 UTC
Forgive me, but do these get attached to this ticket or are they somehow included in the patch?


Comment 3 Yegor Kozlov 2011-01-17 05:26:00 UTC
The attached patch only contains textual changes and does not include binary data.  You need to put all binary files (images, .xls, etc.) in a .zip archive and attach it to this ticket. 

Some useful commands how to capture new files can be found here:

See the "Submitting Patches" section.

Comment 4 Jon Svede 2011-01-17 09:46:51 UTC
Created attachment 26499 [details]
archive that contains the new files

Sorry for that, I misread the docs.  Attached is an archive file with all the new .xml, .xls, .jpg and .java files.


Comment 5 Yegor Kozlov 2011-01-18 10:18:40 UTC
I made some tweaks, please review the updated patch.

Summary of my changes:

(1) added ASF licence header to CalculateMortgage.java and UserDefinedFunctionExample.java. This is a must in Apache projects.
(2) repackaged CalculateMortgage.java and UserDefinedFunctionExample.java from poi.tests to org.apache.poi.ss.examples.formula
(3) removed @Override from implemented interfaces, this is a JDK-1.6 feature. POI's language level is JDK-1.5 and it supports @Override only in sub-classes, not in interface implementatations. 
(4) Removed explicit instantiation of HSSFFormulaEvaluator. POI can work with UDFs in terms of common Spreadsheet interfaces:

  UDFFinder udfToolpack = new DefaultUDFFinder( functionNames, functionImpls ) ;

  // register the user-defined function in the workbook

  FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

The code above transparently works with HSSF and XSSF. 

Other than that, very cool! Thanks for the excellent patch.

The compiled documentation is available here:


If there are no questions / objections, I will apply it later this week.

Comment 6 Yegor Kozlov 2011-01-18 10:19:16 UTC
Created attachment 26507 [details]
updated patch
Comment 7 Jon Svede 2011-01-18 11:56:12 UTC
Created attachment 26508 [details]
new patch file with changes to documentation text and code samples

Thanks, Yegor!  Sorry for the silly packaging mistakes.

I am adding some small changes:

(1) added a missing word in the opening sentence of "Defining You Function".
(2) changed some example code: there was an in correct snipped in the CalculateMortgage file.
(3) removed superfluous commented out code and System.out lines.
(4) added class comments and changed the @author tags appropriately.

Otherwise it's great.  I look forward to seeing it in the next release!

Comment 8 Jon Svede 2011-01-18 11:58:33 UTC
Created attachment 26509 [details]
the actual patch for the previous submission

This is the actual patch for the previous attachment.
Comment 9 Yegor Kozlov 2011-01-19 04:22:48 UTC
Applied in r1060724