Free Support Forum - aspose.com

Non-Support Formulas not available through API?

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 muchYes. 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 functionalityIdea:

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 NullReferenceExceptioCryingn 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! Beer

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.