The use of POI HSSF to manipulate EXCEL Spreadsheets

People uses EXCEL. This is a fact. And IT development can’t avoid this. Sometimes I’ve heard the expression Excel’s Culture. Actually, I don’t know what the hell does this mean and even this term isn’t included on Wikipedia, but we have to assume that Excel is here to stay.

Recently I’ve had to report daily statistics to someone who only reads Excel. Searching the web I found just only two suitable options in order to write Excel data from Java:

  • POI from Jakarta (HSSF is the specific project for Excel manipulations)  
  • JExcel API from Sourceforge

I decided to use POI because it seems to be more mature than the other one. As a beginner, I’ve reached some criteria to establish a kind of best practices after this first usage:

  • Read an original Excel file as template, modify required data and create a new Excel file as output. POI is not designed to reuse the same file for read and write operations.
  • Set all the cell format and style in the original Excel template. Trying to perform this operations using POI is hard and it produces an Excel file with errors in many cases.
  • Use the suitable method to set cell values (Date, Long, HSSFRichTextString, …) according to the cell format.  Otherwise Excel will detect incoherences and formulas won’t work.
  • Before inserting values on a row, ensure this row exists. You can use a code like this:  if (sheet.getRow(rownum) == null) sheet.createRow(rownum);

Another important point to consider is formulas recalculation. Apache’s documentation suggest to include some code after Excel file manipulation (Re-calculating all formulas in a Workbook). This code doesn’t work for me, so I’ve included next method just before saving the Excel file to obtain expected results.

public static void rewriteFormulas(HSSFWorkbook wb) throws Exception {

  for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
    HSSFSheet sheet = wb.getSheetAt(sheetNum);

    for(Iterator rit = sheet.rowIterator(); rit.hasNext();) {
      HSSFRow r = (HSSFRow)rit.next();

      for(Iterator cit = r.cellIterator(); cit.hasNext();) {
        HSSFCell c = (HSSFCell)cit.next();
        if(c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
          c.setCellFormula(c.getCellFormula());
        }
      }
    }
  }

}

Un comentario en “The use of POI HSSF to manipulate EXCEL Spreadsheets

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s