Friday, September 26, 2008

Creating Excel Files with POI - Issue

HSSF (Horrible SpreadSheet Format) is a great library for creating Excel spread-sheets using Java. It is part of the larger Apache POI project. Yesterday I hit an interesting issue while creating an Excel file with a modestly large collection of elements.

The Excel file was generated fine by HSSF but when opening it, Excel greeted me with the following message:

"some text formatting may have changed in this file because the maximum number of fonts was exceeded"

As it turns out, Excel only allows for a limited amount of styles/fonts to be used. Thus, the good advice when dealing with Excel files is to reuse styles and fonts wherever possible. It also results in substantially smaller Excel files. I was able, in my little example, to reduce the file-size from 54kb to 17kb, just by refactoring my usage of styles.

On last interesting observation: Once you hit that popup error in Excel, it does not simply suffice to close the Excel file, but you must restart Excel in order for the message not to popup again.

4 comments:

Unknown said...

Gunnar, I think OpenOffice has the ability to generate Excel documents as well. Haven't tried it myself so I can't vouch for it, but worth taking a look at if you need another alternative.

Gunnar Hillert said...

Hi - thanks for your comment but in this particular case I needed to generate Excel files from my Java code. Another option, though, is JExcelApi (http://jexcelapi.sourceforge.net/). I have not tried it but it looks like a viable alternative.

indiancomet said...

thanks for this post. saved me some time for sure :)

Alex said...

Oh, man, I almost spun my wheels for a REALLY long time until I noticed your last comment about needing to restart Excel. Thank you!