Exporting from #XPages to Excel without Excel, Part 1

As much as I’d love my users to simply access their data in my Notes databases, I know they’re going to need to export it. Mostly, they want to be able to manipulate it to analyze it, but they also want to create static reports and to send those reports to external parties that we don’t want accessing our data live. In old Notes, I could force train people to use the export functions of Notes, or with the advent of Copy As Table, scream at teach them to cut-and-paste. Back when I was at a government agency, helping them email their order via spreadsheet from an outside vendor in a semi-automated process, it required the user to have Excel on their machine (initially, only specific versions of Excel, but I learned to get beyond that). Fortunately, Russ Maher taught me that you don’t have to do it that way in his talk Extending Your XPages Applications with Java at AdminDev 2012.

My favorite part of his talk, since I’d done the aforementioned Notes-to-Excel export, was when he talked about creating Excel files even if the user didn’t have Excel. Apache POI is a Java API for Microsoft documents (download page for the JAR files) and by loading the JAR file into your Notes databases, you can use all of the functionality in your XPages applications. Thus, I can use my Motorola XOOM tablet to access the project database, select the inventory report, generate the Excel file and download it directly to my tablet without having Excel on the tablet. I showed the result to office mate and he came up with the inventory idea — no need to print out the inventory and you can make notes on your tablet as you walk around. Heck, I’m sure I could also write something that would process changes back into the database there as well, but that remains for a future date.

Architectural Information

Before we plunge into the code, let me first explain the architecture and use case involved here. Each of our projects around the world uses one of our applications, which we call TAMIS. In the past, this meant one Notes database for each project,  with perhaps 70 projects active at a time. Each database would start with the same design and then be customized for the project. This has usually meant design changes rather than just configuration, but both methods are used. So, there will be some configuration that is done by local staff and some by the development team. Configuration of these Excel reports is something that has always been done by the development team.

In the XPages version our application, there are actually four databases: Shared Resources, Main, Attachments and Workflow. The design work was done by Scott Good’s folks at Teamwork Solutions, so it bears many marks of their Process It! workflow engine. Shared Resources contains all the XPage design elements, configuration documents and some general information. Main is where the data goes. Attachments is obvious, but Workflow not as much – it contains both the workflow configuration and the workflow tracking documents.

Given all of that, we will have our code in Shared Resources and our data over in Main. Thus, we want to launch from one database, grab data from another and return it all to the user as a seamless download that pops into his machine just by clicking one button on our XPage.

So, how do we do this?

Add JAR to database

Well, first thing to do is to put the JAR file into your Notes database. I’m still on 8.5.3, so I have to put it in \WEB-INF\lib, while you can bring it in directly as a design element in Notes 9. As you can see in the image here, I’m using version 3.6 of the JAR file, despite the fact that 3.9 is the current release, but I don’t really mind being behind the times a little bit, as long as it works. It looks like 3.7 and 3.8 added some fixes for handling dates and numbers better, so I will probably upgrade after our next pilot rolls out. We have seen occasional issues with dates, being a very international company.

Create the Configuration Form

The was arguably my favorite part of the process. Well, until it actually started spitting out spreadsheets. Why did I enjoy it so much? Is it because it’s ‘old Notes’? I don’t think so. While the form did build off the keyword documents that my old development mentors (Elvis Lezcano and John Mirza) created back in the 1990s when we were all at Exxon-Mobil, the neat part was utilizing some of the knowledge I’d gained about navigating design elements in simple, front-end LotusScript. Basically, the user selects the view (via name or alias) and then can choose the columns right from the view design rather than requiring any pre-configuration by me. While I do need to work on the configuration form to add more choices about sorting and styling, I got pretty happy with the results.

The configuration form supplies the view name, the column headers and numbers, and identifies which columns to total. This gets used by the Export Stream Library when the user initiates the export.

Create your Export XPage

This was the simplest design element, though also, since I’m still so new to XPages, the most challenging. With the configuration document, I was working with LotusScript, so the new wrinkles were just fun extensions of my knowledge. The Export Stream Library was initially just something I took wholesale from Russ’ presentation. While I did start with Russ’ XPage, I ended up, because of the configuration document and the UI things I wanted to do, making myself jump through a few hurdles. Worse yet, when I went to move it from it’s original implementation, I stumbled several times on the fact that the button was…. disabled. Nothing like clicking and checking your code 10 different ways and then examining it using ‘Inspect Element’ to realize the problem is that the disabled attribute simply computes as true every time.

Our Export Xpage allows the user to select from all of the report configurations we’ve created and provide both the filename (to which it will append .xls) and the name for the worksheet. I’m sure that it wouldn’t take much additional work to export multiple worksheets into a single file for a more complex and useful product, but we’re starting pretty simple.

