让数据在滑动数据网格上滑动

滑动数据网格 是一个为 ReactJS Web 框架设计的高性能数据网格。

¥Glide Data Grid is a high-performance data grid designed for the ReactJS web framework.

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

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

该演示使用 Glide Data Grid 和 SheetJS 从电子表格中提取数据并在数据网格中显示内容。我们将探讨如何将数据从文件导入到数据网格以及如何将修改后的数据从网格导出到工作簿。

¥This demo uses Glide Data Grid and SheetJS to pull data from a spreadsheet and display the content in a data grid. We'll explore how to import data from files into the data grid and how to export modified data from the grid to workbooks.

"演示" 部分包括一个完整的示例，该示例显示用户提供的工作表中的数据并将数据导出到 XLSX 工作簿：

¥The "Demo" section includes a complete example that displays data from user-supplied sheets and exports data to XLSX workbooks:

测试部署

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

¥This demo was tested in the following environments:

浏览器	版本	日期
Chromium 125	5.3.2	2024-06-09

集成详情

¥Integration Details

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

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

使用 npm 工具，此命令安装 SheetJS 和 Glide Data Grid：

¥Using the npm tool, this command installs SheetJS and Glide Data Grid:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz @glideapps/glide-data-grid@5.3.2

两个库中的方法和组件都可以使用 import 加载到页面中：

¥Methods and components in both libraries can be loaded in pages using import:

import { read, utils, writeFile } from 'xlsx';
import { DataEditor, GridCellKind, GridCell, Item } from '@glideapps/glide-data-grid';

Glide 数据网格主要是基于事件的。它不直接管理状态。相反，开发者应该在用户编辑单元格时管理更新。

¥Glide Data Grid is primarily event-based. It does not manage state directly. Instead, developers are expected to manage updates when users edit cells.

后备存储

¥Backing Store

在底层，DataEditor 组件旨在调用方法并请求数据在网格中显示。通常将数据存储在组件状态之外。getCellContent 回调将从外部后备存储中提取数据，而 SheetJS 操作将直接作用于存储。

¥Under the hood, the DataEditor component is designed to call methods and request data to display in the grid. It is typical to store data outside of component state. A getCellContent callback will pull data from the external backing store, while SheetJS operations will directly act on the store.

对于此演示，数据存储有两个部分：

¥For this demo, there are two parts to the data store:

• data 是一个 "对象数组"，将保存原始数据 [^1]。

  ¥data is an "Array of Objects" that will hold the raw data[^1].

• header 是标头名称的数组

  ¥header is an array of the header names

遵循 Glide 数据网格约定 [^2]，这两个对象都在组件脚本的顶层定义。它们是在组件外部声明的！

¥Following the Glide Data Grid conventions[^2], both objects are defined at the top level of the component script. They are declared outside of the component!

// !! THESE ARRAYS ARE DEFINED OUTSIDE OF THE COMPONENT FUNCTION !!

// this will store the raw data objects
let data: any[] = [];
// this will store the header names
let header: string[] = [];

属性

¥Props

这是一个高层次的概述。应查阅官方文档。[^3]

¥This is a high-level overview. The official documentation should be consulted.[^3]

列

¥Columns

DataEditor 期望列元数据通过 columns 属性传递。这应该在组件状态中进行管理：

¥DataEditor expects column metadata to be passed through a columns prop. This should be managed in the component state:

import { useState } from 'react';
import { DataEditor, GridColumn } from '@glideapps/glide-data-grid';

function App() {
  const [cols, setCols] = useState<GridColumn[]>([]); // gdg column objects
  // ...
  return ( <>
    // ...
    <DataEditor
      // ... props
      columns={cols}
    />
    // ...
  </> );
}
export default App;

每个 GridColumn 对象都需要一个代表显示名称的 title 和一个代表数据对象内索引键的 id。

¥Each GridColumn object expects a title representing the display name and an id representing the key to index within the data object.

数据

¥Data

DataEditor 组件期望 getCellContent 回调来提供数据。回调接受列和行索引。应该使用列索引来查找标题键：

¥The DataEditor component expects a getCellContent callback for supplying data. The callback accepts column and row indices. The column index should be used to find the header key:

import { useCallback } from 'react';
import { DataEditor, GridCellKind, GridCell, Item } from '@glideapps/glide-data-grid';

// ...

function App() {
  // ...
  // backing data store -> gdg
  const getContent = useCallback((cell: Item): GridCell => {
    const [col, row] = cell;
    return {
      kind: GridCellKind.Text,
      // header[col] is the name of the field
      displayData: String(data[row]?.[header[col]]??""),
      data: data[row]?.[header[col]],
    };
  }, []);
  // ...
  return ( <>
    // ...
    <DataEditor
      // ... props
      getCellContent={getContent}
    />
    // ...
  </> );
}

