Search code examples
excelapachecoldfusioncfspreadsheet

Coldfusion: using Apache POI event API

I need to parse big Excel spreadsheet (approximately 20 sheets) sheet by sheet with ColdFusion. cfspreadsheet tag fails when processing large amount of data with [java.lang.OutOfMemoryError: GC overhead limit exceeded]. Apache POI User API directly behaves the same way:

<cfscript>
  pkg = CreateObject("java", "org.apache.poi.openxml4j.opc.OPCPackage").open(JavaCast("string", fileName));
  // error on next line
  wb = CreateObject("java", "org.apache.poi.xssf.usermodel.XSSFWorkbook").Init(pkg);
</cfscript>

I tried to use Apache POI event API instead of User API but faced problems with java inheritance. Has anyone ever used XSSF and SAX (Event API) for big spreadsheets processing in ColdFusion?

Solution

  • After all I succeeded in using CF + Apache POI Event API + Mark Mandel's JavaLoader.cfc, Thank you @Leigh, @barnyr for all your help. I implemented excel parser in java using XSSF and SAX Event API, now it works and works very fast. This wasn't easy due template to parse wasn't simple and as were denoted in comments increasing heap size may be cheaper.