Skip to main content

"Universal" POI spreadsheet

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

Popular posts from this blog

Rows per page selection: Part 1

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....

Pass data between XPages and existing LS scripts

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:

XPages Application Framework (Part 1?)

Note: I changed projects and priorities after my last POI article, resulting in a long hiatus. I anticipate using that framework on my current project and will likely refine and complete my related article series. For the past several months, I've been fortunate enough to lead a project overhaul from traditional Domino Webapp to XPages. I had a few goals in mind, but the top three were integrating Bootstrap, embracing MVC principles, and eliminating all SSJS . I imagine the motivation for Bootstrap integration is self-explanatory. There is an OpenNTF project out there called Bootstrap4XPages. I didn't use that for a number of reasons - the main being a policy in the current environment. But you may ask yourself (if you didn't follow that link), why so much hatred for SSJS? I have a litany of reasons. It impacts readability of the XPage source; it hurts maintainability when logic is scattered across dozens of XPages, custom controls, and script libraries; it mixes log...