The web is full of data, and is an excellent medium for finding and displaying data. Once the data is located, many users want to analyze the data in a way that suits their own thought processes. Some web applications attempt to satisfy this need by providing a broader and broader range of options to view the data. In some cases, this is adequate. For example, eCommerce sites can provide price and feature comparisons. In many other cases, a browser is an awkward tool for data analysis, especially for power users. Financial data is probably the best example. Business Intelligence site users want to see their data in a variety of ways and attempting to create separate presentations for each user will lead to poor usability and high maintenance. Personal investors are frequently frustrated by the limitations of stock analysis sites.
There are some really expensive platforms that can create web pages where users can manipulate data to their own satisfaction. Once you get past the license expense, the hardware expense, and the training expense, they are even more expensive to develop with. For some applications, this expense has an excellent ROI. For the rest of us, it would be so much simpler to provide the data in a format the user can manipulate any way they like.
Spreadsheets will do nicely, and those expensive platforms will produce them for you.
Or you can create them yourself for free.
Recently, you explored how users could provide data to Java applications using Excel workbooks in “Reading Excel Files with Apache POI HSSF.” Now, look at how you can give data to users in the same format.
The One-Minute Spreadsheet
For really simple spreadsheets, Microsoft has done all the work for you by allowing Office applications to read and write HTML. All that is required to turn an HTML table into a spreadsheet is some proper formatting, such as using the <th> tag for the header row, as in this example:
<table border="1" bordercolor="black"> <tr> <th style="background-color: black; color: white;"> Col 1 </th> <th style="background-color: black; color: white;"> Col 2 </th> <th style="background-color: black; color: white;"> Col 3 </th> </tr> <tr> <td bgcolor="red">1</td> <td bgcolor="green">2</td> <td bgcolor="red">3</td> </tr> <tr> <td bgcolor="green">4</td> <td bgcolor="red">5</td> <td bgcolor="green">6</td> </tr> </table>
The Java code was left out due to author laziness, so just imagine your own iterations over data sets to populate the header and field values.
Next, simply set the mime type for JSP:
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <% response.reset(); response.setContentType("application/xls"); response.setHeader("Content-Disposition", "attachment;filename=simpleDemo.xls"); %>
Some web servers require setting a mime type defined in web.xml, like this:
<mime-mapping> <extension>xls</extension> <mime-type>application/vnd.ms-excel</mime-type> </mime-mapping>
A standard href link to our JSP results in a spreadsheet:
Figure 1: Download Dialog from Mime Type
Figure 2: Voilà, the One-Minute Spreadsheet
Once More, with Style
The one-minute spreadsheet approach will be adequate for many web applications to provide spreadsheets to the user. But, it is not always enough. Some users are very fussy about not having the unused cells blank, for example. Although that may not excite most developers into learning a new API, a more compelling use case is when the application needs to provide formulas or multiple sheets. In both cases, the Apache POI HSSF library comes to the rescue.
Here, you will walk through creating a workbook with two worksheets. The HSSF library is extensive, so you will look at some basic functions, and you can go through the well-documented JavaDoc for further refinements.
For the simplicity of design, I would recommend either specific classes for specific spreadsheets or types of spreadsheets. For the example, you will define some caveats for the sake of speed, with the understanding that your data model and workbook output can be as simple or complex as you would like for any application.
You will create two identical worksheets to keep the example work to a minimum. Worksheets can be as varied as you like within the workbook. This example uses some basic stock analysis values. A simple bean will hold the data for each row, and the writer class will be specific to only this data, so you can hard code the header values in the writer class rather than taking it as a parameter or using reflection to build it dynamically. Bundling the stock beans into a Collection makes it easy to work with.
With your data defined, your writer class will use the basic HSSF components to create your final Excel spreadsheet:
HSSFWorkbook excelWorkbook = new HSSFWorkbook(); HSSFSheet excelSheet = null; HSSFRow sheetRow = null; HSSFCell rowCell = null;
The HSSFCellStyle object defines styles for the spreadsheet. Skimming the API, it would seem that using a HSSFCellStyle for each HSSFCell object would be a simple way to style the spreadsheet. In actual implementation, this becomes an issue with large spreadsheets, resulting in a spreadsheet that may have too many definitions to render in Excel. The way to use HSSFCellStyle is to create a single HSSFCellStyle object for each style desired and then apply it to the appropriate HSSFCell object. So, in addition to specific writer classes, you may find it useful to have specific objects for holding the collection of styles used for types of spreadsheets. If you have a limited number of styles across your spreadsheets, a single shared object will do. A very basic style object would look something like this:
import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; public class SimpleStyleExample { private static HSSFCellStyle headerStyle; public SimpleStyleExample(HSSFWorkbook excelWorkBook) { this.excelWorkBook = excelWorkBook; headerStyle = this.excelWorkBook.createCellStyle(); this.headerStyle.setFont(headerFont); this.headerStyle.setFillPattern(headerStyle.SOLID_FOREGROUND); this.headerStyle.setBorderBottom(headerStyle.BORDER_THIN); this.headerStyle.setBorderLeft(headerStyle.BORDER_THIN); this.headerStyle.setBorderRight(headerStyle.BORDER_THIN); this.headerStyle.setBorderTop(headerStyle.BORDER_THIN); this.headerStyle.setBottomBorderColor(HSSFColor.WHITE.index); this.headerStyle.setLeftBorderColor(HSSFColor.WHITE.index); this.headerStyle.setRightBorderColor(HSSFColor.WHITE.index); this.headerStyle.setTopBorderColor(HSSFColor.WHITE.index); } public HSSFCellStyle getHeaderStyle() { return headerStyle; }
Above, only one style is defined, whereas in an actual implementation you would define all styles to be used in a spreadsheet or type of spreadsheet along with any particular data formats, fonts, and palettes. By making the style static, you avoid creating too many style objects within the final spreadsheet which makes it hard to load in Excel, and impossible to run with large data sets.
Throwing Back the Sheets
Finally, you add your style class to your writer class and map the styles locally to avoid calling get over and over:
ExcelWriterDemoStyles styles = new ExcelWriterDemoStyles(excelWorkbook); HSSFCellStyle headerStyle = styles.getHeaderStyle(); HSSFDataFormat dataFormat = styles.getDataFormat(); HSSFFont headerFont = styles.getHeaderFont(); HSSFPalette palette = styles.getPalette();
Next, just like creating a worksheet inside Excel, you create the first sheet, set the column widths, and populate the first row with header values:
excelSheet = excelWorkbook.createSheet("Today Stock Analysis"); sheetRow = excelSheet.createRow(0); excelSheet.setColumnWidth(0, 6000); excelSheet.setColumnWidth(1, 6000); excelSheet.setColumnWidth(2, 2500); excelSheet.setColumnWidth(3, 2500); excelSheet.setColumnWidth(4, 3000); sheetRow.setHeight((short)600); for(int cellIndex = 0; cellIndex < HEADINGS.length; cellIndex++) { rowCell = sheetRow.createCell(cellIndex); rowCell.setCellType(HSSFCell.CELL_TYPE_STRING); rowCell.setCellValue(new HSSFRichTextString(HEADINGS[cellIndex])); rowCell.setCellStyle(headerStyle); }
Then, you add the data in each column by rows:
for(Iterator<ExcelWriterDemoStock> stockIt = data[0].getDemoDataCollection().iterator(); stockIt.hasNext(); rowIndex++) { sheetRow = excelSheet.createRow(rowIndex); stock = stockIt.next(); rowCell = sheetRow.createCell(0); rowCell.setCellType(HSSFCell.CELL_TYPE_STRING); rowCell.setCellStyle(styles.getDataStringStyle()); rowCell.setCellValue(new HSSFRichTextString(stock.getSymbol())); rowCell = sheetRow.createCell(1); rowCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); rowCell.setCellStyle(styles.getDataDollarStyle()); rowCell.setCellValue(stock.getPrice()); rowCell = sheetRow.createCell(2); rowCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); rowCell.setCellValue(stock.getPeg()); rowCell = sheetRow.createCell(3); rowCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); rowCell.setCellValue(stock.getYield()); rowCell = sheetRow.createCell(4); rowCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); rowCell.setCellValue(stock.getChange()); }
Repeat as needed for each worksheet in your workbook.
Once your workbook is created, you stream it as an XLS mime type, as with your One-Minute example:
<%@ page import="com.fywservices.poi.hssf.*"%> <%@ page import="org.apache.poi.hssf.usermodel.*" %> <% ExcelWriterDemoData[] demoData = new ExcelWriterDemoData[]{ new ExcelWriterDemoData(), new ExcelWriterDemoData()}; ExcelWriterDemo demo = new ExcelWriterDemo(); HSSFWorkbook excelWorkbook = demo.createExcelWorkbook(demoData); response.reset(); response.setContentType("application/xls"); response.setHeader("Content-Disposition", "attachment;filename=StockDemo.xls"); excelWorkbook.write(response.getOutputStream()); %>
And, finally, you get your multi-sheet, styled workbook:
Figure 3: Is It Windows or Is It HSSF?
The Whole Nine Yards
For ease of reference and replication for your own experiments, here is the entire writer class used in this example:
package com.fywservices.poi.hssf; import java.util.ArrayList; import java.util.Iterator; import java.util.Map; import org.apache.poi.hssf.usermodel.*; public class ExcelWriterDemo { private static final String[] HEADINGS = new String[]{"Symbol", "Price", "PEG", "Yield", "Change"}; public HSSFWorkbook createExcelWorkbook(ExcelWriterDemoData[] data) { HSSFWorkbook excelWorkbook = new HSSFWorkbook(); HSSFSheet excelSheet = null; HSSFRow sheetRow = null; HSSFCell rowCell = null; ExcelWriterDemoStyles = new ExcelWriterDemoStyles(excelWorkbook); ExcelWriterDemoStock stock = null; HSSFCellStyle headerStyle = styles.getHeaderStyle(); HSSFDataFormat dataFormat = styles.getDataFormat(); HSSFFont headerFont = styles.getHeaderFont(); HSSFPalette palette = styles.getPalette(); int rowIndex = 1; excelSheet = excelWorkbook.createSheet("Today Stock Analysis"); sheetRow = excelSheet.createRow(0); excelSheet.setColumnWidth(0, 6000); excelSheet.setColumnWidth(1, 6000); excelSheet.setColumnWidth(2, 2500); excelSheet.setColumnWidth(3, 2500); excelSheet.setColumnWidth(4, 3000); sheetRow.setHeight((short)600); for(int cellIndex = 0; cellIndex < HEADINGS.length; { rowCell = sheetRow.createCell(cellIndex); rowCell.setCellType(HSSFCell.CELL_TYPE_STRING); rowCell.setCellValue(new HSSFRichTextString(HEADINGS[cellIndex])); rowCell.setCellStyle(headerStyle); } for(Iterator<ExcelWriterDemoStock> stockIt = data[0].getDemoDataCollection().iterator(); stockIt.hasNext(); rowIndex++) { sheetRow = excelSheet.createRow(rowIndex); stock = stockIt.next(); rowCell = sheetRow.createCell(0); rowCell.setCellType(HSSFCell.CELL_TYPE_STRING); rowCell.setCellStyle(styles.getDataStringStyle()); rowCell.setCellValue(new HSSFRichTextString(stock.getSymbol())); rowCell = sheetRow.createCell(1); rowCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); rowCell.setCellStyle(styles.getDataDollarStyle()); rowCell.setCellValue(stock.getPrice()); rowCell = sheetRow.createCell(2); rowCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); rowCell.setCellValue(stock.getPeg()); rowCell = sheetRow.createCell(3); rowCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); rowCell.setCellValue(stock.getYield()); rowCell = sheetRow.createCell(4); rowCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); rowCell.setCellValue(stock.getChange()); } excelSheet = excelWorkbook.createSheet("Yesterday Stock Analysis"); sheetRow = excelSheet.createRow(0); excelSheet.setColumnWidth(0, 6000); excelSheet.setColumnWidth(1, 6000); excelSheet.setColumnWidth(2, 2500); excelSheet.setColumnWidth(3, 2500); excelSheet.setColumnWidth(4, 3000); sheetRow.setHeight((short)600); for(int cellIndex = 0; cellIndex < HEADINGS.length; cellIndex++) { rowCell = sheetRow.createCell(cellIndex); rowCell.setCellType(HSSFCell.CELL_TYPE_STRING); rowCell.setCellValue(new HSSFRichTextString(HEADINGS[cellIndex])); //TODO: SHOW RICH TEXT STRING APPROACH TOO rowCell.setCellStyle(headerStyle); } rowIndex = 1; for(Iterator<ExcelWriterDemoStock> stockIt = data[1].getDemoDataCollection().iterator(); stockIt.hasNext(); rowIndex++) { sheetRow = excelSheet.createRow(rowIndex); stock = stockIt.next(); rowCell = sheetRow.createCell(0); rowCell.setCellType(HSSFCell.CELL_TYPE_STRING); rowCell.setCellStyle(styles.getDataStringStyle()); rowCell.setCellValue(new HSSFRichTextString(stock.getSymbol())); rowCell = sheetRow.createCell(1); rowCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); rowCell.setCellStyle(styles.getDataDollarStyle()); rowCell.setCellValue(stock.getPrice()); rowCell = sheetRow.createCell(2); rowCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); rowCell.setCellValue(stock.getPeg()); rowCell = sheetRow.createCell(3); rowCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); rowCell.setCellValue(stock.getYield()); rowCell = sheetRow.createCell(4); rowCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); rowCell.setCellValue(stock.getChange()); } return excelWorkbook; } }
Conclusion
Web applications provide a useful way to present users with information. Once presented, the next step is to make the information itself useful. Even though Web 2.0 is one approach to making web-based data more useful, there are many ways that have been available before there were buzzwords for selling enterprise on usability. One of those “old” ways (using Internet chronology) that deserves a new look is the Apache POI project and the HSSF libraries. Or just well-formatted HTML and a mime type.
About the Author
Scott Nelson provides optimization services designing, developing, and maintaining web-based applications for manufacturing, pharmaceutical, financial services, non-profits, and real estate agencies for use by employees, customers, vendors, franchisees, executive management, and others who use a browser. For information on how he can help with your web applications, please visit http://www.fywservices.com/. He also blogs all of the humorous emails forwarded to him at Frequently Unasked Questions.
