Calculate ROUND Excel function/formula via Aspose.Cells in .NET

Aspose.Cells Product Family
1

Hello,

When I read a rounded calculated field from an excel-file with Aspose.Cells, I get an unexpected value.

For instance, I have the following calculated field: =ROUND(18750*0,6033;2)

In Excel I get the value: 11311,88
In Aspose.Cells I get the value: 11311,87

I added an attachment with the calculated field and below the code to reproduce this:

FileStream stream = File.Open(@"E:\reproduction_test.xls", FileMode.Open);
Workbook workbook = new Workbook(stream);
workbook.CalculateFormula();
Worksheet uitvoerWorksheet = workbook.Worksheets["test"];
string value2 = uitvoerWorksheet.Cells["A1"].StringValue;
stream.Close();
 
Kind regards,
Jan Kok
2

Hi Jan Kok,


Thank you for bringing this to our knowledge.
We are able to reproduce the said issue with our latest fix version of Aspose.Cells for .NET v5.3.3.5. So we have logged it in our Bug Tracking System under Ticket ID CELLSNET-28399. Soon we will get back to you on this.
3

Hi,

We have fixed this issue. Please download Aspose.Cells for .NET v5.3.3.6.

4

Hi Shakeel Faiz,

I've done some intensive testing. In some situations the round-function is not giving the right value. You can test this with the attachment and the following code:

FileStream stream = File.Open(@"E:\reproductionTest.xls", FileMode.Open);
Workbook workbook = new Workbook(stream);
workbook.CalculateFormula();
Worksheet rekenbladWorksheet = workbook.Worksheets["rekenblad"];
string valueA = rekenbladWorksheet.Cells["L38"].StringValue;
string valueB = rekenbladWorksheet.Cells["L39"].StringValue;
string valueC = rekenbladWorksheet.Cells["L40"].StringValue;
string valueD = rekenbladWorksheet.Cells["O40"].StringValue;
stream.Close();
L38 and L39 contains the value '128323,235'

after the ROUND(L38;2) and ROUND(L39;2) I get the following results:
L40 and O40 contains the value '128323,23' 
Kind regards,
Jan Kok
5

Hi Jan Kok,


I have verified the issue as mentioned in your last post. Moreover, if you comment the statement Workbook.CalculateFormula(), then the results are as expected. I have re-opened the Ticket associated with this thread, also I have attached your comments to it. We will soon sort this out and let you know here.

Thank you for your feedback.
6

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

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
7

Hi Babar Raza,

Did you had some time to find the problem?

Kind regards,

Jan Kok

8

Hi Babar Raza,

I've attached a new excel-file with a calculation and a round. You can see it rounds down instead of up.

FileStream stream = File.Open(@"test.xls", FileMode.Open);
Workbook workbook = new Workbook(stream);
workbook.CalculateFormula();

Worksheet rekenbladWorksheet1 = workbook.Worksheets[“Blad1”];

string value1 = rekenbladWorksheet1.Cells[“A1”].StringValue;

string value2 = rekenbladWorksheet1.Cells[“A2”].StringValue;

string value3 = rekenbladWorksheet1.Cells[“A3”].StringValue;
stream.Close();


The calculated value in A1 = 534,185. In A2 with ROUND(A1;2) I get 534,18 with Aspose.
When I add 0,001 to A1 (534,186) I get 534,19 with Aspose. I expect in both cases the number 534,19.
 
I Hope you can help me soon with this issue.
 
Kind regards,
Jan Kok
9

Hi Jan Kok,


Thank you for your feedback and testing. I have attached your comments to the Ticket.

For reference, by logging a Ticket we have assigned this issue to our Development Team. Now, I have elevated the Ticket’s priority and also have requested for the updates on this issue. As soon as we receive any news, we will post it here.

Regards,
10

Hi Jan Kok,


We have just released Aspose.Cells for .NET v6.0.0.1 as a fix to your mentioned issue in our Formula Engine. Please test your scenario with this latest assembly and let us know of your feedback.

Thank you for your patience.

11

Hi Barbar Raza,

It's unfortunately not fixed yet.

ROUND(534,185;2) ==> 534,18

but

ROUND(499,185;2) ==> 499,19

I hope you can find what's going wrong with the ROUND-functionality.

Kind regards,

Jan Kok

12

Hi Jan Kok,


Thank you for your feedback.
I have noticed this inconsistency of results between Excel and Aspose. Therefore, I have re-opened the Ticket associated with this thread and also have logged my comments to it. We will try to fix this problem as soon as possible.
13

Hi,

These issues are caused by .NET Framework.

1 - The previous two issues are caused by double precision. Please check “Floating-Point Values and Loss of Precision” on the doc: http://msdn.microsoft.com/en-us/library/system.double.aspx

For example : double x = 18750 * 0.6033;

The actual value of x is 11311.874999999998, the display string value(11311.875) is same as x.ToString(“R15”).

