Skip to main content

导出教程

许多现代数据源提供 API 来下载 JSON 格式的数据。许多用户更喜欢使用电子表格软件。SheetJS 库通过将程序员友好的 JSON 转换为用户友好的工作簿来帮助弥合差距。

¥Many modern data sources provide an API to download data in JSON format. Many users prefer to work in spreadsheet software. SheetJS libraries help bridge the gap by translating programmer-friendly JSON to user-friendly workbooks.

此示例的目标是生成包含美国总统名称和出生日期的 XLSX 工作簿。我们将使用标准 JavaScript 函数下载并整理 JSON 数据集。一旦我们有了简单的名称和生日列表，我们将使用 SheetJS API 函数构建工作簿对象并导出到 XLSX。

¥The goal of this example is to generate a XLSX workbook of US President names and birthdates. We will download and wrangle a JSON dataset using standard JavaScript functions. Once we have a simple list of names and birthdates, we will use SheetJS API functions to build a workbook object and export to XLSX.

"在线演示" 部分在此页面中包含一个工作演示！"在本地运行演示" 展示了如何在 iOS / Android 应用、桌面应用、NodeJS 脚本和其他环境中运行工作流程。

¥The "Live Demo" section includes a working demo in this page! "Run the Demo Locally" shows how to run the workflow in iOS / Android apps, desktop apps, NodeJS scripts and other environments.

下面的序列图显示了该过程：

¥The following sequence diagram shows the process:

获取数据​

¥Acquire Data

原始数据以 JSON 格式 [^1] 提供。已在 https://xlsx.nodejs.cn/executive.json 镜像

¥The raw data is available in JSON form[^1]. It has been mirrored at https://xlsx.nodejs.cn/executive.json

原始数据​

¥Raw Data

使用 fetch 获取数据非常简单：

¥Acquiring the data is straightforward with fetch:

const url = "https://xlsx.nodejs.cn/executive.json";
const raw_data = await (await fetch(url)).json();
Code Explanation (click to show)

fetch is a low-level API for downloading data from an endpoint. It separates the network step from the response parsing step.

Network Step

fetch(url) returns a Promise representing the network request. The browser will attempt to download data from the URL. If the network request succeeded, the Promise will "return" with a Response object.

Using modern syntax, inside an async function, code should await the fetch:

const response = await fetch(url);

Checking Status Code

If the file is not available, the fetch will still succeed.

The status code, stored in the status property of the Response object, is a standard HTTP status code number. Code should check the result.

Typically servers will return status 404 "File not Found" if the file is not available. A successful request should have status 200 "OK".

Extracting Data

Response#json will try to parse the data using JSON.parse. Like fetch, the json method returns a Promise that must be await-ed:

const raw_data = await response.json();

The Response object has other useful methods. Response#arrayBuffer will return the raw data as an ArrayBuffer, suitable for parsing workbook files.

Production Use

Functions can test each part independently and report different errors:

async function get_data_from_endpoint(url) {
  /* perform network request */
  let response;
  try {
    response = await fetch(url);
  } catch(e) {
    /* network error */
    throw new Error(`Network Error: ${e.message}`);
  }

  /* check status code */
  if(response.status == 404) {
    /* server 404 error -- file not found */
    throw new Error("File not found");
  }
  if(response.status != 200) {
    /* for most servers, a successful response will have status 200 */
    throw new Error(`Server status ${response.status}: ${response.statusText}`);
  }

  /* parse JSON */
  let data;
  try {
     data = await response.json();
  } catch(e) {
    /* parsing error */
    throw new Error(`Parsing Error: ${e.message}`);
  }

  return data;
}

原始数据是对象 [^2] 的数组。对于本次讨论，John Adams 的相关数据如下：

¥The raw data is an Array of objects[^2]. For this discussion, the relevant data for John Adams is shown below:

