CalculateFormula causing runtime exception


#1

Hi,
at the minute we are evaluating Aspose.Excel to wrap-up the existing functionality in a set of spreadsheets developed by our business users. I’ve checked the spreadsheet so that only the supported formula are contained within but I’m still getting a runtime exception when I try to execute the CalculateFormula() method:

at Aspose.Excel.?.?(Cell ?, Byte[] ?)
at Aspose.Excel.?.?(Cell ?)
at Aspose.Excel.Excel.CalculateFormula()
at ConsoleAsposeTest.TestExcel.Main(String[] args) in c:\projects\work\b2b\ex
celtest\consoleasposetest\exceltest.cs:line 24


I have attached the spreadsheet. and here is the simple code I’m using to open it :

Excel excel = new Excel();

//Open a spreadsheet

excel.Open(“Rating - NOTRUNC.xls”);

try

{

excel.CalculateFormula();

}

catch (Exception ex)

{

Console.WriteLine(ex.StackTrace);

}

many thanks for your time,

Dave.

#2

The HLOOKUP function is not supported in formula calculation engine. I think that caused this error.


#3

Thanks again Laurence for such a rapid response.

I had just removed the TRUNC function from the spreadsheet as it wasn’t listed in the supported functions, but HLOOKUP and VLOOKUP both are listed as being supported(http://www.aspose.com/Products/Aspose.Excel/Api/Aspose.Excel.Cell.Formula.html). Do you plan to implement these functions in the near future?

Thanks again,

Dave.


#4

When you use Cell.Formula to get/set formula to a cell, Aspose.Excel only parses and saves it. Only when you call Excel.CalculateFormula method, formula will be calculated. So in Excel.CalculateFormula reference, the supported list is shorter.

If you don’t need to retrieve data in each formula cell, you don’t need to call Excel.CalculateFormula method. When opening in MS Excel, MS Excel will calculate them automatically.