Generating a CSV file from XPages

After spending the day figuring out how to generate an Excel file using the Apache POI library I was informed that I actually needed to generate a comma separated format file ( .csv ). Not wanting to waste any of the code or logic that I had written to build the Excel file I looked for a CSV library and I found the apache.commons.csv library.

This library is very simple to implement, once the jar file is added to your XPage application you just need to create a CSVPrinter object and then add all the values that you need to it. The quickest way to do this is create an xAgent style XPage with the code to create the CSV file in the beforePageLoads event.

Here is a simple example of the code required that will loop thru all the documents in a view and export a couple of fields to the CSV file and then send it all to the browser. Note this is just an example, production code would be full of error trapping etc.

// Load the java packages
importPackage(java.io);
importPackage(org.apache.commons.csv);

// generate CSVPrinter Object
var csvBAOS:ByteArrayOutputStream = new ByteArrayOutputStream();
var csvWriter:OutputStreamWriter = new OutputStreamWriter(csvBAOS);
var csvPrinter:CSVPrinter = new CSVPrinter(csvWriter,CSVFormat.DEFAULT);

// Add the header row
csvPrinter.print("EmployeeID");
csvPrinter.print("Lastname");
csvPrinter.print("Firstname");
csvPrinter.println();

// add each exported document
var exportView:NotesView = database.getView("vw_export");

if (exportView.getEntryCount() != 0){
	var exportDoc:NotesDocument = exportView.getFirstDocument();

	while (exportDoc != null){
		var nextExportDoc:NotesDocument = exportView.getNextDocument(exportDoc);

		csvPrinter.print(exportDoc.getItemValueString("employeeID"));
		csvPrinter.print(exportDoc.getItemValueString("lastName"));
		csvPrinter.print(exportDoc.getItemValueString("firstName"));
		csvPrinter.println();

		exportDoc.recycle();
		var exportDoc:NotesDocument = nextExportDoc;
	}
}

csvPrinter.flush();

// Open the servlet Response
var con = facesContext.getExternalContext();
var response:com.ibm.xsp.webapp.XspHttpServletResponse = con.getResponse();

//setting response headers for browser to recognize data
response.setContentType("text/csv");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", -1);
response.setContentLength(csvBAOS.size());
response.setHeader( "Content-Disposition", "attachment; filename="export.csv"" );

// get a handle on the actual output stream and write the Workbook
var servletOutputStream = response.getOutputStream();
csvBAOS.writeTo(servletOutputStream);
servletOutputStream.flush();
servletOutputStream.close();

// Close the renderer and return the document to the browser
facesContext.responseComplete();
}

Rather then writing directly to the response OutputStream I setup a ByteArrayOutputStream in memory and then, because the CSVPrinter requires an appendable outputstream I create an OutputStreamWriter from it and then setup the CSVPrinter to write to that.

Printing a value is as simple as using the CSVPrinter.print(string) method, each time you call this it adds a new value. When you get to the end of the line you need to use a CSVPrinter.println() to add the newline character and then you finish the file it is a good idea to call the CSVPrinter.flush() method to make sure everything is flushed out into the OutputStreamWriter. Then I send the ByteArrayOutputStream to the response OutputStream which is what sends it down to the browser to be saved.

Very easy to use and another great example of how you can use a prebuilt library to extend the functionality of XPages.