解析Excel不同版本文件格式：BIFF结构详解

5星 · 超过95%的资源需积分: 14 127 浏览量更新于2024-07-20 收藏 990KB PDF 举报

本文档深入探讨了Microsoft Excel文件格式在不同版本（包括2.3, 4, 5, 95, 97, 2000, XP, 2003等）中的演变和OpenOffice.org对其的理解与支持。作者Daniel Rentz是该文档的主要贡献者之一，他通过公开许可证（Public Documentation License）分享了这些知识，允许他人自由使用和修改。文档详细地介绍了文件结构、二进制交换文件格式（BIFF）以及其记录结构，这对于理解和解析Excel文件至关重要。 1. **Excel文件格式的历史**： - 从早期的BIFF（Binary Interchange File Format）版本开始，每个版本都有其特定的记录结构，比如BIFF1到BIFF8分别对应Excel 2.3到2003年版本。随着软件的更新，文件格式也在不断优化，以适应新功能和兼容性要求。 2. **文档类型和结构**： - 文档结构分为两种类型：文档类型（Document Types）和二进制交互文件格式。前者定义了文件的基本组成部分，后者则是数据在文件内的存储方式。 3. **BIFF记录结构**： - BIFF记录是Excel文件的核心组成部分，每个记录包含了特定的功能或数据，如工作表数据、公式、图表信息等。理解这些记录有助于开发者解析和创建自定义的Excel文件。 4. **跨平台支持**： - OpenOffice.org（后来的LibreOffice）提供了对Excel文件格式的支持，通过XML格式的文档（如excelfileformat.sxw和excelfileformat.odt），使得不同软件之间能够更好地兼容和转换数据。 5. **开发资源**： - 提供了下载链接，包括PDF版本的官方文档和用于OpenOffice.org不同版本的XML源代码，以便于开发人员查阅和学习。此外，还提供了邮件列表订阅，以便获取关于格式更改和其他更新的信息。 6. **项目背景与更新**： - 该项目始于2001年6月29日，最后一次重大修订是在2008年4月2日，反映出作者持续关注并维护着这个关键的IT技术细节。这篇文档对于理解Excel文件格式的开发者、数据分析师和软件开发者来说，是一份宝贵的技术参考资源，它揭示了Excel文件的内部机制，对于实现数据迁移、开发工具和处理跨平台问题具有重要意义。

2 Document Structure

 Substreams in the BIFF4 Workspace Stream

In BIFF4 Workspace Streams, the Sheet Substreams are embedded in the Workspace Stream. They are similar in their

structure to BIFF4 Worksheet Streams.

Common structure of a BIFF4W Workspace Stream:

Workspace Stream (required)

First embedded BIFF4 Sheet Substream (optional)

Second embedded BIFF4 Sheet Substream (optional)

Third embedded BIFF4 Sheet Substream (optional)

⋮

2.4 BIFF Record Structure

Definition: BIFF Record

Most of the Excel streams or substreams (including all streams described in 2.3.3 and substreams described in

2.3.4) are divided into records. Each record contains specific data for the various contents or features in a

document. It consists of a header specifying the record type and size, followed by the record data.

Common structure of a BIFF record:

Offset Size Contents

0 2 Identifier

2 2 Size of the following data (sz)

}

Record header

Record data

The maximum size of the record data is limited and depends on the BIFF version. If the size of the record data exceeds

the current limit, one or more CONTINUE records (5.21) will be added. Inside a CONTINUE record the data of the

previous record continues as usual.

In this documentation only the record data without the headers is shown. All offsets are relative to the beginning of the

record data and not to the entire record. The contents of most of the records differ from BIFF version to version. This

will be described in separate tables. A few older records are replaced in newer BIFF versions. Excel does not write these

old records in new BIFF versions anymore.

2.5 Common Record Substructures

This chapter contains information about basic substructures which do not belong to specific records, for instance strings,

error codes, constant values, URLs, or line and area formatting.

2.5.1 Formatting Runs

Formatting runs describe the character formatting of strings. A formatting run contains the index of a character and the

index of a font in the font buffer. The font is used to format the indexed character and the following characters, until the

string ends or another formatting run follows.

Formatting run, BIFF2-BIFF5:

Offset Size Contents

0 1 First formatted character (zero-based)

1 1 Index to FONT record (5.45)