{
  "name": {
    "first": "John",          // <-- first name
    "last": "Adams"           // <-- last name
  },
  "bio": {
    "birthday": "1735-10-19", // <-- birthday
  },
  "terms": [                  // <-- array of presidential terms
    { "type": "viceprez", "start": "1789-04-21", },
    { "type": "viceprez", "start": "1793-03-04", },
    { "type": "prez",     "start": "1797-03-04", } // <-- presidential term
  ]
}

筛选总统​

¥Filtering for Presidents

该数据集包括亚伦·伯尔 (Aaron Burr)，他是一位从未担任过总统的副总统！

¥The dataset includes Aaron Burr, a Vice President who was never President!

每个对象的 terms 字段是一个术语数组。如果 type 属性是 "prez"，则任期为总统任期。我们对至少任职一届的总统感兴趣。下面的行创建了一个 President 数组：

¥The terms field of each object is an array of terms. A term is a Presidential term if the type property is "prez". We are interested in Presidents that served at least one term. The following line creates an array of Presidents:

const prez = raw_data.filter(row => row.terms.some(term => term.type === "prez"));

JavaScript 代码可以非常简洁。"代码说明" 块更详细地解释了代码。

¥JavaScript code can be extremely concise. The "Code Explanation" blocks explain the code in more detail.

Code Explanation (click to show)

Verifying if a person was a US President

Array#some takes a function and calls it on each element of an array in order. If the function ever returns true, Array#some returns true. If each call returns false, Array#some returns false.

The following function tests if a term is presidential:

const term_is_presidential = term => term.type == "prez";

To test if a person was a President, that function should be tested against every term in the terms array:

const person_was_president = person => person.terms.some(term => term.type == "prez");

Creating a list of US Presidents

Array#filter takes a function and returns an array. The function is called on each element in order. If the function returns true, the element is added to the final array. If the function returns false, the element is not added.

Using the previous function, this line filters the dataset for Presidents:

const prez = raw_data.filter(row => person_was_president(row));

Placing the person_was_president function in-line, the final code is:

const prez = raw_data.filter(row => row.terms.some(term => term.type == "prez"));

按第一项排序​

¥Sorting by First Term

数据集按第一任总统或副总统任期按时间顺序排序。给定任期内的副总统和总统按字母顺序排序。

¥The dataset is sorted in chronological order by the first presidential or vice presidential term. The Vice President and President in a given term are sorted alphabetically.

2009 年，巴拉克·奥巴马 (Barack Obama) 成为总统，约瑟夫·拜登 (Joseph Biden) 成为副总统。由于 "拜登" 在字母顺序上位于 "奥巴马" 之前，因此拜登的数据首先出现。

¥Barack Obama became President and Joseph Biden became Vice President in 2009. Since "Biden" is alphabetically before "Obama", Biden's data appears first.

目标是按总统的初始任期顺序对其进行排序。

¥The goal is to sort the presidents in order of their initial presidential term.

第一步是将第一个总统任期开始日期添加到数据集中。以下代码查看每位总统并创建一个代表第一个总统任期开始时间的 start 属性。

¥The first step is adding the first presidential term start date to the dataset. The following code looks at each president and creates a start property that represents the start of the first presidential term.

prez.forEach(row => row.start = row.terms.find(term => term.type === "prez").start);
Code Explanation (click to show)

Finding the first presidential term

Array#find will find the first value in an array that matches a criterion. The first presidential term can be found with the following function:

const first_prez_term = prez => prez.terms.find(term => term.type === "prez");

If no element in the array matches the criterion, Array#find does not return a value. In this case, since prez was created by filtering for people that served at least one presidential term, the code assumes a term exists.

The start of a President's first Presidential term is therefore

const first_prez_term_start = prez => first_prez_term(prez).start;

Adding the first start date to one row

The following function creates the desired start property:

const prez_add_start = prez => prez.start = first_prez_term_start(prez);

Adding the first start date to each row

