Frequently Asked Questions

Questions

Answers

What is the HSSF "eventmodel"?

The HSSF eventmodel package is a new API for reading XLS files more efficiently. It does require more knowledge on the part of the user, but reduces memory consumption by more than tenfold. It is based on the AWT event model in combination with SAX. If you need read-only access to a given XLS file, this is the best way to do it.

Why can't read the document I created using Star Office 5.1?

Star Office 5.1 writes some records using the older BIFF standard. This causes some problems with POI which supports only BIFF8.

Why am I getting an exception each time I attempt to read my spreadsheet?

It's possible your spreadsheet contains a feature that is not currently supported by HSSF. For example - spreadsheets containing cells with rich text are not currently supported.

Does HSSF support protected spreadsheets?

Protecting a spreadsheet encrypts it. We wont touch encryption because we're not legally educated and don't understand the full implications of trying to implement this. If you wish to have a go at this feel free to add it as a plugin module. We wont be hosting it here however.

How do you tell if a field contains a date with HSSF?

Excel stores dates as numbers therefore the only way to determine if a cell is actually stored as a date is to look at the formatting. There is a helper method in HSSFDateUtil (since after 1.6.0-dev) that checks for this. Thanks to Jason Hoffman for providing the solution.

            case HSSFCell.CELL_TYPE_NUMERIC:
                  double d = cell.getNumericCellValue();
                  // test if a date!
                  if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // format in form of M/D/YY
                    cal.setTime(HSSFDateUtil.getJavaDate(d));
                    cellText =
                      (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
                    cellText = cal.get(Calendar.MONTH)+1 + "/" +
                               cal.get(Calendar.DAY_OF_MONTH) + "/" +
                               cellText;
                  }



            

I'm trying to stream an XLS file from a servlet and I'm having some trouble. What's the problem?

The problem usually manifests itself as the junk characters being shown on screen. The problem persists even though you have set the correct mime type.

The short answer is, dont depend on IE to display a binary file type you an attachment properly if you stream it via a servlet. Every minor version of IE has different bugs on this issue.

The problem in most versions of IE is that it does not use the mime type on the HTTP response to determine the file type; rather it uses the file extension on the request. Thus you might want to add a .xls to your request string. For example http://yourserver.com/myServelet.xls?param1=xx. This is easily accomplished through URL mapping in any servlet container. Sometimes a request like http://yourserver.com/myServelet?param1=xx&dummy=file.xls is also known to work.

To guarantee opening the file properly in Excel from IE, write out your file to a temporary file under your web root from your servelet. Then send an http response to the browser to do a client side redirection to your temp file. (If you do a server side redirect using RequestDispatcher, you will have to add .xls to the request as mentioned above.)

Note also that when you request a document that is opened with an external handler, IE sometimes makes two requests to the webserver. So if your generating process is heavy, it makes sense to write out to a temporary file, so that multiple requests happen for a static file.

None of this is particular to Excel. The same problem arises when you try to generate any binary file dynamically to an IE client. For example, if you generate pdf files using FOP, you will come across many of the same issues.

Ant LogoCocoon LogoKrysalis LogoKrysalis Centipede Logo