行数

¥Row Count

DataEditor 还接受指示行数的 rows 属性。这最好在状态下管理：

¥DataEditor also accepts a rows property indicating the number of rows. This is best managed in state:

import { useState } from 'react';
import { DataEditor } from '@glideapps/glide-data-grid';

function App() {
  const [rows, setRows] = useState<number>(0); // number of rows
  // ...
  return ( <>
    // ...
    <DataEditor
      // ... props
      rows={rows}
    />
    // ...
  </> );
}
export default App;

编辑数据

¥Editing Data

该演示使用 onCellEdited 回调写回数据存储。

¥The demo uses the onCellEdited callback to write back to the data store.

解析数据

¥Parsing Data

SheetJS 到数据存储

¥SheetJS to Data Store

SheetJS read 方法解析来自多个来源的数据 [^4]。它返回一个工作簿对象，其中包含工作表对象和其他数据 [^5]。

¥The SheetJS read method parses data from a number of sources[^4]. It returns a workbook object which holds worksheet objects and other data[^5].

原始数据对象可以使用 SheetJS sheet_to_json 函数 [^6] 生成。

¥Raw data objects can be generated with the SheetJS sheet_to_json function[^6].

可以从工作表的第一行拉出标题。sheet_to_json 方法接受 range 选项，其他 SheetJS API 函数可用于计算标题名称 [^7] 的正确范围。

¥The headers can be pulled from the first row of the sheet. The sheet_to_json method accepts a range option, and other SheetJS API functions can be used to calculate the correct range for the header names[^7].

此示例从工作簿中的第一个工作表生成行对象：

¥This example generates row objects from the first sheet in the workbook:

import { utils, WorkBook } from 'xlsx';

// ...

const update_backing_store = (wb: WorkBook) => {
  // get first worksheet
  const sheet = wb.Sheets[wb.SheetNames[0]];

  // set data
  data = utils.sheet_to_json<any>(sheet);

  // create a range consisting of the first row
  const range = utils.decode_range(sheet["!ref"]??"A1"); // original range
  range.e.r = range.s.r; // set ending row to starting row (select first row)

  // pull headers
  header = utils.sheet_to_json<string[]>(sheet, {header: 1, range})[0];
};

// ...

从数据存储导入

¥Importing from Data Store

安排 DataEditor 的刷新涉及通过标准状态更新网格列元数据和行计数。它还需要一个特殊的 updateCells 调用来指示网格将缓存数据标记为过时：

¥Scheduling a refresh for the DataEditor involves updating the grid column metadata and row count through the standard state. It also requires a special updateCells call to instruct the grid to mark the cached data as stale:

import { useRef } from 'react'
import { WorkBook } from 'xlsx'
import { DataEditor, GridColumn, Item, DataEditorRef } from '@glideapps/glide-data-grid'

function App() {
  const ref = useRef<DataEditorRef>(null); // gdg ref
  // ...
  const parse_wb = (wb: WorkBook) => {
    update_backing_store(wb);

    // update column metadata by pulling from external header keys
    setCols(header.map(h => ({title: h, id: h} as GridColumn)));

    // update number of rows
    setRows(data.length);

    if(data.length > 0) {
      // create an array of the cells that must be updated
      let cells = data.map(
        (_,R) => Array.from({length:header.length}, (_,C) => ({cell: ([C,R] as Item)}))
      ).flat();
      // initiate update using the `ref` attached to the DataEditor
      ref.current?.updateCells(cells)
    }
  };
  // ...
  return ( <>
    // ...
    <DataEditor
      // ... props
      ref={ref}
    />
    // ...
  </> );
}
export default App;

写入数据

¥Writing Data

SheetJS json_to_sheet 方法直接从 data 数组 [^8] 生成工作表对象：

¥The SheetJS json_to_sheet method generates worksheet objects directly from the data array[^8]:

const ws = utils.json_to_sheet(data); // easy :)

通过创建 SheetJS 工作簿对象 [^9] 并使用 writeFile 或 writeFileXLSX[^10] 写入，可以将工作表导出到 XLSX：

¥The worksheet can be exported to XLSX by creating a SheetJS workbook object[^9] and writing with writeFile or writeFileXLSX[^10]:

const wb = utils.book_new();
utils.book_append_sheet(wb, ws, "Sheet1");
writeFileXLSX(wb, "sheetjs-gdg.xlsx");

由于编辑器可以更改标题标题，因此强烈建议从状态中提取列数据并重写标题行：

¥Since the editor can change the header titles, it is strongly recommended to pull column data from the state and rewrite the header row:

