Sheet Objects工作表对象

Excel supports 4 different types of "sheets":Excel支持4种不同类型的“工作表”：

• "worksheets": normal sheets：普通工作表
• "chartsheets": full-tab charts：完整选项卡图表
• "macrosheets": legacy (pre-VBA) macros：旧版（VBA之前）宏
• "dialogsheets": legacy (pre-VBA) dialog windows：旧版（VBA之前）对话框窗口

Generic Sheet Object常规工作表对象

Generic sheets are plain JavaScript objects. 泛型表是纯JavaScript对象。Each key that does not start with ! is an A1-style address whose corresponding value is a cell object.每个不以!开头的键是A1样式的地址，其对应的值是单元格对象。

Cell Storage单元格存储

By default, the parsers and utility functions generate "sparse-mode" worksheet objects. sheet[address] returns the cell object for the specified address.默认情况下，解析器和实用程序函数生成“稀疏模式”工作表对象。sheet[address]返回指定地址的单元格对象。

Dense Mode密集模式

When the option dense: true is passed, parsers will generate a "dense-mode" worksheet where cells are stored in an array of arrays. 当传递选项dense:true时，解析器将生成一个“稠密模式”工作表，其中单元格存储在数组中。sheet["!data"][R][C] returns the cell object at row R and column C (zero-indexed values).返回R行和C列的单元格对象（零索引值）。

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. read、readFile、write、writeFile和各种API函数支持稀疏和密集的工作表。Functions that accept worksheet or workbook objects (e.g. writeFile and sheet_to_json) will detect dense sheets.接受工作表或工作簿对象的函数（例如writeFile和sheet_to_json）将检测密集的工作表。

The option dense: true should be used when creating worksheet or book objects.创建工作表或工作簿对象时应使用选项dense:true。

Update code that manually searches for cells更新手动搜索单元格的代码 (adding dense mode support):（添加密集模式支持）：

Addressing Cells寻址单元格

• Works everywhere
• New in 2020

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

-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:行和列可以使用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:手动循环工作表对象的代码应测试"!data"键：

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 Properties工作表特性

Each key starts with !. 每把钥匙都以!开头！。The properties are accessible as sheet[key].属性可以sheet[key]的形式访问。

• sheet['!ref']: A-1 based range representing the sheet range. Functions that work with sheets should use this parameter to determine the range. ：基于A-1的范围，表示工作表范围。使用工作表的函数应使用此参数来确定范围。Cells that are assigned outside of the range are not processed. In particular, when writing a sheet by hand, cells outside of the range are not included不处理分配到范围之外的单元格。特别是，当用手书写纸张时，不包括范围之外的单元格

  Functions that handle sheets should test for the presence of !ref field. 处理表单的函数应测试是否存在!ref字段。If the !ref is omitted or is not a valid range, functions are free to treat the sheet as empty or attempt to guess the range. 如果!ref被省略或不是有效的范围，函数可以将工作表视为空或尝试猜测范围。The standard utilities that ship with this library treat sheets as empty (for example, the CSV output is empty string).此库附带的标准实用程序将图纸视为空（例如，CSV输出为空字符串）。

  When reading a worksheet with the sheetRows property set, the ref parameter will use the restricted range. 读取设置了sheetRows属性的工作表时，ref参数将使用受限制的范围。 The original range is set at ws['!fullref']原始范围设置为ws['!fullref']

• 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:默认值遵循Excel的“正常”预设。Excel也有一个“宽”和“窄”预设，但它们被存储为原始测量值。主要特性如下所示：

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']: array of column objects. ：列对象的数组。Each column object encodes properties including level, width and visibility每个列对象对包括标高、宽度和可见性在内的特性进行编码.

• ws['!rows']: array of row objects. ：行对象的数组。Each row object encodes properties including level, height and visibility.每一行对象都对包括标高、高度和可见性在内的特性进行编码。

• ws['!merges']: array of range objects corresponding to the merged cells in the worksheet. ：与工作表中合并单元格相对应的区域对象数组。Plain text formats do not support merge cells. 纯文本格式不支持合并单元格。CSV export will write all cells in the merge range if they exist, so be sure that only the first cell (upper-left) in the range is set.CSV导出将写入合并范围中的所有单元格（如果存在），因此请确保仅设置了范围中的第一个单元格（左上角）。

• ws['!outline']: configure how outlines should behave. Options default to the default settings in Excel 2019:：配置轮廓的行为方式。选项默认为Excel 2019中的默认设置：

key	Excel featureExcel功能	default默认值
above	Disable "Summary rows below detail"禁用“明细下方的摘要行”	false
left	Disable "Summary rows to the right of detail"禁用“明细右侧的摘要行”	false

• ws['!protect']: object of write sheet protection properties. ：写入工作表保护属性的对象。 The password key specifies the password for formats that support password-protected sheets (XLSX/XLSB/XLS). password密钥指定支持受密码保护的工作表（XLSX/XLSB/XLS）格式的密码。The writer uses the XOR obfuscation method. 作者使用XOR模糊处理方法。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:以下键控制工作表保护——设置为false可在工作表锁定时启用功能，设置为true可禁用功能：

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']: AutoFilter object following the schema:：遵循架构的自动筛选对象：

type AutoFilter = {
  ref:string; // A-1 based range representing the AutoFilter table range表示自动筛选表范围的基于A-1的范围
}

Other Sheet Types其他工作表类型

Chartsheet Object图表表对象

Chartsheets are represented as standard sheets. 图表表表示为标准表。They are distinguished with the !type property set to "chart".它们用!type属性设置为"chart"来区分。

The underlying data and !ref refer to the cached data in the chartsheet. 基础数据和!ref是指图表表中缓存的数据。The first row of the chartsheet is the underlying header.图表表的第一行是基础标题。

Macrosheet Object宏表对象

Macrosheets are represented as standard sheets. They are distinguished with the !type property set to "macro".宏图纸表示为标准图纸。它们用!type属性设置为"macro"来区分。

Dialogsheet Object对话框工作表对象

Dialogsheets are represented as standard sheets. They are distinguished with the !type property set to "dialog".对话框工作表表示为标准工作表。它们用!type属性设置为"dialog"来区分。