有人可以确认Microsoft Excel 2007在内部如何表示数字吗? [英] Can someone confirm how Microsoft Excel 2007 internally represents numbers?

问题描述

我非常了解IEEE 754浮点标准，因为我必须为考试而学习.我确切地知道如何使用浮点数以及它们可能存在的问题.我可以对浮点数的二进制表示形式进行任何操作.

I know the IEEE 754 floating point standard by heart as I had to learn it for an exam. I know exactly how floating point numbers are used and the problems that they can have. I can manually do any operation on the binary representation of floating point numbers.

但是，我还没有找到一个单一的来源，它明确指出excel使用64位浮点数来内部表示excel中除文本以外的每个单元格类型".我不知道某些类型使用带符号整数还是无符号整数，而某些类型使用64位浮点数.

However, I have not found a single source which unambiguously states that excel uses 64 bit floating point numbers to internally represent every single cell "type" in excel except for text. I have no idea whether some of the types use signed or unsigned integers and some use 64 bit floating point.

我发现有数以万亿计的文章，其中1)描述了浮点数，然后2)谈论由于浮点数而对excel保持谨慎.我还没有找到一条陈述说除文本外，所有类型均为64位浮点数".我还没有找到一条陈述说:更改单元格的类型只会更改其视觉表示，而不会更改其内部表示，除非您将类型从文本更改为非文本的其他类型，或者将其他类型更改为非文本.不是文本到文本".

I have found literally trillions of articles which 1) describe floating point numbers and then 2) talk about being careful with excel because of floating point numbers. I have not found a single statement saying "all types are 64 bit floating point numbers except text". I have not found a single statement which says "changing the type of a cell only changes its visual representation and not its internal representation, unless you change the type from text to some other type which is not text or you change some other type which is not text to text".

从字面上看，这是我想知道的全部内容，它是如此简单和公理，令我惊讶的是，我发现有数以万计的文章和页面围绕这些陈述而没有直接陈述.

This is literally all I want to know, and it's so simple and axiomatic that I am amazed that I can find trillions of articles and pages which talk around these statements but do not state them directly.

推荐答案

Excel 2007支持OpenXML格式，该格式是一个ZIP文件(.XLSX)，其中包含一堆XML文件.有一个可以使用OpenXML格式的SDK，您可以在此处获取文档，然后下载

Excel 2007 supports the OpenXML format which is a ZIP file (.XLSX) containing a bunch of XML files. There is an SDK to work with the OpenXML format you can get the docs for it here, and download it here.

基本上，数字是作为纯文本存储在元素中的，因此，如果UI中单元格A1的数字为42，而单元格A2的数字为81.56，则XML看起来像下面的XML片段:

Basically numbers are stored as plain text within a element so if cell A1 has the number 42 and cell A2 has the number 81.56 in the UI, the XML would look like the follow XML fragment:

<row r="1" spans="1:2">
    <c r="A1">
        <v>42</v>
    </c>
    <c r="B1">
        <v>81.569999999999993</v>
    </c>
</row>

在使用OpenXML时，我强烈建议您仅使用SDK，而不要自己动手使用它.

When working with OpenXML I would highly recommend just using the SDK and not going after it on your own.

这篇关于有人可以确认Microsoft Excel 2007在内部如何表示数字吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！