Can someone confirm how Microsoft Excel 2007 inter

问题:

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.

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.

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.

回答1:

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.

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>

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

回答2:

This page has a reference for internal Excel data types.

回答3:

It stored the numbers as double.