Spreadsheet Features电子表格功能

Even for basic features like date storage, the official Excel formats store the same content in different ways. 即使对于数据存储等基本功能，官方Excel格式也以不同的方式存储相同的内容。The parsers are expected to convert from the underlying file format representation to the Common Spreadsheet Format. 解析器将从底层文件格式表示转换为通用电子表格格式。Writers are expected to convert from CSF back to the underlying file format.编写器需要从CSF转换回底层文件格式。

The following topics are covered in sub-pages:以下主题包含在子页面中：

• Formulae公式
• Hyperlinks超链接
• Dates and Times日期和时间

Row and Column Properties行和列属性

Format Support格式支持 (click to show)

Row Properties行属性: XLSX/M, XLSB, BIFF8 XLS, XLML, SYLK, DOM, ODS

Column Properties列属性: XLSX/M, XLSB, BIFF8 XLS, XLML, SYLK, DOM

Row and Column properties are not extracted by default when reading from a file and are not persisted by default when writing to a file. 从文件中读取时，默认情况下不会提取行和列属性，写入文件时，默认情况下不会保留行和列属性。The option cellStyles: true must be passed to the relevant read or write function.选项cellStyles:true必须传递给相关的读或写函数。

Column Properties列属性

The !cols array in each worksheet, if present, is a collection of ColInfo objects which have the following properties:这个每个工作表中的!cols数组（如果存在）是具有以下属性的ColInfo对象的集合：

type ColInfo = {
  /* visibility */
  hidden?: boolean; // if true, the column is hidden

  /* column width is specified in one of the following ways: */
  wpx?:    number;  // width in screen pixels
  width?:  number;  // width in Excel "Max Digit Width", width*256 is integral
  wch?:    number;  // width in characters

  /* other fields for preserving features from files */
  level?:  number;  // 0-indexed outline / group level
  MDW?:    number;  // Excel "Max Digit Width" unit, always integral
};

Row Properties行属性

The !rows array in each worksheet, if present, is a collection of RowInfo objects which have the following properties:这个每个工作表中的!rows数组（如果存在）是具有以下属性的RowInfo对象的集合：

type RowInfo = {
  /* visibility */
  hidden?: boolean; // if true, the row is hidden

  /* row height is specified in one of the following ways: */
  hpx?:    number;  // height in screen pixels
  hpt?:    number;  // height in points

  level?:  number;  // 0-indexed outline / group level
};

Outline / Group Levels Convention大纲/组级别约定

The Excel UI displays the base outline level as 1 and the max level as 8. Excel UI将基本大纲级别显示为1，最大级别显示为8。Following JS conventions, SheetJS uses 0-indexed outline levels wherein the base outline level is 0 and the max level is 7.遵循JS惯例，SheetJS使用0索引的大纲级别，其中基本大纲级别为0，最大级别为7。

Why are there three width types?为什么有三种宽度类型？ (click to show)

There are three different width types corresponding to the three different ways spreadsheets store column widths:有三种不同的宽度类型对应于电子表格存储列宽的三种不同方式：

SYLK and other plain text formats use raw character count. SYLK和其他纯文本格式使用原始字符计数。Contemporaneous tools like Visicalc and Multiplan were character based. Visicalc和Multiplan等同期工具都是基于角色的。Since the characters had the same width, it sufficed to store a count. 由于字符具有相同的宽度，因此足以存储计数。This tradition was continued into the BIFF formats.BIFF格式延续了这一传统。

SpreadsheetML (2003) tried to align with HTML by standardizing on screen pixel count throughout the file. SpreadsheetML(2003)试图通过标准化整个文件中的屏幕像素数来与HTML对齐。Column widths, row heights, and other measures use pixels. 列宽、行高和其他度量使用像素。When the pixel and character counts do not align, Excel rounds values.当像素和字符计数不对齐时，Excel会舍入值。

