Writing Excel Files with Apache POI HSSF
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