问题描述
我遇到了一个问题，在使用 Microsoft ACE 驱动程序 打开 Excel 电子表格.
I am having a problem where it seems that the results of some calculations change after having used the Microsoft ACE driver to open an Excel spreadsheet.
下面的代码重现了这个问题.
The code below reproduces the problem.
对 DoCalculation 的前两次调用产生相同的结果.然后我调用函数OpenSpreadSheet，它使用ACE 驱动程序打开和关闭Excel 2003 电子表格.您不会期望 OpenSpreadSheet 对上次调用 DoCalculation 有任何影响，但事实证明结果实际上发生了变化.这是程序生成的输出:
The first two calls to DoCalculation yield the same results. Then I call the function OpenSpreadSheet which opens and closes an Excel 2003 spreadsheet using the ACE driver. You would not expect OpenSpreadSheet to have any effect on the last call to DoCalculation but it turns out that the result actually changes. This is the output that the program generates:
1,59142713593566
1,59142713593566
1,59142713593495
注意最后 3 位小数的差异.这看起来差别不大，但在我们的生产代码中，计算很复杂，因此产生的差异非常大.
Note the differences on the last 3 decimals. This does not seem like a big difference but in our production code the calculations are complex and the resulting differences are quite large.
如果我使用 JET 驱动程序而不是 ACE 驱动程序没有区别.如果我将类型从 double 更改为 decimal，错误就会消失.但这不是我们的生产代码中的选项.
It makes no difference if I use the JET driver instead of the ACE driver. If I change the types from double to decimal the error goes away. But this is not an option in our production code.
我在 Windows 7 64 位上运行，并且程序集是为 .NET 4.5 x86 编译的.使用 64 位 ACE 驱动程序不是一个选项，因为我们正在运行 32 位 Office.
I am running on a Windows 7 64 bit and the assemblies are compiled for .NET 4.5 x86. Using the 64 bit ACE driver is not an option as we are running 32 bit Office.
有人知道为什么会发生这种情况以及如何解决吗?
Does anybody know why this is happening and how I can fix it?
以下代码重现了我的问题:
The following code reproduces my problem:
static void Main(string[] args)
{
DoCalculation();
DoCalculation();
OpenSpreadSheet();
DoCalculation();
}
static void DoCalculation()
{
// Multiply two randomly chosen number 10.000 times.
var d1 = 1.0003123132;
var d3 = 0.999734234;
double res = 1;
for (int i = 0; i < 10000; i++)
{
res *= d1 * d3;
}
Console.WriteLine(res);
}
public static void OpenSpreadSheet()
{
var cn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;data source=c: empworkbook1.xls;Extended Properties=Excel 8.0");
var cmd = new OleDbCommand("SELECT [Column1] FROM [Sheet1$]", cn);
cn.Open();
using (cn)
{
using (OleDbDataReader reader = cmd.ExecuteReader())
{
// Do nothing
}
}
}
推荐答案
这在技术上是可行的，非托管代码可能会修改 FPU 控制字并改变其计算方式.众所周知的麻烦制造者是使用 Borland 工具编译的 DLL，它们的运行时支持代码会取消屏蔽可能导致托管代码崩溃的异常.而 DirectX，它以修改 FPU 控制字以将 double 的计算作为 float 执行以加快图形数学运算而闻名.
This is technically possible, unmanaged code may be tinkering with the FPU control word and change the way it calculates. Well-known trouble makers are DLLs compiled with Borland tools, their runtime support code unmasks exceptions that can crash managed code. And DirectX, it is known for tinkering with the FPU control word to get calculations with double to be performed as float to speed up graphics math.
这里出现的 FPU 控制字更改的具体类型是舍入模式，当 FPU 需要将具有 80 位精度的内部寄存器值写入 64 位内存位置时，它会使用这种模式.它有 4 个选项来进行这种转换:向上舍入、向下舍入、截断和舍入到偶数(银行家的舍入).非常小的差异，但你确实努力快速积累它们.如果你的数值模型不稳定，那么你肯定会看到最终结果的不同.这并不能使它或多或少准确，只是不同.
The specific kind of FPU control word change that appears to be made here is the rounding mode, used by the FPU when it needs to write an internal register value with 80-bit precision to a 64-bit memory location. It has 4 options to make that conversion: round up, round down, truncate and round-to-even (banker's rounding). Very small differences but you do make an effort to accumulate them rapidly. And if your numerical model is unstable then you certainly will see a difference in the end result. That doesn't make it more or less accurate, just different.
托管代码对执行此操作的代码毫无防备，您无法直接访问 FPU 控制字.它需要编写汇编代码.你有一个可用的技巧，高度无证但非常有效.CLR 将在处理异常时重置 FPU.所以你可以这样做:
Managed code is pretty defenseless against code that does this, you cannot directly access the FPU control word. It requires writing assembly code. You've got one trick available, highly undocumented but pretty effective. The CLR will reset the FPU whenever it handles an exception. So you could do this:
public static void ResetMathProcessor()
{
if (IntPtr.Size != 4) return; // No need in 64-bit code, it uses SSE
try {
throw new Exception("Please ignore, resetting the FPU");
}
catch (Exception ex) {}
}
请注意，这很昂贵，因此请尽量少用.当您调试代码时，它是一个主要的 pita，因此您可能希望在 Debug 构建中禁用它.
Do beware that this is expensive so use as infrequently as possible. And it is a major pita when you debug code so you might want to disable this in the Debug build.
我应该提到一个替代方案，您可以在 msvcrt.dll 中调用 _fpreset() 函数.但是，如果您在也执行浮点数学的方法中使用它是有风险的，抖动优化器不知道这个函数会抖动地板垫.您需要彻底测试发布版本:
I should mention an alternative, you can pinvoke the _fpreset() function in msvcrt.dll. It is however risky if you use it inside of a method that also performs floating point math, the jitter optimizer doesn't know that this function jerks the floor mat. You'll need to thoroughly test the Release build:
[System.Runtime.InteropServices.DllImport("msvcrt.dll")]
public static extern void _fpreset();
请记住，这样做不会让您的计算结果在任何方面都更加准确.只是不同.就像在没有调试器的情况下运行代码的 Release 版本会产生与 Debug 版本不同的结果.Release 构建代码将不那么频繁地执行这种舍入，因为抖动优化器会努力将 FPU 中的中间结果保持在 80 位精度.产生与 Debug 构建不同的结果，但实际上更准确.给予或接受.这种 80 位中间格式是英特尔犯下的十亿美元错误，在 SSE2 指令集中没有重复.
And do keep in mind that this does not make your calculation results more accurate in any way. Just different. Just like running the Release build of your code without a debugger will produce different results than the Debug build. The Release build code will perform this kind of rounding less frequently since the jitter optimizer makes an effort to keep intermediate results inside the FPU at 80-bit precision. Producing a different result from the Debug build but one that actually is more accurate. Give or take. This 80-bit intermediate format was Intel's billion dollar mistake, not repeated in the SSE2 instruction set.
这篇关于Microsoft ACE 驱动程序更改了我程序其余部分的浮点精度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！