Array#forEach takes a function and calls it for every element in the array. Any modifications to objects affect the objects in the original array.

The previous function can be used directly:

prez.forEach(row => prez_add_start(row));

Working in reverse, each partial function can be inserted in place. These lines of code are equivalent:

/* start */
prez.forEach(row => prez_add_start(row));

/* put `prez_add_start` definition into the line */
prez.forEach(row => row.start = first_prez_term_start(row));

/* put `first_prez_term_start` definition into the line */
prez.forEach(row => row.start = first_prez_term(row).start);

/* put `first_prez_term` definition into the line */
prez.forEach(row => row.start = row.terms.find(term => term.type === "prez").start);

此时，prez 数组中的每一行都有一个 start 属性。由于 start 属性是字符串，因此以下行对数组进行排序：

¥At this point, each row in the prez array has a start property. Since the start properties are strings, the following line sorts the array:

prez.sort((l,r) => l.start.localeCompare(r.start));
Code Explanation (click to show)

Comparator Functions and Relative Ordering in JavaScript

A comparator takes two arguments and returns a number that represents the relative ordering. comparator(a,b) should return a negative number if a should be placed before b. If b should be placed before a, the comparator should return a positive number.

If the start properties were numbers, the following comparator would suffice:

const comparator_numbers = (a,b) => a - b;

For strings, JavaScript comparison operators can work:

const comparator_string_simple = (a,b) => a == b ? 0 : a < b ? -1 : 1;

However, that comparator does not handle diacritics. For example, "z" < "é". It is strongly recommended to use String#localeCompare to compare strings:

const comparator_string = (a,b) => a.localeCompare(b);

Comparing two Presidents

The start properties of the Presidents should be compared:

const compare_prez = (a,b) => (a.start).localeCompare(b.start);

Sorting the Array

Array#sort takes a comparator function and sorts the array in place. Using the Presidential comparator:

prez.sort((l,r) => compare_prez(l,r));

Placing the compare_prez function in the body:

prez.sort((l,r) => l.start.localeCompare(r.start));

重塑数组​

¥Reshaping the Array

对于此示例，名称将是名字与姓氏的组合 (row.name.first + " " + row.name.last)，生日将在 row.bio.birthday 提供。使用 Array#map，可以在一次调用中处理数据集：

¥For this example, the name will be the first name combined with the last name (row.name.first + " " + row.name.last) and the birthday will be available at row.bio.birthday. Using Array#map, the dataset can be massaged in one call:

const rows = prez.map(row => ({
  name: row.name.first + " " + row.name.last,
  birthday: row.bio.birthday
}));
Code Explanation (click to show)

Wrangling One Data Row

The key fields for John Adams are shown below:

{
  "name": {
    "first": "John",          // <-- first name
    "last": "Adams"           // <-- last name
  },
  "bio": {
    "birthday": "1735-10-19", // <-- birthday
  }
}

If row is the object, then

  • row.name.first is the first name ("John")
  • row.name.last is the last name ("Adams")
  • row.bio.birthday is the birthday ("1735-10-19")

The desired object has a name and birthday field:

function get_data(row) {
  var name = row.name.first + " " + row.name.last;
  var birthday = row.bio.birthday;
  return ({
    name: name,
    birthday: birthday
  });
}

This can be shortened by adding the fields to the object directly:

function get_data(row) {
  return ({
    name: row.name.first + " " + row.name.last,
    birthday: row.bio.birthday
  });
}

When writing an arrow function that returns an object, parentheses are required:

//  open paren required --V
  const get_data = row => ({
    name: row.name.first + " " + row.name.last,
    birthday: row.bio.birthday
  });
// ^-- close paren required

Wrangling the entire dataset

Array#map calls a function on each element of an array and returns a new array with the return values of each function.

Using the previous method:

const rows = prez.map(row => get_data(row));

The get_data function can be added in place:

const rows = prez.map(row => ({
  name: row.name.first + " " + row.name.last,
  birthday: row.bio.birthday
}));

