Currently only XLS files (MS Excel 97-2004 format) are supported
This addon is packaged with a demo space that you can import into your Confluence wiki for easier learning curve. It demonstrates most of the features available in the addon.
This sample report is a live POI Form macro demo, but the file is locked for editing. However you can still download a Sample MS Excel attachment used by this macro for review.
Please wait for the POI Form to load...
POI Forms supports 'collaborative authoring' allowing multiple users to read and modify the same Excel file. It uses so-called 'short polling' when changes made by one user are propagated to other users within 3 seconds.
There are some restrictions that prevent using websockets the way Confluence does in Synchrony while long polling imposes a burden of locking valuable server resources. Thus short polling every 3 seconds is the only available option so far. This may change in future.
To add new rows to your report you would use an 'Add Row' hyperlink. Creating an 'Add Row' link in your MS Excel file is a three-step process:
- select a source row to copy from and give it a name (note the top left field on the screenshot below).You may also use a set of rows, but it needs to be a continuous set of rows
- place a hyperlink on your worksheet with address
#?AddRows(<row name goes here>), for example #?AddRows(ROW_REVENUES_OTHER). Use 'Add Row' (or whatever you find appropriate) as a text to display
- hide the source row (rows) to keep you POI form tidy
In Confluence clicking the 'Add Row' link will copy the source row (rows) and insert it right before the clicked link. It automatically fixes affected Excel named areas and recalculates formulae depending on the added rows.
Creating autocomplete from Excel strings is also a three-step process:
- place a list of values on reference data page and give it a name (again, use top left field in MS Excel – see the screenshot below)
- reference the list by placing a hyperlink on your worksheet with address
#?AutoComplete(<name goes here>), for example #?AutoComplete(REF_REVENUES_OTHERS). Put a placeholder text as a 'text to display'
- apply normal style to the cell with a hyperlink (by default it will be blue underline, which is probably not what you would like it to be)
You define the function in the body of your macro, for example:
The function takes two parameters: 'text' is the text entered by a user, and 'callback' is a callback that processes a list of values that you function produces.
Please wait for the POI Form to load...
Placeholders are commonly used in HTML forms. To get this feature working in POI Forms add the following hyperlink to a cell:
#?Placeholder. Put your placeholder as 'text to display' and there you are.
Another important point to keep in mind: to let Apache POI recognise your UDF it requires the function to be defined in VBA first, whether you are going to use it or not. If you don't define the function than POI will consider expression
Downloading and Uploading Files
It is possible to download Excel attachment to edit it using MS Excel and upload back to Confluence page. The addon will detect a new file version and switch to a new file. Of course the Add Row or Reference Data functionality will not be available in MS Excel, but all normal functionality will be available. The addon caches MS Excel files in memory for faster access and more responsive edit. All changes made to Excel file using POI Form will be serialised to appropriate attachment in about a minute after the edit. Please keep this in mind when you download your Excel attachment.
If you want to protect certain cells in your POI Form from editing do as follows:
- enable cell protection directly or via style in MS Excel
- enable protection in POI Form macro
To control the amount of space occupied by cached Excel files, or to import a demo space into your Confluence use the Addon's Configuration page:
The addon provides JMX interface to cached Excel files which you may use to force serialization or cleanup:
To report a bug please use the Issue Tracker