Apache POI

Apache POI is a set of Java classes for directly manipulating Microsoft Excel and Word files using pure Java code. Using POI, you can create custom documents and have full control over cell styles; you can merge cells, support multisheet workbooks, and embed charts within spreadsheets. POI is even included with ColdFusion MX 7 because it's used internally by the reporting engine when producing Excel reports.

TIP

When creating single-sheet Excel workbooks, developers will often find it easier and require less hand-coding to take advantage of the built-in reporting features introduced in ColdFusion MX 7. Reporting is discussed in the ColdFusion MX 7 Web Application Construction Kit.

The POI project includes four subprojects: POIFS, HSSF, HWPF, and HPSF. POIFS is a set of low-level APIs for reading and writing OLE Compound Documents, including Microsoft Office documents as well as other documents created with Microsoft technologies. HSSF is a set of both low-level and high-level APIs for reading and writing Microsoft Excel documents. HWPF adds support for Microsoft Word documents, and HPSF supports Document Properties, discussed earlier in this chapter.

This section focuses primarily on HSSFExcel documents. HWPF is not considered production-ready and is not included with the standard POI distribution. POIFS is for low-level manipulation onlyprimarily for use by the other subprojects. HPSF does provide support for Document Properties and can be used with ColdFusion MX 7, but the API is considerably more complex than that provided by the DSOFile COM object discussed earlier in this chapter.

The HSSF API provides two styles for working with Excel filesuser model and event model. The user model keeps the entire Excel document in memory and provides for random access to any object within the document using an object model familiar to anyone accustomed to the Excel user interface and terminology. The event model provides a low-level mechanism for reading an Excel file as it is streamed through memory, similar to processing an XML file via SAX. The event model API uses considerably less memory and is substantially faster, but is also more complex and provides only a read-only single-pass view of the document.

The example and explanation that follow demonstrate the user model API for creating an Excel document. With the user model API, reading files and writing files are accomplished using identical objects and techniques. Listing 27.4 demonstrates creating a multisheet workbook with POI. The example reads sales data from the demo database that accompanies this chapter and lists all order details for each customer on a separate worksheet.

Listing 27.4. OrderDetails.cfmCreate a Multisheet Workbook Using Apache POI

 <!---   Filename: OrderDetails.cfm   Purpose: Dump customer order details to a multi-sheet workbook   Requires: IntegratingOffice datasource ---> <!--- Select all order details for analysis ---> <cfquery name="orders" dataSource="IntegratingOffice">   SELECT     C.CustomerName,     O.OrderDate,     P.ProductName,     OD.Quantity,     P.UnitCost,     OD.Quantity * P.UnitCost AS TotalCost   FROM     ((Customers C         INNER JOIN     Orders O         ON       C.CustomerID = O.CustomerID)         INNER JOIN     OrderDetails OD         ON       O.OrderID = OD.OrderID)         INNER JOIN     Products P         ON       OD.ProductID = P.ProductID   ORDER BY     C.CustomerName,     O.OrderDate,     P.ProductName </cfquery> <!--- store the package in a variable for more compact code ---> <cfset pkg = "org.apache.poi.hssf.usermodel"> <!--- create a new workbook ---> <cfset wb = CreateObject("java", "#pkg#.HSSFWorkbook")> <!--- create styles that we'll use to format the data ---> <cfscript>   // create a new font object to specify some styles are bold   boldFont = wb.createFont();   boldFont.setBoldweight(boldFont.BOLDWEIGHT_BOLD);   // create a header style with a border and bold text   headerStyle = wb.createCellStyle();   headerStyle.setBorderBottom(headerStyle.BORDER_THIN);   headerStyle.setFont(boldFont);   // get reference to the data format object in order to access   // the built-in formats   dataFormat = CreateObject("java","#pkg#.HSSFDataFormat");   dateStyle = wb.createCellStyle();   dateStyle.setDataFormat(dataFormat.getBuiltinFormat("m/d/yy"));   dateStyle.setAlignment(dateStyle.ALIGN_LEFT);   // notice how we have to escape style formats   numStyle = wb.createCellStyle();   numStyle.setDataFormat(dataFormat.getBuiltinFormat("##,####0"));   // style for grand total--bold number   grandStyle = wb.createCellStyle();   grandStyle.setDataFormat(dataFormat.getBuiltinFormat("##,####0"));   grandStyle.setFont(boldFont);   // style for grand total label--bold   boldStyle = wb.createCellStyle();   boldStyle.setFont(boldFont);   // default style   defaultStyle = wb.createCellStyle(); </cfscript> <!--- create helper function to set a cell value and style ---> <cfscript>   function createCell(row, index, value, style) {     var cell = row.createCell(index);     var calendar = 0;     var dateValue = 0;     // special handling for date styles     if (style is dateStyle) {       calendar = CreateObject("java", "java.util.Calendar");       dateValue = calendar.getInstance();       dateValue.set(year(value),month(value),day(value));       value = dateValue;     }     cell.setCellValue(value);     cell.setCellStyle(style);   } </cfscript> <!--- loop through data and create a new sheet for each customer ---> <cfoutput query="orders" group="CustomerName">   <cfset ws = wb.createSheet(orders.CustomerName)>   <!--- create header row, notice row and cell indices are zero based --->   <cfset row = ws.createRow(0)>   <cfset createCell(row, 0, "Order Date", headerStyle)>   <cfset createCell(row, 1, "Product", headerStyle)>   <cfset createCell(row, 2, "Quantity", headerStyle)>   <cfset createCell(row, 3, "Unit Cost", headerStyle)>   <cfset createCell(row, 4, "Total Cost", headerStyle)>   <cfset ws.setColumnWidth(0, 3072)>   <cfset ws.setColumnWidth(1, 3072)>   <cfset ws.setColumnWidth(2, 2560)>   <cfset ws.setColumnWidth(3, 2560)>   <cfset ws.setColumnWidth(4, 2560)>   <!--- loop through data and add to sheet --->   <cfset rowNum = 1>   <cfoutput>     <cfset row = ws.createRow(JavaCast("int",rowNum))>     <cfset createCell(row, 0, orders.OrderDate, dateStyle)>     <cfset createCell(row, 1, orders.ProductName, defaultStyle)>     <cfset createCell(row, 2, JavaCast("double", orders.Quantity), numStyle)>     <cfset createCell(row, 3, JavaCast("double", orders.UnitCost), numStyle)>     <cfset createCell(row, 4, JavaCast("double", orders.TotalCost), numStyle)>     <cfset rowNum = rowNum + 1>   </cfoutput>   <!--- Display grand total --->   <cfset rowNum = rowNum + 1>   <cfset row = ws.createRow(JavaCast("int",rowNum))>   <cfset createCell(row, 0, "Grand Total", boldStyle)>   <cfset cell = row.createCell(4)>   <cfset cell.setCellStyle(grandStyle)>   <cfset cell.setCellFormula("SUM(E1:E#rowNum#)")> </cfoutput> <!--- save the workbook to a file ---> <cfset fileName = GetTempFile(GetTempDirectory(), "xls")> <cfset stream = CreateObject("java","java.io.FileOutputStream")> <cfset stream.init(fileName)> <cfset wb.write(stream)> <cfset stream.close()> <!--- tell the browser this is an Excel file and output the file ---> <cfheader   name="Content-Disposition"   value="attachment; filename=""orders.xls"""> <cfcontent  reset="yes"  type="application/vnd.ms-excel"  file="#fileName#"  deleteFile="true"> 

