Sheet 对象

Excel 支持 4 种不同类型的 "sheets"：

¥Excel supports 4 different types of "sheets":

• "worksheets"：普通床单

  ¥"worksheets": normal sheets

• "chartsheets"：全选项卡图表

  ¥"chartsheets": full-tab charts

• "macrosheets"：旧版（VBA 之前）宏

  ¥"macrosheets": legacy (pre-VBA) macros

• "dialogsheets"：旧版（VBA 之前）对话框窗口

  ¥"dialogsheets": legacy (pre-VBA) dialog windows

通用工作表对象

¥Generic Sheet Object

通用工作表是纯 JavaScript 对象。每个不以 ! 开头的键都是一个 A1 样式的地址，其对应的值是一个单元格对象。

¥Generic sheets are plain JavaScript objects. Each key that does not start with ! is an A1-style address whose corresponding value is a cell object.

工作表范围

¥Worksheet Range

!ref 属性存储 A1 样式范围。

¥The !ref property stores the A1-style range.

与工作表一起使用的函数应使用此属性来确定范围。分配到范围之外的单元格不会被处理。

¥Functions that work with sheets should use this property to determine the range. Cells that are assigned outside of the range are not processed.

例如，在以下稀疏工作表中，单元格 A3 将被忽略，因为它超出了工作表范围 (A1:B2)：

¥For example, in the following sparse worksheet, the cell A3 will be ignored since it is outside of the worksheet range (A1:B2):

var ws = {
  // worksheet range is A1:B2
  "!ref": "A1:B2",

  // A1 is in the range and will be included
  "A1": { t: "s", v: "SheetJS" },

  // cell A3 is outside of the range and will be ignored
  "A3": { t: "n", v: 5433795 },
};

实用函数 和处理工作表的函数应测试 !ref 字段是否存在。如果省略 !ref 或不是有效范围，函数应将工作表视为空。

¥Utility functions and functions that handle sheets should test for the presence of the !ref field. If the !ref is omitted or is not a valid range, functions should treat the sheet as empty.

单元格储存

¥Cell Storage

默认情况下，解析器和实用程序函数会生成 "sparse-mode" 工作表。对于给定的 A1 样式地址，sheet[ref] 是相应的单元格对象。

¥By default, the parsers and utility functions generate "sparse-mode" worksheets. For a given A1-style address, sheet[ref] is the corresponding cell object.

密集模式

¥Dense Mode

当选项 dense: true 被传递时，解析器将生成一个 "dense-mode" 工作表，其中单元格存储在数组的数组中。sheet["!data"][R][C] 返回第 R 行和第 C 列的单元格对象（零索引值）。

¥When the option dense: true is passed, parsers will generate a "dense-mode" worksheet where cells are stored in an array of arrays. sheet["!data"][R][C] returns the cell object at row R and column C (zero-indexed values).

在旧环境中处理小型工作表时，稀疏工作表比密集工作表更有效。在较新的浏览器中，当处理非常大的工作表时，密集的工作表使用更少的内存并且往往更高效。

¥When processing small worksheets in older environments, sparse worksheets are more efficient than dense worksheets. In newer browsers, when dealing with very large worksheets, dense sheets use less memory and tend to be more efficient.

Migrating to Dense Mode (click to show)

read, readFile, write, writeFile, and the various API functions support sparse and dense worksheets. Functions that accept worksheet or workbook objects (e.g. writeFile and sheet_to_json) will detect dense sheets.

The option dense: true should be used when creating worksheet or book objects.

Update code that manually searches for cells (adding dense mode support):

Addressing Cells

Works everywhere

-var cell = sheet["B7"];
+var cell = sheet["!data"] != null ? (sheet["!data"][6]||[])[1] : sheet["B3"];

New in 2020

-var cell = sheet["B7"];
+var cell = sheet["!data"] != null ? sheet["!data"]?.[6]?.[1] : sheet["B3"];

The row and column can be calculated using XLSX.utils.decode_cell:

 var addr = "B7";
-var cell = sheet[addr];
+var _addr = XLSX.utils.decode_cell(addr);
+var cell = sheet["!data"] != null ? sheet["!data"]?.[_addr.r]?.[_addr.c] : sheet[addr];

XLSX.utils.encode_cell will be using the desired row and column indices:

-var cell = sheet[XLSX.utils.encode_cell({r:R, c:C})];
+var cell = sheet["!data"] != null ? sheet["!data"]?.[R]?.[C] : sheet[XLSX.utils.encode_cell({r:R, c:C})];

Looping across a Worksheet

Code that manually loops over worksheet objects should test for "!data" key:

const { decode_range, encode_cell } = XLSX.utils;

function log_all_cells(ws) {
  var range = decode_range(ws["!ref"]);
  var dense = ws["!data"] != null; // test if sheet is dense
  for(var R = 0; R <= range.e.r; ++R) {
    for(var C = 0; C <= range.e.c; ++C) {
      var cell = dense ? ws["!data"]?.[R]?.[C] : ws[encode_cell({r:R, c:C})];
      console.log(R, C, cell);
    }
  }
}

Update workbook and worksheet generation code

read

-var workbook = XLSX.read(data, {...opts});
+var workbook = XLSX.read(data, {...opts, dense: true});

readFile

-var workbook = XLSX.readFile(data, {...opts});
+var workbook = XLSX.readFile(data, {...opts, dense: true});

aoa_to_sheet

-var sheet = XLSX.utils.aoa_to_sheet([[1,2,3],[4,5,6]], {...opts});
+var sheet = XLSX.utils.aoa_to_sheet([[1,2,3],[4,5,6]], {...opts, dense: true});

