Skip to main content

Export Tutorial导出教程

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.许多现代数据源提供了一个API来下载JSON格式的数据。许多用户更喜欢使用电子表格软件。SheetJS库通过将程序员友好的JSON转换为用户友好的工作簿来帮助弥补这一差距。

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. 此示例的目标是生成一个包含美国总统姓名和出生日期的XLSX工作簿。我们将使用标准JavaScript函数下载并讨论JSON数据集。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.一旦我们有了一个简单的名称和出生日期列表，我们将使用SheetJS API函数来构建一个工作簿对象并导出到XLSX。

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.展示了如何在iOS/Android应用程序、桌面应用程序、NodeJS脚本和其他环境中运行工作流。

The following sequence diagram shows the process:以下序列图显示了该过程：

UserPageAPIprocess datamake workbookexport fileclick buttonfetch dataraw datadownload workbookUserPageAPI

Acquire Data获取数据​

The raw data is available in JSON form1. 原始数据以JSON形式1提供。It has been mirrored at https://sheetjs.com/data/executive.json它已镜像在https://sheetjs.com/data/executive.json。

Raw Data原始数据​

Acquiring the data is straightforward with fetch:使用fetch获取数据非常简单：

const url = "https://sheetjs.com/data/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.是用于从端点下载数据的低级API。它将网络步骤与响应解析步骤分开。

Network Step网络步骤

fetch(url) returns a Promise representing the network request. 返回表示网络请求的Promise。The browser will attempt to download data from the URL. 浏览器将尝试从URL下载数据。If the network request succeeded, the Promise will "return" with a Response object.如果网络请求成功，Promise将“返回”一个Response对象。

Using modern syntax, inside an async function, code should await the fetch:使用现代语法，在async函数内部，代码应该await获取：

const response = await fetch(url);

Checking Status Code检查状态代码

If the file is not available, the fetch will still succeed.如果该文件不可用，则fetch仍将成功。

The status code, stored in the status property of the Response object, is a standard HTTP status code number. Code should check the result.状态代码存储在Response对象的status属性中，是一个标准的HTTP状态代码编号。代码应该检查结果。

Typically servers will return status 404 "File not Found" if the file is not available. 如果文件不可用，服务器通常会返回状态404“找不到文件”。A successful request should have status 200 "OK".成功的请求应具有状态200“OK”。

Extracting Data提取数据

Response#json will try to parse the data using JSON.parse. 将尝试使用JSON.parse解析数据。Like fetch, the json method returns a Promise that must be await-ed:与fetch一样，json方法返回一个必须await的Promise：

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.Response对象还有其他有用的方法。Response#arrayBuffer将以arrayBuffer的形式返回原始数据，适用于分析工作簿文件。

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服务器404错误--找不到文件 */
    throw new Error("File not found");
  }
  if(response.status != 200) {
    /* for most servers, a successful response will have status 200对于大多数服务器，成功响应的状态为200 */
    throw new Error(`Server status ${response.status}: ${response.statusText}`);
  }

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

  return data;
}

The raw data is an Array of objects2. For this discussion, the relevant data for John Adams is shown below:原始数据是一个对象数组2。对于本次讨论，John Adams的相关数据如下所示：

{
  "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筛选总统​

The dataset includes Aaron Burr, a Vice President who was never President!数据集包括Aaron Burr，一位从未担任过总统的副总统！

The terms field of each object is an array of terms. 每个对象的terms字段是一个术语数组。A term is a Presidential term if the type property is "prez". 如果type属性为"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 code can be extremely concise. JavaScript代码可以非常简洁。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.如果函数返回true，那么Array#some将返回true。如果每个调用都返回false，那么Array#some将返回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:为了测试一个人是否是总统，应针对terms数组中的每个术语测试该功能：

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. 如果函数返回true，则将元素添加到最终数组中。If the function returns false, the element is not added.如果函数返回false，则不添加元素。

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:将person_was_president函数放入行中，最终代码为：

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. Joe Biden and Barack Obama were Vice President and President respectively in 2009. 副总统和特定任期内的总统按字母顺序排列。乔·拜登和巴拉克·奥巴马分别在2009年担任副总统和总统。Since "Biden" is alphabetically before "Obama", Biden's data point appears first. The goal is to sort the presidents in order of their presidential term.由于“拜登”按字母顺序排列在“奥巴马”之前，因此拜登的数据点首先出现。目标是按照总统任期的顺序对总统进行排序。

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.第一步是将第一个总统任期开始日期添加到数据集中。以下代码查看每一位总统，并创建一个表示第一个总统任期开始的start属性。

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. 如果数组中没有符合条件的元素，则Array#find不会返回值。In this case, since prez was created by filtering for people that served at least one presidential term, the code assumes a term exists.在这种情况下，由于prez是通过筛选至少连任过一届总统的人而创建的，因此该代码假设存在一个任期。

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:以下函数创建所需的start属性：

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);

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数组中的每一行都有一个start属性。由于start属性是字符串，因此以下行对数组进行排序：

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

