导入教程

许多政府机构在工作簿中分发官方数据和统计数据。SheetJS 库帮助将这些文件转换为有用的信息。

¥Many government agencies distribute official data and statistics in workbooks. SheetJS libraries help translate these files to useful information.

此示例的目标是处理 XLS 工作表中的联邦学生援助组合数据。我们将从美国教育部下载并解析一本工作簿。解析原始数据后，我们将提取未偿还美元总额并将数据显示在表格中。

¥The goal of this example is to process Federal Student Aid Portfolio data from a XLS worksheet. We will download and parse a workbook from the US Department of Education. Once the raw data is parsed, we will extract the total outstanding dollar amount and display the data in a table.

"在线演示" 部分在此页面中包含一个工作演示！"在本地运行演示" 展示了如何在 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:

下载文件

¥Download File

原始数据可在 XLS 工作簿 [^1] 中找到。已在 https://xlsx.nodejs.cn/PortfolioSummary.xls 镜像

¥The raw data is available in a XLS workbook[^1]. It has been mirrored at https://xlsx.nodejs.cn/PortfolioSummary.xls

该官方数据集以 XLS 工作簿形式分发。

¥This official dataset is distributed in XLS workbooks.

SheetJS 支持多种传统和现代格式，确保历史数据不会在时间的流逝中丢失。

¥SheetJS supports a number of legacy and modern formats, ensuring that historical data is not lost in the sands of time.

使用 fetch 下载文件非常简单：

¥Downloading the file is straightforward with fetch:

const url = "https://xlsx.nodejs.cn/PortfolioSummary.xls";
const file = await (await fetch(url)).arrayBuffer();

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#arrayBuffer will pull the raw bytes into an ArrayBuffer, an object which can represent the file data. Like fetch, the arrayBuffer method returns a Promise that must be await-ed:

const file = await response.arrayBuffer();

The Response object has other useful methods. Response#json will parse the data with JSON.parse, suitable for data from an API endpoint.

Production Use

Functions can test each part independently and report different errors:

async function get_file_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}`);
  }

  /* get data */
  let ab;
  try {
     ab = await response.arrayBuffer();
  } catch(e) {
    /* data error */
    throw new Error(`Data Error: ${e.message}`);
  }

  return ab;
}

文件数据存储在 ArrayBuffer。

¥The file data is stored in an ArrayBuffer.

解析文件

¥Parse File

有了文件数据，XLSX.read[^2] 就会解析工作簿：

¥With the file data in hand, XLSX.read[^2] parses the workbook:

const workbook = XLSX.read(file);

workbook 对象遵循 "通用电子表格格式"[^3]，一种用于表示工作簿、工作表、单元格和电子表格功能的内存中格式。

¥The workbook object follows the "Common Spreadsheet Format"[^3], an in-memory format for representing workbooks, worksheets, cells, and spreadsheet features.

探索数据集

¥Explore Dataset

电子表格在野外使用许多不同的不一致约定。

¥Spreadsheets in the wild use many different inconsistent conventions.

要确定如何处理数据，最好先检查文件。

¥To determine how to process the data, it is best to inspect the file first.

列表工作表名称

¥List Sheet Names

如 "工作簿对象"[^4] 部分中所述，SheetNames 属性是工作簿中工作表名称的有序列表。

¥As explained in the "Workbook Object"[^4] section, the SheetNames property is a ordered list of the sheet names in the workbook.

以下实时代码块显示工作表名称的有序列表：

¥The following live code block displays an ordered list of the sheet names:

Result

Live Editor

function SheetJSheetNames() {
  const [names, setNames] = React.useState([]);
  React.useEffect(() => { (async() =>{
    /* parse workbook */
    const url = "https://xlsx.nodejs.cn/PortfolioSummary.xls";
    const file = await (await fetch(url)).arrayBuffer();
    const workbook = XLSX.read(file);
    /* display sheet names */
    setNames(workbook.SheetNames);
  })(); }, []);
  return ( <>
    <b>Sheet Names</b><br/>
    <ol start={0}>{names.map(n => (<li>{n}</li>))}</ol>
  </> )
}

检查工作表数据

¥Inspect Worksheet Data

工作簿对象 [^5] 的 Sheets 属性是一个对象，其键是工作表名称，其值是工作表对象。例如，通过索引 SheetNames 并使用名称索引 Sheets 来拉取第一个工作表：

¥The Sheets property of the workbook object[^5] is an object whose keys are sheet names and whose values are sheet objects. For example, the first worksheet is pulled by indexing SheetNames and using the name to index Sheets:

var first_sheet = workbook.Sheets[workbook.SheetNames[0]];

实际的工作表对象可以直接检查 [^6]，但强烈建议使用实用函数来呈现 JS 友好的数据结构。

¥The actual worksheet object can be inspected directly[^6], but it is strongly recommended to use utility functions to present JS-friendly data structures.

预览 HTML

¥Preview HTML

sheet_to_html 实用函数 [^7] 从工作表对象生成 HTML 表。以下在线示例显示了表中的前 20 行数据：

¥The sheet_to_html utility function[^7] generates an HTML table from worksheet objects. The following live example shows the first 20 rows of data in a table:

Live example (click to show)

SheetJS CE primarily focuses on data processing.

SheetJS Pro supports reading cell styles from files and generating styled HTML tables with colors, fonts, alignment and rich text.

Result

Live Editor

function SheetJSHTMLView() {
  const [__html, setHTML] = React.useState("");
  React.useEffect(() => { (async() =>{
    /* parse workbook, limiting to 20 rows */
    const url = "https://xlsx.nodejs.cn/PortfolioSummary.xls";
    const workbook = XLSX.read(await (await fetch(url)).arrayBuffer(), {sheetRows:20});
    /* get first worksheet */
    const worksheet = workbook.Sheets[workbook.SheetNames[0]];
    /* generate and display HTML */
    const table = XLSX.utils.sheet_to_html(worksheet);
    setHTML(table);
  })(); }, []);
  return ( <div dangerouslySetInnerHTML={{__html}}/> );
}

从表中看，要点是：

¥The key points from looking at the table are:

数据从第 7 行开始

¥The data starts on row 7
第 5 行和第 6 行是标题行，其中合并了常见标题的单元格

¥Rows 5 and 6 are the header rows, with merged cells for common titles
对于年度数据（2007-2012），A 列和 B 列被合并

¥For yearly data (2007-2012), columns A and B are merged
对于季度数据（2013 年第一季度及以后），A 列存储年份。单元格可以垂直合并以跨越 4 个季度

¥For quarterly data (2013Q1 and later), column A stores the year. Cells may be merged vertically to span 4 quarters

提取数据

¥Extract Data

提取原始数据

¥Extract Raw Data

XLSX.utils.sheet_to_json[^8] 从工作表对象生成数据数组。

¥XLSX.utils.sheet_to_json[^8] generates arrays of data from worksheet objects.

对于像这样的复杂布局，最简单的方法是生成 "数组的数组"，其中每行都是单元值数组。屏幕截图显示第 5-8 行：

¥For a complex layout like this, it is easiest to generate an "array of arrays" where each row is an array of cell values. The screenshot shows rows 5-8:

Rows 5-8

在数组的数组中，第 5 行有多个间隙，对应于空单元格和合并范围中覆盖的单元格：

¥In the array of arrays, row 5 has a number of gaps corresponding to empty cells and cells that are covered in the merge ranges:

// Row 5 -- the gaps correspond to cells with no content
[ , , "Direct Loans", , "Federal Family Education Loans (FFEL)", , "Perkins Loans", , "Total1" ]

第 7 行包括 2007 财年的数据：

¥Row 7 includes the data for FY2007:

// Row 7 -- column B is covered by the merge
[ 2007, , 106.8, 7, 401.9, 22.6, 8.2, 2.8, 516, 28.3 ]

如果指定了选项 header: 1，XLSX.utils.sheet_to_json 将生成一个数组的数组 [^9]：

¥XLSX.utils.sheet_to_json will generate an array of arrays if the option header: 1 is specified[^9]:

const worksheet = workbook.Sheets[workbook.SheetNames[0]];
const raw_data = XLSX.utils.sheet_to_json(worksheet, {header: 1});

填充合并块

¥Fill Merged Blocks

单元格 A13:A16 被合并：

¥Cells A13:A16 are merged:

Rows 13-16

合并的数据仅适用于左上角的单元格 (A13)。数组的数组将在单元格 A14:A16 中出现洞（写为 null）：

¥The merged data only applies to the top-left cell (A13). The array of arrays will have holes in cells A14:A16 (written as null):

// Row 13
[2013, "Q1", 508.7, 23.4, 444.9, 22.1, 8.2, 3,    961.9, 38.7]

// Row 14
[null, "Q2", 553,   24.1, 437,   21.6, 8.3, 3,    998.6, 38.9]

// Row 15
[null, "Q3", 569.2, 24.3, 429.5, 21.2, 8.2, 2.9, 1006.8, 38.7]

// Row 16
[null, "Q4", 609.1, 25.6, 423,   20.9, 8.1, 2.9, 1040.2, 39.6]

Live example (click to show)

Result

Live Editor

function SheetJSAoAHoles() {
  const [rows, setRows] = React.useState([]);
  React.useEffect(() => { (async() =>{
    /* parse workbook */
    const url = "https://xlsx.nodejs.cn/PortfolioSummary.xls";
    const workbook = XLSX.read(await (await fetch(url)).arrayBuffer());
    /* get first worksheet */
    const worksheet = workbook.Sheets[workbook.SheetNames[0]];
    const raw_data = XLSX.utils.sheet_to_json(worksheet, {header:1});

    /* pull Excel rows 13:16 (SheetJS 12:15) */
    const rows_13_16 = raw_data.slice(12,16);
    /* display data */
    setRows(rows_13_16);
  })(); }, []);
  return ( <pre>Rows 13:16{rows.map(r => "\n"+JSON.stringify(r))}</pre> );
}

工作表 !merges 属性 [^10] 包括工作表中的每个合并范围。可以循环遍历每个合并块并填充单元格，但在这种情况下，对原始数据进行后处理会更容易：

¥The worksheet !merges property[^10] includes every merge range in the sheet. It is possible to loop through every merge block and fill cells, but in this case it is easier to post-process the raw data:

let last_year = 0;
raw_data.forEach(r => last_year = r[0] = (r[0] != null ? r[0] : last_year));

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

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

Code Explanation (click to show)

Analyzing every row in the dataset

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.

For example, this loop will print out the first column in the arrays:

raw_data.forEach(r => {
  console.log(r);
});

Tracking the last value seen in a column

When looping over the array, Array#forEach can modify variables outside of the function body. For example, the following loop keeps track of the last value:

let last_value = null;
raw_data.forEach(r => {
  if(r[0] != null) last_value = r[0];
});

Filling in data

Array#forEach can mutate objects. The following code will assign the last value to the first column if it is not specified:

let last_value = null;
raw_data.forEach(r => {
  if(r[0] != null) last_value = r[0];
  else if(r[0] == null && last_value != null) r[0] = last_value;
});

Simplifying the code

When r[0] == null and last_value == null, assigning r[0] = last_value will not affect the result in the actual data rows:

let last_value = null;
raw_data.forEach(r => {
  if(r[0] != null) last_value = r[0];
  else if(r[0] == null) r[0] = last_value;
});

For simple data rows, either r[0] == null or r[0] != null, so the if block can be rewritten as a ternary expression:

let last_value = null;
raw_data.forEach(r => {
  (r[0] != null) ? (last_value = r[0]) : (r[0] = last_value);
});

Observing that r[0] must equal last_value, the inner statement can be rewritten to compute the final value and assign to both variables:

let last_value = null;
raw_data.forEach(r => {
  last_value = r[0] = (r[0] != null ? r[0] : last_value);
});

It is tempting to take advantage of implicit logical rules:

let last_value = null;
raw_data.forEach(r => {
  last_value = r[0] = (r[0] || last_value);
});

This is strongly discouraged since the value 0 is false. The explicit null test distinguishes null and undefined from 0

后处理后，行现在具有正确的年份字段：

¥After post-processing, the rows now have proper year fields:

// Row 13
[2013, "Q1", 508.7, 23.4, 444.9, 22.1, 8.2, 3,    961.9, 38.7]

// Row 14
[2013, "Q2", 553,   24.1, 437,   21.6, 8.3, 3,    998.6, 38.9]

// Row 15
[2013, "Q3", 569.2, 24.3, 429.5, 21.2, 8.2, 2.9, 1006.8, 38.7]

// Row 16
[2013, "Q4", 609.1, 25.6, 423,   20.9, 8.1, 2.9, 1040.2, 39.6]

Live example (click to show)

Result

Live Editor

function SheetJSAoAFilled() {
  const [rows, setRows] = React.useState([]);
  React.useEffect(() => { (async() =>{
    /* parse workbook */
    const url = "https://xlsx.nodejs.cn/PortfolioSummary.xls";
    const workbook = XLSX.read(await (await fetch(url)).arrayBuffer());
    /* get first worksheet */
    const worksheet = workbook.Sheets[workbook.SheetNames[0]];
    const raw_data = XLSX.utils.sheet_to_json(worksheet, {header:1});
    /* fill years */
    var last_year = 0;
    raw_data.forEach(r => last_year = r[0] = (r[0] != null ? r[0] : last_year));

    /* pull Excel rows 13:16 (SheetJS 12:15) */
    const rows_13_16 = raw_data.slice(12,16);
    /* display data */
    setRows(rows_13_16);
  })(); }, []);
  return ( <pre>Rows 13:16{rows.map(r => "\n"+JSON.stringify(r))}</pre> );
}

选择数据行

¥Select Data Rows

此时，每个数据行的第 A 列中将包含年份，第 C 列中将包含美元值。年份将在 2007 年至 2024 年之间，且值为正值。以下函数测试数据行：

¥At this point, each data row will have the year in column A and dollar value in column C. The year will be between 2007 and 2024 and the value will be positive. The following function tests a data row:

const is_valid_row = r =>
  r[0] >= 2007 && r[0] <= 2024 // year (column A) is between 2007 and 2024
  && r[2] > 0; // dollar value (column C) is positive

Array#filter，利用前面的测试，可以选择匹配的行：

¥Array#filter, using the previous test, can select the matching rows:

const rows = raw_data.filter(r => r[0] >= 2007 && r[0] <= 2024 && r[2] > 0);

Live example (click to show)

Result

Live Editor

function SheetJSAoAFiltered() {
  const [rows, setRows] = React.useState([]);
  React.useEffect(() => { (async() =>{
    /* parse workbook */
    const url = "https://xlsx.nodejs.cn/PortfolioSummary.xls";
    const workbook = XLSX.read(await (await fetch(url)).arrayBuffer());
    /* get first worksheet */
    const worksheet = workbook.Sheets[workbook.SheetNames[0]];
    const raw_data = XLSX.utils.sheet_to_json(worksheet, {header:1});
    /* fill years */
    var last_year = 0;
    raw_data.forEach(r => last_year = r[0] = (r[0] != null ? r[0] : last_year));
    /* select data rows */
    const rows = raw_data.filter(r => r[0] >= 2007 && r[0] <= 2024 && r[2] > 0);
    /* display data */
    setRows(rows);
  })(); }, []);
  return ( <pre>{rows.map(r => JSON.stringify(r)+"\n")}</pre> );
}

生成行对象

¥Generate Row Objects

查看标题：

¥Looking at the headers:

Rows 5-8

所需数据位于 I 列中。列索引可以使用 XLSX.utils.decode_col[^11] 计算。

¥The desired data is in column I. The column index can be calculated using XLSX.utils.decode_col[^11].

Column Index calculation (click to show)

Result

Live Editor

function SheetJSDecodeCol() {
  const cols = ["A", "B", "I"];

  return ( <table><thead><tr><th>Label</th><th>Index</th></tr></thead>
    <tbody>{cols.map(col => ( <tr>
      <td>{col}</td>
      <td>{XLSX.utils.decode_col(col)}</td>
    </tr> ))}</tbody>
  </table> );
}

所需的列是：

¥The desired columns are:

柱子	描述	对象中的属性
一个/0	财政年度	`FY`
乙/1	财政季度（如果适用）	`FQ`
我/8	未偿总额	`total`

对数据进行 Array#map 可以生成所需的行对象：

¥An Array#map over the data can generate the desired row objects:

const objects = rows.map(r => ({FY: r[0], FQ: r[1], total: r[8]}));

这将生成一个行对象数组。每个行对象将类似于以下行：

¥This will generate an array of row objects. Each row object will look like the following row:

// 2016 Q1 - $1220.3 (billion)
{ "FY": 2016, "FQ": "Q1", "total": 1220.3 }

Live example (click to show)

Result

Live Editor

function SheetJSObjects() {
  const [rows, setRows] = React.useState([]);
  React.useEffect(() => { (async() =>{
    /* parse workbook */
    const url = "https://xlsx.nodejs.cn/PortfolioSummary.xls";
    const workbook = XLSX.read(await (await fetch(url)).arrayBuffer());
    /* get first worksheet */
    const worksheet = workbook.Sheets[workbook.SheetNames[0]];
    const raw_data = XLSX.utils.sheet_to_json(worksheet, {header:1});
    /* fill years */
    var last_year = 0;
    raw_data.forEach(r => last_year = r[0] = (r[0] != null ? r[0] : last_year));
    /* select data rows */
    const rows = raw_data.filter(r => r[0] >= 2007 && r[0] <= 2024 && r[2] > 0);
    /* generate row objects */
    const objects = rows.map(r => ({FY: r[0], FQ: r[1], total: r[8]}));
    /* display data */
    setRows(objects);
  })(); }, []);
  return ( <pre>{rows.map(r => JSON.stringify(r)+"\n")}</pre> );
}

当前数据

¥Present Data

此时，objects 是一个对象数组。

¥At this point, objects is an array of objects.

ReactJS

本示例中的在线演示使用 ReactJS。在 ReactJS 中，对象数组最好以简单的 HTML 表格 [^12] 的形式呈现：

¥The live demos in this example use ReactJS. In ReactJS, arrays of objects are best presented in simple HTML tables[^12]:

<table>
  <thead><tr><th>Fiscal Year</th><th>Quarter</th><th>Total (in $B)</th></tr></thead>
  <tbody>
    {objects.map((o,R) => ( <tr key={R}>
      <td>{o.FY}</td>
      <td>{o.FQ}</td>
      <td>{o.total}</td>
    </tr>))}
  </tbody>
</table>

普通 JS

¥Vanilla JS

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

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

如果没有框架，可以使用 document.createElement 以编程方式创建 HTML 表格行元素并将其添加到表格主体元素。例如，如果页面有存根表：

¥Without a framework, HTML table row elements can be programmatically created with document.createElement and added to the table body element. For example, if the page has a stub table:

<table>
  <thead><tr><th>Fiscal Year</th><th>Quarter</th><th>Total (in $B)</th></tr></thead>
  <tbody id="tbody"></tbody>
</table>

可以使用 appendChild 将 TR 元素添加到表体中：

¥TR elements can be added to the table body using appendChild:

/* add rows to table body */
objects.forEach(o => {
  const row = document.createElement("TR");
  row.innerHTML = `<td>${o.FY}</td><td>${o.FQ||""}</td><td>${o.total}</td>`;
  tbody.appendChild(row);
});

命令行工具

¥Command-Line Tools

在命令行中，有多种方法可以显示表格中的数据：

¥In the command line, there are ways to display data in a table:

 FY    FQ    Total
 --    --    -----
2007         516
2013   Q1    961.9

对于数据管道，强烈建议使用制表符分隔的行：

¥For data pipelines, tab-separated rows are strongly recommended:

  /* print header row*/
  console.log(`FY\tFQ\tTotal`);
  /* print tab-separated values */
  objects.forEach(o => {
    console.log(`${o.FY}\t${o.FQ||""}\t${o.total}`);
  });

在线演示

¥Live Demo

该演示在网络浏览器中运行！它应该自动获取数据文件并显示表格。

¥This demo runs in the web browser! It should automatically fetch the data file and display a table.

此示例包括可以增加或减少的行数

¥This example includes a row count that can be increased or decreased

Result

Live Editor

function StudentAidTotal() {
  const [rows, setRows] = React.useState([]);
  const [num, setNum] = React.useState(5);
  React.useEffect(() => { (async() =>{
    /* parse workbook */
    const url = "https://xlsx.nodejs.cn/PortfolioSummary.xls";
    const workbook = XLSX.read(await (await fetch(url)).arrayBuffer());

    /* get first worksheet */
    const worksheet = workbook.Sheets[workbook.SheetNames[0]];
    const raw_data = XLSX.utils.sheet_to_json(worksheet, {header:1});

    /* fill years */
    var last_year = 0;
    raw_data.forEach(r => last_year = r[0] = (r[0] != null ? r[0] : last_year));

    /* select data rows */
    const rows = raw_data.filter(r => r[0] >= 2007 && r[0] <= 2024 && r[2] > 0);

    /* generate row objects */
    const objects = rows.map(r => ({FY: r[0], FQ: r[1], total: r[8]}));

    /* display data */
    setRows(objects);
  })(); }, []);
  return ( <>
    <a onClick={()=>{setNum(Math.max(num-5,0))}}>Show Less </a>
    <b>Showing {num} rows </b>
    <a onClick={()=>{setNum(num+5)}}>Show More</a>
    <table>
      <thead><tr><th>Fiscal Year</th><th>Quarter</th><th>Total (in $B)</th></tr></thead>
      <tbody>
        {rows.slice(0, num).map((o,R) => ( <tr key={R}>
          <td>{o.FY}</td>
          <td>{o.FQ}</td>
          <td>{o.total}</td>
        </tr>))}
      </tbody>
    </table>
  </> );
}

在本地运行演示

¥Run the Demo Locally

Web Browser
Command-Line (NodeJS)
Desktop App
Mobile App

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

¥Save the following script to SheetJSStandaloneDemo.html:

SheetJSStandaloneDemo.html
<body>
<table>
  <thead><tr><th>Fiscal Year</th><th>Quarter</th><th>Total (in $B)</th></tr></thead>
  <tbody id="tbody"></tbody>
</table>
<script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>
<script>
(async() => {
  /* parse workbook */
  const url = "https://xlsx.nodejs.cn/PortfolioSummary.xls";
  const workbook = XLSX.read(await (await fetch(url)).arrayBuffer());

  /* get first worksheet */
  const worksheet = workbook.Sheets[workbook.SheetNames[0]];
  const raw_data = XLSX.utils.sheet_to_json(worksheet, {header:1});

  /* fill years */
  var last_year = 0;
  raw_data.forEach(r => last_year = r[0] = (r[0] != null ? r[0] : last_year));

  /* select data rows */
  const rows = raw_data.filter(r => r[0] >= 2007 && r[0] <= 2024 && r[2] > 0);

  /* generate row objects */
  const objects = rows.map(r => ({FY: r[0], FQ: r[1], total: r[8]}));

  /* add rows to table body */
  objects.forEach(o => {
    const row = document.createElement("TR");
    row.innerHTML = `<td>${o.FY}</td><td>${o.FQ||""}</td><td>${o.total}</td>`;
    tbody.appendChild(row);
  });
})();
</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.

安装依赖：

¥Install the dependencies:

NodeJS
Bun

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz

bun install https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz

将以下脚本保存到 SheetJSNodeJS.js：

¥Save the following script to SheetJSNodeJS.js:

SheetJSNodeJS.js
const XLSX = require("xlsx");
(async() => {
  /* parse workbook */
  const url = "https://xlsx.nodejs.cn/PortfolioSummary.xls";
  const workbook = XLSX.read(await (await fetch(url)).arrayBuffer());

  /* get first worksheet */
  const worksheet = workbook.Sheets[workbook.SheetNames[0]];
  const raw_data = XLSX.utils.sheet_to_json(worksheet, {header:1});

  /* fill years */
  var last_year = 0;
  raw_data.forEach(r => last_year = r[0] = (r[0] != null ? r[0] : last_year));

  /* select data rows */
  const rows = raw_data.filter(r => r[0] >= 2007 && r[0] <= 2024 && r[2] > 0);

  /* generate row objects */
  const objects = rows.map(r => ({FY: r[0], FQ: r[1], total: r[8]}));

  /* print header row*/
  console.log(`FY\tQ\tTotal`);
  /* print tab-separated values */
  objects.forEach(o => {
    console.log(`${o.FY}\t${o.FQ||""}\t${o.total}`);
  });
})();

保存脚本后，运行脚本：

¥After saving the script, run the script:

NodeJS
Bun

node SheetJSNodeJS.js

bun run SheetJSNodeJS.js

此脚本将以制表符分隔值 (TSV) 格式打印行：

¥This script will print the rows in tab-separated values (TSV) format:

FY      Q       Total
2007            516
2008            577
...
2013    Q1      961.9
2013    Q2      998.6
2013    Q3      1006.8
...

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

¥Save the following script to SheetJSNW.html:

SheetJSNW.html
<body>
<table>
  <thead><tr><th>Fiscal Year</th><th>Quarter</th><th>Total (in $B)</th></tr></thead>
  <tbody id="tbody"></tbody>
</table>
<script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>
<script>
(async() => {
  /* parse workbook */
  const url = "https://xlsx.nodejs.cn/PortfolioSummary.xls";
  const workbook = XLSX.read(await (await fetch(url)).arrayBuffer());

  /* get first worksheet */
  const worksheet = workbook.Sheets[workbook.SheetNames[0]];
  const raw_data = XLSX.utils.sheet_to_json(worksheet, {header:1});

  /* fill years */
  var last_year = 0;
  raw_data.forEach(r => last_year = r[0] = (r[0] != null ? r[0] : last_year));

  /* select data rows */
  const rows = raw_data.filter(r => r[0] >= 2007 && r[0] <= 2024 && r[2] > 0);

  /* generate row objects */
  const objects = rows.map(r => ({FY: r[0], FQ: r[1], total: r[8]}));

  /* add rows to table body */
  objects.forEach(o => {
    const row = document.createElement("TR");
    row.innerHTML = `<td>${o.FY}</td><td>${o.FQ||""}</td><td>${o.total}</td>`;
    tbody.appendChild(row);
  });
})();
</script>
</body>

将以下内容保存到 package.json：

¥Save the following to package.json:

package.json
{
  "name": "sheetjs-nwjs",
  "author": "sheetjs",
  "version": "0.0.0",
  "main": "SheetJSNW.html",
  "dependencies": {
    "nw": "0.77.0",
    "xlsx": "https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz"
  }
}

安装依赖并运行：

¥Install dependencies and run:

npm i
npx nw .

该应用将在表格中显示数据。

¥The app will show the data in a table.

初始设置

在测试演示之前，请遵循 React Native 文档的环境设置。

¥Follow the Environment Setup of the React Native documentation before testing the demo.

对于 Android 测试，React Native 需要 Java 11。它不适用于当前的 Java 版本。

¥For Android testing, React Native requires Java 11. It will not work with current Java releases.

在 React Native 中，有多种方法来显示数据行。该演示使用原生 FlatList 组件。

¥In React Native, there are a number of ways to display rows of data. This demo uses the native FlatList component.

通过在终端中运行以下命令来创建一个新项目：

¥Create a new project by running the following commands in the Terminal:

npx react-native@0.72.4 init SheetJSSL --version="0.72.4"
cd SheetJSSL
npm i -S https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz

将以下内容保存到项目中的 App.tsx：

¥Save the following to App.tsx in the project:

App.tsx
import React, { useState } from 'react';
import { Alert, Button, SafeAreaView, Text, View, FlatList } from 'react-native';
import { utils, version, read } from 'xlsx';

const Item = ({FY, FQ, total}) => (
  <View style={{borderColor: "#000000", borderWidth: 1}}>
    <Text style={{fontSize: 12}}>{String(FY)} {String(FQ||"")} : ${String(total)} B</Text>
  </View>
);

const App = () => {
  const [rows, setRows] = React.useState([]);
  React.useEffect(() => { (async() =>{
    /* parse workbook */
    const url = "https://xlsx.nodejs.cn/PortfolioSummary.xls";
    const workbook = read(await (await fetch(url)).arrayBuffer());

    /* get first worksheet */
    const worksheet = workbook.Sheets[workbook.SheetNames[0]];
    const raw_data = utils.sheet_to_json(worksheet, {header:1});

    /* fill years */
    var last_year = 0;
    raw_data.forEach(r => last_year = r[0] = (r[0] != null ? r[0] : last_year));

    /* select data rows */
    const rows = raw_data.filter(r => r[0] >= 2007 && r[0] <= 2024 && r[2] > 0);

    /* generate row objects */
    const objects = rows.map(r => ({FY: r[0], FQ: r[1], total: r[8]}));

    /* display data */
    setRows(objects);
  })(); }, []);

  return ( <SafeAreaView>
    <View style={{ marginTop: 32, padding: 24 }}>
      <Text style={{ fontSize: 24, fontWeight: 'bold' }}>SheetJS {version} Import Demo</Text>
      <FlatList
        data={rows}
        renderItem={({item}) => <Item FQ={item.FQ} FY={item.FY} total={item.total} />}
        keyExtractor={item => String(item.FY) + (item.FQ||"")}
      />
    </View>
  </SafeAreaView> );
}

export default App;

Android
iOS

Android 演示已在 Windows 10 和 macOS 中进行了测试。

¥The Android demo has been tested in Windows 10 and in macOS.

在 Android 模拟器中测试应用：

¥Test the app in the Android simulator:

npx react-native start

一旦 Metro 准备就绪，它将显示命令：

¥Once Metro is ready, it will display the commands:

r - reload the app
d - open developer menu
i - run on iOS
a - run on Android

按 a 在 Android 上运行。

¥Press a to run on Android.

设备测试

该演示也可以在真实的 Android 设备上运行！启用 USB 调试 [^13] 后，Android 设备可以通过 USB 线连接到电脑。

¥The demo also runs on real Android devices! After enabling USB debugging[^13], the Android device can be connected to the computer with a USB cable.

此演示在 iOS 中运行，需要安装了 Xcode 的 Macintosh 计算机。

¥This demo runs in iOS and requires a Macintosh computer with Xcode installed.

在 iOS 模拟器中测试应用：

¥Test the app in the iOS simulator:

npm run ios

加载应用时，数据将按行显示。

¥When the app is loaded, the data will be displayed in rows.

[^1]: 多年来，数据集 URL 已更改多次。CC0 许可的数据集的当前位置可以通过在 data.gov 上搜索 "国家学生贷款数据系统" 找到。PortfolioSummary.xls 是数据集中的文件名。

¥The dataset URL has changed many times over the years. The current location for the CC0-licensed dataset can be found by searching for "National Student Loan Data System" on data.gov. PortfolioSummary.xls is the file name within the dataset.

[^2]: 见 read 于 "读取文件"

¥See read in "Reading Files"

[^3]: 见 "SheetJS 数据模型"

¥See "SheetJS Data Model"

[^4]: 见 "工作簿对象"

¥See "Workbook Object"

[^5]: 见 "工作簿对象"

¥See "Workbook Object"

[^6]: 见 "Sheet 对象"

¥See "Sheet Objects"

[^7]: 见 sheet_to_html 于 "实用工具"

¥See sheet_to_html in "Utilities"