Category Archives: Java

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


40th birthday – time for a present

Hello All

Today is my 40th birthday. I think this is a good moment for a little present. Today we release the first version of POI 4 XPages. A powerfull extension for the XPages community. I started developing this project in august 2012 focussed on the 4th DeveloperContest. But as a lucky guy, my boss nominated for the position as one of the OpenNTF Directors and they community voted for me.

The contest changed my role in this project. As a director you are not allowed to contribute projects for the contest. But our plans to release our POI 4 XPages extension where still intact. The pressure form the contest was gone,but  some short demonstrations to customers showed us the high value. So some of our team asked: “Why contributing POI 4 XPages – its so powerfull, it’s so handy and it’s such a strong work – Let us sell this piece of work”.

The answer was very simple: You have just given all the reasons, why we should opensource this project!”

So on my 40th birthday I can give the developer community a powerful extension. Generating Word and Excel Files out of XPages was never easier. The groundbreaking work was done by the Apache POI team, our job was to bring this in the XPages Developer Framework.

So I hope that every XPage developer enjoy this.

Download here

1 Comment

Posted by on December 22, 2012 in Domino, Java, XPages


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

JSON Arrray parsing with

I’m currently developing the backendservices for our new absenceplanner. We submit all data to a rest service (CustomRestService in the ExtLib). So this service should be able to parse an array of json data. The data has the following format:

“reqTitle”:”meine wohlverdienten ferien”,

To parse the stream in to a JsonJavaObject I’ve used:

JsonJavaFactory factory = JsonJavaFactory.instanceEx;
Reader r = request.getReader();
json = (JsonJavaObject) JsonParser.fromJson(factory, r);
String strMethod = json.getString(“method”);

It’s very easy to extract all properties. An object can also be accessed with json.getJsonObject(“name”) . But no arrays. There is no getter.

But, how did others (like the ExtLib programmers) solve this? With this question I’ve read the code of the “twitter parsing” in the sbt of the ExtLib. And I found the solution in the class, which guides me to the following code:


Object arrDates = factory.getProperty(json, “dates”);
for (Iterator<Object> itDate = factory.iterateArrayValues(arrDates); itDate.hasNext();) {

JsonJavaObject jsDate = (JsonJavaObject);
//-> here do all the funny stuff with the jsDate object.

Thanks to Philippe Riand (IBM) for sharing your code. Its so inspiring to read your code.


Posted by on September 11, 2012 in Java, XPages


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

Accessing Beans from Java Code in XPages (learned by Reading the SourceCode of the ExtensionLibrary)

Hello Java and XPages Fans

During our work for the myWebGate 2.0 OpenNTF Edition i was faced with a very simple Problem. I had to access a public method in one of our managed beans. This managed beans are designed to act as session facade. So the bean name is directory bean and the scope is session. The ClassName is based on our internal guidlines DirectorySessionFasade.

In this class, is a method called getMySelf() witch gives me some information about my current user in context of the myWebGate Framework. The powerfull thing about managed beans is, that the object exists only one time baed on the scope, in this case one time each session.

For our activtystream implementation I have to acces this getMySelf() methode, because all my relations are stored in the returning object. A short google search doesnt help, but I rember that I’ve ready a pattern in the ExtensionLibrary, that maby can help. An voilaz there it was, written by Phillip Riand, I found some thing that I changed to this for my situation:

public static final String BEAN_NAME = “directoryBean”; //$NON-NLS-1$
public static DirectorySessionFacade get(FacesContext context) {
        DirectorySessionFacade bean = (DirectorySessionFacade)context.getApplication().getVariableResolver().resolveVariable(context, BEAN_NAME);
        return bean;
    public static DirectorySessionFacade get() {
        return get(FacesContext.getCurrentInstance());

This simple codesnipped gives you the capability to execute DirectorySessionFacade.get().getMySelf(). The DirectorySessionFacade.get() Code calls the get( FacesContext context() and there the inmemory Variable directoryBean can be accessed.

What a simple construct to access the same instance in java as in the SSJS and XPAGES. Thanks to IBM for sharing the ExtensionLibrary as OpenSource.


myWebGate Building Process – Dojo und Templates

Das Benutzerverzeichnis ist ein Kernteil des myWebGate Framworks. Um dem Benutzer ein besonderes Erlebnis bei der Bedienung zu bieten, ist das Benutzerverzeichnis als “OnePage” Applikation designet. D.h. wir präsentieren alle Inhalte dynamisch. Die Daten werden via REST API direkt vom Server gelesen und dann on the Fly gerendert.

JSON 2 HTML Renderer gibts wie Sand am Meer. Unser Ehrgeiz war aber, dass wir das Problem mittels DOJO lösen (naja auch einige Lizenzfragen sind dann einfach geklärt). Leider ist es mit DOJO so, wie mit manchem genialen Projekt. Irgendwie hat der Dokumentator frei gehabt. Erschwerend kommt dazu, dass im Gebrauch mit XPages noch ein paar Dinge zu beachten sind.

Also zuerst das Kleingedruckte (oder wie krieg ich so ein Design hin, wie es Rechts zu sehen ist).

  1. Wir haben das HTML Template als PAGE erfasst und dort den Contentype auf HTML gesetzt.
  2. Das HTML Template darf nur 1 Root Element haben, sonst hat DOJO ein Problem
  3. Das neue DOJO Modul muss mittels
    <xp:dojoModulePath url=”biz.webgate.mywebgate.person”
    eingebunden werden.

Tja und dann ist alles ziemlich simpel. Wir erstellen eine Javascript Client -Library, die wir biz.webgate.mywebgate.person.js nenne und schreiben dort folgenden Code rein:

Mittels dojo.provide definieren wir, was dieses File liefert, dojo.require definiert was wir alles brauchen, hier ist dojox.dtl._Templated die Rendering Enginge, die auf django basiert. Im dojo.declare geschieht die Magie. Mittel der cache Anweisung lesen wir das HTML File ein und peron:null erweitert unser Widget um ein neues Objekt.

In der Funktion openPersonTab() fügen wir dann die Person ein. Dabei greifen wir auf ein Spezielles TAB Objekt zu, dass uns das dynamische generieren der Tabs erlaubt. Die Variable xhrArgs definiert den Zugriff auf unser REST Service und lädt von dort das Personen Objekt. Der effektive Aufruf geschicht dann mit dojo.xhrGet(…), aber im Objekt haben wir auch die Verarbeitung des Resultats definiert. load wird dann aufgerufen, wenn wir erfolgreich die Daten abgegriffen haben. Innerhalb dieser Funktion initalisieren wir ein neues Widget und geben die data (das ist das Person Objekt vom Rest Service) direkt an das Widget weiter. dojox.dtl._Template redert dann das Template. Und durch placeAt() hängen wir das Renderresulat an das richtige Ort.

Blicken wir noch in das HTML Template:

Der Zugriff erfolgt über die {{person.____ }} Anweisungen. Wie man aus dem Code erkennen kann sind sowohl IF abfragen, wie auch for Schleifen möglich. Der Sprachumfang von DJANGO wird hier detailiert beschrieben.

Der REST Service liefert übrigens folgenden Code zurück:

Dieses Konstrukt lässt es nun zu, dass wir ohne Rendering Aufgabe für den Server die verschiedensten Personen öffnen:

Der vollständige Code wird im Juni auf openNTF veröffentlicht und ist unter der Apache Lizenz verfügbar.


N-Tier Struktur mit IBM Domino

In der modernen Software Architektur ist das Schichtenmodell (N-Tier) Standard und weit verbreitet. Die Vorteile liegen auf der Hand, da die Trennung von Präsentation, Logik und Datenhaltung auch eine horizontale Entwicklung begünstigt.
Die traditionelle Lotus Notes / Domino Entwicklung hat die Trennung von Präsentation, Logik und Datenhaltung bis jetzt nicht gefördert. Durch das Fast-Prototyping wurden sogar bewusst die Schichten übersprungen und die vertikale Entwicklung, sowie knappe Budgets haben eine saubere Trennung der Schichten stark behindert. Die logische Konsequenz war, dass Domino den Ruf bekam, nicht modern zu sein, keine N-Tier Struktur zu unterstützen. Automatisch wurde somit Domino von Architekten, die N-Tier als Muss-Anforderung definierten, gestrichen. Und dies zu Unrecht. Ich habe während meiner Programmiererzeit schon viele Anwendungen gesehen, die keine N-Tier Struktur besassen, aber auf Plattformen liefen, die als N-Tier Plattformen galten. Genau so habe ich einige Domino Anwendungen gesehen, die das N-Tier Architektur Prinzip passgenau umgesetzt hatten.
Mit dem neuen Relese 8.5.3 geht aber IBM konsequent den Schritt weiter, um diesen Missstand zu bekämpfen. XPages impliziert eigentlich eine N-Tier Struktur. Es ist jetzt nicht nur möglich Applikationen nach den N-Tier Architektur Prinzipien auf zu bauen, nein es ist sogar gewünscht.

Aber wie erreichen wir das? Hier einige Ideen dazu:

1. Verstehen, was Präsentation, Businesslogik und Datenhaltung bedeutet
2. Horizontale Entwicklung fördern
3. Definierte Schnittstellen zum Datenaustausch zwischen den Ebenen nutzen (zb. Präsentation zu Logik via JSON/REST, Logik zu NSF Store via NotesAPI)

Und vor allem darüber sprechen (und zwar mit dem neuen Vocabular)

Leave a comment

Posted by on February 18, 2012 in Domino, Java, XPages


Tags: , ,