Hi All,
I’m reviewing this product to see if it will meet our needs. Basically, I need to access a spreadsheet using a .NET web form, I need to insert some values to a few cells and then tell the spreadsheet to re-calculate and pull out some values.
It looks like this is possible with the excel.calculateFormula function.
I just read something in the documentation that says only certain operators and functions are available. Does this mean that the pre-existing formulas (i.e. - those in the spreadsheet before I open it) will only re-calculate if they are in the set of functions and operators supported by .CalculateFormula?
If the spreadsheet is using VBA to calculate some values (when the workbook is open in Excel, for instance), is there any way for aspose.excel to trigger that same VBA functionality and produce the same values?
Thanks in advance!
Chris
Hi Chris,
1.Does this mean that the pre-existing formulas (i.e. - those in the spreadsheet before I open it) will only re-calculate if they are in the set of functions and operators supported by .CalculateFormula?
Yes. We are working to enhance the set of functions to extend the list.
2. If the spreadsheet is using VBA to calculate some values (when the workbook is open in Excel, for instance), is there any way for aspose.excel to trigger that same VBA functionality and produce the same values?
No. Aspose.Excel can only load and save VBA in Excel files. It cannot trigger those VBA.
@cmacek,
Aspose.Excel is discarded now and no more under active development. It is replaced by Aspose.Cells that supports a vast range of formulas supported by MS Excel. You may review the list of formulas that are supported by Aspose.Cells. CalculateFormula will calculate all the formulas those are listed in the above-mentioned document. Aspose.Cells now supports detecting the circular reference. You can also interrupt or cancel a calculation if it is taking too long to finish. You are also suggested to visit ways to calculate formulas for more information about working with formulas.
Please note that VBA script cannot be executed with Aspose.Cells as well.
Here is a simple code that demosntrates the usage of CalculateFormula().
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];
// Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);
// Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);
// Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);
// Adding a SUM formula to "A4" cell
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";
// Calculating the results of formulas
workbook.CalculateFormula();
// Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();
// Saving the Excel file
workbook.Save(dataDir + "output.xls");
Get the latest free trial version here to test different features of Aspose.Cells.
Aspose.Cells for .NET (Latest Version)
Download here the latest solution containing all the sample codes to test multiple features of this new product.