Npoi Round Formula

Question

I am new in NPOI while I was working on a project I faced a strange issue and have no idea how to fix it. I am reading formula data from an Excel sheet which contains the Round function and revaluate cell using npoi evaluator XSSFFormulaEvaluator. Every thing until this moment work fine with me with no problem until facing this issue.

In Excel sheet I have this formula =round(693.9648;2) the result in Excel for this number will be 693.96 but the result with NPOI Evaluator will be 693.97

Does any one have an answer how I can make the evaluator act the same like Excel sheet?

Answer 1

I had almost exactly the same problem. The number 481.75478 was being used in the Excel ROUND() function to 2dp. e.g. ROUND(481.75478, 2)

When executing this using the same workbook:

• Excel would evaluate to 481.75
• NPOI would evaluate to 481.76

It seemed to be down to the way that NPOI had implemented the Excel ROUND function in C#. In the end I downloaded the source code for the NPOI library, changed the ROUND function to use standard .NET rounding (using MidpointRounding.AwayFromZero as an argument for our purposes) and used my custom DLL.

I downloaded the source code and made the following modifications:

File: \main\SS\Formula\Functions\MathX.cs

Original Code

    public static double Round(double n, int p)
    {
        double retval;

        if (double.IsNaN(n) || double.IsInfinity(n))
        {
            retval = double.NaN;
        }
        else if (double.MaxValue == n)
            return double.MaxValue;
        else if (double.MinValue == n)
            return 0;
        else
        {
            if (p >= 0)
            {
                int temp = (int)Math.Pow(10, p);
                double delta = 0.5;
                int x = p + 1;
                while (x > 0)
                {
                    delta = delta / 10;
                    x--;
                }
                retval = (double)(Math.Round((decimal)(n + delta) * temp) / temp);
            }
            else
            {
                int temp = (int)Math.Pow(10, Math.Abs(p));
                retval = (double)(Math.Round((decimal)(n) / temp) * temp);
            }
        }

        return retval;
    }

Updated Code

    public static double Round(double n, int p)
    {
        double retval;

        if (double.IsNaN(n) || double.IsInfinity(n))
        {
            retval = double.NaN;
        }
        else if (double.MaxValue == n)
            return double.MaxValue;
        else if (double.MinValue == n)
            return 0;
        else
        {
            if (p >= 0)
            {
                // ***** updated to use .NET MidpointRounding.AwayFromZero rounding ***** //
                retval = (double)Math.Round((decimal)n, p, MidpointRounding.AwayFromZero);
            }
            else
            {
                retval = (double)(Math.Round((decimal)(n) / temp) * temp);
            }
        }

        return retval;
    }

Note/Disclaimer

This hasn't been tested yet, so could cause some other unforeseen errors (I'm not sure why the original code was doing some sort of deltafication), but this has fixed the issue I had where rounding was differing between Excel and NPOI. I'll come back and update the answer if I find any issues when the app gets used in anger.

Update 2017-12-04

This has been in production now for a while with no issues. I also realised I should have submitted a PR for this to the project which I now have.