Writing Excel Files with Apache POI HSSF

January 7, 2009
By Scott Nelson

Send Email »
More Articles »

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());
%>