Writing Excel Files with Apache POI 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.
Page 4 of 4
This article was originally published on January 7, 2009