TIP

The Listing 27.4 example and explanation touch on only a fraction of the functionality provided by POI and HSSF. For more detailed information and documentation on POI, refer to the website at http://jakarta.apache.org/poi.

The example starts by querying sales data from the database. Then it creates a new workbook through a call to CreateObject. Notice that we store the Java package name in a variable for later reference; we'll need this string several times and it's quite long, so the variable saves some space and improves readability.

After we have our new, empty workbook created, we start by creating several HSSFCellStyle style objects through the createCellStyle factory method. We do this before adding any sheets and outside the data loop in order to reuse styles. If we were to create a new style object each time we applied a style, we'd run into a "Too many styles" error. Through the styles, we specify borders, fonts, data formats, and alignment. Notice that for data formats we take advantage of Excel's built-in formats using the HSSFDataFormat.getBuiltinFormat static method. The format string passed to these methods must match a built-in format exactly or the default General format will be used. All built-in formats are listed in the POI documentation. Also notice that numeric formats that use the number sign must be escaped in ColdFusion.

After creating our styles, we author our own createCell UDF to facilitate creating styled cells. This function creates the cell and sets the value and style. It also provides special functionality to handle dates, since JavaCast does not support dates as a value type.

With our workbook setup complete, we start to loop through the data to create the worksheets. Even though we're not outputting any data, we're using <cfoutput> to take advantage of its grouping functionality. For each customer we create a new worksheet, create the header row, and then use our custom function to add headers. Notice how each time we reference a row or a cell, we're using create functions such as createRow and createCell. This is because HSSF starts with a truly blank workbookno worksheets in the workbook, no rows in the worksheets, and no cells in the rows. Also notice that all indices for rows and cells are zero based, as is the standard for Java APIs.

After the headers are created, we set the column widths. The setColumnWidth method accepts as its parameters the column index and the width. The unit used for width is of a character. The value 2560 corresponds to 10 characters, and 3072 corresponds to 12 characters. HSSF does not support Excel's auto-fit columns property and does not have the ability to measure text to provide that functionality internally.

Next we loop through the order detail records for each customer, add a row for each, and create cells for each value. Notice the repeated use of JavaCast. HSSFCell's createCell method uses the Java type of the specified value to specify the type to Excel. Since CFML uses untyped variables, we use JavaCast to provide the type information. Notice that JavaCast is not needed for the string value, ProductName, and casting the date value, OrderDate, is handled internally by our createCell UDF.

At the end of each customer we add a row for the grand total and use an Excel formula to calculate the total. Notice that the formula does not include the equals sign.

Finally we save the workbook to a temporary file using a Java FileOutputStream and then use the familiar <cfheader> and <cfcontent> tags to send the file to the user. Figure 27.4 shows the resulting Excel document.

Figure 27.4. Multisheet workbook generated through Apache POI's HSSF API.

As you can see from this example, Apache POI provides a straightforward and extensive API for reading and writing Excel files. However, the methodology is verbose and would not be appropriate when creating single-sheet workbooks that don't require the flexibility POI provides. For those workbooks, ColdFusion MX 7's built-in reporting engine will provide a more streamlined approach. For advanced customizations like those demonstrated here, direct use of Apache POI is a good tool to have in your developer's arsenal.