POI操作Excel揭秘：微软Excel文件格式详解

需积分: 49 192 浏览量更新于2024-07-27 收藏 1.14MB PDF 举报

在IT领域中，掌握Microsoft Excel的工作原理和文件格式对于数据处理和应用开发至关重要。本文档标题"ExcelFileFormat"详细介绍了如何通过POI（Apache POI）库来理解和操作Excel文件，这是Microsoft Office系列中的核心组件，支持多种版本如2.3、95、97等。POI库允许开发者在Java和其他语言环境中读写Excel文件，但深入理解其内部结构有助于提高性能和兼容性。文档首先强调了与OpenOffice.org的协作，OpenOffice.org提供了对Excel文件格式的官方文档，由Daniel Rentz等人编撰并遵循Public Documentation License。这些文档揭示了Excel文件的基本构成，包括： 1. **字节顺序**：由于不同计算机体系结构（如小端模式和大端模式）可能存在差异，理解Excel文件的字节顺序（即数据存储的位序）是至关重要的，这会影响解析和保存文件时的数据处理。 2. **文档结构**：文档分为不同类型，如工作簿、工作表和单元格，每个元素都有其特定的二进制表示。BIFF（Binary Interchange File Format）是Excel早期版本的核心文件格式，用于存储这些信息。 3. **文件结构**：一个Excel文件包含多个记录，这些记录按照特定的顺序排列，如BIFF头、工作表记录、公式记录、图表记录等。理解这些组成部分有助于开发者定位和修改文件内容。 4. **BIFF记录结构**：BIFF记录是Excel文件的基本组成单元，它们描述了不同类型的Excel对象，如数据、格式设置、公式和图表。理解BIFF记录的类型、长度和功能是实现POI操作的关键。 5. **兼容性**：文档还提到了不同Excel版本之间的差异，包括OpenOffice.org的1.x和2.x版本，以及与Microsoft Office的兼容问题。了解这些版本之间的变化有助于开发者确保跨平台和版本的兼容性。此外，文档还提供了链接到Wikipedia获取更深入的信息，以及相关的邮件列表和下载资源，如PDF格式的详细规格文档、OpenOffice.org不同版本的XML源代码，以及项目开始和最后更新的时间以及修订历史。这个文档为开发者提供了一个全面的指南，帮助他们深入理解Microsoft Excel文件的内部机制，并通过POI库高效地操作和处理Excel数据。这对于开发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页未读，继续阅读

crazyxrp

粉丝: 1
资源: 3

POI操作Excel揭秘：微软Excel文件格式详解

Excel File Format

MICROSOFT EXCEL FILE FORMAT

Microsoft 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

最新资源