结果是一个没有嵌套的 "simple" 对象数组：

¥The result is an array of "simple" objects with no nesting:

[
  { name: "George Washington", birthday: "1732-02-22" },
  { name: "John Adams", birthday: "1735-10-19" },
  // ... one row per President
]

创建工作簿​

¥Create a Workbook

使用清理后的数据集，XLSX.utils.json_to_sheet[^3] 生成一个工作表：

¥With the cleaned dataset, XLSX.utils.json_to_sheet[^3] generates a worksheet:

const worksheet = XLSX.utils.json_to_sheet(rows);

XLSX.utils.book_new[^4] 创建一个新工作簿，XLSX.utils.book_append_sheet[^5] 将工作表附加到该工作簿。新工作表将被称为 "日期"：

¥XLSX.utils.book_new[^4] creates a new workbook and XLSX.utils.book_append_sheet[^5] appends a worksheet to the workbook. The new worksheet will be called "Dates":

const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Dates");

清理工作簿​

¥Clean up Workbook

数据在工作簿中并且可以导出。

¥The data is in the workbook and can be exported.

Rough export

有多种改进机会：标题可以重命名，列宽可以调整。

¥There are multiple opportunities for improvement: the headers can be renamed and the column widths can be adjusted.

SheetJS 专业版 提供了额外的样式选项，例如单元格样式和冻结行。

¥SheetJS Pro offers additional styling options like cell styling and frozen rows.

Changing Header Names (click to show)

By default, json_to_sheet creates a worksheet with a header row. In this case, the headers come from the JS object keys: "name" and "birthday".

The headers are in cells A1 and B1. XLSX.utils.sheet_add_aoa[^6] can write text values to the existing worksheet starting at cell A1:

XLSX.utils.sheet_add_aoa(worksheet, [["Name", "Birthday"]], { origin: "A1" });
Changing Column Widths (click to show)

Some of the names are longer than the default column width. Column widths are set by setting the "!cols" worksheet property.[^7]

The following line sets the width of column A to approximately 10 characters:

worksheet["!cols"] = [ { wch: 10 } ]; // set column A width to 10 characters

One Array#reduce call over rows can calculate the maximum width:

const max_width = rows.reduce((w, r) => Math.max(w, r.name.length), 10);
worksheet["!cols"] = [ { wch: max_width } ];

清理后，生成的工作簿如下图所示：

¥After cleanup, the generated workbook looks like the screenshot below:

Final export

导出文件​

¥Export a File

XLSX.writeFile[^8] 创建一个电子表格文件并尝试将其写入系统。在浏览器中，它会尝试提示用户下载文件。在 NodeJS 中，它将写入本地目录。

¥XLSX.writeFile[^8] creates a spreadsheet file and tries to write it to the system. In the browser, it will try to prompt the user to download the file. In NodeJS, it will write to the local directory.

XLSX.writeFile(workbook, "Presidents.xlsx", { compression: true });

在线演示​

¥Live Demo

该演示在网络浏览器中运行！单击 "点击生成文件！"，浏览器应尝试创建 Presidents.xlsx

¥This demo runs in the web browser! Click "Click to Generate File!" and the browser should try to create Presidents.xlsx