Formatting run, BIFF8:

Offset Size Contents

0 2 First formatted character (zero-based)

2 2 Index to FONT record (5.45)

2.5.2 Byte Strings (BIFF2-BIFF5)

All Excel file formats up to BIFF5 contain simple byte strings. The byte string consists of the length of the string

followed by the character array. The length is stored either as 8-bit value or as 16-bit value, depending on the current

record. The string is not zero-terminated. The encoding of the character array is dependent on the current record (for

example taken from the CODEPAGE record, 5.17, or from the FONT record, 5.45).

Offset Size Contents

0 1 or 2 Length of the string (character count, ln)

1 or 2

Character array (8-bit characters)

2.5.3 Unicode Strings (BIFF8)

From BIFF8 on, strings are always stored using UTF-16LE

text encoding. The character array is a sequence of 16-bit

values

. Additionally it is possible to use a compressed format, which omits the high bytes of all characters, if they are

all zero.

The following table describes the standard format of the entire string, but in many records the strings differ from this

format. This will be mentioned separately. It is possible (but not required) to store Rich-Text formatting information and

Asian phonetic information inside a Unicode string. This results in four different ways to store a string. The character

array is not zero-terminated.

For more information see http://en.wikipedia.org/wiki/UTF-16.

In most cases each value corresponds to a Unicode character. Only the Unicode characters above U+FFFF are encoded with a

“surrogate pair”, that are two 16-bit code values in UTF-16 (see footnote 3).

2.5 Common Record Substructures

 Asian Phonetic Settings Block

Asian phonetic text

(Ruby) can be used to provide extended phonetic information for specific characters or words. It

appears above the regular text (or to the right of vertical text), and can refer to single characters, groups of characters, or

entire words.

Offset Size Contents

0 2 Unknown identifier 0001

2 2 Size of the following data (10 + 2∙ln + 6∙np)

4 2 Index to FONT record (5.45) used for the Asian phonetic text

6 2 Additional settings for the Asian phonetic text:

Bit Mask Contents

1-0

0003

H Type of Japanese phonetic text (type):

= Katakana (narrow) 10

= Hiragana

= Katakana (wide)

3-2

000C

H Alignment of all portions of the Asian phonetic text (align):

= Not specified (Japanese only) 10

= Centered

= Left (Top for vertical text) 11

= Distributed

5-4

0030

H 11

(always set)

8 2 Number of portions the Asian phonetic text is broken into (np).

If np = 0, the Asian phonetic text refers to the entire cell text.

10 2 Total length of the following Asian phonetic text (number of characters, ln)

12 2 Repeated total length of the text

14 2∙ln or 2 Character array of Asian phonetic text, no Unicode string header, always 16-bit characters.

Note: If ln = 0, this field is not empty but contains 0000

14+2∙ln 6∙np List of np structures that describe the position of each portion in the main text. Each

structure contains the following fields:

Offset Size Contents

0 2 First character in the Asian phonetic text of this portion (cpa)

2 2 First character of the main text belonging to this portion (cpm)

4 2 Number of characters in main text belonging to this portion (ccm)

Example: Japanese word Tokyo (東京) with added hiragana (とうきょう)

. The following examples show the

contents of the important fields of the Asian Phonetic Settings Block.

Example 1: Hiragana centered over the entire word:

とうきょう

東京

type = 10

(hiragana)

align = 10

(centered)

np = 0 (no portions, hiragana refers to entire text)

ln = 5 (length of entire hiragana text)

No portion structures

For more information see http://en.wikipedia.org/wiki/Ruby_characters.

Example taken from http://en.wikipedia.org/wiki/Ruby_characters.

剩余249页未读，继续阅读

FredaAn

粉丝: 0
资源: 8

解析Excel不同版本文件格式：BIFF结构详解

ExcelFormat

MICROSOFT EXCEL FILE FORMAT

Excel File Format

ValueError: Excel file format cannot be determined, you must specify an engine manually.是什么问题

Excel file format cannot be determined, you must specify an engine manually.代码解释

什么代码解决excel file format cannot be determined you must specify an engine manually

Excel file format cannot be determined, you must specify an engine manually

ValueError: Excel file format cannot be determined, you must specify an engine manually.

Excel file format cannot be determined,you must specify an engine manually

{ValueError}Excel file format cannot be determined, you must specify an engine manually

最新资源