December 2013 – LinQed

Adding an export to Excel used to be a pain in the old days. But now, with XPages and the highly recommended POI4XPages library*, that has gotten really easy and is a feature I can just throw in to any project. And yes I know: spreadsheets… But fact is that a lot of the people I work with are still very much Excel oriented: so why not service them?

I had a requirement the past week to add some calculations on a generated Excel file: creating totals and hiding cells based on others. I thought that was dead easy: add some formulas to the template and done. Exported some data and… nothing. The formulas were there in the spreadsheet, but it didn’t recalculate them based on the data. Sure, I could recalculate it manually after the sheet is opened, but that’s just annoying. Changing the calucation settings in Excel also didn’t fix that.

So I decided to look at the POI API docs and found a method called setForceFormulaRecalculation. That looked like it would do what I needed. As of version 1.1. POI4Xpages comes with a binding called postGenerationProcess: a very powerful feature that gives you access to the generated workbook object, right before it is send to the browser. Only thing I needed to do was to add this snippet to that binding and the formulas were calculated:

workbook.setForceFormulaRecalculation(true);

Import Excel files

Besides creating them, Apache POI is able to read Excel files too. Since POI4XPages is build on top of Apache POI, I was wondering if I’m able to read Excel files in XPages too, with only having the POI4XPages library installed on my Domino server. I only have a requirement for the Office Open XML format (.xslx) that’s supported since Excel 2007.

And guess what: it worked right out of the box. Only thing you need is piece of Java code:

File excel = new File ("C:/spreadsheet.xlsx");
fis = new FileInputStream(excel);</code>

XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet ws = wb.getSheetAt(0);

int rowNum = ws.getLastRowNum() + 1;
int colNum = ws.getRow(0).getLastCellNum();
String [][] data = new String [rowNum] [colNum];

for (int i = 0; i &lt;rowNum; i++) {
  XSSFRow row = ws.getRow(i);
  for (int j = 0; j &lt; colNum; j++) {
    XSSFCell cell = row.getCell(j);
    String value = cell.toString();
    data[i][j] = value;
    System.out.println("Value of cell in row " + 
      i + ", col " + j + ": " + value);
  }
}

fis.close();

Here’s what this code is doing:

I’m creating a FileInputStream based on a file on my file system. Of course I can also use a document attachment for that using the getInputStream() method of the EmbeddedObject class. With that FileInputStream I create a new POI XSSFWorkbook and then retrieve the first worksheet. The XSSFWorkbook also has a method to retrieve a worksheet by name. I then get the number of rows and columns and loop through them.

* POI4XPages is part of the OpenNTF Essentials. Check it out if you haven’t!

Month: December 2013

Import Excel files and calculations with POI 4 XPages

Import Excel files