XLSX internally stores column widths in a nebulous "Max Digit Width" form. XLSX在内部以模糊的“最大数字宽度”形式存储列宽。The Max Digit Width is the width of the largest digit when rendered (generally the "0" character is the widest). 最大数字宽度是渲染时最大数字的宽度（通常“0”字符最宽）。The internal width must be an integer multiple of the the width divided by 256. 内部宽度必须是宽度除以256的整数倍。ECMA-376 describes a formula for converting between pixels and the internal width. ECMA-376描述了像素和内部宽度之间的转换公式。This represents a hybrid approach.这代表了一种混合方法。

Read functions attempt to populate all three properties. 读取函数尝试填充所有三个属性。Write functions will try to cycle specified values to the desired type. 写入函数将尝试将指定的值循环到所需的类型。In order to avoid potential conflicts, manipulation should delete the other properties first. 为了避免潜在的冲突，操作应首先删除其他属性。For example, when changing the pixel width, delete the wch and width properties.例如，更改像素宽度时，删除wch和width属性。

Implementation details实施细节 (click to show)

Row Heights行高度

Excel internally stores row heights in points. Excel在内部以点为单位存储行高。The default resolution is 72 DPI or 96 PPI, so the pixel and point size should agree. 默认分辨率为72 DPI或96 PPI，因此像素和点大小应一致。For different resolutions they may not agree, so the library separates the concepts.对于不同的分辨率，它们可能不一致，因此库将概念分开。

Even though all of the information is made available, writers are expected to follow the priority order:即使所有信息都可用，作者也应遵循优先顺序：

1) use hpx pixel height if available 2) use hpt point height if available如果可用，请使用hpx像素高度2）如果可用，请使用hpt点高度

Column Widths列宽

Given the constraints, it is possible to determine the MDW without actually inspecting the font! 考虑到这些约束，可以在不实际检查字体的情况下确定MDW！The parsers guess the pixel width by converting from width to pixels and back, repeating for all possible MDW and selecting the MDW that minimizes the error. 解析器通过从宽度到像素再向后转换来猜测像素宽度，对所有可能的MDW进行重复，并选择将错误最小化的MDW。XLML actually stores the pixel width, so the guess works in the opposite direction.XLML实际上存储像素宽度，因此猜测的方向相反。

Even though all of the information is made available, writers are expected to follow the priority order:即使所有信息都可用，作者也应遵循优先顺序：

1) use width field if available 2) use wpx pixel width if available 3) use wch character count if available1) 如果可用，请使用width度字段 2)如果可用，请使用wpx像素宽度 3)如果可用，请使用wch字符计数

Number Formats数字格式

The cell.w formatted text for each cell is produced from cell.v and cell.z format. 每个单元格的cell.w格式文本由cell.v和cell.z格式生成。If the format is not specified, the Excel General format is used. 如果未指定格式，则使用ExcelGeneral格式。The format can either be specified as a string or as an index into the format table. 格式可以指定为字符串，也可以指定为格式表的索引。Parsers are expected to populate workbook.SSF with the number format table. 解析器需要用数字格式表填充workbook.SSF。Writers are expected to serialize the table.写入程序需要序列化表。

The following example creates a custom format from scratch:以下示例从头开始创建自定义格式：

var wb = {
  SheetNames: ["Sheet1"],
  Sheets: {
    Sheet1: {
      "!ref":"A1:C1",
      A1: { t:"n", v:10000 },                    // <-- General format
      B1: { t:"n", v:10000, z: "0%" },           // <-- Builtin format
      C1: { t:"n", v:10000, z: "\"T\"\ #0.00" }  // <-- Custom format
    }
  }
}

The rules are slightly different from how Excel displays custom number formats. 这些规则与Excel显示自定义数字格式的方式略有不同。In particular, literal characters must be wrapped in double quotes or preceded by a backslash. 特别是，文字字符必须用双引号括起来，或者前面有反斜杠。For more info, see the Excel documentation article Create or delete a custom number format or ECMA-376 18.8.31 (Number Formats)有关更多信息，请参阅Excel文档文章“创建或删除自定义数字格式”或ECMA-376 18.8.31（数字格式）

