I have read other posts in your forums about the calculation engine for the .NET version of Aspose.Cells,
and I have a similar need for the Java version of Aspose.Cells.
The problem is that (at a minimum) I need a way to know if a formula is not supported by your calculation engine. For example, if my application tries to read in a value from a cell that contains an unsupported formula, I need to be able to display a message so that the user realizes that value was not updated.
I know that you have discussed the issue in some threads on the .NET version of Aspose.Cells
(see Adding Event to signal unrecognized functions in formula's
and http://www.aspose.com/Products/Aspose.Cells/Api/Aspose.Cells.Excel.CalculateFormula3.html )
and I will also need a solution (either the same solution or a different one) to address the same problem for the java version. Being able to extend the calculation engine would be nice, but isn’t absolutely required, but I really do need a way to find out if a spreadsheet has cells with an unsupported formula and a way to tell the user what formula is unsupported and which cells have that formula.
I will also email you a list of some other methods that you may want to consider supporting in the java version of the product… although I realize that you may never support all of the excel functions and are willing to add functions as I need them, I need to have some level of function support built-in so that I can show my customers that they aren’t overly restricted when they use software based on Aspose.Cells.
Sincerely,
-Phil
Hi Philip,
For unsupport functions, we can provide 3 solutions:
1. Ignore it and keep on calculation.
2. Throw exception when meeting unsupported functions.
3. Return an error message list including all unsupported functions. For exampe:
"Errors in calculating Cell A1: ABC1 function is not supported."
"Errors in calculating Cell D10: ABC2 function is not supported."
I think 2 or 3 can serve your need.
Yes, Laurence. Either 2 or 3 would meet my need. I think that 3 would be most useful to me.
I would assume that it would still throw an exception, but I guess I need to see your proposed API… maybe you would implement it without throwing an exception. Whatever it is, I’m sure that I can work with it.
-Phil
Hi,
You can use Workbook.calculateFormula(boolean ignoreError) to decide throw or not throw Exception.
3,We will take some time to implete it.
Now Excel built-in functions are supported in this method:
ABS, ACOS, ACOSH, AND, ASIN, ASINH, ATAN, ATAN2, ATANH, AVERAGE
CHAR, CEILING, COLUMN, COMBIN, CONCATENATE, COS, COSH, COUNT, COUNTA, COUNTBLANK, COUNTIF
DATE
EVEN, EXP
FACT, FALSE, FIND,FLOOR
GROWTH
HLOOKUP, HOUR
IF, INT, INDEX,ISBLANK, ISNA, ISERR, ISERROR, ISLOGICAL, ISNONTEXT, ISNUMBER, ISREF, ISTEXT
LEFT, LEN, LINEST, LN, LOG, LOG10, LOGEST, LOWER
MAX, MID, MIN, MINUTE, MOD, MONTH
NA, NOT, NOW
ODD, OFFSET, OR
PI, POWER, PRODUCT
RAND, REPLACE, RIGHT, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS
SEARCH, SECOND, SIGN, SIN , SINH, STDEV, STDEVP, SUM, SUMIF
T, TEXT, TIME, TODAY, TRIM, TRUE, TRUNC
UPPER
VALUE, VLOOKUP
WEEKDAY
YEAR
Excellent! These are all of the functions on my “must have” list… and more. I will test over the next week to see how everything works. Regarding unsupported functions, the feature that you have is fine… I really don’t need the features listed in option 3.
-Phil