数据数组
对象数组是 JavaScript 数据库连接器和其他通用数据源中的常见数据格式。
¥Arrays of objects are a common data format in JavaScript database connectors and other general data sources.
数值数据集通常使用数字数组的数组。
¥Numeric datasets commonly use arrays of arrays of numbers.
"数据存储" 部分概述了 JavaScript 中常见的数组格式。
¥The "Data Storage" section gives a general overview of common array formats in JavaScript.
"函数" 部分描述了相关功能。
¥The "Functions" section describes the related functions.
数据存储
¥Data Storage
数组的数组
¥Array of Arrays
JavaScript 中网格的精神等价物是数组的数组:
¥The spiritual equivalent of the grid in JavaScript is an array of arrays:
Spreadsheet | Array of Arrays |
---|---|
|
结构中的每个数组对应一行。可以通过按行索引和按列索引进行索引来读取各个数据点:
¥Each array within the structure corresponds to one row. Individual data points can be read by indexing by row index and by column index:
var aoa = [
["Name", "Index"],
["Bill Clinton", 42],
["GeorgeW Bush", 43],
["Barack Obama", 44],
["Donald Trump", 45],
["Joseph Biden", 46]
];
var value_at_B4 = aoa[3][1]; // 44
var value_at_A2 = aoa[1][0]; // Bill Clinton
对象数组
¥Arrays of Objects
对象数组通常用于表示数据库中的行:
¥Arrays of objects are commonly used to represent rows from a database:
Spreadsheet | Array of Objects |
---|---|
|
结构中的每个对象对应一个数据行。电子表格的第一行被解释为标题行。
¥Each object within the structure corresponds to one data row. The first row of the spreadsheet is interpreted as the header row.
函数
¥Functions
"输入" 小节描述了从数据数组生成 SheetJS 工作表对象的函数。
¥The "Input" subsections describe functions that generate SheetJS worksheet objects from arrays of data.
"数组输出" 小节定义了从 SheetJS 工作表对象中提取数据的函数。
¥The "Array Output" subsection defines functions that extract data from SheetJS worksheet objects.
示例表
¥Example Sheet
在线示例基于以下工作表:
¥The live examples are based on the following worksheet:
S | h | e | e | t | J | S |
1 | 2 | 5 | 6 | 7 | ||
2 | 3 | 6 | 7 | 8 | ||
3 | 4 | 7 | 8 | 9 | ||
4 | 5 | 6 | 7 | 8 | 9 | 0 |
该表包含重复的列标签("e" 和 "S" 在第一行中出现两次)和间隙(三个数据行缺少字段)。
¥This table includes duplicate column labels ("e" and "S" appear twice in the first row) and gaps (three data rows have missing fields).
数组的数组输入
¥Array of Arrays Input
从数组数组创建工作表
¥Create a worksheet from an array of arrays
var ws = XLSX.utils.aoa_to_sheet(aoa, opts);
XLSX.utils.aoa_to_sheet
接受 JS 值数组的数组并返回与输入数据类似的工作表。值解释如下:
¥XLSX.utils.aoa_to_sheet
takes an array of arrays of JS values and returns a
worksheet resembling the input data. Values are interpreted as follows:
-
数字、布尔值和字符串存储为相应的类型。
¥Numbers, Booleans and Strings are stored as the corresponding types.
-
日期对象存储为日期单元格或日期代码(请参阅
cellDates
选项)¥Date objects are stored as Date cells or date codes (see
cellDates
option) -
数组空洞和显式
undefined
值将被跳过。¥Array holes and explicit
undefined
values are skipped. -
null
值可能会被存根(请参阅sheetStubs
和nullError
选项)¥
null
values may be stubbed (seesheetStubs
andnullError
options) -
单元格对象按原样使用。
¥Cell objects are used as-is.
该函数接受一个选项参数:
¥The function takes an options argument:
选项名称 | 默认 | 描述 |
---|---|---|
dateNF | 菌群移植 14 | 在字符串输出中使用指定的日期格式 |
cellDates | false | 将日期存储为 d 类型(默认为 n ) |
sheetStubs | false | 为 null 值创建 z 类型的单元格对象 |
nullError | false | 如果为 true,则针对 null 值触发 #NULL! 错误单元 |
UTC | false | 如果为 true,则使用 UTC 方法解释日期 ** |
dense | false | 触发 致密片 |
¥UTC option is explained in "Dates"
以下实例重现了 示例工作表:
¥The following live example reproduces the example worksheet:
function SheetJSExportAOA() { /* array of arrays of data */ var aoa = [ ["S", "h", "e", "e", "t", "J", "S"], [ 1, 2, , , 5, 6, 7], [ 2, 3, , , 6, 7, 8], [ 3, 4, , , 7, 8, 9], [ 4, 5, 6, 7, 8, 9, 0] ]; return ( <button onClick={() => { /* create worksheet */ var ws = XLSX.utils.aoa_to_sheet(aoa); /* create workbook and export */ var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "SheetJSExportAOA.xlsx"); }}>Click to export Array of Arrays</button> ); }
将数组中的数据添加到现有工作表
¥Add data from an array of arrays to an existing worksheet
XLSX.utils.sheet_add_aoa(ws, aoa, opts);
XLSX.utils.sheet_add_aoa
接受 JS 值数组的数组并更新现有的工作表对象。它遵循与 aoa_to_sheet
相同的过程并接受选项参数:
¥XLSX.utils.sheet_add_aoa
takes an array of arrays of JS values and updates an
existing worksheet object. It follows the same process as aoa_to_sheet
and
accepts an options argument:
选项名称 | 默认 | 描述 |
---|---|---|
dateNF | 菌群移植 14 | 在字符串输出中使用指定的日期格式 |
cellDates | false | 将日期存储为 d 类型(默认为 n ) |
sheetStubs | false | 为 null 值创建 z 类型的单元格对象 |
nullError | false | 如果为 true,则针对 null 值触发 #NULL! 错误单元 |
origin | 使用指定的单元格作为起点(见下文) | |
UTC | false | 如果为 true,则使用 UTC 方法解释日期 ** |
¥UTC option is explained in "Dates"
origin
预计为以下之一:
¥origin
is expected to be one of:
origin | 描述 |
---|---|
(单元格对象) | 使用指定单元格(单元格对象) |
(字符串) | 使用指定单元格(A1 型单元格) |
(数量 >= 0) | 从指定行的第一列开始(0 索引) |
-1 | 从第一列开始附加到工作表底部 |
(默认) | 从单元格 A1 开始 |
示例工作表 可以按以下顺序构建:
¥The example worksheet can be built up in the following order:
Spreadsheet | Operations | |||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|
/* Start from an empty worksheet */
var ws = XLSX.utils.aoa_to_sheet([[]]);
/* First row */
XLSX.utils.sheet_add_aoa(ws, [ "SheetJS".split("") ], {origin: "A1"});
/* Write data starting at A2 */
XLSX.utils.sheet_add_aoa(ws, [[1,2], [2,3], [3,4]], {origin: "A2"});
/* Write data starting at E2 */
XLSX.utils.sheet_add_aoa(ws, [[5,6,7], [6,7,8], [7,8,9]], {origin:{r:1, c:4}});
/* Append row */
XLSX.utils.sheet_add_aoa(ws, [[4,5,6,7,8,9,0]], {origin: -1});
function SheetJSAddAOA() { return ( <button onClick={() => { /* Start from an empty worksheet */ var ws = XLSX.utils.aoa_to_sheet([[]]); /* First row */ XLSX.utils.sheet_add_aoa(ws, [ "SheetJS".split("") ], {origin: "A1"}); /* Write data starting at A2 */ XLSX.utils.sheet_add_aoa(ws, [[1,2], [2,3], [3,4]], {origin: "A2"}); /* Write data starting at E2 */ XLSX.utils.sheet_add_aoa(ws, [[5,6,7], [6,7,8], [7,8,9]], {origin:{r:1, c:4}}); /* Append row */ XLSX.utils.sheet_add_aoa(ws, [[4,5,6,7,8,9,0]], {origin: -1}); /* create workbook and export */ var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "SheetJSAddAOA.xlsx"); }}>Click to export Array of Arrays</button> ); }
对象数组输入
¥Array of Objects Input
从对象数组创建工作表
¥Create a worksheet from an array of objects
var ws = XLSX.utils.json_to_sheet(aoo, opts);
XLSX.utils.json_to_sheet
接受一个对象数组并返回一个工作表,其中包含根据对象的键自动生成的 "headers"。默认列顺序由使用 Object.keys
的字段的第一次出现确定。该函数接受一个选项参数:
¥XLSX.utils.json_to_sheet
takes an array of objects and returns a worksheet
with automatically-generated "headers" based on the keys of the objects. The
default column order is determined by the first appearance of the field using
Object.keys
. The function accepts an options argument:
选项名称 | 默认 | 描述 |
---|---|---|
header | 使用指定的字段顺序(默认 Object.keys )** | |
dateNF | 菌群移植 14 | 在字符串输出中使用指定的日期格式 |
cellDates | false | 将日期存储为 d 类型(默认为 n ) |
skipHeader | false | 如果为 true,则输出中不包含标题行 |
nullError | false | 如果为 true,则针对 null 值触发 #NULL! 错误单元 |
UTC | false | 如果为 true,则使用 UTC 方法解释日期 ** |
dense | false | 触发 致密片 |
¥UTC option is explained in "Dates"
每行的所有字段都将被写入!header
暗示特定的顺序,但不是唯一的。要从导出中删除字段,请过滤数据源。
¥All fields from each row will be written! header
hints at a particular order
but is not exclusive. To remove fields from the export, filter the data source.
某些数据源具有用于过滤属性的特殊选项。例如,MongoDB 在从集合中查找数据时会添加 _id
字段:
¥Some data sources have special options to filter properties. For example,
MongoDB will add the _id
field when finding data from a collection:
const aoo_with_id = await coll.find({}).toArray();
const ws = XLSX.utils.json_to_sheet(aoo_with_id); // includes _id column
这可以通过 projection
属性过滤掉:
¥This can be filtered out through the projection
property:
const aoo = await coll.find({}, {projection:{_id:0}}).toArray(); // no _id !
const ws = XLSX.utils.json_to_sheet(aoo);
如果数据源没有提供过滤选项,可以手动过滤:
¥If a data source does not provide a filter option, it can be filtered manually:
const aoo = data.map(obj => Object.fromEntries(Object.entries(obj).filter(r => headers.indexOf(r[0]) > -1)));
-
如果
header
是一个数组,则缺失的键将按照首次使用的顺序添加。¥If
header
is an array, missing keys will be added in order of first use. -
单元格类型是根据每个值的类型推断出来的。例如,
Date
对象将生成日期单元格,而字符串将生成文本单元格。¥Cell types are deduced from the type of each value. For example, a
Date
object will generate a Date cell, while a string will generate a Text cell. -
默认情况下将跳过空值。如果
nullError
为 true,则对应于#NULL!
的错误单元格将写入工作表中。¥Null values will be skipped by default. If
nullError
is true, an error cell corresponding to#NULL!
will be written to the worksheet.
function SheetJSExportAOO() { /* array of arrays of data */ var aoo = [ { Name: "Bill Clinton", Index: 42 }, { Name: "GeorgeW Bush", Index: 43 }, { Name: "Barack Obama", Index: 44 }, { Name: "Donald Trump", Index: 45 }, { Name: "Joseph Biden", Index: 46 } ]; return ( <button onClick={() => { /* create worksheet */ var ws = XLSX.utils.json_to_sheet(aoo); /* create workbook and export */ var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "SheetJSExportAOO.xlsx"); }}>Click to export Array of Objects</button> ); }
示例表 无法使用普通对象重现,因为 JS 对象键必须是唯一的。
¥The example sheet cannot be reproduced using plain objects since JS object keys must be unique.
通常,原始数据源将具有不同的列名称。用 json_to_sheet
写入后,aoa_to_sheet
方法可以重写头部:
¥Typically the original data source will have different column names. After
writing with json_to_sheet
, the aoa_to_sheet
method can rewrite the headers:
/* original array of objects */
var data = [
{ S:1, h:2, t:5, J:6, S_1:7 },
{ S:2, h:3, t:6, J:7, S_1:8 },
{ S:3, h:4, t:7, J:8, S_1:9 },
{ S:4, h:5, e:6, e_1:7, t:8, J:9, S_1:0 },
];
/* column order for the generated worksheet */
// | A | B | C | D | E | F | G |
var data_headers = [ "S", "h", "e", "e_1", "t", "J", "S_1" ];
/* new headers for the first row of the worksheet */
// | A | B | C | D | E | F | G |
var new_headers = [ "S", "h", "e", "e", "t", "J", "S" ];
/* write data with using data headers */
var ws = XLSX.utils.json_to_sheet(data, { header: data_headers });
/* replace first row */
XLSX.utils.sheet_add_aoa(worksheet, [new_headers], { origin: "A1" });
将对象数组中的数据添加到现有工作表
¥Add data from an array of objects to an existing worksheet
XLSX.utils.sheet_add_json(ws, aoo, opts);
XLSX.utils.sheet_add_json
采用对象数组并更新现有工作表对象。它遵循与 json_to_sheet
相同的过程并接受选项参数:
¥XLSX.utils.sheet_add_json
takes an array of objects and updates an existing
worksheet object. It follows the same process as json_to_sheet
and accepts
an options argument:
选项名称 | 默认 | 描述 |
---|---|---|
header | 使用指定的列顺序(默认 Object.keys ) | |
dateNF | 菌群移植 14 | 在字符串输出中使用指定的日期格式 |
cellDates | false | 将日期存储为 d 类型(默认为 n ) |
skipHeader | false | 如果为 true,则输出中不包含标题行 |
nullError | false | 如果为 true,则针对 null 值触发 #NULL! 错误单元 |
origin | 使用指定的单元格作为起点(见下文) | |
UTC | false | 如果为 true,则使用 UTC 方法解释日期 ** |
¥UTC option is explained in "Dates"
origin
预计为以下之一:
¥origin
is expected to be one of:
origin | 描述 |
---|---|
(单元格对象) | 使用指定单元格(单元格对象) |
(字符串) | 使用指定单元格(A1 型单元格) |
(数量 >= 0) | 从指定行的第一列开始(0 索引) |
-1 | 从第一列开始附加到工作表底部 |
(默认) | 从单元格 A1 开始 |
此示例工作表可以按照 A1:G1, A2:B4, E2:G4, A5:G5
的顺序构建:
¥This example worksheet can be built up in the order A1:G1, A2:B4, E2:G4, A5:G5
:
/* Start from an empty worksheet */
var ws = XLSX.utils.aoa_to_sheet([[]]);
/* Header order */
var header = ["A", "B", "C", "D", "E", "F", "G"];
/* First row */
XLSX.utils.sheet_add_json(ws, [
{ A: "S", B: "h", C: "e", D: "e", E: "t", F: "J", G: "S" }
], {header: header, skipHeader: true});
/* Write data starting at A2 */
XLSX.utils.sheet_add_json(ws, [
{ A: 1, B: 2 }, { A: 2, B: 3 }, { A: 3, B: 4 }
], {header: header, skipHeader: true, origin: "A2"});
/* Write data starting at E2 */
XLSX.utils.sheet_add_json(ws, [
{ A: 5, B: 6, C: 7 }, { A: 6, B: 7, C: 8 }, { A: 7, B: 8, C: 9 }
], {header: ["A", "B", "C"], skipHeader: true, origin: { r: 1, c: 4 }});
/* Append row */
XLSX.utils.sheet_add_json(ws, [
{ A: 4, B: 5, C: 6, D: 7, E: 8, F: 9, G: 0 }
], {header: header, skipHeader: true, origin: -1});
如果 header
选项是数组,则 sheet_add_json
和 sheet_to_json
将追加缺失的元素。
¥If the header
option is an array, sheet_add_json
and sheet_to_json
will
append missing elements.
这种设计可以在调用之间实现一致的标头顺序:
¥This design enables consistent header order across calls:
function SheetJSHeaderOrder() { /* Use shared header */ const header = []; const ws1 = XLSX.utils.json_to_sheet([ {C: 2, D: 3}, ], {header}); XLSX.utils.sheet_add_json(ws1, [ {D: 1, C: 4}, ], {header, origin: -1, skipHeader: true}); /* only use header in first call */ const ws2 = XLSX.utils.json_to_sheet([ {C: 2, D: 3}, ], {header:[]}); XLSX.utils.sheet_add_json(ws2, [ {D: 1, C: 4}, ], {origin: -1, skipHeader: true}); return (<pre> <b>Objects</b> {"\n[\n { C: 2, D: 3 },\n { D: 1, C: 4 } // different key order\n]\n"}<br/> <b>Worksheet when same `header` array is passed to `sheet_add_json`</b> <div dangerouslySetInnerHTML={{__html:XLSX.utils.sheet_to_html(ws1)}}/> <i>New contents of `header`</i><br/> {JSON.stringify(header)}<br/> <br/> <b>Worksheet when no `header` property is passed to `sheet_add_json`</b> <div dangerouslySetInnerHTML={{__html:XLSX.utils.sheet_to_html(ws2)}}/> </pre>) }
数组输出
¥Array Output
- JavaScript
- TypeScript
var arr = XLSX.utils.sheet_to_json(ws, opts);
var aoa = XLSX.utils.sheet_to_json(ws, {header: 1, ...other_opts});
TypeScript 类型纯粹是提供信息的。它们不包含在运行时,并且不影响 sheet_to_json
函数的行为。
¥TypeScript types are purely informational. They are not included at run time
and do not influence the behavior of the sheet_to_json
function.
sheet_to_json
不进行字段验证!
¥sheet_to_json
does not perform field validation!
主要类型签名将每一行视为 any
:
¥The main type signature treats each row as any
:
const data: any[] = XLSX.utils.sheet_to_json(ws, opts);
any[][]
重载设计用于与 header: 1
(数组的数组)一起使用:
¥The any[][]
overload is designed for use with header: 1
(array of arrays):
const aoa: any[][] = XLSX.utils.sheet_to_json(ws, { header: 1, ...other_opts });
接口可以作为通用参数传递。sheet_to_json
仍将返回一个普通对象数组(类型不影响运行时行为):
¥An interface can be passed as a generic parameter. sheet_to_json
will still
return an array of plain objects (the types do not affect runtime behavior):
interface President {
Name: string;
Index: number;
}
const data: President[] = XLSX.utils.sheet_to_json<President>(ws);
XLSX.utils.sheet_to_json
生成一个 JS 对象数组。该函数接受一个选项参数:
¥XLSX.utils.sheet_to_json
generates an array of JS objects. The function takes
an options argument:
选项名称 | 默认 | 描述 |
---|---|---|
raw | true | 使用原始值 (true) 或格式化字符串 (false) |
range | ** | 覆盖范围(见下表) |
header | 控制输出格式(见下表) | |
dateNF | 菌群移植 14 | 在字符串输出中使用指定的日期格式 |
defval | 使用指定值代替 null 或未定义 | |
blankrows | ** | 在输出中包含空行 ** |
skipHidden | false | 不为隐藏的行/列生成对象 |
UTC | false | 如果为 true,则 UTC 日期将是正确的 ** |
-
raw
仅影响具有格式代码 (.z
) 字段或格式化文本 (.w
) 字段的单元格。¥
raw
only affects cells which have a format code (.z
) field or a formatted text (.w
) field. -
如果指定
header
,则第一行被认为是数据行;如果未指定header
,则第一行是标题行,不被视为数据。¥If
header
is specified, the first row is considered a data row; ifheader
is not specified, the first row is the header row and not considered data. -
当未指定
header
时,转换将通过附加_
和从1
开始的计数来自动消除标题条目的歧义。例如,如果三列的标题为foo
,则输出字段为foo
、foo_1
、foo_2
¥When
header
is not specified, the conversion will automatically disambiguate header entries by affixing_
and a count starting at1
. For example, if three columns have headerfoo
the output fields arefoo
,foo_1
,foo_2
-
当
raw
为 true 时返回null
值,而当raw
为 false 时则跳过。¥
null
values are returned whenraw
is true but are skipped when false. -
如果未指定
defval
,则通常会跳过 null 和未定义的值。如果指定,所有空点和未定义点都将用defval
填充¥If
defval
is not specified, null and undefined values are skipped normally. If specified, all null and undefined points will be filled withdefval
-
当
header
为1
时,默认生成空行。blankrows
必须设置为false
以跳过空白行。¥When
header
is1
, the default is to generate blank rows.blankrows
must be set tofalse
to skip blank rows. -
当
header
不是1
时,默认跳过空白行。blankrows
必须为 true 才能生成空白行¥When
header
is not1
, the default is to skip blank rows.blankrows
must be true to generate blank rows
range
预计为以下之一:
¥range
is expected to be one of:
range | 描述 |
---|---|
(数字) | 使用工作表范围但将起始行设置为值 |
(字符串) | 使用指定范围(A1 样式有界范围字符串) |
(默认) | 使用工作表范围 (ws['!ref'] ) |
header
预计为以下之一:
¥header
is expected to be one of:
header | 描述 |
---|---|
1 | 生成数组的数组 |
"A" | 行对象键是字面量列标签 |
字符串数组 | 使用指定的字符串作为行对象中的键 |
(默认) | 读取第一行作为键并消除歧义 |
-
如果 header 不是
1
,则行对象将包含不可枚举属性__rowNum__
,该属性表示与条目对应的工作表的行。¥If header is not
1
, the row object will contain the non-enumerable property__rowNum__
that represents the row of the sheet corresponding to the entry. -
如果 header 是一个数组,则不会消除键的歧义。如果数组值不唯一,这可能会导致意外结果!
¥If header is an array, the keys will not be disambiguated. This can lead to unexpected results if the array values are not unique!
对于示例工作表:
¥For the example worksheet:
function SheetJSToJSON() { /* original data */ var ws = XLSX.utils.aoa_to_sheet([ ["S", "h", "e", "e", "t", "J", "S"], [ 1, 2, , , 5, 6, 7], [ 2, 3, , , 6, 7, 8], [ 3, 4, , , 7, 8, 9], [ 4, 5, 6, 7, 8, 9, 0] ]); /* display JS objects with some whitespace */ const aoo = o => o.map(r => " " + JSON.stringify(r).replace(/,"/g, ', "').replace(/:/g, ": ").replace(/"([A-Za-z_]\w*)":/g, '$1:')).join("\n"); const aoa = o => o.map(r => " " + JSON.stringify(r).replace(/,/g, ', ').replace(/null/g, "")).join("\n"); return ( <pre> <b>Worksheet (as HTML)</b> <div dangerouslySetInnerHTML={{__html: XLSX.utils.sheet_to_html(ws)}}/> <b>XLSX.utils.sheet_to_json(ws, {'{'} header: 1 {'}'}) [array of arrays]</b><br/> [<br/>{aoa(XLSX.utils.sheet_to_json(ws, { header: 1 }))}<br/>]<br/><br/> <b>XLSX.utils.sheet_to_json(ws) [objects with header disambiguation]</b><br/> [<br/>{aoo(XLSX.utils.sheet_to_json(ws))}<br/>]<br/><br/> <b>XLSX.utils.sheet_to_json(ws, {'{'} header: "A" {'}'}) [column names as keys]</b><br/> [<br/>{aoo(XLSX.utils.sheet_to_json(ws, { header: "A" }))}<br/>]<br/><br/> <b>XLSX.utils.sheet_to_json(ws, {'{'} header: ["A","E","I","O","U","6","9"] {'}'})</b><br/> [<br/>{aoo(XLSX.utils.sheet_to_json(ws, { header: ["A","E","I","O","U","6","9"] }))}<br/>]<br/> </pre> ); }