Default Number Formats默认数字格式 (click to show)

The default formats are listed in ECMA-376 18.8.30:默认格式在ECMA-376 18.8.30中列出：

ID	Format格式
0	General
1	0
2	0.00
3	#,##0
4	#,##0.00
9	0%
10	0.00%
11	0.00E+00
12	# ?/?
13	# ??/??
14	m/d/yy (see below)
15	d-mmm-yy
16	d-mmm
17	mmm-yy
18	h:mm AM/PM
19	h:mm:ss AM/PM
20	h:mm
21	h:mm:ss
22	m/d/yy h:mm
37	#,##0 ;(#,##0)
38	#,##0 ;[Red](#,##0)
39	#,##0.00;(#,##0.00)
40	#,##0.00;[Red](#,##0.00)
45	mm:ss
46	[h]:mm:ss
47	mmss.0
48	##0.0E+0
49	@

Format 14 (m/d/yy) is localized by Excel: even though the file specifies that number format, it will be drawn differently based on system settings. 格式14（m/d/yy）由Excel本地化：即使文件指定了数字格式，也会根据系统设置以不同的方式绘制。It makes sense when the producer and consumer of files are in the same locale, but that is not always the case over the Internet. 当文件的生产者和消费者在同一地区时，这是有意义的，但在互联网上并不总是这样。To get around this ambiguity, parse functions accept the dateNF option to override the interpretation of that specific format string.为了避免这种歧义，解析函数接受dateNF选项来覆盖特定格式字符串的解释。

Cell Comments单元格注释

Format Support格式支持 (click to show)

Simple Notes/Comments简单注释/注释: XLSX/M, XLSB, BIFF8 XLS (read only), XLML, ODS (read only)

Threaded Comments线程注释: XLSX/M, XLSB (read only)

Cell comments are objects stored in the c array of cell objects. 单元格注释是存储在c单元格对象数组中的对象。The actual contents of the comment are split into blocks based on the comment author. 注释的实际内容根据注释作者分为几个部分。 The a field of each comment object is the author of the comment and the t field is the plain text representation.每个注释对象的a字段是注释的作者，t字段是纯文本表示。

For example, the following snippet appends a cell comment into cell A1:例如，以下代码段将单元格注释附加到单元格A1中：

if(!ws.A1.c) ws.A1.c = [];
ws.A1.c.push({a:"SheetJS", t:"I'm a little comment, short and stout!"});

Note: XLSB enforces a 54 character limit on the Author name. 注：XLSB对作者姓名强制执行54个字符的限制。Names longer than 54 characters may cause issues with other formats.超过54个字符的名称可能会导致其他格式的问题。

To mark a comment as normally hidden, set the hidden property:要将注释标记为正常隐藏，请设置hidden属性：

if(!ws.A1.c) ws.A1.c = [];
ws.A1.c.push({a:"SheetJS", t:"This comment is visible"});

if(!ws.A2.c) ws.A2.c = [];
ws.A2.c.hidden = true;
ws.A2.c.push({a:"SheetJS", t:"This comment will be hidden"});

Threaded Comments线程注释

Introduced in Excel 365, threaded comments are plain text comment snippets with author metadata and parent references. 在Excel 365中引入的线程注释是带有作者元数据和父引用的纯文本注释片段。They are supported in XLSX and XLSB.它们在XLSX和XLSB中受支持。

To mark a comment as threaded, each comment part must have a true T property:要将注释标记为线程，每个注释部分必须具有true的T属性：

if(!ws.A1.c) ws.A1.c = [];
ws.A1.c.push({a:"SheetJS", t:"This is not threaded"});

if(!ws.A2.c) ws.A2.c = [];
ws.A2.c.hidden = true;
ws.A2.c.push({a:"SheetJS", t:"This is threaded", T: true});
ws.A2.c.push({a:"JSSheet", t:"This is also threaded", T: true});

There is no Active Directory or Office 365 metadata associated with authors in a thread.线程中没有与作者关联的活动目录或Office 365元数据。