Result
Loading...
Live Editor
function Presidents() { return ( <button onClick={async () => {
  /* fetch JSON data and parse */
  const url = "https://xlsx.nodejs.cn/executive.json";
  const raw_data = await (await fetch(url)).json();

  /* filter for the Presidents */
  const prez = raw_data.filter(row => row.terms.some(term => term.type === "prez"));

  /* sort by first presidential term */
  prez.forEach(row => row.start = row.terms.find(term => term.type === "prez").start);
  prez.sort((l,r) => l.start.localeCompare(r.start));

  /* flatten objects */
  const rows = prez.map(row => ({
    name: row.name.first + " " + row.name.last,
    birthday: row.bio.birthday
  }));

  /* generate worksheet and workbook */
  const worksheet = XLSX.utils.json_to_sheet(rows);
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, "Dates");

  /* fix headers */
  XLSX.utils.sheet_add_aoa(worksheet, [["Name", "Birthday"]], { origin: "A1" });

  /* calculate column width */
  const max_width = rows.reduce((w, r) => Math.max(w, r.name.length), 10);
  worksheet["!cols"] = [ { wch: max_width } ];

  /* create an XLSX file and try to save to Presidents.xlsx */
  XLSX.writeFile(workbook, "Presidents.xlsx", { compression: true });
}}><b>Click to Generate file!</b></button> ); }

https://sheetjs.com/pres.html 是该演示的托管版本。

¥https://sheetjs.com/pres.html is a hosted version of this demo.

在本地运行演示​

¥Run the Demo Locally

将以下脚本保存到 SheetJSStandaloneDemo.html：

¥Save the following script to SheetJSStandaloneDemo.html:

SheetJSStandaloneDemo.html
<body>
<script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>
<script>
(async() => {
  /* fetch JSON data and parse */
  const url = "https://xlsx.nodejs.cn/executive.json";
  const raw_data = await (await fetch(url)).json();

  /* filter for the Presidents */
  const prez = raw_data.filter(row => row.terms.some(term => term.type === "prez"));

  /* sort by first presidential term */
  prez.forEach(row => row.start = row.terms.find(term => term.type === "prez").start);
  prez.sort((l,r) => l.start.localeCompare(r.start));

  /* flatten objects */
  const rows = prez.map(row => ({
    name: row.name.first + " " + row.name.last,
    birthday: row.bio.birthday
  }));

  /* generate worksheet and workbook */
  const worksheet = XLSX.utils.json_to_sheet(rows);
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, "Dates");

  /* fix headers */
  XLSX.utils.sheet_add_aoa(worksheet, [["Name", "Birthday"]], { origin: "A1" });

  /* calculate column width */
  const max_width = rows.reduce((w, r) => Math.max(w, r.name.length), 10);
  worksheet["!cols"] = [ { wch: max_width } ];

  /* create an XLSX file and try to save to Presidents.xlsx */
  XLSX.writeFile(workbook, "Presidents.xlsx", { compression: true });
})();
</script>
</body>

保存文件后，在包含 HTML 文件的文件夹中运行本地 Web 服务器。例如，如果安装了 NodeJS：

¥After saving the file, run a local web server in the folder with the HTML file. For example, if NodeJS is installed:

npx http-server .

服务器进程将显示一个 URL（通常为 http://127.0.0.1:8080）。在浏览器中打开 http://127.0.0.1:8080/SheetJSStandaloneDemo.html。

¥The server process will display a URL (typically http://127.0.0.1:8080). Open http://127.0.0.1:8080/SheetJSStandaloneDemo.html in your browser.

[^1]: https://theunitedstates.io/congress-legislators/executive.json 是示例数据集的原始位置。数据集的贡献者将内容奉献给公共字段。

¥https://theunitedstates.io/congress-legislators/executive.json is the original location of the example dataset. The contributors to the dataset dedicated the content to the public domain.

[^2]: 请参阅数据集文档中的 "行政部门"。

¥See "The Executive Branch" in the dataset documentation.

[^3]: 见 json_to_sheet 于 "实用工具"

¥See json_to_sheet in "Utilities"

[^4]: 见 book_new 于 "实用工具"

¥See book_new in "Utilities"

[^5]: 见 book_append_sheet 于 "实用工具"

¥See book_append_sheet in "Utilities"

[^6]: 见 sheet_add_aoa 于 "实用工具"

¥See sheet_add_aoa in "Utilities"

[^7]: 见 "列属性"

¥See "Column Properties"

[^8]: 见 writeFile 于 "写入文件"

¥See writeFile in "Writing Files"