Skip to main content

POI Spreadsheet Manager - Part 1

I've been working on this project for about two years off and on now. Some parts of it shaped up early on, while others have undergone significant refactoring. Most of my reports are in Excel, but some few are done in Word. So I started with a class that accepts any POI XML document and a file name, and sends that file to the browser.


This is pretty straightforward, but is the black magic behind delivering the actual spreadsheet through the browser.

package itd.poi;

import itd.extlib.utils.jsf.JSFUtils;

import java.io.OutputStream;
import java.io.Serializable;

import javax.faces.context.FacesContext;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;

import com.ibm.xsp.webapp.XspHttpServletResponse;

/*
 * This is a bridge between an XAgent and a PoiXmlDocument. It handles writing
 * the document back to the browser.
 */
public abstract class PoiBase implements Serializable {
    protected static final long serialVersionUID = 1L;

    protected static void write(POIXMLDocument poiXmlDoc, String filename) {
        // Create variables
        FacesContext facesContext = FacesContext.getCurrentInstance();
        XspHttpServletResponse response = JSFUtils.getResponse();
        OutputStream pageOut = null;

        try {
            pageOut = response.getOutputStream();
            if (poiXmlDoc instanceof XWPFDocument) {
                response.setContentType("application/vnd.openxmlformats-officedocument.wordprocessingml.document");
            } else if (poiXmlDoc instanceof XSSFWorkbook) {
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            }
            response.setHeader("Cache-Control", "no-cache");
            response.setHeader("Content-Disposition", "attachment; filename=" + filename);

            poiXmlDoc.write(pageOut);
            pageOut.flush();
            pageOut.close();
        } catch (Exception e) {
            throw new RuntimeException("Error generating dynamic XML document: " + e.getMessage());
        } finally {
            facesContext.responseComplete();
        }
    }

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