Skip to main content

Complete Example完整示例

SheetJS presents a simple JS interface that works with "Array of Arrays" and "Array of JS Objects". SheetJS提供了一个简单的JS接口，可与“数组数组”和“JS对象数组”一起使用。The API functions are building blocks that should be combined with other JS APIs to solve problems.API函数是构建块，应该与其他JSAPI结合来解决问题。

The discussion focuses on the problem solving mindset. 讨论的重点是解决问题的心态。API details are covered in other parts of the documentation.API详细信息包含在文档的其他部分中。

The goal of this example is to generate a XLSX workbook of US President names and birthdays. 本例的目标是生成一个包含美国总统姓名和生日的XLSX工作簿。Click here点击此处 to jump to the live demo跳转到现场演示

Acquire Data​

Raw Data​

The raw data is available in JSON form. 原始数据以JSON形式提供。For convenience, it has been mirrored here为方便起见，此处已对其进行了镜像。

The data result is an Array of objects. 数据结果是一个对象数组。 This is the data for John Adams:以下是John Adams的数据：

{
  "id": { /* (data omitted) */ },
  "name": {
    "first": "John",          // <-- first name
    "last": "Adams"           // <-- last name
  },
  "bio": {
    "birthday": "1735-10-19", // <-- birthday
    "gender": "M"
  },
  "terms": [
    { "type": "viceprez", /* (other fields omitted) */ },
    { "type": "viceprez", /* (other fields omitted) */ },
    { "type": "prez", /* (other fields omitted) */ }
  ]
}

Filtering for Presidents筛选总统​

The dataset includes Aaron Burr, a Vice President who was never President!数据集包括亚伦·伯尔，一位从未当过总统的副总统！

Array#filter creates a new array with the desired rows. 使用所需行创建新数组。A President served at least one term with type set to "prez". 总统至少任期一届，type设置为"prez"。To test if a particular row has at least one "prez" term, Array#some is another native JS function. 为了测试特定行是否至少有一个"prez"项，Array#some是另一个本机JS函数。The complete filter would be:完整的筛选器将是：

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

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 the subfield 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，可以在一次调用中对数据集进行按摩：

constrows=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:结果是一个没有嵌套的“简单”对象数组：

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

Create a Workbook创建工作簿​

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

constworksheet= XLSX.utils.json_to_sheet(rows);

XLSX.utils.book_new creates a new workbook and XLSX.utils.book_append_sheet appends a worksheet to the workbook. XLSX.utils.book_new创建新工作簿，XLSX.utils.book_append_sheet将工作表附加到工作簿。The new worksheet will be called "Dates":新工作表将被称为“日期”：

constworkbook= 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. 默认情况下，json_to_sheet创建一个带有标题行的工作表。In this case, the headers come from the JS object keys: "name" and "birthday".在本例中，标题来自JS对象键：“name”和“birthday”。

The headers are in cells A1 and B1. 标题位于单元格A1和B1中。XLSX.utils.sheet_add_aoa 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"工作表属性来设置列宽。

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 } ];

Export a File导出文件​

XLSX.writeFile 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.在Node.js中，它将写入本地目录。

XLSX.writeFile(workbook, "Presidents.xlsx");

Final export

Live Demo​

Result
Loading...
Live Editor

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

Save the following script to snippet.html and open the page. 将以下脚本保存到snippet.html并打开页面。The page must be hosted (no file:/// access).页面必须托管（不能用file:///访问）。

https://sheetjs.com/pres.html is a hosted version of the page.是页面的托管版本。

<body>
<script src="https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js"></script>
<script>
(async() => {
  /* fetch JSON data and parse */
  const url = "https://sheetjs.com/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"));

  /* 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");
})();
</script>
<body>