I'm using Aspose.Cells for .NET v4.4.0.0 in a .NET 1.1 web application.
The spreadsheet template file we use was recently changed to make heavy use of the "OFFSET" and "MATCH" Excel functions. The application copies the template file and performs a CalculateFormula(false) call to calculate the formulas. After the calculation is complete the cell values are copied (cell by cell to another copy of a "breakout" spreadsheet template. If I open the source template spreadsheet in Excel, I see the values I expect. The breakout spreadsheets, however have all zeros in them.
Using the debugger I have halted my program immediately after the CalculateFormula(false) method call to examine the calculated values. All values are zero...including the cells where I see the expected values in Excel. There appears to be some sort of issue with the the handling of OFFSET() and MATCH() functions. The levels of Excel function calls are fairly deep and I'm wondering if Aspose.Cells is having a problem dealing with the depth. Has the CalculateFormula() method been tested with MATCH() function calls made within an OFFSET() call such as OFFSET(reference, MATCH(lookupval1,lookuparr1,0),MATCH(lookupval2,lookuparr2, 0))?
Is there any way to determine which portion of my spreadsheet that the calculation engine is having problems dealing with? It is my understanding from reading other posts that the calculation engine "ignores" formulas that it does not understand (by setting the values to 0)? Is there a way to determine where the parser is "giving up".
Thanks,
Mark