Aspose.Cells Cannot Calculate COUNTIFS formula Correctly

Aspose.Cells Product Family
1

Hi,

Aspose.Cells is used in our project for some spreadsheet calculation and spreadsheet output. During development, I found an issue that Aspose.Cells sometimes cannot calculate COUNTIFS formula sometimes.I attached the sample spreadsheet, and I just use following simple code to read some cell value in it:

License lic = new License();

lic.SetLicense("Aspose.Total.lic");

Workbook wb = new Workbook(@"calTest.xlsx");

Console.WriteLine(wb.Worksheets[1].Cells["C23"].Value);

Console.WriteLine(wb.Worksheets[1].Cells["F5"].Value);

Please pay attention that Cell 'C23' is using COUNTIFS formula, which should return value 2, but I got 1 !!! It will cause incorrect value in other cells which reference it.

I also try using Workbook.CalculateFormula method before getting values, but it doesn't work. So could anybody help to look into this issue?

Thanks.

2

Hi,


Thanks for sharing the template file and sharing sample code.

After an initial test, I observed the issue as you mentioned by using your template file with your sample code. I got incorrect results even using Workbook.CalculateFormula() method.

Sample code:

Workbook wb = new Workbook(@“e:\test2\calTest.xlsx”);
wb.CalculateFormula();

Console.WriteLine(wb.Worksheets[“Grouping and Tranche Names”].Cells[“C23”].Value);//1 should be 2

Console.WriteLine(wb.Worksheets[“Grouping and Tranche Names”].Cells[“F5”].Value);//null should be 3

I have logged a ticket with an id “CELLSNET-42137” for your issue. We will look into your issue soon.

Once we have any update on it, we will let you know here.

Thank you.
3

Hi,


Thank you for your reply. So will your team include the fix in next release, or could you give me some alternative approach to get value correctly? Otherwise I have to let our template owner try changing the formula in the cells.

Thanks.
4

Hi,


Well, we have not figured out your issue yet, so our next official release of the product (i.e., v7.6.1 which is due in early next week) might not include the fix for your issue. However, we might provide you the fixed version (e.g v7.6.1.X) after we release of our next official version (v7.6.1) and before the end of next week or so.

Once we have any update on it, we will let you know here.

Thank you.
5

Hi,

Thanks for using Aspose.Cells for .NET.

We have fixed this issue.

Please download and try this fix: Aspose.Cells for .NET v7.6.0.7 and let us know your feedback.

6

Hi,

Thank you for your update. But unfortunately, after using your given 7.6.0.7 version, I still got error value 1 for 'C23'. Could you guys check it again?

Another question is it looks not an official version of Aspose.Cells and what official version would contain the fix?

Thanks.

7

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and try the major release: Aspose.Cells for .NET 7.6.1 it works fine and giving the correct value of 2.

The previous release is minor release. It is equally useful for production environment without any issue. The major release includes the fixes of all minor releases too.

8

Hi,

I'm sorry that I still got value of 1 for C23 and NULL value for F5 in attached spreadsheet after using 7.6.1.0. Number of this issue is 42137, but I didn't see it was listed in resolved bugs of 7.6.1 release. Could you confirm that?

thanks.

9

Hi Sheng,

Thanks for your posting and using Aspose.Cells.

I have rechecked this issue with the latest version: Aspose.Cells for .NET 7.6.1 and did not find any issue. I have tested it with the following code. I have attached the screenshot showing the output of the code. Please make sure you are using the latest version and not using any older version mistakenly.

Your issue number will be included in the next major release which will be due in the first week of next month. You will also get the automatic notification on this thread when the major release will be published.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\calTest.xlsx”;


Workbook wb = new Workbook(filePath);

wb.CalculateFormula();


Console.WriteLine(wb.Worksheets[“Grouping and Tranche Names”].Cells[“C23”].Value);//1 should be 2


Console.WriteLine(wb.Worksheets[“Grouping and Tranche Names”].Cells[“F5”].Value);//null should be 3


Screenshot:

10

Hi,

I missed wb.CalculateFormula() method call in my code... After I added that line of code, the values were correct. Thank you very much.

11

Hi Sheng,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved now. If you encounter any other issue, please feel free to post, we will be glad to help you further.

12

The issues you have found earlier (filed as CELLSNET-42137) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.