Exception on GetConditionalFormattingResult in specific case

Aspose.Cells Product Family
1

Consider the following file: ConditionalFormatResultError.xlsx.zip (7.3 KB)

With the following code:

var workbook = new Workbook("ConditionalFormatResultError.xlsx");

var cell = workbook.Worksheets[0].Cells["C1"];
var result = cell.GetConditionalFormattingResult();

The last line throws the following exception: “System.ArgumentOutOfRangeException: Count cannot be less than zero. (Parameter ‘count’)”

We can work around it with a try/catch, but this appears to be a problem in Aspose.Cells. We’re using Aspose.Cells v23.8 (.NET Core).

3

@perfectxl,

Please add a line to your code, it works as expected.

var workbook = new Workbook(“ConditionalFormatResultError.xlsx”);

var cell = workbook.Worksheets[0].Cells[“C1”];
workbook.CalculateFormula();
var result = cell.GetConditionalFormattingResult();

4

That’s not an option, because we loop over all cells in all worksheets and cannot afford the costly operation of calculating all formulas.

When I look at the stack trace of the exception, it seems some internal bug in this method in Aspose.Cells. Is this because this method cannot handle the specific contents of this cell?

5

@perfectxl,
You don’t need to call Workbook.CalculateFormula() every time. You only need to call it once after the file is loaded.
The sample code as follows:

var workbook = new Workbook(“ConditionalFormatResultError.xlsx”);
workbook.CalculateFormula();
// do your work
var cell = workbook.Worksheets[0].Cells[“C1”];
var result = cell.GetConditionalFormattingResult();
6

@perfectxl,

C1 cell has a formula that needs to be calculated before obtaining conditional formatting results. If you want to avoid calculating all the formulas, you can calculate the formula of the underlying cell(s) only. Please refer to the updated code segment for guidance.

var workbook = new Workbook("ConditionalFormatResultError.xlsx");
var cell = workbook.Worksheets[0].Cells["C1"];
cell.Calculate(new CalculationOptions());
var result = cell.GetConditionalFormattingResult();
7

Thank you for explaining. For performance reasons, calculating the whole workbook is not an option. Calculating one cell is also not an possible, because we do not know on forehand which cell we should recalculate. This is just one example cell obtained from a sheet with a large number of formula cells.

So if there is no fix possible in the method itself, we should add a try/catch block to ensure the code keeps running and does not fail on this specific formula cell.

8

Thanks for sharing your concerns.

We have opened the ticket “CELLSNET-54162” for thorough evaluation and analysis of your issue. We will look into it and get back to you soon.

9

@perfectxl
We are glad to inform you the issue CELLSNET-54162 has been resolved now. The fix will be included into our next official version 23.11 and you will be notified when it is published.

10

The issues you have found earlier (filed as CELLSNET-54162) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi