Apache POI Speed Optimizations

Details

Category: Framework

Published on Monday, 29 October 2012 23:39

Written by Administrator

Hits: 1353

Tweet

The Apache POI Project's mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008). We have a complete API for porting other OOXML and OLE2 formats and welcome others to participate.

Switch Off logging

From the documentation at http://poi.apache.org/utils/logging.html

Logging in POI is used only as a debugging mechanism, not a normal runtime logging system. Logging is ONLY for autopsie type debugging, and should NEVER be enabled on a production system. Enabling logging will reduce performance by at least a factor of 100. If you are not developing POI or trying to debug why POI isn't reading a file correctly, then DO NOT enable logging. You've been warned.

In order to effectively disable the logging functionality in Apache POI you must use an alternative logger. This is accomplished by providing a property to the POILogFactory to override the default logger. You can add one of these –D to your JVM settings

-Dorg.apache.poi.util.POILogger=org.apache.poi.util.NullLogger

-Dorg.apache.poi.util.POILogger=org.apache.commons.logging.impl.NoOpLog

I found Apache POI to slightly better perform with the NoOpLog of apache common!

Recompile poi with more adapted settings

You can create a custom build of Apache POI 3.8 and alter the following properties to better match the size of the excel files you are generating or reading:

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFWorkbook.html#INITIAL_CAPACITY

Don’t use xlsx, prefer xls!

This will only work if you do not reach xls limitations which may avoid you to go to that extreme solution. XLS is not compressed (XLSX is xml based and compressed) and your workbook may double size in memory as a result!

For example, data beyond 256 (IV) columns by 65,536 rows will not be saved in xls! In Excel 2010 and Excel 2007, the worksheet size is 16,384 columns by 1,048,576 rows, but the worksheet size of Excel 97-2003 is only 256 columns by 65,536 rows. Data in cells outside of this column and row limit is lost in Excel 97-2003. But there is a lot more limitations listed at office.com…

The biggest side effect was that my excel file went from 354kb to 967kb, but the speed increase was quite interesting: more than 44% less evaluation time.

Small localized optimization

I don’t think these bring a lot of speed, JIT should optimize this bad piece of code for us but it is always worth trying Speeding up org.apache.poi.hssf.usermodel.HSSFRow.compareTo() and http://affy.blogspot.ch/2004/04/poi-optimization-speeding-_108265938673224937.html

You might also like:

36 days ago

Apache POI list of Excel supported functions

The Apache POI Project's mission is to create and maintain Java APIs for manipulating various file

2057 days ago

Fully Time Deterministic Java

The AIAA (American Institute of Aeronautics and Astronautics) paper [.pdf] plan to use java (For c

2603 days ago

Playing with Axis C++ and Apache 1.3.1

Here is a How to since it take me a very long time to install something which should have been trivi

2898 days ago

Open source libraries to read, write, change PDF files

2908 days ago

iText a free Java library

 iText is a library that allows you to generate PDF files on the fly. The iText classes are ver

3182 days ago

Apache Log4j

Log4J: A logging framework for J2EE Log4j homepage: http://jakarta.apache.org/log4j/ Reference

CedThumbnails

• < Prev
• Next >