Hello,
We are currently evaluating Aspose.Excel to see if the tool meets all of our needs for handling excel files within a few of our applications - and I must say that we like it very much
. One major drawback is the tool's short list of supported excel functions for calculation, however I presume this list hasn't been expanded for complexity and performance reasons during parsing.
With that said, we would like to have the ability to build our own evaluation logic of functions that are not support by the tool natively. So there are a few ways that I can think of as options that you may consider for allowing such functionality
:
1)
Allow formulas that your engine could not parse to be available from a property within the cell. An example right now would be if you use the formula "SUM(A1:A2)" in a designer file, when it is open the formula property for the cell would return Nothing (null) and the R1C1Formula property actually throws a NullReferenceExceptio
n error. However the IsFormula property still returns True denoting that the cell is formula based. If we could have API access to non-parsed formulas as a string it would allow us to parse the formula ourselves and replace the value/formula with something that the Aspose engine can handle. It would also be ideal to have this property work with the FindFormula and FindFormulaContains methods of the Cells collection class
2)
The second solution would be to have an event such as "UnsupportedFormula(Formula as String, Cell)" be called during your parsing method that would allow us to handle the formula evaluation and store a value directly to the cell during calculation.
Thoughts?
Cheers! 
Adam Evans
Project Manager
Strata Decision Technology
Dear Adam,
Thanks you for considering Aspose.Excel.
About the first problem, I didn’t find the problem in my place. Which version are you using?
Please check the attached sample file with my test code:
Excel excel = new Excel();
excel.Open(“d:\formula.xls”);
Console.WriteLine(excel.Worksheets[0].Cells[“c1”].Formula);
If you still find the problem with the latest version, could you please post your file here?
About the second issue, if you find some formula are not supported, please let use know, we will add it in Aspose.Excel.
Laurence,
Version 3.2.4.1 (Evaluation Copy)
Your excel file works fine in parsing that formula - seems that perhaps the file that I’m using is a different version? I’d like to send you my excel file but I don’t want to post it to this discussion forum - can you contact me (aevans@strata-decision.com) directly so I can send you the file?
Other functions that we currently use that are not supported:
- SUMIF
- COUNTBLANK
- COUNTA
- OR
- VLOOKUP
- HLOOKUP
- INDIRECT
- ISERROR
- NPV
- PV
- IRR
I mean in an ideal world we’d like to support all functions (like you haven’t heard that before) but I understand that this would mean even slower saving on large files (we’re dealing with 1-50MB files, average being around 5MB)
Thanks!
Adam
You can post your file here because your posted file can be only accessed by you and Aspose developers.
If you prefer to send by email, please send it to nanjing@aspose.com.
@aevans,
Aspose.Excel is discontinued and no more available now. It is replaced by a new product Aspose.Cells that supports all the features of different versions of MS Excel as well as its predecessor. We are glad to share that the new product Aspose.Cells has support for all the formulas that were not supported by Aspose.Excel mentioned above. You may visit the following article where the list of all the supported formulas/functions is available.
Supported Formula Functions
Formulas
Here is the link to the free trial version of this new product:
Aspose.Cells for .NET(Latest version)
You can download a ready to run solution here that can be used to test different features of this product without any coding.