September 19, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Writing Excel Files with Apache POI HSSF

  • January 7, 2009
  • By Scott Nelson
  • Send Email »
  • More Articles »

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);
}




Page 2 of 4



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel