Cell.Calculate() and Cell.GetPrecedents()

Aspose.Cells Product Family
1
Hello,

So, I was looking at the documentation of these two functions.

http://www.aspose.com/api/net/cells/M_Aspose_Cells_Cell_Calculate
http://www.aspose.com/api/net/cells/M_Aspose_Cells_Cell_GetPrecedents

Is it safe to assume that a call to Calculate() recursively triggers a Calculate() on each of the GetPrecedents() cells in a way that leaves a logically consistent result in the cell being calculated? I am finding this is a major speed improvement over recalculating the entire workbook and just wanted to make sure that I was not overlooking any details that would result in inaccuracies.

Thanks,

Eric Rini
eric.rini@gmail.com
2

Hi,

Thanks for your posting and considering Aspose.Cells.

Workbook.CalculateFormula() recursively calculates all the formulas that are dependent on the affected cells.

Please check the source excel file attached with this post and the following sample code and its console output for your reference. It calculates the value of cell H10 which is dependent on many cells as shown in the attached screenshot.

C#

Workbook workbook = new Workbook(“source.xlsx”);

Worksheet worksheet = workbook.Worksheets[0];

worksheet.Cells[“A1”].PutValue(10);
worksheet.Cells[“B1”].PutValue(10);
worksheet.Cells[“C1”].PutValue(10);
worksheet.Cells[“D1”].PutValue(10);

Console.WriteLine("Before Calculation H10: " + worksheet.Cells[“H10”].StringValue);

workbook.CalculateFormula();

//Now the value of cell H10 should be 209
Console.WriteLine("After Calculation H10: " + worksheet.Cells[“H10”].StringValue);

Console Output:
Before Calculation H10: 156
After Calculation H10: 209

3

So the issue is that I have a VERY large workbook (20+ worksheets) and I need to do what is effectively a trial-and-error process on one sheet. I can’t modify the workbook, it’s got all kinds of legal certifications on it and is frequently revised. However recalculating the entire workbook is taking far too long for said trial and error process.


Would it be appropriate to infer that this routine would do largely the same thing as Workbook.CalculateFormulas(), but avoid triggering calculations on cells that were not part of the subset relevant to the cell I need to read?
private void recalculateCell(Workbook workbook, Cell cell) {

ReferredAreaCollection areas = cell.GetPrecedents();

for (int areaIndex = 0; areaIndex < areas.Count; areaIndex++) {
	ReferredArea area = areas[areaIndex];
	Worksheet worksheet = workbook.Worksheets[area.SheetName];

	if (area.IsArea) {
		for (int rowIndex = area.StartRow; rowIndex < area.EndRow; rowIndex++) {
			for (int columnIndex = area.StartColumn; columnIndex < area.EndColumn; columnIndex++) {
				recalculateCell(workbook, worksheet, rowIndex, columnIndex);
			}
		}
	}
	else {
		recalculateCell(workbook, worksheet, area.StartRow, area.StartColumn);
	}
}



}


private void recalculateCell(Workbook workbook, Worksheet worksheet, int rowIndex, int columnIndex) {

string name = CellsHelper.CellIndexToName(rowIndex, columnIndex);

Cell cell = worksheet.Cells[name];

recalculateCell(workbook, cell);

cell.Calculate(new CalculationOptions());

}


recalculateCell(workbook, workbook.Sheets[‘Sheet1’].Cells[‘H10’]);

After looking at this a bit, I am starting to appreciate the problem here. It may actually be more optimal to calculate the entire workbook once if you read several cells with overlapping precedents rather than duplicating the recalculation of those overlapping precedents.


Regardless what I am looking for is an optimization that performs the MINIMAL work necessary to get a single cell (or possibly a list of cells smaller than the entire workbook) into a valid state.
4

Hi,

Thanks for your posting and using Aspose.Cells.

We have logged your inquiry in our database for analyzing your requirements. We will look into it and advise you. Once there is some news for you, we will update you asap.

This issue has been logged as

  • CELLSNET-44170 - What is optimal and require minimal work Cell.Calculate() or Workbook.CalculateFormula()

5

Hi,

Thanks for using Aspose.Cells.

If you need to modify and calcuate some cells frequently, you may try to set WorkbookSettings.CreateCalcChain=true. With this setting, the workbook will be calculated completely on once and later call of Workbook.Calculate() will only calculate those cells that have been modified or their precedents have been modified.