Hi- I’m getting the error shown in the subject above when trying to call Workbook.CalculateFormula. The full error says that the issue is “Based on cell Data!B2366” in the workbook I will provide. The formula in this cell and other cells around it are fairly basic - just =SUM() of other hard-coded cells. Could someone see if you can replicate the error and possibly create an Aspose.Cells bug log if appropriate?
Thank you.
The error you are encountering, IndexOutOfRangeException: Index was outside the bounds of the array, typically occurs when the formula references a cell or range that does not exist or is out of the defined limits of the worksheet. This can happen with formulas like =SUM() if the referenced cells are not properly defined or if they exceed the available range.
Here are some steps you can take to troubleshoot and potentially resolve the issue:
Check Cell References: Ensure that the cell references in your =SUM() formula are valid and within the bounds of the worksheet. For example, if your formula references Data!B2366, make sure that this cell exists in the specified worksheet.
Inspect the Workbook: If possible, open the workbook in Excel and verify that the cells being referenced in the formula contain valid data. Sometimes, hidden rows or columns can lead to unexpected behavior.
Use the CalculateFormula Method: When calling the CalculateFormula method, ensure that you are doing so on the correct workbook or worksheet object. You can calculate all formulas in the workbook or just those in a specific worksheet.
Enable Calculation Chain: If your workbook contains many formulas, consider enabling the calculation chain to improve performance. This can be done by setting FormulaSettings.EnableCalculationChain to true.
Debugging: If the issue persists, you may want to isolate the problematic formula by creating a new workbook and adding only the relevant formulas to see if the error can be replicated.
Here is a simple code example to demonstrate how to calculate formulas in a workbook:
// Create a workbook
Workbook workbook = new Workbook();
// Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Add values to cells
worksheet.Cells["A1"].PutValue(1);
worksheet.Cells["A2"].PutValue(2);
worksheet.Cells["A3"].PutValue(3);
// Add a SUM formula to a cell
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";
// Calculate the results of formulas
workbook.CalculateFormula();
// Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();
Console.WriteLine("Calculated Value: " + value);
If you continue to experience issues, consider providing a sample workbook for further analysis, as it may help in identifying the specific cause of the error.
For more detailed information on calculating formulas with Aspose.Cells, you can refer to the official documentation here and here.
If the problem persists after these checks, it may be worth reporting it as a bug to Aspose support for further investigation.
Sources:
[1]: Calculate Formulas - Aspose Documentation
[2]: Calculate or Recalculate formulas dynamically - Aspose Documentation
Note, we are using Aspose.Cells v24.7.
I tested your scenario/case using the latest version, Aspose.Cells v25.5 (Releases | NuGet). Using your template Excel file and the following sample code, everything worked smoothly. There was no exception or other issue, and the output Excel file was fine tuned.
e.g.,
Sample code:
Workbook workbook = new Workbook("e:\\test2\\StandAloneAsposeError.xlsm");
Worksheet worksheet = workbook.Worksheets["Data"];
workbook.CalculateFormula();
//Get the calculated value of the cell
object value = worksheet.Cells["B2366"].Value;
Console.WriteLine("Calculated Value: " + value.ToString());
string displayvalue = worksheet.Cells["B2366"].DisplayStringValue;
Console.WriteLine("Displayed calculated value: " + displayvalue);
workbook.Save("e:\\test2\\out1.xlsx");
console output:
Calculated Value: 0
Displayed calculated value: 0.0%
Please try latest version/fix: Aspose.Cells v25.5.
Let us know if you still find any issue.
Thank you. I can confirm that I am no longer getting this error in v25.5.
I’m glad to hear your issue is resolved. If you have any more questions or comments, please don’t hesitate to reach out to us.