Skip to main content

数据数组

对象数组是 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:

SpreadsheetArray of Arrays

pres.xlsx data

[
["Name", "Index"],
["Bill Clinton", 42],
["GeorgeW Bush", 43],
["Barack Obama", 44],
["Donald Trump", 45],
["Joseph Biden", 46]
]

结构中的每个数组对应一行。可以通过按行索引和按列索引进行索引来读取各个数据点:

¥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:

SpreadsheetArray of Objects

pres.xlsx data

[
{ 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 }
]

结构中的每个对象对应一个数据行。电子表格的第一行被解释为标题行。

¥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:

SheetJS
12 567
23 678
34 789
4567890

该表包含重复的列标签("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 值可能会被存根(请参阅 sheetStubsnullError 选项)

    ¥null values may be stubbed (see sheetStubs and nullError options)

  • 单元格对象按原样使用。

    ¥Cell objects are used as-is.

该函数接受一个选项参数:

¥The function takes an options argument:

选项名称默认描述
dateNF菌群移植 14在字符串输出中使用指定的日期格式
cellDatesfalse将日期存储为 d 类型(默认为 n
sheetStubsfalsenull 值创建 z 类型的单元格对象
nullErrorfalse如果为 true,则针对 null 值触发 #NULL! 错误单元
UTCfalse如果为 true,则使用 UTC 方法解释日期 **
densefalse触发 致密片

UTC 选项在 "日期" 中解释

¥UTC option is explained in "Dates"

以下实例重现了 示例工作表

¥The following live example reproduces the example worksheet:

Result
Loading...
Live Editor
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在字符串输出中使用指定的日期格式
cellDatesfalse将日期存储为 d 类型(默认为 n
sheetStubsfalsenull 值创建 z 类型的单元格对象
nullErrorfalse如果为 true,则针对 null 值触发 #NULL! 错误单元
origin使用指定的单元格作为起点(见下文)
UTCfalse如果为 true,则使用 UTC 方法解释日期 **

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:

SpreadsheetOperations
SheetJS
12 567
23 678
34 789
4567890
  1. aoa_to_sheet([[]]) 创建一个空工作表

    ¥aoa_to_sheet([[]]) creates an empty worksheet

  2. sheet_add_aoa 写入 A1:G1(红色)

    ¥sheet_add_aoa writes A1:G1 (red)

  3. sheet_add_aoa 写入 A2:B4(蓝色)

    ¥sheet_add_aoa writes A2:B4 (blue)

  4. sheet_add_aoa 写入 E2:G4(绿色)

    ¥sheet_add_aoa writes E2:G4 (green)

  5. sheet_add_aoa 写入 A5:G5(黄色)

    ¥sheet_add_aoa writes A5:G5 (yellow)

/* 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});
Result
Loading...
Live Editor
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在字符串输出中使用指定的日期格式
cellDatesfalse将日期存储为 d 类型(默认为 n
skipHeaderfalse如果为 true,则输出中不包含标题行
nullErrorfalse如果为 true,则针对 null 值触发 #NULL! 错误单元
UTCfalse如果为 true,则使用 UTC 方法解释日期 **
densefalse触发 致密片

UTC 选项在 "日期" 中解释

¥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.

Result
Loading...
Live Editor
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在字符串输出中使用指定的日期格式
cellDatesfalse将日期存储为 d 类型(默认为 n
skipHeaderfalse如果为 true,则输出中不包含标题行
nullErrorfalse如果为 true,则针对 null 值触发 #NULL! 错误单元
origin使用指定的单元格作为起点(见下文)
UTCfalse如果为 true,则使用 UTC 方法解释日期 **

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_jsonsheet_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:

Result
Loading...
Live Editor
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

var arr = XLSX.utils.sheet_to_json(ws, opts);

var aoa = XLSX.utils.sheet_to_json(ws, {header: 1, ...other_opts});

XLSX.utils.sheet_to_json 生成一个 JS 对象数组。该函数接受一个选项参数:

¥XLSX.utils.sheet_to_json generates an array of JS objects. The function takes an options argument:

选项名称默认描述
rawtrue使用原始值 (true) 或格式化字符串 (false)
range**覆盖范围(见下表)
header控制输出格式(见下表)
dateNF菌群移植 14在字符串输出中使用指定的日期格式
defval使用指定值代替 null 或未定义
blankrows**在输出中包含空行 **
skipHiddenfalse不为隐藏的行/列生成对象
UTCfalse如果为 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; if header is not specified, the first row is the header row and not considered data.

  • 当未指定 header 时,转换将通过附加 _ 和从 1 开始的计数来自动消除标题条目的歧义。例如,如果三列的标题为 foo,则输出字段为 foofoo_1foo_2

    ¥When header is not specified, the conversion will automatically disambiguate header entries by affixing _ and a count starting at 1. For example, if three columns have header foo the output fields are foo, foo_1, foo_2

  • raw 为 true 时返回 null 值,而当 raw 为 false 时则跳过。

    ¥null values are returned when raw 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 with defval

  • header1 时,默认生成空行。blankrows 必须设置为 false 以跳过空白行。

    ¥When header is 1, the default is to generate blank rows. blankrows must be set to false to skip blank rows.

  • header 不是 1 时,默认跳过空白行。blankrows 必须为 true 才能生成空白行

    ¥When header is not 1, the default is to skip blank rows. blankrows must be true to generate blank rows

  • UTC 选项在 "日期" 中解释

    ¥UTC option is explained in "Dates"

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:

Result
Loading...
Live Editor
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> );
}