在使用脚本语言对Excel文件进行操作时, 有时会发现, 脚本读取出的值和实际在Excel单元格中显示的值不一致的情况 搜索资料后整理出如下笔记

Number Format(数字格式)

Excel 已经有许多内置的数字格式, 如 货币, 百分比等, 你还可以根据自己的需求设置自定义的数字格式

Applying a number format only changes how the number is displayed and doesn’t affect cell values that’s used to perform calculations. You can see the actual value in the formula bar.
应用数字格式只会改变数字的显示方式, 并不会影响用于计算的单元格值. 在公式栏(编辑栏)中可以看到实际的值

注意: 公式栏中显示的也不一定是单元格内容的真实值, 有些数字格式, 本身是一种复杂的数据类型, 比如日期, 公式栏中显示的值, 类似编程语言中默认的 toString() 方法的结果, 而对单元格设置数字格式后的显示值, 类似 format() 方法的结果

单元格的数字精度问题

当数字的长度达到12位及以上, 会自动以科学计数法显示该数字(仅限Microsoft Excel)

经实测在WPS表格中, 数字达到12位及以上, 会以文本格式显示该内容(默认靠左对齐, 完整显示内容), 而非科学计数法

当单元格中的数字长度超过15位时, 会截取15位之后的数字, 全部变成0, 并且在计算时, 使用非精确的数值进行计算

如, 在单元格中输入 1234567890123456789 回车后, 再查看编辑栏中的数值, 会显示 1234567890123450000

同样的, 小数部分过长的位数也会被截取

解决以上问题, 可以使用 添加单引号前缀的方法或者事先将单元格格式设置为文本类型

Microsoft Excel是根据IEEE 754规范设计的, 以确定它如何存储和计算浮点数.
然而, IEEE 754规范存在一些限制, 可以分为三个一般类别:

• 最大/最小限制:
  IEEE 754规范对浮点数的表示范围有一定的限制, 它不能表示超出一定范围的数值, 即存在最大值和最小值的限制
• 精度:
  由于浮点数是用有限的位数来表示, 所以存在精度问题. 在进行一些计算时, 可能会出现舍入误差, 导致结果不够准确.
• 重复的二进制数:
  由于二进制中一些小数（如1/3）在浮点数表示时是无限循环的, 所以在使用浮点数进行计算时, 可能会出现循环小数的问题.
  这些限制可能会影响在Excel中进行特定类型的计算时的准确性和精度. 为了避免这些问题, 需要在特定情况下谨慎处理浮点数的计算和比较.

格式自动转换

there are scenarios where Excel may automatically change the number format from General to something else, based on the characters that you typed into the cell. This feature saves you from having to manually make the easily recognized number format changes. The following table outlines a few examples where this can occur:
在某些情况下, Excel会根据你输入到单元格中的字符, 自动将数字格式从常规（General）更改为其他格式. 这个特性省去了手动进行常见数字格式更改的步骤. 以下是几个可能发生这种情况的例子:

解压查看单元格真实值

将xlsx格式解压, 打开 xl/worksheets/sheets.xml 文件可以看到

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

<sheetData>
    <row r="1" spans="1:6">
        <c r="A1" s="2">
            <v>45132</v>
        </c>
        <c r="B1" t="s">
            <v>0</v>
        </c>
        <c r="C1">
            <v>9</v>
        </c>
        <c r="D1" s="4" t="s">
            <v>1</v>
        </c>
        <c r="F1" s="1">
            <v>3.14</v>
        </c>
    </row>
    <row r="2" spans="6:6">
        <c r="F2" s="1">
            <v>45.6789</v>
        </c>
    </row>
</sheetData>

其中A1单元格中的 45132 就是数字格式的单元格内容储存的实际值, 表示从 1900-01-01 开始的日期天数

s="2" 表示在 styles.xml 中引用的样式的下标(下标从0开始), 对应下面的 numFmt 第三个格式标签, 最终在Excel中显示日期为 2023年7月2日

而B1, D1 中的 0 和 1 并不是单元格的值, B1, D1 中的 t="s" 表示引用了 sharedStrings.xml, 0 和 1 是引用的 sharedStrings.xml 的下标