import { utils, writeFileXLSX } from 'xlsx';

function App() {
  // ...
  const exportXLSX = useCallback(() => {
    // generate worksheet using data with the order specified in the columns array
    const ws = utils.json_to_sheet(data, {header: cols.map(c => c.id ?? c.title)});

    // rewrite header row with titles
    utils.sheet_add_aoa(ws, [cols.map(c => c.title ?? c.id)], {origin: "A1"});

    // create workbook
    const wb = utils.book_new();
    utils.book_append_sheet(wb, ws, "Export"); // replace with sheet name
    // download file
    writeFileXLSX(wb, "sheetjs-gdg.xlsx");
  }, []);
  // ...
  return ( <>
    // ...
    <button onClick={exportXLSX}><b>Export XLSX!</b></button>
    // ...
  </> );
}
export default App;

演示

¥Demo

1. 从 react-ts 模板创建一个新项目：

   ¥Create a new project from the react-ts template:

npm create vite@latest -- sheetjs-gdg --template react-ts
cd sheetjs-gdg
npm i

2. 安装 SheetJS 和 Glide 数据网格库：

   ¥Install SheetJS and Glide Data Grid libraries:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz @glideapps/glide-data-grid@5.3.2

3. 启动开发服务器：

   ¥Start dev server:

npm run dev

终端窗口将显示一个 URL（通常为 http://localhost:5173）。使用 Web 浏览器打开 URL 并确认页面加载。

¥The terminal window will display a URL (typically http://localhost:5173). Open the URL with a web browser and confirm that a page loads.

4. 下载 App.tsx 并替换 src/App.tsx：

   ¥Download App.tsx and replace src/App.tsx:

curl -L -o src/App.tsx https://xlsx.nodejs.cn/gdg/App.tsx

测试

¥Testing

5. 刷新浏览器窗口。应显示网格：

   ¥Refresh the browser window. A grid should be displayed:

该演示下载并处理 https://xlsx.nodejs.cn/pres.numbers 。

¥The demo downloads and processes https://xlsx.nodejs.cn/pres.numbers .

6. 对网格数据进行一些更改。

   ¥Make some changes to the grid data.

一些统计学家认为格罗弗·克利夫兰总统应该被计算一次。这意味着克林顿总统的指数应为 41，而其他总统的指数应递减。

¥Some statisticians believe President Grover Cleveland should be counted once. That would imply President Clinton should be index 41 and the indices of the other presidents should be decremented.

双击“索引”列中的每个单元格并递减每个值。新值应为 41、42、43、44 和 45，如下面的屏幕截图所示：

¥Double-click on each cell in the Index column and decrement each value. The new values should be 41, 42, 43, 44, and 45, as shown in the screenshot below:

7. 单击 "导出" 按钮。浏览器应尝试下载 XLSX 文件 (sheetjs-gdg.xlsx)。保存文件。

   ¥Click on the "Export" button. The browser should attempt to download a XLSX file (sheetjs-gdg.xlsx). Save the file.

打开生成的文件并验证内容与网格匹配。

¥Open the generated file and verify the contents match the grid.

8. 重新加载页面。内容将恢复到原始表。

   ¥Reload the page. The contents will revert back to the original table.

9. 单击 "选择文件" 并选择新的 sheetjs-gdg.xlsx 文件。该表应使用文件中的数据进行更新。

   ¥Click "Choose File" and select the new sheetjs-gdg.xlsx file. The table should update with the data in the file.

[^1]: 见 ReactJS 演示中的 "对象数组"

¥See "Array of Objects" in the ReactJS demo

[^2]: Glide 数据网格故事书中的 "入门" 页 的 "故事" 部分存储组件外部的对象数组。

¥The "Story" section of the "Getting Started" page in the Glide Data Grid Storybook stores an Array of Objects outside of the component.

[^3]: 请参阅 Glide 数据网格 API 文档中的 DataEditorProps 中的 "数据" 部分。

¥See the "Data" section in DataEditorProps in the Glide Data Grid API documentation.

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

¥See read in "Reading Files"

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

¥See "SheetJS Data Model"

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

¥See sheet_to_json in "Utilities"

[^7]: "地址和范围" 涵盖一般概念和实用功能，包括 decode_range。

¥"Addresses and Ranges" covers general concepts and utility functions including decode_range.

[^8]: 见 "对象数组输入" 于 "实用工具"

¥See "Array of Objects Input" in "Utilities"

[^9]: 有关 book_new 和 book_append_sheet 的详细信息，请参阅 "工作簿助手" 于 "实用工具"。

¥See "Workbook Helpers" in "Utilities" for details on book_new and book_append_sheet.

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

¥See writeFile in "Writing Files"