Sheet Visibility工作表可见性

Format Support格式支持 (click to show)

Hidden Sheets隐藏的工作表: XLSX/M, XLSB, BIFF8/BIFF5 XLS, XLML

Very Hidden Sheets非常隐蔽的床单: XLSX/M, XLSB, BIFF8/BIFF5 XLS, XLML

Excel enables hiding sheets in the lower tab bar. Excel允许在下方的选项卡栏中隐藏工作表。The sheet data is stored in the file but the UI does not readily make it available. 工作表数据存储在文件中，但用户界面无法使其可用。Standard hidden sheets are revealed in the "Unhide" menu. 标准隐藏页显示在“Unhide”（取消隐藏）菜单中。Excel also has "very hidden" sheets which cannot be revealed in the menu. Excel还有“非常隐藏”的工作表，无法在菜单中显示。It is only accessible in the VB Editor!它只能在VB编辑器中访问！

The visibility setting is stored in the Hidden property of sheet props array.可见性设置存储在工作表prop数组的Hidden属性中。

Value值	Definition释义	VB Editor "Visible" Property
0	Visible	-1 - xlSheetVisible
1	Hidden	0 - xlSheetHidden
2	Very Hidden	2 - xlSheetVeryHidden

If the respective Sheet entry does not exist or if the Hidden property is not set, the worksheet is visible.如果相应的工作表条目不存在或未设置Hidden属性，则工作表可见。

List all worksheets and their visibilities列出所有工作表及其可见性

wb.Workbook.Sheets.map(function(x) { return [x.name, x.Hidden] })
// [ [ 'Visible', 0 ], [ 'Hidden', 1 ], [ 'VeryHidden', 2 ] ]

Check if worksheet is visible检查工作表是否可见

Non-Excel formats do not support the Very Hidden state. 非Excel格式不支持非常隐藏的状态。The best way to test if a sheet is visible is to check if the Hidden property is logical truth:测试工作表是否可见的最佳方法是检查Hidden属性是否为逻辑真理：

wb.Workbook.Sheets.map(function(x) { return [x.name, !x.Hidden] })
// [ [ 'Visible', true ], [ 'Hidden', false ], [ 'VeryHidden', false ] ]

Live Example实例 (click to show)

This test file测试文件 has three sheets:有三个工作表：

• "Visible" is visible是可见的
• "Hidden" is hidden是隐藏的
• "VeryHidden" is very hidden是非常隐藏的

Live demo

Result

Loading...

Live Editor

function Visibility(props) { const [sheets, setSheets] = React.useState([]); const names = [ "Visible", "Hidden", "Very Hidden" ]; React.useEffect(async() => { const f = await fetch("/files/sheet_visibility.xlsx"); const ab = await f.arrayBuffer(); const wb = XLSX.read(ab); /* State will be set to the `Sheets` property array */ setSheets(wb.Workbook.Sheets); }, []); return (<table> <thead><tr><th>Name</th><th>Value</th><th>Hidden</th></tr></thead> <tbody>{sheets.map((x,i) => (<tr key={i}> <td>{x.name}</td> <td>{x.Hidden} - {names[x.Hidden]}</td> <td>{!x.Hidden ? "No" : "Yes"}</td> </tr>))}</tbody></table>); }

function Visibility(props) {
  const [sheets, setSheets] = React.useState([]);
  const names = [ "Visible", "Hidden", "Very Hidden" ];

  React.useEffect(async() => {
    const f = await fetch("/files/sheet_visibility.xlsx");
    const ab = await f.arrayBuffer();
    const wb = XLSX.read(ab);

    /* State will be set to the `Sheets` property array */
    setSheets(wb.Workbook.Sheets);
  }, []);

  return (<table>
    <thead><tr><th>Name</th><th>Value</th><th>Hidden</th></tr></thead>
    <tbody>{sheets.map((x,i) => (<tr key={i}>

      <td>{x.name}</td>

      <td>{x.Hidden} - {names[x.Hidden]}</td>

      <td>{!x.Hidden ? "No" : "Yes"}</td>

    </tr>))}</tbody></table>);
}