1
2
3
4
5
6
7
8

<numFmts count="6">
    <numFmt numFmtId="42" formatCode="_ "￥"* #,##0_ ;_ "￥"* \-#,##0_ ;_ "￥"* "-"_ ;_ @_ "/>
    <numFmt numFmtId="44" formatCode="_ "￥"* #,##0.00_ ;_ "￥"* \-#,##0.00_ ;_ "￥"* "-"??_ ;_ @_ "/>
    <numFmt numFmtId="176" formatCode="yyyy"年"m"月"d"日";@"/>
    <numFmt numFmtId="177" formatCode="0.000###"/>
    <numFmt numFmtId="41" formatCode="_ * #,##0_ ;_ * \-#,##0_ ;_ * "-"_ ;_ @_ "/>
    <numFmt numFmtId="43" formatCode="_ * #,##0.00_ ;_ * \-#,##0.00_ ;_ * "-"??_ ;_ @_ "/>
</numFmts>

VBA

首先需要注意的是, 真实值和显示值是Office软件实现的(如 Excel, WPS), 而非VBA

使用VBA可以获取Excel单元格的真实值和显示值

使用 .Value2 或者.Value 和可以获取单元格内容的真实值(此处暂不讨论.Value2和.Value的区别), 真实值会被转化成VBA的数据类型

使用.Text 获取单元格的显示值, 显示值是所见即所得的(WYSIWYG), 在Excel单元格中看到是什么内容, VBA读出的就是什么内容, 显示值的数据类型是 String

VBA 数据处理应该避免使用显示值

在使用VBA进行自动化表格处理时, 应该避免使用 .Text 获取单元格显示值来进行处理

影响显示值的因素有很多, 显示值会受到单元格宽度, 单元格的数字格式, 甚至是国家地区等因素的影响

1. 当在单元格中设置输入日期, 当单元格宽度足够时, 会显示完整日期, 缩短单元格长度, 无法完整显示日期文本时候, 单元格的显示值就变成了一组由 ‘#’ 组成的符号(#号的数量取决于单元格长度)

2. 常规格式下, 单元格中数字长度达到12位及以上时, 显示值会变成科学计数法(MS-Excel中)

3. 在中国, 数字 123456789, 设置为 金额格式, 则显示值为 123,456,789.00, 前面还会加上货币符号 ￥, 而在某些国家, 如德国巴西等, 千分位分隔符和小数点的表示方法是相反的, 金额格式会显示为 123.456.789,00

Python openpyxl

openpyxl 中并没有显示值的概念, 因为 openpyxl 只是一个python库, 而真实值显示值的概念, 是在Office软件中实现的

所以, 使用Python(openpyxl或其他库)无法获取单元格的显示值

举一个例子, 一份 Excel 文件就像一个代码文件 xxxx.py, 而Office软件就像是IDE或者代码编辑器, 如 PyCharm, 代码文件本身只是一个普通的文本文件, 而代码的高亮显示, 自动提示等功能, 其实是由IDE提供的

它只能获取单元格的真实值以及数字格式, 无法获取对这个值设置对应数字格式之后的显示值

使用python时, 可以通过编写自定义函数的方式, 将真实值格式化为指定的数值格式

单引号

在单元格输入中, 在要输入的内容开头添加单引号', 会强制将输入的内容作为文本处理, 且使用脚本读取时, VBA 和 Python 都会忽略前面的单引号, 从而能够保证读取的内容和输入的内容完全一致(除了单引号前缀), 且单引号的优先级高于数字格式及单元格宽度等, 改变数字格式不会改变读取值

Keeping leading zeros and large numbers - Microsoft Support

如果在单元格开头插入单引号本身而不是作为文本标识符号转义, 可以参考下方链接中的做法 How to Add Single Quotes in Excel (5 Easy Methods) - ExcelDemy

excel - What is the difference between .text, .value, and .value2? - Stack Overflow

Available number formats in Excel - Microsoft Support

Format numbers as currency - Microsoft Support

Control settings in the Format Cells dialog box - Office | Microsoft Learn

Last digits are changed to zeros when you type long numbers in cells of Excel - Office | Microsoft Learn