Hi 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 Floriano
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.
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.
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?
Greg - that seems like a pragmatic solution - we should just change to this: private static final int DEFAULT_MAX_NUM_OPERANDS = SpreadsheetVersion.EXCEL2007.getMaxFunctionArgs();
Related: bug 58975
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.
This is resolved as far as I see.