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

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


Documents and Spreadsheets with XPages – building the kernel (Part II)

Let us begin with building the kernel for the document processing. This kernel is also a part, which we have used as proof of concept. In the previous post ( our focus was on the idea. This entry is about the main processing.

If you want to try this in your own project, please perform the following steps:

  1. Download Eclipse Indigo

  2. Download Apache POI 3.8

  3. Prepare a project based on the following instructions of René Winkelmeyer: (starts at page 23)

The first step is, create a “lib” directory in your project and add the apache-poi libraries to this directory.

Add the libraries to the build-path (without the commons-log and log4j), by selecting the libraries: Left click and select “Build-Path/add to build-path”.

Commons-log and log4j are not needed because the extensible API delivers his own implementation of log4j. If you need a dedicated version of those libraries, it is recommended to encapsulate the code in a separate plugin, which exports only the functionality to the plugin, which extends the api. Both plugins have to be in the same feature (we will come back to this later).

We can now build an interface, witch represents the bookmarks.


public interface IDocumentBookmark {

    public String getName();

    public String getValue();


Programming against an interface will definitely safe time. While I was building the prototype of this application for a proof of concept, I had to build my own implementation of IDocumentBookmark, because it was not possible to solve all dependencies of the Xpages Framework. See the difference. The first sample is my prototype implementation, the second one is my library implementation:

1. Prototype implementation


public class BMImplementation implements IDocumentBookmark {

private String m_Name;
private String m_Value;

public BMImplementation(String name, String value) {

m_Name = name;
m_Value = value;



public String getName() {




public String getValue() {

return m_Value;



2. Library implementation:


import javax.faces.context.FacesContext;
import javax.faces.el.ValueBinding;

public class DocumentBookmark extends ValueBindingObjectImpl implements IDocumentBookmark {

private String m_Name;
private String m_Value;

public String getName() {

if (m_Name != null) {

return m_Name;


ValueBinding vb = getValueBinding(“name”);

if (vb != null) {

return (String) vb.getValue(getFacesContext());


return null;


public void setName(String name) {

m_Name = name;


public String getValue() {

if (m_Value != null) {

return m_Value;


ValueBinding vb = getValueBinding(“value”);

if (vb != null) {

return (String) vb.getValue(getFacesContext());


return null;


public void setValue(String value) {

m_Value = value;



public void restoreState(FacesContext context, Object value) {

Object[] state = (Object[]) value;
super.restoreState(context, state[0]);
m_Name = (String) state[1];
m_Value = (String) state[2];



public Object saveState(FacesContext context) {

Object[] state = new Object[3];
state[0] = super.saveState(context);
state[1] = m_Name;
state[2] = m_Value;
return state;



Now let’s build the kernel. We do a simple 3 step approach.

  1. Building a XWPFDocument (from a InputStream)

  2. Replacing all Bookmarks in the document

  3. Writing the XWPFDocument to a ByteArrayBuffer

Here is the code to build a XWPFDocument

public XWPFDocument getDocument(InputStream inDocument) {

try {

XWPFDocument dxReturn = new XWPFDocument(inDocument);
return dxReturn;

} catch (Exception e) {


return null;


The bookmark replacement is little bit harder. Let’s begin with the main entry:

publicint processBookmarks2Document(XWPFDocument dxProcess, List<IDocumentBookmark> arrBookmarks) {

// First Prozessing all paragraphs.

for (XWPFParagraph paraCurrent : dxProcess.getParagraphs()) {

processBookmarks2Paragraph(arrBookmarks, paraCurrent);


// All Tables

for (XWPFTable tabCurrent : dxProcess.getTables()) {

processBookmarks2Table(arrBookmarks, tabCurrent);


// All Headers

for (XWPFHeader headCurrent : dxProcess.getHeaderList()) {

for (XWPFParagraph paraCurrent : headCurrent.getParagraphs()) {

processBookmarks2Paragraph(arrBookmarks, paraCurrent);


for (XWPFTable tabCurrent : headCurrent.getTables()) {

processBookmarks2Table(arrBookmarks, tabCurrent);



// All Footers

for (XWPFFooter footCurrent : dxProcess.getFooterList()) {

for (XWPFParagraph paraCurrent : footCurrent.getParagraphs()) {

processBookmarks2Paragraph(arrBookmarks, paraCurrent);


for (XWPFTable tabCurrent : footCurrent.getTables()) {

processBookmarks2Table(arrBookmarks, tabCurrent);



return 1;


First we process all paragraphs in the document, followed by all tables. Then we do the same with the headers and footers. They do also contains tables and paragraphs. So let’s see how we process the paragraphs:

private void processBookmarks2Paragraph( List<IDocumentBookmark> arrBookmarks, XWPFParagraph paraCurrent) {

for (XWPFRun runCurrent : paraCurrent.getRuns()) {
processBookmarks2Run(runCurrent, arrBookmarks);


Paragraphs contain “Run” elements, which contain a text, that has the same styling and formatting. These run elements are processed in the following function:

public int processBookmarks2Run(XWPFRun runCurrent, List<IDocumentBookmark> arrBookmarks) {

String strText = runCurrent.getText(0);

if (strText != null) {

for (IDocumentBookmark bmCurrent : arrBookmarks) {

String strValue = bmCurrent.getValue();
strValue = strValue == null ? “” : strValue;

if (bmCurrent.getName() != null) {

strText = strText.replaceAll(“<<“ + bmCurrent.getName()+ “>>”, strValue);




runCurrent.setText(strText, 0);
return 1;


Our tables also contain paragraphs and run elements. We will also reuse this method. Tables are built on rows and cells, see how we browse that:

private void processBookmarks2Table(List<IDocumentBookmark> arrBookmarks, XWPFTable tabCurrent) {

for (XWPFTableRow tabRow : tabCurrent.getRows()) {

for (XWPFTableCell tabCell : tabRow.getTableCells()) {

for (XWPFParagraph paraCurrent : tabCell.getParagraphs()) {

processBookmarks2Paragraph(arrBookmarks, paraCurrent);





That’s all you need for the kernel. The full class will be available with the source code later this year. The next step is to build the UI for the domino designer. Watch out for the next episode.


Tags: , , , ,

Documents and Spreadsheets with XPages – the Idea (Part I)

In several projects we are faced with the request to export data to a spreadsheet (mostly excel) or build a document for ms-word. So our development team requested the following: “Build an extension that helps us generate word documents or export datasets to spreadsheets”.
After some research, I found a project called Apache POI.

Apache POI covers all the stories, which we try to implement. So let’s begin with the first story: “Generating a new ms-word document”.
The story is very simple, but also very powerful. In our example the starting point is a document like the OpenNTF Contributor License Agreement. This document is needed for a company to contribute code to OpenNTF. Typically, this document has some lines (fields), which you have to fill in with your values.  Let’s try to automate this process. We will build a form in the XPages application, where you can fill in your company name, address and other things. Then we modify the standard OpenNTF form a little bit, as you can see on the screenshot below:

In your XPages form we implement the new POI Document element and then we do some wiring:

  1. We define where the modified OpenNTF Licence Agreement is. This could be a file resource in the application, or a document accessible via view and a predefined key.
  2. Defining “Bookmarks”:
    As you see on the word document, we have defined several “<<NAME OF BOOKMARK>>” tags in the text. This text must be in the same style, so they are in the same “run” Element on the document.
  3. In our “POI Document” – Control could we now define a bookmark for each <<…>> element, which contains the name and a value.
  4. As you see, the value can be computed (so a binding to a viewScope variable is possible)
  5. We define a “download” button and connect the onClick event on a new action called “Generate Document”

The user can now fill in their values on the page and generate a customized word document.  Developers are able to build in applications to produce customized documents in a very short time.
In the next blog entries, we will show you how we have build an extension of this function.


Tags: , , , ,