Impossible To Read From Specific Calculated Field

Hi there. I am dealing with a cell that has the following formula and info:


Column: 4
ContainsExternalLink: false
DateTimeValue: {12/31/1899 12:00:00 AM}
DisplayStringValue: “0”
DoubleValue: 0
FloatValue: 0
Formula: “=IF($B$8>=MAX($B$26:$B$65),0,$B$12+$B$16+$B$21+$B$18+$B$22)”
HtmlString: “<Font Style=“FONT-FAMILY: Calibri;FONT-SIZE: 11pt;COLOR: #000000;”>0”
IntValue: 0
IsArrayHeader: false
IsErrorValue: false
IsFormula: true
IsInArray: false
IsInTable: false
IsMerged: false
IsStyleSet: false
Name: “E100”
NumberCategoryType: General
R1C1Formula: “=IF(R8C2>=MAX(R26C2:R65C2),0,R12C2+R16C2+R21C2+R18C2+R22C2)”
Row: 99


That is an output of the located cell in our worksheet. No matter what…Aspose thinks there is a zero in that field. There is definitely a value in that field. We have opened the sheet anumber of times…moved the calculated value into another cell…removed formatting…etc…a value will not be read from this cell. However, other cells with seemingly similar calculated values are being read just fine.

Yes, we call _Woorkbook.CalculateFormula() before our call…but to no joy. Are there known field types or formatting that makes it difficult to read from some cells? Please help…thank you!

Stephen Gyves


Hi Stephen,

Thanks for your posting and using Aspose.Cells.

After calling Workbook.CalculateFormula(), you should be able to read the calculated field correctly. Please download and try the latest version: Aspose.Cells
for .NET v8.6.1.2
and see if it makes any difference.

If your problem still occurs, then please provide us your source excel file as well as sample code replicating the issue with the latest version. It will help us investigate the issue at our end and we will fix it asap. Thanks for your cooperation.

Hi Shakeel I tested the cell here on my side and noticed the formula is the cause to this issue.

When I use the following formula listed below (example 1) I am able to retrieve the data contained in the cell.
However when i attempt to retrieve the data in the cell when example 2 formula is present i get back 0.00.

example 1: =$B$12+$B$16+$B$21+$B$18+$B$22

example 2: =IF($B$8>=MAX($B$26:$B$65),0,$B$12+$B$16+$B$21+$B$18+$B$22)

My requirement is to use example 2.

Please let us know if your team can provide a workaround.

I also tried the newest library as you suggested and did not get the cell to work.

Hi,

Thanks for your issue description and using Aspose.Cells.

We have tested this issue with the following sample code using the latest version: Aspose.Cells
for .NET v8.6.1.2
with the attached source excel file. If you check the source excel file, you will see the cell E6 contains your formula and its value is 100. And if you change the value of B16 to 222, the cell E6 value becomes 289 which is exactly the same calculated by Aspose.Cells.

Please see the following code and its console output for a reference.

C#
String filePath = “D:\Downloads\book1.xlsx”;

Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];

//Enter some value in B16 to calculate the new value of the E6
worksheet.Cells[“B16”].PutValue(222);

//Now calculate the formulas
workbook.CalculateFormula();

//Check the calculated value of cell E6, 289 means it is correct
Cell cell = worksheet.Cells[“E6”];

Console.WriteLine("Cell Formula: " + cell.Formula);
Console.WriteLine("Cell Value: " + cell.StringValue);

Console Output:
Cell Formula: =IF($B$8>=MAX($B$26:$B$65),0,$B$12+$B$16+$B$21+$B$18+$B$22)
Cell Value: 289

Hello, looks like when i use Workbook.CalculateFormula(true); it zeros out. If i remove that section of code my excel data comes back as expected. Odd

Hi,

Thanks for your posting and using Aspose.Cells.

Please provide us your sample console application project replicating this issue so that we could investigate it at our end and provide you a correct sample code or a fix. Thanks for your cooperation.