Skip to main content

ReactJS 站点中的工作表

ReactJS 是一个用于构建用户界面的 JavaScript 库。

¥ReactJS is a JavaScript library for building user interfaces.

SheetJS 是一个用于从电子表格读取和写入数据的 JavaScript 库。

¥SheetJS is a JavaScript library for reading and writing data from spreadsheets.

该演示使用 ReactJS 和 SheetJS 来处理和生成电子表格。我们将探索如何在 ReactJS 站点中加载 SheetJS 并比较常见的状态模型和数据流策略。

¥This demo uses ReactJS and SheetJS to process and generate spreadsheets. We'll explore how to load SheetJS in a ReactJS site and compare common state models and data flow strategies.

该演示重点介绍 ReactJS 概念。其他演示涵盖一般部署：

¥This demo focuses on ReactJS concepts. Other demos cover general deployments:

安装​

¥Installation

"构架" 章节 涵盖了 Yarn 和其他包管理器的安装。

¥The "Frameworks" section covers installation with Yarn and other package managers.

该库可以直接从 JS 或 JSX 代码导入：

¥The library can be imported directly from JS or JSX code with:

import { read, utils, writeFile } from 'xlsx';

内部状态​

¥Internal State

各种 SheetJS API 可处理各种数据形状。首选状态取决于应用。

¥The various SheetJS APIs work with various data shapes. The preferred state depends on the application.

对象数组​

¥Array of Objects

通常，一些用户会创建一个电子表格，其中包含应加载到网站中的源数据。该工作表将具有已知的列。

¥Typically, some users will create a spreadsheet with source data that should be loaded into the site. This sheet will have known columns.

状态​

¥State

示例 总统表 有一个标题行，其中包含 "名称" 和 "索引" 列。自然的 JS 表示是每行一个对象，使用第一行中的值作为键：

¥The example presidents sheet has one header row with "Name" and "Index" columns. The natural JS representation is an object for each row, using the values in the first rows as keys:

SpreadsheetState

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

ReactJS useState[^1] 钩子可以配置状态：

¥The ReactJS useState[^1] hook can configure the state:

import { useState } from 'react';

/* the component state is an array of objects */
const [pres, setPres] = useState<any[]>([]);

当提前知道电子表格标题行时，可以进行行输入：

¥When the spreadsheet header row is known ahead of time, row typing is possible:

import { useState } from 'react';

interface President {
  Name: string;
  Index: number;
}

/* the component state is an array of presidents */
const [pres, setPres] = useState<President[]>([]);

这些类型信息丰富。它们不强制工作表包含命名列。应使用运行时数据验证库来验证数据集。

¥The types are informative. They do not enforce that worksheets include the named columns. A runtime data validation library should be used to verify the dataset.

当事先不知道文件头时，应使用 any。

¥When the file header is not known in advance, any should be used.

更新状态​

¥Updating State

SheetJS read 和 sheet_to_json 函数简化了状态更新。它们最好用在 useEffect[^2] 和 useCallback[^3] 钩子的函数体中。

¥The SheetJS read and sheet_to_json functions simplify state updates. They are best used in the function bodies of useEffect[^2] and useCallback[^3] hooks.

当用户加载站点时，useEffect 钩子可以下载并更新状态：

¥A useEffect hook can download and update state when a person loads the site:

import { useEffect } from 'react';
import { read, utils } from 'xlsx';

/* Fetch and update the state once */
useEffect(() => { (async() => {
  /* Download from https://xlsx.nodejs.cn/pres.numbers */
  const f = await fetch("https://xlsx.nodejs.cn/pres.numbers");
  const ab = await f.arrayBuffer();

  /* parse */
  const wb = read(ab);

  /* generate array of presidents from the first worksheet */
  const ws = wb.Sheets[wb.SheetNames[0]]; // get the first worksheet
  const data: President[] = utils.sheet_to_json<President>(ws); // generate objects

  /* update state */
  setPres(data); // update state
})(); }, []);

渲染数据​

¥Rendering Data