Comparator Functions and Relative Ordering in JavaScriptJavaScript中的比较器函数和相对排序

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. 如果a应放在b之前，则应返回负数。If b should be placed before a, the comparator should return a positive number.如果b应该放在a之前，比较器应该返回一个正数。

If the start properties were numbers, the following comparator would suffice:如果start属性是数字，则以下比较器就足够了：

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

For strings, JavaScript comparison operators can work:对于字符串，JavaScript比较运算符可以工作：

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

However, that comparator does not handle diacritics. 但是，该比较器不处理变音符号。For example, "z" < "é". 例如"z" < "é"。It is strongly recommended to use String#localeCompare to compare strings:强烈建议使用String#localeCompare来比较字符串：

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

Comparing two Presidents比较两位总统

The start properties of the Presidents should be compared:应比较总统的start属性：

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:将compare_prez函数放置在主体中：

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

Reshaping the Array重塑数组​

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. 在本例中，该名称将是名字与姓氏的组合（row.name.first + " " + row.name.last），生日将在row.bio.birthday提供。Using Array#map, the dataset can be massaged in one call:使用Array#map，可以在一次调用中对数据集进行按摩：

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:John Adams的关键字段如下所示：

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

If row is the object, then如果row是对象，那么

  • 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")是生日（“1735-10-19”）

The desired object has a name and birthday field:所需对象具有name和birthday字段：

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:get_data函数可以添加到位：

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

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创建工作簿​

With the cleaned dataset, XLSX.utils.json_to_sheet3 generates a worksheet:使用已清理的数据集，XLSX.utils.json_to_sheet3生成一个工作表：

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

XLSX.utils.book_new4 creates a new workbook and XLSX.utils.book_append_sheet5 appends a worksheet to the workbook. 创建一个新工作簿，XLSX.utils.book_append_sheet5会将一个工作表附加到工作簿中。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 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".默认情况下，json_to_sheet会创建一个带有标题行的工作表。在这种情况下，标题来自JS对象键：“name”和“birthday”。

The headers are in cells A1 and B1. 标题位于单元格A1和B1中。XLSX.utils.sheet_add_aoa6 can write text values to the existing worksheet starting at cell A1:可以将文本值写入从单元格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.有些名称的长度超过了默认的列宽。列宽是通过设置"!cols"工作表属性来设置的。7

The following line sets the width of column A to approximately 10 characters:下一行将列A的宽度设置为大约10个字符：

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

One Array#reduce call over rows can calculate the maximum width:一个Array#reduce对rows的调用可以计算最大宽度：

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.writeFile8 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.在浏览器中，它将尝试提示用户下载该文件。在NodeJS中，它会写入本地目录。

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

Live Demo现场演示​

This demo runs in the web browser! Click "Click to Generate File!" and the browser should try to create Presidents.xlsx此演示在web浏览器中运行！单击“单击以生成文件！”，浏览器应尝试创建Presidents.xlsx

Result
Loading...
Live Editor

https://sheetjs.com/pres.html is a hosted version of this demo.是此演示的托管版本。

Run the Demo Locally在本地运行演示​

Save the following script to SheetJSStandaloneDemo.html:将以下脚本保存到SheetJSStandaloneDemo.html：

SheetJSStandaloneDemo.html
<body>
<script src="https://cdn.sheetjs.com/xlsx-0.20.1/package/dist/xlsx.full.min.js"></script>
<script>
(async() => {
  /* fetch JSON data and parse获取JSON数据并解析 */
  const url = "https://sheetjs.com/data/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>

After saving the file, run a local web server in the folder with the HTML file. For example, if NodeJS is installed:保存文件后，在包含HTML文件的文件夹中运行本地web服务器。例如，如果安装了NodeJS：

npx http-server .

The server process will display a URL (typically http://127.0.0.1:8080). 服务器进程将显示一个URL（通常http://127.0.0.1:8080）。Open http://127.0.0.1:8080/SheetJSStandaloneDemo.html in your browser.在您的浏览器中打开http://127.0.0.1:8080/SheetJSStandaloneDemo.html。

  1. 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. See json_to_sheet in "Utilities"请参阅“实用程序”中的json_to_sheet↩
  4. See book_new in "Utilities"请参阅“实用程序”中的book_new↩
  5. See book_append_sheet in "Utilities"请参阅“实用程序”中的book_append_sheet↩
  6. See sheet_add_aoa in "Utilities"请参阅“实用程序”中的sheet_add_aoa↩
  7. See "Column Properties"请参阅“列属性”↩
  8. See writeFile in "Writing Files"请参阅“写入文件”中的writeFile↩