Free Support Forum - aspose.com

Behavior different between aspose.cells and excel

i have seen some different behavior in aspose.cells

this function in a2 : =@INDEX(D1:D5;MATCH(IF(COUNTIF(C1:C5;B1);B1;5089);C1:C5);0)

image.png (6.0 KB)

   [TestMethod]
        public void TestFetchIndexData()
        {
            var workbook = new Aspose.Cells.Workbook(@".\resources\book.xlsx");
            var worksheet = workbook.Worksheets["TestMatch"];

           
            worksheet.Cells["B1"].PutValue(5089);

            workbook.CalculateFormula();
            var resultWithoutColumn = worksheet.Cells["A2"].Value;
            var resultWithColumn = worksheet.Cells["A3"].Value;
            workbook.Save(@".\resources\TestFetchIndexData.xlsx");

            Assert.AreEqual("l", resultWithColumn);
            Assert.AreEqual("l", resultWithoutColumn); // will fail... in excel this cell is l 
        }<a class="attachment" href="/uploads/discourse_instance3/34827">

see attachment

@Pietswieb,
Thank you for your query.
I have tried to create the template file book.xlsx here but facing some issues. Could you please share your template file book.xlsx with us for testing? We will use it to test the scenario and provide our feedback accordingly.

@Pietswieb,

I was able to download the attachment after I updated/fixed your post for scripts. I have tested your scenario/ case and reproduced the issue as you mentioned by using your template file and sample code. I found formula calculation is different for A2 cell in Aspose.Cells and MS Excel:
e.g
Sample code:

var workbook = new Aspose.Cells.Workbook(“e:\test2\book.xlsx”);
var worksheet = workbook.Worksheets[“TestMatch”];

        worksheet.Cells["B1"].PutValue(5089);

        workbook.CalculateFormula();
        var resultWithoutColumn = worksheet.Cells["A2"].Value;//0.0 - Not Ok
        var resultWithColumn = worksheet.Cells["A3"].Value;//1 - Ok

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

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

@Pietswieb,
This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-47149”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

1 Like

@Pietswieb,

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

Your issue should be fixed in it.

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

i have tested the new version, but i can’t load the book.xlsx anymore. when i revert back to 20.1.0 it will load the workbook with no problem… .

this is the error i encounter

at .(Stream , TextWriter , Encoding , EventHandler`1 )
at .(Stream )
at .(String , Stream , LoadOptions )
at Aspose.Cells.Workbook.(String , LoadOptions )
at POC_Aspose.CellCheck.UnitTest1.TestFetchIndexData() in \POC_Aspose.CellCheck\POC_Aspose.CellCheck\UnitTest1.cs:line 43

@Pietswieb,

I did test using your original file and sample code, it works fine and I do not find any issue. The file is loaded fine and values are retrieved as expected, see the screenshot for your reference:
sc_shot12.png (10.5 KB)

Please make sure that you are using the latest version/fix: Aspose.Cells for .NET v20.1.9, you may even print the version number using the line of code at the start of your code:
Console.WriteLine(CellsHelper.GetVersion());

? CellsHelper.GetVersion()
“20.1.9”

i might be the .net version. I am using .Net core 2.2

@Pietswieb,

Well, you need to have .NET Core fix. I am afraid, currently it is not available at the moment. Please wait for a few days, we will either share the .NET Core fix here or let our next official release Aspose.Cells for .NET v20.2 comes out (the release is scheduled to be published in the next week or so, the release archive may include the NET Standard library (fix)).

1 Like

@Pietswieb,

Please try our latest version/fix: Aspose.Cells v20.1.11 (attached)

Your issue should be fixed in it.

Let us know your feedback.
Aspose.Cells20.1.11 For .NetStandard20.Zip (4.1 MB)

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