VBA and MacrosVBA和宏

Format Support格式支持 (click to show)

VBA ModulesVBA模块: XLSM, XLSB, BIFF8 XLS

VBA Macros are stored in a special data blob that is exposed in the vbaraw property of the workbook object when the bookVBA option is true. 当bookVBA选项为true时，VBA宏存储在工作簿对象的vbaraw属性中公开的特殊数据块中。They are supported in XLSM, XLSB, and BIFF8 XLS formats. XLSM、XLSB和BIFF8-XLS格式支持它们。The supported format writers automatically insert the data blobs if it is present in the workbook and associate with the worksheet names.支持的格式编写器会自动插入工作簿中存在的数据块，并与工作表名称关联。

The vbaraw property stores raw bytes. vbaraw属性存储原始字节。SheetJS Pro offers a special component for extracting macro text from the VBA blob, editing the VBA project, and exporting new VBA blobs.SheetJS Pro提供了一个特殊组件，用于从VBA blob中提取宏文本、编辑VBA项目和导出新的VBA blob。

Round-tripping Macro Enabled Files启用宏的往返文件

In order to preserve macro when reading and writing files, the bookVBA option must be set to true when reading and when writing. 为了在读取和写入文件时保留宏，在读取和写入文件时必须将bookVBA选项设置为true。In addition, the output file format must support macros. 此外，输出文件格式必须支持宏。XLSX notably does not support macros, and XLSM should be used in its place:XLSX不支持宏，应使用XLSM代替它：

/* Reading data */
var wb = XLSX.read(data, { bookVBA: true }); // read file and distill VBA blob
var vbablob = wb.vbaraw;

Code Names代码名称

By default, Excel will use ThisWorkbook or a translation DieseArbeitsmappe for the workbook. 默认情况下，Excel将使用ThisWorkbook或工作簿的翻译模板DieseArbeitsmappe。Each worksheet will be identified using the default Sheet# naming pattern even if the worksheet names have changed.即使工作表名称已更改，也将使用默认Sheet#命名模式标识每个工作表。

A custom workbook code name will be stored in wb.Workbook.WBProps.CodeName. 自定义工作簿代码名将存储在wb.Workbook.WBProps.CodeName。For exports, assigning the property will override the default value.对于导出，指定特性将覆盖默认值。

Worksheet and Chartsheet code names are in the worksheet properties object at wb.Workbook.Sheets[i].CodeName. 工作表和图表工作表代码名位于wb.Workbook.Sheets[i].CodeName的工作表属性对象中。Macrosheets and Dialogsheets are ignored.忽略宏表和对话框表。

The readers and writers preserve the code names, but they have to be manually set when adding a VBA blob to a different workbook.读写器保留代码名，但在将VBA blob添加到其他工作簿时，必须手动设置这些代码名。

Macrosheets宏工作表

Older versions of Excel also supported a non-VBA "macrosheet" sheet type that stored automation commands. 旧版本的Excel还支持存储自动化命令的非VBA“macrosheet”工作表类型。These are exposed in objects with the !type property set to "macro".这些都暴露在对象中，其!type属性设置为"macro"。

Under the hood, Excel treats Macrosheets as normal worksheets with special interpretation of the function expressions.在后台，Excel将宏表视为普通工作表，并对函数表达式进行特殊解释。

Detecting Macros in Workbooks检测工作簿中的宏

The vbaraw field will only be set if macros are present. 只有存在宏时，才会设置vbaraw字段。Macrosheets will be explicitly flagged. 宏表将显式标记。Combining the two checks yields a simple function:将这两种检查结合起来，可以得到一个简单的函数：

function wb_has_macro(wb/*:workbook*/)/*:boolean*/ {
    if(!!wb.vbaraw) return true;
    const sheets = wb.SheetNames.map((n) => wb.Sheets[n]);
    return sheets.some((ws) => !!ws && ws['!type']=='macro');
}