Excel 2010中的整数表示与Excel 2003（C + +插件） [英] Representation of integers in Excel 2010 vs Excel 2003 (C++ plugin)

问题描述

简介

我继承了一个使用VS2008，xlw 2.0和excel 2003开发的旧版插件项目>

我已经设法将它移植到xlw 4.0标题（一些方法更改的名称，当支持Excel 2007被添加，类似的东西）。我不会生成任何东西（还），因为旧的代码是手工编写的，我不会改变的东西，这是工作，并有一个合理的结构和代码逻辑只是为了它的缘故。

代码在excel 2003中工作正常。我今天用excel 2010试试，发现了一个最令人困惑（和可怕的）错误。

$ b

困惑的行为

n行，1列）（作为小正整数）作为XlfOper&并且我想将其转换为（旧式）数组。代码基本上遍历每个行，然后调用对应给定行的XlfOper上的AsInt（）方法。例如：

  for（long iy = 0; iy< numcols; ++ iy）
 {
 for（long ix = 0; ix  convertXflOper（x（ix，iy），a1D [iy * numrows + ix]）; 
} 
  

其中a1D是在此特定项目中使用的1维数组类型，convertXflOper是一个模板函数，用于这些类型：

  void convertXflOper（const XlfOper& x，long& y）
 {
y = x.AsInt（）; 
} 
  

在给定的行，我在excel工作簿中的数字10.

在Excel 2003中，此值将转换为10.在Excel 2010下，此值将转换为9.

我已经调试它，问题是，它真的读取不是10，而是一个双近似。 （立即窗口的）输出是

  x.lpxloper4 _-> val.num 
 9.9999999999999982 // wrong ：我不是要求10.0，我要求在Excel 2010中10 
  

>

  x.lpxloper4 _-> val.num 
 10.000000000000000 // right：整数是整数
  

在Excel 2003中。因此，C ++代码，就像它漂亮（或不漂亮），似乎没有错。 AsInt（）方法的行为类似于转换， static_cast< long>（9.9999999999999982）是9而不是10。

问题

这个东西会让我很不好意思，主要是因为很容易忽略它：对于许多其他整数值它在2003年和2010年的excel中正常工作。

我在excel接口中做错了什么，认为整数必须被视为double？我怎么能确定这是不会发生谁知道在哪里和什么时候？

解决方案

Excel不会在内部保存整数 - 所有数字（整数，日期，货币等）持有两倍。所以Oper val.num总是一个double，并且xltypeNum的操作符总是double。
（Opers可以在val.w中保存整数，但只是作为XLM宏流控制的一部分，已经过时了几十年并且不可能在实践中得到满足。）

我不知道为什么你在Excel 2007中得到9.9999999 - 你如何将值传递给Oper？

Introduction

I have inherited a legacy plugin project (in C++) developed with VS2008, xlw 2.0 and excel 2003.

I have managed to port it to xlw 4.0 headers (some method changed name when support for Excel 2007 was added, things like that). I am not generating anything (yet), since the old code was written by hand and I am not going to change something which is working, and has a reasonable structure and code logic only for the sake of it.

The code works as expected under excel 2003. I tried today with excel 2010 and found a most puzzling (and scarying) bug.

Puzzling behaviour

I receive (from a VBA macro) a column (n rows, 1 column) of numbers (small positive integers by construction) as a XlfOper & and I want to convert it to an (old-style) array. The code is basically going through each row and then is calling the AsInt() method on the XlfOper corresponding to the given row. Something like:

for (long iy = 0; iy < numcols; ++iy)
{
for (long ix = 0; ix < numrows; ++ix)
    convertXflOper( x(ix, iy), a1D[iy*numrows + ix] ); 
}

where a1D is the 1 dimensional array type used in this particular project, and convertXflOper is a template function, which is for these types:

void convertXflOper (const XlfOper &x, long & y)
{
    y = x.AsInt();
}

At a given row, I have in the excel workbook the number 10.

Under Excel 2003, this is converted to 10. Under Excel 2010 this is converted to 9.

I have debugged it, and the problem is that it is really reading not 10, but a double approximation to it. The output (of the immediate window) is

x.lpxloper4_->val.num
9.9999999999999982 // wrong: I am not asking for 10.0, I am asking for 10

in Excel 2010, and

x.lpxloper4_->val.num
10.000000000000000 // right: an integer is an integer

in Excel 2003. So the C++ code, as pretty (or not) as it is, appears not to be at fault. The AsInt() method behaves like a cast, and static_cast<long>(9.9999999999999982) is 9, not 10.

Question

This thing was going to bite me quite badly, mainly because it was very easy to overlook it: for many other integers values it works correctly both in excel 2003 and 2010.

What am I doing wrong in the excel interface in that it thinks that an integer must be treated as a double? How can I be sure that this is not going to happen who-knows-where-and-when? What is the best way to fix this?

解决方案

Excel does not hold integers internally - all numbers (integers, dates, times, currency etc) are held as doubles. So the Oper val.num is always a double, and an oper of xltypeNum is always a double.
(Opers can hold integers in val.w but only as part of things like XLM Macro flow control which have been obsolete for decades and are unlikely to be met in practice).
I don't know why you get 9.9999999 in Excel 2007 - how are you passing the value to the Oper?

这篇关于Excel 2010中的整数表示与Excel 2003（C + +插件）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！