组件通常从对象数组中渲染 HTML 表格。<tr> 表行元素通常是通过映射状态数组生成的，如示例 JSX 代码所示：

¥Components typically render HTML tables from arrays of objects. The <tr> table row elements are typically generated by mapping over the state array, as shown in the example JSX code:

Example JSX for displaying arrays of objects
<table>
  {/* The `thead` section includes the table header row */}
  <thead><tr><th>Name</th><th>Index</th></tr></thead>
  {/* The `tbody` section includes the data rows */}
  <tbody>
    {/* generate row (TR) for each president */}
    {pres.map(row => (
      <tr>
        {/* Generate cell (TD) for name / index */}
        <td>{row.Name}</td>
        <td>{row.Index}</td>
      </tr>
    ))}
  </tbody>
</table>

导出数据​

¥Exporting Data

writeFile 和 json_to_sheet 功能简化了数据导出。它们最适合用于连接到按钮或其他元素的 useCallback[^4] 钩子的功能体中。

¥The writeFile and json_to_sheet functions simplify exporting data. They are best used in the function bodies of useCallback[^4] hooks attached to button or other elements.

当用户单击按钮时，回调可以生成本地文件：

¥A callback can generate a local file when a user clicks a button:

import { useCallback } from 'react';
import { utils, writeFile } from 'xlsx';

/* get state data and export to XLSX */
const exportFile = useCallback(() => {
  /* generate worksheet from state */
  const ws = utils.json_to_sheet(pres);
  /* create workbook and append worksheet */
  const wb = utils.book_new();
  utils.book_append_sheet(wb, ws, "Data");
  /* export to XLSX */
  writeFile(wb, "SheetJSReactAoO.xlsx");
}, [pres]);

完整组件​

¥Complete Component

这个完整的组件示例获取一个测试文件并在 HTML 表中显示内容。单击导出按钮时，回调将导出文件：

¥This complete component example fetches a test file and displays the contents in a HTML table. When the export button is clicked, a callback will export a file:

src/SheetJSReactAoO.js
import React, { useCallback, useEffect, useState } from "react";
import { read, utils, writeFileXLSX } from 'xlsx';

export default function SheetJSReactAoO() {
  /* the component state is an array of presidents */
  const [pres, setPres] = useState([]);

  /* Fetch and update the state once */
  useEffect(() => { (async() => {
    const f = await (await fetch("https://xlsx.nodejs.cn/pres.xlsx")).arrayBuffer();
    const wb = read(f); // parse the array buffer
    const ws = wb.Sheets[wb.SheetNames[0]]; // get the first worksheet
    const data = utils.sheet_to_json(ws); // generate objects
    setPres(data); // update state
  })(); }, []);

  /* get state data and export to XLSX */
  const exportFile = useCallback(() => {
    const ws = utils.json_to_sheet(pres);
    const wb = utils.book_new();
    utils.book_append_sheet(wb, ws, "Data");
    writeFileXLSX(wb, "SheetJSReactAoO.xlsx");
  }, [pres]);

  return (<table><thead><tr><th>Name</th><th>Index</th></tr></thead><tbody>
    { /* generate row for each president */
      pres.map(pres => (<tr>
        <td>{pres.Name}</td>
        <td>{pres.Index}</td>
      </tr>))
    }
  </tbody><tfoot><td colSpan={2}>
    <button onClick={exportFile}>Export XLSX</button>
  </td></tfoot></table>);
}
How to run the example (click to hide)
测试部署

本 demo 在以下环境下进行了测试：

¥This demo was tested in the following environments:

ReactJSViteJS日期
18.2.05.1.62024-03-13

  1. 创建一个新站点：

    ¥Create a new site:

npm create vite@latest sheetjs-react -- --template react

  1. 安装 SheetJS 依赖并启动开发服务器：

    ¥Install the SheetJS dependency and start the dev server:

