Re: Optimizing workbook for speedy evaluation

Hi, I would strongly suggest to do some profiling of your particular use-case first. Just starting some optimization might lead you in the wrong direction altogether. Only when you know where how much time is spent you can make an educated guess at where some change will actually provide an improv

References passed to the AreaReference must be contiguous

Hi, I'm trying to create an AreaReference (org.apache.poi.ss.util.AreaReference) using the following reference: OFFSET($'SEA 05 Staff Data'.$A$2,,,COUNTIF($'SEA 05 Staff Data'.$L$2:$L$100,">0"),14) When doing so I receive the following exception: Exception in thread "main" java.lang.IllegalArgum

Re: Optimizing workbook for speedy evaluation

Good point, thanks! After an initial profiling, the vast majority of the time was spent on two things: 1) parsing the Excel file (parseSheet in XSSFWorkbook) and 2) running finalisers (java.lang.ref.Finalizer). I did create a cache that pools Workbook objects to avoid (1) above, which then als

Re: References passed to the AreaReference must be contiguous

Try https://poi.apache.org/apidocs/org/apache/poi/ss/formula/FormulaParser.html That will give you a list of parse nodes (in RPN order, I believe. That will need to be evaluated by some FormulaEvaluator to resolve the offset. It's marked @Internal so it may be modified or removed without notice.