json_to_sheet

-var sheet = XLSX.utils.json_to_sheet([{x:1,y:2}], {...opts});
+var sheet = XLSX.utils.json_to_sheet([{x:1,y:2}], {...opts, dense: true});

Sheet 属性

¥Sheet Properties

每个键都以 ! 开头。这些属性可通过 sheet[key] 访问。

¥Each key starts with !. The properties are accessible as sheet[key].

• sheet['!ref']：A1 样式表范围字符串

  ¥sheet['!ref']: A1-style sheet range string

• sheet['!margins']：表示页边距的对象。默认值遵循 Excel 的 "normal" 预设。Excel 还具有 "wide" 和 "narrow" 预设，但它们存储为原始测量值。主要属性如下：

  ¥sheet['!margins']: Object representing the page margins. The default values follow Excel's "normal" preset. Excel also has a "wide" and a "narrow" preset but they are stored as raw measurements. The main properties are listed below:

Page margin details (click to show)

key	description	"normal"	"wide"	"narrow"
left	left margin (inches)	0.7	1.0	0.25
right	right margin (inches)	0.7	1.0	0.25
top	top margin (inches)	0.75	1.0	0.75
bottom	bottom margin (inches)	0.75	1.0	0.75
header	header margin (inches)	0.3	0.5	0.3
footer	footer margin (inches)	0.3	0.5	0.3

/* Set worksheet sheet to "normal" */
ws["!margins"]={left:0.7, right:0.7, top:0.75,bottom:0.75,header:0.3,footer:0.3}
/* Set worksheet sheet to "wide" */
ws["!margins"]={left:1.0, right:1.0, top:1.0, bottom:1.0, header:0.5,footer:0.5}
/* Set worksheet sheet to "narrow" */
ws["!margins"]={left:0.25,right:0.25,top:0.75,bottom:0.75,header:0.3,footer:0.3}

工作表对象

¥Worksheet Object

除了上述的工作表键外，工作表还添加：

¥In addition to the aforementioned sheet keys, worksheets also add:

• ws['!cols']：列对象数组。每个列对象对属性进行编码，包括级别、宽度和可见性。

  ¥ws['!cols']: array of column objects. Each column object encodes properties including level, width and visibility.

• ws['!rows']：行对象数组。每个行对象对属性进行编码，包括级别、高度和可见性。

  ¥ws['!rows']: array of row objects. Each row object encodes properties including level, height and visibility.

• ws['!merges']：合并范围数组。每个合并对象都是一个范围对象，代表覆盖的范围。

  ¥ws['!merges']: array of merge ranges. Each merge object is a range object that represents the covered range.

• ws['!outline']：配置大纲的行为方式。选项默认为 Excel 2019 中的默认设置：

  ¥ws['!outline']: configure how outlines should behave. Options default to the default settings in Excel 2019:

key	Excel 功能	default
above	禁用 "详细信息下方的摘要行"	false
left	禁用 "详细信息右侧的摘要行"	false

• ws['!protect']：写表保护属性的对象。password 键指定支持受密码保护的工作表 (XLSX/XLSB/XLS) 的格式的密码。作者使用了 XOR 混淆方法。以下键控制工作表保护 - 设置为 false 以在工作表锁定时启用某项功能，或设置为 true 以禁用某功能：

  ¥ws['!protect']: object of write sheet protection properties. The password key specifies the password for formats that support password-protected sheets (XLSX/XLSB/XLS). The writer uses the XOR obfuscation method. The following keys control the sheet protection -- set to false to enable a feature when sheet is locked or set to true to disable a feature:

Worksheet Protection Details (click to show)

key	feature (true=disabled / false=enabled)	default
selectLockedCells	Select locked cells	enabled
selectUnlockedCells	Select unlocked cells	enabled
formatCells	Format cells	disabled
formatColumns	Format columns	disabled
formatRows	Format rows	disabled
insertColumns	Insert columns	disabled
insertRows	Insert rows	disabled
insertHyperlinks	Insert hyperlinks	disabled
deleteColumns	Delete columns	disabled
deleteRows	Delete rows	disabled
sort	Sort	disabled
autoFilter	Filter	disabled
pivotTables	Use PivotTable reports	disabled
objects	Edit objects	enabled
scenarios	Edit scenarios	enabled

• ws['!autofilter']：遵循架构的自动过滤对象：

  ¥ws['!autofilter']: AutoFilter object following the schema:

type AutoFilter = {
  ref:string; // A-1 based range representing the AutoFilter table range
}

其他板材类型

¥Other Sheet Types

Chartsheet 对象

¥Chartsheet Object

图表表表示为标准表。它们的区别在于 !type 属性设置为 "chart"。

¥Chartsheets are represented as standard sheets. They are distinguished with the !type property set to "chart".

底层数据和 !ref 指的是图表中的缓存数据。图表表的第一行是底层标题。

¥The underlying data and !ref refer to the cached data in the chartsheet. The first row of the chartsheet is the underlying header.

Macrosheet 对象

¥Macrosheet Object

宏表表示为标准表。它们的区别在于 !type 属性设置为 "macro"。

¥Macrosheets are represented as standard sheets. They are distinguished with the !type property set to "macro".

Dialogsheet 对象

¥Dialogsheet Object

对话框表表示为标准表。它们的区别在于 !type 属性设置为 "dialog"。

¥Dialogsheets are represented as standard sheets. They are distinguished with the !type property set to "dialog".