Bug 61252 - Error evaluating formulas with more of 30 arguments on excel file 2007 or major.
Summary: Error evaluating formulas with more of 30 arguments on excel file 2007 or major.
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.15-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2017-07-05 14:58 UTC by Floriano
Modified: 2017-09-19 20:52 UTC (History)
2 users (show)


Note You need to log in before you can comment on or make changes to this bug.
Description Floriano 2017-07-05 14:58:52 UTC
using Poi 3.15 we have a problem with the evaluation of the formulas with more than 30 arguments(in our user case we have a SUM operand).
Taking a look to the code we have seen that in the class MultiOperandNumericFunction.java there is a constant value DEFAULT_MAX_NUM_OPERANDS = 30.
this value is used during the evaluation of the formulas to check the maximum number of arguments. According to us should be better to use the value retrieve from the class SpreadsheetVersion.java but fix in this way the problem require a lot of changes on the code.
Considering that 30 was the maximum number of arguments of Excel 97, a very old version, could be more reasonable to use 255 instead that is the limit for Excel 2007 or major. 
The fix using the limit of 255 is very fast and safe.
we looking forward for your feedback
Best Regards
Comment 1 PJ Fanning 2017-07-05 17:26:31 UTC
Floriano - would you be in a position to submit a patch?
org.apache.poi.ss.SpreadsheetVersion has the operand count as a param - 30 for Excel97 and 255 for Excel2007.
So maybe the fix is to ensure that this gets used instead of using a separate constant.
Comment 2 PJ Fanning 2017-07-05 18:52:24 UTC
Passing a SpreadsheetVersion around in all these function APIs looks like it will be a big change.
I think we might need to use a ThreadLocal, similar to LocaleUtil.
Comment 3 Greg Woolsey 2017-07-05 19:22:01 UTC
Is it really so bad to just use the larger version constant in formula calculation?  Are there cases where we really _need_ to throw an error or stop processing arguments after 30 for older workbooks?  Could we just note in the docs that using a formula call with > 30 arguments and then saving in the older format would cause problems when opening in Excel?
Comment 4 PJ Fanning 2017-07-05 20:46:57 UTC
Greg - that seems like a pragmatic solution - we should just change to this:

private static final int DEFAULT_MAX_NUM_OPERANDS = SpreadsheetVersion.EXCEL2007.getMaxFunctionArgs();
Comment 5 Javen O'Neal 2017-07-05 21:32:50 UTC
Related: bug 58975
Comment 6 PJ Fanning 2017-07-05 22:59:30 UTC
I accidentally committed the change to increase the limit to 255 operands in https://svn.apache.org/viewvc?view=revision&revision=1800949 - I will add some test cases tomorrow.
Comment 7 Dominik Stadler 2017-09-19 20:52:48 UTC
This is resolved as far as I see.