If we directly call Math.Round(x,2, MidpointRounding.AwayFromZero), it will return unexpected value. So we have to process double value before call this method.

2 - The latest issue is more strange, please try the following code:

double x = Math.Round(534.185, 2, MidpointRounding.AwayFromZero);

double y = Math.Round(499.185, 2, MidpointRounding.AwayFromZero);

The x value is 534.18, not 534.19.

We will look into it later.

14

Hi,


We have fixed this issue in Aspose.Cells for .NET v6.0.0.2. Please test your requirement with this latest assembly and let us know of your feedback.

15

Hi Shakeel,

The first testresults are promising with Aspose.Cells version 6.0.0.2. Are you now using decimals instead of doubles?

We did some testing here and noticed that rounding with decimals works:

Math.Round(534.185m, 2, MidpointRounding.AwayFromZero)
==> 534.19
Math.Round(499.185m, 2, MidpointRounding.AwayFromZero);
==> 499.19

Kind regards,

Jan

16

Hi,

It’s great to know, results are promising now. However, I am not sure, if decimals or doubles are being used for rounding. I have therefore forwarded your comment to Aspose.Cells development team. We will update you asap.

17

Hi Jan,

You are right.

we used decimals instead of doubles if the double value less then decimal.MaxValue.

18

Hi Shakeel,

It's getting better, but unfortunately I found still a rounding problem. The value 0.415 is after rounding 0.41 instead of 0.42. This happens in one of our excel-sheets. I have tested this with version 6.0.0.0 and 6.0.0.2 of Aspose.Cells. The problem happens in version 6.0.0.2.

For reproduction you can use the excel-file attached and code below. What you see is that K32 (see variable valueK32) contains the number 0.410 instead of 0.420.

   	    FileStream stream = File.Open(@"E:\rekenschemaGVW_2010v1_0310.xlsx", FileMode.Open);

        <span style="color: rgb(43, 145, 175);">Workbook</span> workbook = <span style="color: blue;">new</span> <span style="color: rgb(43, 145, 175);">Workbook</span>(stream);



            #region

Worksheet invoerWorksheet = workbook.Worksheets[“invoer”];

invoerWorksheet.Cells[“B2”].PutValue(20100101);


        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B7"</span>].PutValue(1989);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B8"</span>].PutValue(1989);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B9"</span>].PutValue(1989);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B10"</span>].PutValue(1989);

        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B17"</span>].PutValue(1825);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B19"</span>].PutValue(1825);

        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B21"</span>].PutValue(<span style="color: rgb(163, 21, 21);">"m2"</span>);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B22"</span>].PutValue(1087);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B23"</span>].PutValue(19);

        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B25"</span>].PutValue(1);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B26"</span>].PutValue(1);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B27"</span>].PutValue(-25);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B28"</span>].PutValue(<span style="color: rgb(163, 21, 21);">"staal"</span>);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B29"</span>].PutValue(50);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B30"</span>].PutValue(30);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B31"</span>].PutValue(20);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B32"</span>].PutValue(27);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B33"</span>].PutValue(22);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B34"</span>].PutValue(17);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B35"</span>].PutValue(47);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B36"</span>].PutValue(27);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B37"</span>].PutValue(17);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B38"</span>].PutValue(27);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B39"</span>].PutValue(7);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B40"</span>].PutValue(5);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B41"</span>].PutValue(1);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B42"</span>].PutValue(1);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B43"</span>].PutValue(1);

        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B44"</span>].PutValue(543.5);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B45"</span>].PutValue(326.1);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B46"</span>].PutValue(217.4);

        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B48"</span>].PutValue(3000);

        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B50"</span>].PutValue(1);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B51"</span>].PutValue(1);
        invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B52"</span>].PutValue(1);

        workbook.CalculateFormula();

        <span style="color: rgb(43, 145, 175);">Worksheet</span> uitvoerWorksheet = workbook.Worksheets[<span style="color: rgb(163, 21, 21);">"uitvoer"</span>];
        <span style="color: blue;">string</span> valueB23 = uitvoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B23"</span>].StringValue;

        <span style="color: rgb(43, 145, 175);">Worksheet</span> rekenbladWorksheet = workbook.Worksheets[<span style="color: rgb(163, 21, 21);">"rekenblad"</span>];
        <span style="color: blue;">string</span> valueT43 = rekenbladWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"T43"</span>].StringValue;
        <span style="color: blue;">string</span> valueS43 = rekenbladWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"S43"</span>].StringValue;
        <span style="color: blue;">string</span> valueK32 = rekenbladWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"K32"</span>].StringValue;



            #endregion

Please use the other reproduction tests in this thread for testing the other cases I submitted when fixing this problem.

Kind regards,

Jan Kok

19
Hi,

Please post/attach your template file here that is used in the code segment, so that we can check your issue soon.

Thank you.
20

Hi Amjad Sahi,

Thanks. I just attached the file to my post (316142).

Kind regards,

Jan Kok