Page tree
Skip to end of metadata
Go to start of metadata

POI Forms is a handy addon to integrate MS Excel and Confluence to enable shared authoring of MS Excel files, collect complex reports width totals, formulas, and user functions. It provides options to integrate with Confluence via REST API or using server-side javascript (known as  Nashorn). POI forms uses  Apache POI  to process and recalculate MS Excel files.

(warning) Currently only XLS files (MS Excel 97-2004 format) are supported

Demo Space

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.

Sample Report

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.

(info) 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.


Add Row

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.

Autocomplete

POI Forms provides autocomplete in two versions: using lists of strings from Excel workbook and using custom javascript.

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)


Custom javascript in autocomplete uses the same #?AutoComplete(...) hyperlink, but in place of Excel named range you will provide a name of a javascript function, for example: #?AutoComplete(javascript:pages)

You define the function in the body of your macro, for example:

pages: function(text,callback){
  $.ajax({
    url: AJS.contextPath() + '/rest/api/content/search',
    data: {
      cql: 'type="page" and title~"' + text + '"',
      limit: 10
    },
    type: 'GET',
    dataType: 'json'
  }).done(function(data){
    var _data = [];
    data.results.sort(function(a,b){return a.title.localeCompare(b.title);}).forEach(function(rec){
      _data.push(rec.title);
    });
    callback(_data);
  }).fail(function(jqxhr,textStatus,errorThrown){
    console.log('poi-forms', jqxhr.responseText);
  });
} 

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

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.

User-Defined Functions

POI Forms supports user-defined functions (UDF). Apache POI does not provide for VBA, so VBA UDFs should be refactored in javascript. For example, take a look at the following VBA function:

Function SayHello(who As String)
    SayHello = "Hey, " & who & ". Hello from VBA!"
End Function

An equivalent code in javascript, consumable by POI Forms, will be

SayHello: function(name){
  return "Hey, " + name + ". Hello from Javascrtipt!";
}

(info) Javascript code is executed on server and uses Nashorn scripting engine. This engine supports many extensions that may not be compatible with browser-side javascript. This may cause errors in Confluence GUI which will prevent Confluence page from loading correctly. Thus caution should be taken when programming UDFs.

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 =SayHello(...) as a name reference, not a function call, and recalculation will fail. Thus every javascript UDF should have its prototype in VBA.

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.

Protecting Cells

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

Administration

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


  • No labels