cd sheetjs-react
npm i
npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz
npm run dev

  1. 打开网络浏览器并访问显示的 URL (http://localhost:5173)

    ¥Open a web browser and access the displayed URL (http://localhost:5173)

  2. 将 src/App.jsx 替换为 src/SheetJSReactAoO.js 示例。

    ¥Replace src/App.jsx with the src/SheetJSReactAoO.js example.

该页面将刷新并显示带有“导出”按钮的表格。单击该按钮，页面将尝试下载 SheetJSReactAoO.xlsx。

¥The page will refresh and show a table with an Export button. Click the button and the page will attempt to download SheetJSReactAoO.xlsx.

  1. 建立网站：

    ¥Build the site:

npm run build

生成的站点将放置在 dist 文件夹中。

¥The generated site will be placed in the dist folder.

  1. 启动本地网络服务器：

    ¥Start a local web server:

npx http-server dist

使用 Web 浏览器访问显示的 URL（通常为 http://localhost:8080）并测试该页面。

¥Access the displayed URL (typically http://localhost:8080) with a web browser and test the page.

页面加载时，应用将获取 https://xlsx.nodejs.cn/pres.xlsx 并在表中显示第一个工作表中的数据。"导出 XLSX" 按钮将生成一个可以在电子表格编辑器中打开的工作簿。

¥When the page loads, the app will fetch https://xlsx.nodejs.cn/pres.xlsx and display the data from the first worksheet in a TABLE. The "Export XLSX" button will generate a workbook that can be opened in a spreadsheet editor.

HTML​

对象数组方法的主要缺点是列的特定性质。对于更一般的用途，传递数组的数组是可行的。但是，这不能很好地处理合并单元格！

¥The main disadvantage of the Array of Objects approach is the specific nature of the columns. For more general use, passing around an Array of Arrays works. However, this does not handle merge cells[^5] well!

sheet_to_html 函数生成可识别合并和其他工作表功能的 HTML。ReactJS dangerouslySetInnerHTML[^6] 属性允许代码设置 innerHTML 属性，从而有效地将代码插入到页面中。

¥The sheet_to_html function generates HTML that is aware of merges and other worksheet features. ReactJS dangerouslySetInnerHTML[^6] prop allows code to set the innerHTML attribute, effectively inserting the code into the page.

在此示例中，组件将 ref 附加到 DIV 容器。导出时，可以使用 table_to_book 解析第一个 TABLE 子元素以生成工作簿对象。

¥In this example, the component attaches a ref to the DIV container. During export, the first TABLE child element can be parsed with table_to_book to generate a workbook object.

src/SheetJSReactHTML.js
import React, { useCallback, useEffect, useRef, useState } from "react";
import { read, utils, writeFileXLSX } from 'xlsx';

export default function SheetJSReactHTML() {
  /* the component state is an HTML string */
  const [__html, setHtml] = useState("");
  /* the ref is used in export */
  const tbl = useRef(null);

  /* Fetch and update the state once */
  useEffect(() => { (async() => {
    const f = await (await fetch("https://xlsx.nodejs.cn/pres.xlsx")).arrayBuffer();
    const wb = read(f); // parse the array buffer
    const ws = wb.Sheets[wb.SheetNames[0]]; // get the first worksheet
    const data = utils.sheet_to_html(ws); // generate HTML
    setHtml(data); // update state
  })(); }, []);

  /* get live table and export to XLSX */
  const exportFile = useCallback(() => {
    const elt = tbl.current.getElementsByTagName("TABLE")[0];
    const wb = utils.table_to_book(elt);
    writeFileXLSX(wb, "SheetJSReactHTML.xlsx");
  }, [tbl]);

  return ( <>
    <button onClick={exportFile}>Export XLSX</button>
    <div ref={tbl} dangerouslySetInnerHTML={{ __html }} />
  </> );
}
How to run the example (click to hide)
测试部署

本 demo 在以下环境下进行了测试：

¥This demo was tested in the following environments:

ReactJSViteJS日期
18.2.05.1.62024-03-13

  1. 创建一个新站点：

    ¥Create a new site:

npm create vite@latest sheetjs-react -- --template react

  1. 安装 SheetJS 依赖并启动开发服务器：

    ¥Install the SheetJS dependency and start the dev server:

cd sheetjs-react
npm i
npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz
npm run dev

  1. 打开网络浏览器并访问显示的 URL (http://localhost:5173)

    ¥Open a web browser and access the displayed URL (http://localhost:5173)

  2. 将 src/App.jsx 替换为 src/SheetJSReactHTML.js 示例。

    ¥Replace src/App.jsx with the src/SheetJSReactHTML.js example.

该页面将刷新并显示带有“导出”按钮的表格。单击该按钮，页面将尝试下载 SheetJSReactHTML.xlsx。

¥The page will refresh and show a table with an Export button. Click the button and the page will attempt to download SheetJSReactHTML.xlsx.

  1. 建立网站：

    ¥Build the site:

npm run build

生成的站点将放置在 dist 文件夹中。

¥The generated site will be placed in the dist folder.

  1. 启动本地网络服务器：

    ¥Start a local web server:

npx http-server dist

使用 Web 浏览器访问显示的 URL（通常为 http://localhost:8080）并测试该页面。

¥Access the displayed URL (typically http://localhost:8080) with a web browser and test the page.

页面加载时，应用将获取 https://xlsx.nodejs.cn/pres.xlsx 并在表中显示第一个工作表中的数据。"导出 XLSX" 按钮将生成一个可以在电子表格编辑器中打开的工作簿。

¥When the page loads, the app will fetch https://xlsx.nodejs.cn/pres.xlsx and display the data from the first worksheet in a TABLE. The "Export XLSX" button will generate a workbook that can be opened in a spreadsheet editor.

行和列​

¥Rows and Columns

一些数据网格和 UI 组件将工作表状态分为两部分：列属性对象数组和行对象数组。前者用于生成列标题和对行对象进行索引。

¥Some data grids and UI components split worksheet state in two parts: an array of column attribute objects and an array of row objects. The former is used to generate column headings and for indexing into the row objects.

最安全的方法是使用数组的数组来表示状态并生成映射到 A1 样式列标题的列对象。

¥The safest approach is to use an array of arrays for state and to generate column objects that map to A1-Style column headers.

React 数据网格演示 使用这种方法并具有以下列和行结构：

¥The React Data Grid demo uses this approach with the following column and row structure:

/* rows are generated with a simple array of arrays */
const rows = utils.sheet_to_json(worksheet, { header: 1 });

/* column objects are generated based on the worksheet range */
const range = utils.decode_range(ws["!ref"]||"A1");
const columns = Array.from({ length: range.e.c + 1 }, (_, i) => ({
  /* for an array of arrays, the keys are "0", "1", "2", ... */
  key: String(i),
  /* column labels: encode_col translates 0 -> "A", 1 -> "B", 2 -> "C", ... */
  name: XLSX.utils.encode_col(i)
}));

Column labels for headers

旧版部署​

¥Legacy Deployments

独立脚本 可以很好地适应不使用打包器的旧部署。

¥The Standalone Scripts play nice with legacy deployments that do not use a bundler.

旧版演示 展示了一个使用 Babel 独立库在浏览器中转译的简单 ReactJS 组件。

¥The legacy demo shows a simple ReactJS component transpiled in the browser using Babel standalone library.

[^1]: 请参阅 ReactJS 文档中的 useState。

¥See useState in the ReactJS documentation.

[^2]: 请参阅 ReactJS 文档中的 useEffect。

¥See useEffect in the ReactJS documentation.

[^3]: 请参阅 ReactJS 文档中的 useCallback。

¥See useCallback in the ReactJS documentation.

[^4]: 请参阅 ReactJS 文档中的 useCallback。

¥See useCallback in the ReactJS documentation.

[^5]: 详细信息请参见 "合并单元格" 于 "SheetJS 数据模型"。

¥See "Merged Cells" in "SheetJS Data Model" for more details.

[^6]: dangerouslySetInnerHTML 是所有内置组件都支持的 ReactJS prop。

¥dangerouslySetInnerHTML is a ReactJS prop supported for all built-in components.