Category Archives: Documents and Spreadsheets – Tutorial

Yes you can….

Today we will have a webinar about POI4XPages. POI4XPages is plugin that enhance and extend the XPages / XWork platform. It gives you the freedom to produce Word and Excel files direct from a application.

We are getting feedback around the globe for the plugin and it’s amazing to hear that developers use POI4XPages to deliver excellent solutions to their customers. POI4XPages is under the Apache V2 License, that means YES YOU CAN ….

  • use it in any project (see this wiki entry
  • build a solution or a product and sell this product
  • getting the source code and extend it
  • taking parts of the source code to solve a problem
  • bundle it to a new set of plugins for the XPages / XWork platform

Bundling POI4XPages to a new set of plugins? Sounds like a good idea! Stay tuned we are thinking about some thing real cool @ OpenNTF. I hope that I can blog about it this week.


Tags: ,

POI4XPages Version 1.2.4 is out

We have released the Version 1.2.4 of the popular POI4XPages framework. The new release contains the following new features:

1. Building Tables in Document:

The document control has a new property called tables. If a table is defined, POI4XPages will replace the existing table on the document with this new tabel. See the example how it works -> docx_document_table.xsp


2. Execute POI Actions

Since version 1.1.6 is the Apache POI API available to the programmers. But in some situations it’s needed to execute Actions with privileged access. To give you the power of this in your hand, we introduce the AbstractPOIPowerAction.

How it works? See the sample file_upload.xsp and the Java classes.

3. Export Views as CSV or Workbook

You have the view designed and need the data as CSV or Workbook? The SimpleViewExport provide this behavior.


Examines the example database. See what you can do with all the controls.

Download the project form OpenNTF.

Visit the install guide and the documentation.

Report bugs and request on GitHub.

And as always… HAVE FUN


POI4XPages 1.1.5 delivers PDF Creation of Documents and computeValue for Row/Column Export

Today we release the version 1.1.5 of POI4XPages. The real big enhancement is the PDF-export for Wordfiles. Thanks to the programmers of docx4j and Apache FOP, we were able to integrate these projects into POI4XPages.

Read also the blogpost about the odyssey of integration I wrote yesterday.

An other enhancement is computeValue for row and column export. Each cell can now be calculated. Have a look at the updated example database to see how to use this.

Have fun and thanks to my team for making this happen.




The odyssey of loading a class in a Eclipse plugin – or how we integrated docx4j in POI4XPages

While I was visiting the IBMConnect in Orlando, Lena investigated about how to convert Word files into PDFs. Because of licence reasons, we decide to use docx4j in conjunction with Apache FOP. Both projects are licensed using the Apache V2 license, in opposition to iText.

Lena built a prototype and tested it with Eclipse against the current JVM of the domino server. All worked fine and we started to integrate docx4j as new plugin project in POI4XPages. We also tested the plugin against a regular java programm. Everything worked fine.

The next step was to integrate the creation of the PDF into the UIDocument and the DocumentGeneration classes. This worked fine as well but when we started the conversion it seemed that docx4j has lost its ‘Focus’. The conversion failed because it could not find ‘’ and ‘’.
After several attempts of building source code based of docx4j and debugging and patching it, I figured out the problem during the night. To make it short, here is the analysis of what had happened:

The problem:

The conversion function in the eclipse plugin was executed by a ClassLoader from Domino. This is not bad at all. But in this case, the context was absolutly relevant to find all the resource files in the docx4j.jar and all the classes. JAXB seems also to be classloading context sensitiv.

The solution:

We changed the ClassLoader context during the PDF conversion as you can see in the code below.
The red marked code does the trick. currentThread.setContextClassLoader( Activator.class.getClassLoader() ) changes the ClassLoader context to the context of the plugin.
When the conversion is done we change the ClassLoader context back to the old context.

public void buildPDF(InputStream isDocument, OutputStream osTarget)
throws PDFException {
Logger logCurrent = LoggerFactory.getLogger(this.getClass().getCanonicalName());
boolean blRC = true;
Exception eRESP = null;
Thread currentThread = Thread.currentThread();
ClassLoader clCurrent = currentThread.getContextClassLoader();“Current thread class loader is: ” +clCurrent);
try {
                    .getClassLoader());“Getting WordprozessingPackage”);
WordprocessingMLPackage wordMLPackage = WordprocessingMLPackage
.load(isDocument);“Getting PdfSettings”);
// 2) Prepare Pdf settings
PdfSettings pdfSettings = new PdfSettings();

// 3) Convert WordprocessingMLPackage to Pdf“Getting PdfConversion”);
PdfConversion converter = new Conversion(wordMLPackage);“do Conversion”);
converter.output(osTarget, pdfSettings);
} catch (Exception e) {
eRESP = e;
logCurrent.log(Level.SEVERE, “Error during PDF Conversion: “+e.getMessage(),e);
blRC = false;
} finally {
if (!blRC) {
throw new PDFException(“Error during FOP PDF Generation”, eRESP);

A happy ending to a long odyssey.
The new release of POI4XPages will be released later this week, together with some bug fixes and new features.


Tags: , ,

New Documentation for POI 4 XPAGES available

Today we released some howtos for POI 4 XPAGES on

Thanks Lena for sharing this with us.


Tags: ,

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