Writing Excel Files with Apache POI HSSF

By Scott Nelson

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