<?xml version="1.0" encoding="UTF-8"?>
<xp:view xmlns:xp="http://www.ibm.com/xsp/core"
	xmlns:xp_1="http://www.ibm.com/xsp/coreex">
	<xp:this.resources>
		<xp:script src="/CreateExcelWorkbookStream.jss" clientSide="false">
		</xp:script>
	</xp:this.resources>
	<xp:label id="label1" value="Create Reports"></xp:label>
	<xp:br></xp:br>
	<xp:br></xp:br>
	<xp:table>
		<xp:tr>
			<xp:td>
				<xp:label value="Choose Report:" id="label2"></xp:label>
			</xp:td>
			<xp:td>
				<xp:comboBox id="comboBox1">
					<xp:selectItems id="selectItems1">
						<xp:this.value><![CDATA[#{javascript:var noval = [];
						noval[0] = "Please select a report";
						var forms = @DbColumn(@DbName(),"ExcelReports",1);
						var vals = noval.concat(forms);
						return vals;}]]></xp:this.value>
					</xp:selectItems>
					<xp:eventHandler event="onchange" submit="true"
						refreshMode="complete" refreshId="wbName" id="eventHandler1">
						<xp:this.script><![CDATA[var x= '#{javascript:getClientId("comboBox1")}'; var z= '#{javascript:getClientId("wbName")}'; var tmp = document.getElementById(x).value; document.getElementById(z).value=tmp.replace(/ /g,"_"); ]]></xp:this.script>
					</xp:eventHandler>
				</xp:comboBox>
			</xp:td>
		</xp:tr>
		<xp:tr>
			<xp:td>
				<xp:label value="File Name:" id="label4" for="wbName">
				</xp:label>
			</xp:td>
			<xp:td>
				<xp:inputText id="wbName"
					disableClientSideValidation="true" required="true"
					defaultValue="Sample">
					<xp:this.validators>
						<xp:validateRequired>
							<xp:this.message><![CDATA[#{javascript:return getLabelFor(this).getValue() + " is a required field.";}]]></xp:this.message>
						</xp:validateRequired>
					</xp:this.validators>
				</xp:inputText>
			</xp:td>
		</xp:tr>
		<xp:tr>
			<xp:td>
				<xp:label value="Sheet Name:" id="label5"
					for="sheetName">
				</xp:label>
			</xp:td>
			<xp:td>
				<xp:inputText id="sheetName"
					disableClientSideValidation="true" required="true"
					defaultValue="Report">
					<xp:this.validators>
						<xp:validateRequired>
							<xp:this.message><![CDATA[#{javascript:return getLabelFor(this).getValue() + " is a required field.";}]]></xp:this.message>
						</xp:validateRequired>
					</xp:this.validators>
				</xp:inputText>
			</xp:td>
		</xp:tr>
		<xp:tr>
			<xp:td>

			</xp:td>
			<xp:td>
				<xp:messages id="messages1"></xp:messages>
			</xp:td>
		</xp:tr>
	</xp:table>
	<xp:br></xp:br>
	<xp:br></xp:br>
	<xp:button value="Create Report" id="button1">
		<xp:this.disabled><![CDATA[#{javascript:getComponent("comboBox1").getValue() == "Please select a report";}]]></xp:this.disabled>
		<xp:eventHandler event="onclick" submit="true"
			refreshMode="complete">
			<xp:this.action><![CDATA[#{javascript:
function postValidationError(control, msg) {
    if ((typeof msg) != "string")
            return;
    var msgObj = new javax.faces.application.FacesMessage(javax.faces.application.FacesMessage.SEVERITY_ERROR, msg, msg);
    facesContext.addMessage(control.getClientId(facesContext), msgObj);
    control.setValid(false);
}
var control = getComponent("comboBox1");

var wbName = getComponent("wbName").getValue();
var sheetName = getComponent("sheetName").getValue();
var formName = getComponent("comboBox1").getValue();
var fpapp:NotesDatabase = session.getDatabase(@DbName()[0],@DbName()[1]);
var lkey:java.util.Vector = new java.util.Vector;
lkey.addElement(formName);
var lview = fpapp.getView("ExcelReports");
var doc:NotesDocument = lview.getDocumentByKey(lkey,true);
if ( !@IsNull (doc) ) {
	var viewName = doc.getItemValue("ViewName");
	postValidationError(control,"ViewName: " + viewName);
	var columns = doc.getItemValue("columnNumbers");
	postValidationError(control,"columnNumbers: " + columns);
	var colFields = [];
	for(var i=0;i<=columns.length-1;i++){
		colFields.push(columns[i]);
	}
	labelList = doc.getItemValue("Value");
	var labels = [];
	for(var i=0;i<=labelList.length-1;i++){
		labels.push(labelList[i]);
	}
	var columnsWithTotals = doc.getItemValue("ColumnsWithTotals");
	var totalLabels = [];
	for(var i=0;i<=columnsWithTotals.length-1;i++){
		totalLabels.push(columnsWithTotals[i]);
	}
	createWorkbookStreamWithLabels(wbName,sheetName,colFields,viewName[0],labels,totalLabels);
}
}]]></xp:this.action>
		</xp:eventHandler>
	</xp:button>
	<xp:br></xp:br>
</xp:view>

Create your Export Stream Library

Due to the length of this post, the library is in a second post. I’ve also added a sample database.