Problem with Excel functions and range names in Formula

Aspose.Cells Product Family
1

I am evaluating Aspose.Excel for use in our J# web application. I have just about completed the validation but I ran into an issue with populating a cell with a formula that contains Excel functions and range names.

I am able to use .set_Formula(formula) to populate a cell with simple formulas like:

formula = “=I14+K14”;
formula = “=sum(I14:K14)”;

However, when I use more complex formulas like the following, the spreadsheet is generated but there is nothing (formula or data) in the cell:

formula = “=IF(B14=” + ‘"’ + “Pending Buy” + ‘"’ + “,(I14+K14)-1,I14+K14)";
formula = “=IF(side=” + ‘"’ + “Pending Buy” + ‘"’ + ",(OriginalCost+AccruedInterest)-1,OriginalCost+AccruedInterest)”;

In the second formula above, OriginalCost and AccruedInterest are range names created with Aspose.Excel. This is my ultimate goal for the application. I am able to generate the Excel spreadsheet and then manually key in the formulas and they work. Why can’t I do this through Aspose.Excel?

I read some of the other threads that mention problems with SUBTOTAL and custom functions as Add-ins. I downloaded Aspose.Excel.dll dated 6/3/2003 which was supposed to fix the problem with SUBTOTAL but it did not fix my problem.

Could someone please help me fix this last problem so I can get approval to spend a bunch of $$$ on Aspose.Excel.

Thanks in advance.

2

Hi, thanks for your consideration.

Currently Aspose.Excel does not support range names in formula.

I key in the formula in Microsoft Excel:

formula = “=IF(B14=” + ‘"’ + “Pending Buy” + ‘"’ + “,(I14+K14)-1,I14+K14)";
formula = “=IF(side=” + ‘"’ + “Pending Buy” + ‘"’ + ",(OriginalCost+AccruedInterest)-1,OriginalCost+AccruedInterest)”;

But they do not work.

Could you send me a designer file which contains the formula you listed? Then I can do some investigation about your problem.




3

Sorry, I forget to tell you my email address.

excel@aspose.com