Monthly Archives: January 2013

POI 4 XPages Version 1.1.0 released!

Hi All

Only a few hours and I will travel with Roman, Peter and Andre to the IBMConnect2013. The last years “LotusSphere 2012” (the old name of the IBMConnect) was a game changer in my life. I meet great people like Niklas Heidloff, Philippe Riand, Martin Donnelly, Dan O’Conner and many more.

But these guys have inspired me to build myWebGate and POI 4 XPages.

We have greate news about both projects, but this post ist about POI 4 XPages. We have made some progresses on the project that we would like to share in the version 1.1.0:

  1. The POI 4 XPages supports now the version 3.9 of the brilliant Apache POI Project, which is the newest release
  2. We fixed some typos and a bug in the creation of Workbooks. If you try to export data to a sheet wich doesn’t exist on the exceltemplate and you choose “create” = “false”, the export fails. This was not the idea behind the “create”=”false” option. Now the export definition with this settings will be ignored, if the sheet doesn’t exist. (Marco Baumann has reported this issue at the
  3. We implemented the POI  – ListObjectDataSource which is designed to export the java.util.List<?> object in version 1.0.1 but we did make a mistake in the value definition. This values have to be assigned with a SSJS function, but our binding was against a value property. We fixed this with a new “MethodBinding” assignment (like the createObject function from the ObjectDataSource in the ExtLib) called “buildValues”. This means that “values” is deprecated and only for backwards compatibilty available
  4. With version 1.1.0 we introduce a brand new feature in the workbook and document controll. A binding which we called postGenerationProcess. This feature is really powerful, let me explain why.

postGenerationProcess – all power of apache poi in your hand:

POI 4 XPages build your Workbook or Document with the “generate….” function. But when the action is started, you don’t have access to the Workbook or Document object. “postGenerationProcess” change this. In the postGenerationProcess your SSJS / Java Code will be executed, right before the Workbook or Document is transmitted from the server to the client.

The follwowing code in the postGenerationProcess prints the name of the first sheet on a workbook to the server console:

print( workbook.getSheetAt(0).getSheetName() );

While we are calling the postGenerationProcess, the POI 4 XPages Code assign the current workbook to the variable workbook, or the current document to the variable xwpfdocument. This variable are representation of the the following apache poi classes:

workbook: -> javadoc

xwpfdocument: org.apache.poi.xwpf.usermodell.XWPFDocument -> javadoc

Imagine what you can do now, afert a document or a workbook is created!

Have Fun

Download POI4XPages here


How to export a view to excel with POI4XPAGES

Our scenario:

You have built a beautiful web application where you list all your contacts on a page.  Although the contacts can be accessed online some users request an export to excel:


To fullfill this request do the following steps:

  1. Download POI4Xpages
  2. Install the Extension on your Server and Designer Client

Let’s begin with some coding:

1. Prepare your Excel file and a export view.


We have highlighted several things on the screenshot.

  • <<user>> <<date> are placehoders. They will be replaced with some calculated values
  • Contacts is the name of the spreadsheet
  • The export should start on row 5

Our export view looks like this. Keep the column titles in mind, as we are going to use them for the column definition of our export:


Save your Excel template and insert it as a FileResource to the appliction


2. Open the XPage on which the export should be performed.

Add the ‘Poi Workbook’ control to the Page


Select the POI Workbook control and switch to the properties panel. It’s time to configure the export.


  • downloadFileName =specifies the name of the created file

Add a templateSource to the element. The template source defines which excel file will be used. You can choose between “resourcetemplate” (a resourcefile) or “attachmenttemplate” (a attachment in a document from a defined database).

In this example we use ‘resourcetemplate’


  • databaseName: The database where the resource file is saved, empty means the current database
  • filename: The name of the resource file

As a next step we define the spreadsheet. The spreadsheet represents a sheet in a workbook:


  • name: The name of the spreadsheet on the workbook
  • create: if yes is selected, the spreadsheet will be created if it doesn’t exist. Otherwise the spreadsheet definition will be ignored.

It’s time to define some cellValues. Remember the <<user>> and <<date>> values on the sheet. We will now set the values for these fields:


With the definition of the cellBookmark all the <<user>> and <<date>> tags on the spreadsheet (not within the whole workbook) will be replaced with the specified values during the export.
Be aware that you have to write ‘user’ instead of ‘<<user>>’ for the cellBookmark name.

So, the configuration of the excel file is done and we can now proceed to export our view. Therefore we define an ‘exportDefinition’


We can choose between data2rowexport and data2columnexport which represents the export direction.

  • startRow: 0 based. Defines the row where the export should start
  • stepSize: 1 or higher. It’s possible to export a dataset to 1, 2 or more rows. (see columns for how to assign an other row)

Assign the dataSource:


  • database: define the database which has to be used, empty means the current database
  • key: works like getAllDocumentsByKey
  • maxRow: the maximum (currently 1’500 entries) of entries to export
  • search: a fulltext search query
  • viewName: the name of the view to export

Assign the columns:


Each column needs a columnDefinition.

  • columnNumber: the number of the column (0 based)
  • columnTitle:he title of the column in the datasource (if you use a ListObjectDataSource the columnTitle will be invoked with “get<NAME>” on the object in the list)
  • rowShift:this defines how many rows down the value should be shifted (multiple row export per dataset)

Now everything is defined. Let us export the excel file by button. The button needs an “onClick()” eventhandler like this:


Choose “Generate Workbook” and select the ID of workbook definition.

Test it and have fun!


Tags: , , , , ,

POI 4 XPages is under the apache 2 licence available

Yesterday I received an email from our IP Manager Peter Tanner, which confirmed that POI 4 XPages is ready for the openNTF apache 2 licence catalog.

A simple message I was very excited about. Why, you may think. Here some thougths about this simple mail:

  1. Its a huge and important work:
    Peter scans every work we submit on openNTF to ensure that every content is legal and that the right people get credit fo their work.
  2. OpenSource is a contract of trust:
    As a developer of open source software, you open your “heart” and share your work with others. Your expectation is honor and respect for your work. But this works only, if you give other developers credit for their work as well.
  3. Understanding how all this different licence type can interact and work together is very hard.

Thanks Peter for your work. Your work is very important.

The apache 2 licences gives us the ability to use and reuse code and make business work.

So use POI 4 XPages in your projects, build applications and bundle POI 4 XPages in your projects. We have tried to make the installation as easy as the extlib can be installed and and we believe that you customers will be excited, when they see your output which you now can generate so easy.

And please give us feedback.

Download POI 4 XPages here