Aspose cells read true/false cell different then excel

@info.verne.nu,
We were able to observe the issue for file ExcelSaveSample3.xlsb but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as
CELLSNET-47118 – Incorrect value 'TRUE' retrieved from Cell instead of value 'FALSE'

Hi @Amjad_Sahi and @ahsaniqbalsidiqui

We were wondering if you have an update for us?
Thanks in advance.

Regards,
Carsten

@info.verne.nu,

As we logged the issue yesterday, so kindly spare us little time (3-5 days or so) to evaluate your issue precisely. If the issue is not complex, it should be resolved within 7-10 days or even before it.

Once we have any new information available, we will share it with you.

Hi Amjed_Sahi,
We did some further investigation.
What i see is that
sheet = document.Worksheets[“ClaimEngine”];
cell = sheet.Cells[“AJ43”];
The current formula is:

        cell.Formula = "=IF(AND(OR(AJ7=\"Declaration\",AJ7=\"FastLane\"),AJ146=FALSE),IF(COLUMN()=3,TRUE,IF(COUNTIF(C43:AI43,TRUE)=0,TRUE,FALSE)),\"\")";

document.CalculateFormula(true);

But Cell C43 of the sheet “ClaimEngine”]; the value is true;

Hoewever the result from aspose returns false;
Changing the formula from countif(C43 to Countif(B43

        cell.Formula = "=IF(AND(OR(AJ7=\"Declaration\",AJ7=\"FastLane\"),AJ146=FALSE),IF(COLUMN()=3,TRUE,IF(COUNTIF(B43:AI43,TRUE)=0,TRUE,FALSE)),\"\")";

document.CalculateFormula(true);

The result is changed to the correct value. Could it be that the countif function starts one cell to late?

Hope to hear from you soon.

Regards,

René

@info.verne.nu,
Thank you for providing the additional information. We have logged these comments with the ticket for our reference and will write back here once any update is available in this regard.

Hi Ahsan Iqbal,

I did another test and if we use aspose version 19.7 cell EH13 of sheet Out_ValidatedClaimregistrationD is read ok. However then we have our original problems back.
Hope you can fix it soon.

Regards,

René

@info.verne.nu,

Thanks for sharing your findings.

I have logged this with your existing ticket into our database. It may help to investigate the issue precisely.

We will keep you updated with the status of the issue.

@info.verne.nu

This is to inform you that we have fixed your issue “CELLSNET-47118” precisely now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

Hi Amjad_Sahi,

That is great news. Can I get the pre-release version so that i can test here as well?

Regards,

René

@info.verne.nu,
We are discussing your requirement here and will share our feedback soon.

@info.verne.nu,

Please try our latest version/fix: Aspose.Cells for .NET v20.1.7 (attached)

Your issue should be fixed in it.

Let us know your feedback.
Aspose.Cells20.1.7 For .Net2_AuthenticodeSigned.Zip (5.0 MB)
Aspose.Cells20.1.7 For .Net4.0.Zip (5.0 MB)

Hello @Amjad_Sahi

Everything looks okay for now.
When can we expect the official version release as nugget package?

Kind regards,
Carsten

@info.verne.nu,

Good to know that your issue is sorted out by the new fix. Well, you may expect the next official release (in nuget repos. and Downloads section) before the end of this week. You will also be notified once the new release is published (nuget repos. and Downloads section).

The issues you have found earlier (filed as CELLSNET-47118) have been fixed in Aspose.Cells for .NET v20.2. This message was posted using Bugs notification tool by Amjad_Sahi

Hi Amjad,

I’m sorry to inform that aspose still is showing different information in cells then excel is doing.
Attached is a unit test with all the test cases that we we found earlier. There is a new test case called Schadegarant. This case is failing because excel reports the value 0 where aspose reports value 2020109530.

I already updated to apose cells 20.3.0 but that did not solve the problem.

Hope that you can take a quick look and reproduce the problem.

Regards,

René

Expected cell.Value to be 0, but found “2020109530”.Aspose Unit Test.zip (4.3 MB)

@info.verne.nu,
We have observed the issue but we need to look into it more. We will provide you our feedback soon.

@info.verne.nu,
The calculated value saved in the template file Schadegarant1.xlsb for OUT_ProcessInformation!C10 is incorrect. You may confirm this by re-calculate formulas with newer MS Excel, or clear the existing CalculationId in the template file (such as, by re-saving the file as xlsx, and then edit workbook.xml and remove the element “calcPr”) and open the modified xlsx again with MS Excel and check the cell’s value.

Let us know your feedback.

hi ahsaniqbalsidiqui

i don’t understate your answer.

the formula of cell C51 of sheet validations contains:
=IFERROR(INDEX(SchadeNummer,1,MATCH(1,(SchadeDate>=C48-C40)*(SchadeDate<=C48+C40),0)),“0”)
So if the formula contains a error zero should be returned.

I replaced the test with the following info:
sheet = document.Worksheets[“Validaties”];
cell = sheet.Cells[“C51”];

	-> value read shows  2020109530

        Console.WriteLine(cell.Value);
        cell = sheet.Cells["D51"];
        cell.Formula = "INDEX(SchadeNummer,1,MATCH(1,(SchadeDate>=C48-C40)*(SchadeDate<=C48+C40),0))";
        document.CalculateFormula(true);

	-> however if i remove the iferror condition the error is displayed om cell D51

        Console.WriteLine(cell.Value);

hope that you can help me sort out the problem.
I save the xlb file to a excel file but still excel is showing a zero as existing claimnumber.

Regards,

René

@info.verne.nu,
We have noted your observation and analyzing it more. We will write back here once any feedback is ready to share.

Hi,

I also changed the xlb to a xlsx and manualliy modified xl\workbook.xml and removed the line:
calcPr calcId=“191029” iterateCount=“0” iterateDelta=“0” concurrentCalc=“0”/

But opening in excel still shows the value 0 as existing policy number.
The removed line is added by excel again.

Hope this helps find the solution.

Regards,

René