十进制分隔符国际化Excel和自动化的问题 [英] Decimal Seperator Internationalization Issues with Excel and automation

问题描述

 

我们有一个excel电子表格，它使用VLookups和字符串连接从工程表中检索常量。 我们使用Excel自动化界面提供此数据

当我们使用欧洲数字时出现问题。  （他们互换"，"和"。"。） 这打破了一切。 重新格式化我的自动输入数字无济于事。 我怀疑它与字符串cat和vtable查找有关

有没有办法强制excel使用句号"。"作为小数分隔符。

以编程方式暂时更改LCID（区域设置ID）不是一个好的解决方案，因为如果excel已经打开，这将无效。 对于当前可能正在运行和打开的程序，也会产生不可预测的后果。

解决方案

我不确定为什么你必须依赖在查找时查看句点和数字。 我过去曾遇到过类似的问题，但从未真正考虑过为什么会出现这种问题。 我的解决方案一直只是执行字符串查找。 
现在您发布了问题我正在考虑正确的解决方案。

如果您将所有数字条目转换为数字（不是字符串），那么您应该没有问题。  ;您可能需要将单元格的格式从常规更改为数字（不确定）。

我曾经使用如下所示的查找。 我在第一个执行字符串查找的数字周围有引号。

= VLOOKUP（"12.5"，G1：H3,2）

更改为数字查找 将删除对国家/地区的依赖，如下面的行。

= VLOOKUP（12.5，G1：H3,2）

 

 

We have an excel spreadsheet which uses VLookups and string concatinations to retrieve constants from an engineering table.  We feed this data in using an Excel Automation interface

The problem comes when we use european numbers.  (They interchange the "," and ".")  This breaks everything.  Reformatting my automation input numbers doesn't help.  I suspect it has to do with the string cats and vtable lookups

Is there a way to force excel to use the period "." as the decimal seperator.

Temporarily changing the LCID (Locale ID) programatically is not a good solution as this will not work if excel is already open.  There is also unpredictable consequences for programs that might be currently running and open.

解决方案

I'm not sure why you have to rely on looking at the periods and numbers when doing a lookup.  I have had similar problems in the past but never really thought about why it was occuring.  My solution has always been to just perform a string lookup.  Now that you posted you question I thinking about the correct solution.

If you convert all you numeric entries to numbers (not strings) you shouldn't have a problem.  You may need to change the format of the cells from general to a Number (not sure).

I used to use a lookup like the one below.  I have quotes around the first number which is performing a string lookup.

=VLOOKUP("12.5",G1:H3,2)

Changing to a number lookup would remove the dependency on the country like the line below.

=VLOOKUP(12.5,G1:H3,2)

 

这篇关于十进制分隔符国际化Excel和自动化的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！