Skip to main content

"Universal" POI spreadsheet

By

Cracked a nut today. I currently have some reports in XPages that export to Excel, but it is completely separate code for what is seen in the browser vs. what is in the spreadsheet. This is terrible for a number of reasons, but the main two being discrepancies and the length of time it takes to generate a spreadsheet after you've already waited for the report.

Ideally, you could use the same data source for the XPage and the spreadsheet. But that turns out to be more difficult than it seems at first glance. The view wants to be fed a list of row data, but POI needs a lot of help knowing what to do with that data.

So today I finished a POI wrapper that accepts List<List<Object>>, applies custom formatting without generating a ton of duplicate Fonts and CellStyles, and automatically sums numeric data. It aligns headers with data, and allows me to specify custom formatting that is universal for the application.

More to come...

Comments

Post a Comment

Popular posts from this blog

Pass data between XPages and existing LS scripts

By
I'm working on modernizing a fairly hefty application with a lot of existing script libraries which we want to leverage within the XPages environment. Here is a technique that works very well. First, create an in-memory document in SSJS. We can set any input values needed for the back end. Then we pass that document to a LS Agent which can work it's magic using the values set in SSJS and use the same document to return values back to the XPage. Here is how it works in detail:
6 comments
Read more

Rows per page selection: Part 1

By
I was asked to create a control that would allow users to select the number of rows per page in a view/repeat control (the application uses both). It seemed simple at first, but I ran into a few issues that I thought I'd share the solutions to. First, lets start at the beginning. I went through the relevant design elements and set row="#{viewScope.tableRows}" , and I created an xp:comboBox with value="#{viewScope.tableRows}" and added items for 20, 30, 50, and 100, and I assigned it an onChange event handler that did a partial execution and partial refresh of a div containing the combo box, pager and the table. Then I started fixing all the problems. Problem 1: The combobox value was a string, but the rows parameter requires an integer. This was causing IllegalArgumentException / java.lang.String incompatible with java.lang.Integer. I added a NumberConverter, but this only slightly changed the exception message to java.lang.Long incompatible with java.lang....
Post a Comment
Read more

Quick tip: Convert a number to String in EL

By
I just had a need to do this and a Google search didn't immediately turn up a solution. So I thought for a couple of minutes and came up with this: value="0#{numberVar}" This takes advantage of the way Java auto-converts objects to strings when doing a concatenation. So if your number is 13, Java EL turns this into new String("0"+13), which becomes "013". You can then strip off the leading zero or just parse the string back into a number.
Post a Comment
Read more