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.
