Material 用户界面

Material UI 是遵循 Google Material Design 系统 的 ReactJS 组件集合

¥Material UI is a collection of ReactJS Components that follows the Google Material Design system

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

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

此演示使用 Material UI 和 SheetJS 从电子表格中提取数据并显示数据。我们将探讨如何从电子表格导入数据并将数据导出到电子表格。将测试以下 Material UI 组件：

¥This demo uses Material UI and SheetJS to pull data from a spreadsheet and display the data. We'll explore how to import data from spreadsheets and export data to spreadsheets. The following Material UI components will be tested:

• "表" 基于核心 HTML TABLE 元素。

  ¥"Table" is based on the core HTML TABLE element.

• "数据网格" 是一个用于更大数据集的数据网格。

  ¥"Data Grid" is a data grid for larger datasets.

ReactJS 演示 涵盖了基本的 ReactJS 概念。在阅读此演示之前，应该先仔细阅读。

¥The ReactJS demo covers basic ReactJS concepts. It should be perused before reading this demo.

集成详情

¥Integration Details

"构架" 章节 涵盖了使用 Material UI 在项目中的安装。

¥The "Frameworks" section covers installation in projects using Material UI.

在 ReactJS 项目中安装 SheetJS 模块后，import 语句可以加载库的相关部分。

¥After installing the SheetJS module in a ReactJS project, import statements can load relevant parts of the library.

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

Material UI 表

¥Material UI Table

Table 组件在 HTML 中抽象 <table> 元素。

¥The Table component abstracts the <table> element in HTML.

导入数据

¥Importing Data

从 SheetJS 工作表对象 [^1] 开始，sheet_to_json 方法 [^2] 生成行对象数组。

¥Starting from a SheetJS worksheet object[^1], the sheet_to_json method[^2] generates an array of row objects.

在 ReactJS "对象数组" 演示 中，对象数组通过手动映射数据来呈现。例如，从以下电子表格和数据开始：

¥In the ReactJS "Array of Objects" demo, the array of objects is rendered by manually mapping over data. For example, starting from the following spreadsheet and data:

Spreadsheet	State
	[ { 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 } ]

HTML 表元素映射到 MUI 组件：

¥The HTML table elements map to MUI components:

HTML	MUI
TABLE	Table
THEAD	TableHead
TBODY	TableBody
TR	TableRow
TD	TableCell

该库需要 TableContainer 容器组件。

¥The library requires a TableContainer container component.

以下示例 JSX 使用 HTML 和 MUI 组件显示表格：

¥The following example JSX shows a table using HTML and using MUI components:

ReactJS

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>

Material UI

Example JSX for displaying arrays of objects

<TableContainer><Table>
  {/* The `TableHead` section includes the table header row */}
  <TableHead><TableRow><TableCell>Name</TableCell><TableCell>Index</TableCell></TableRow></TableHead>
  {/* The `TableBody` section includes the data rows */}
  <TableBody>
    {/* generate row (TableRow) for each president */}
    {pres.map((row, idx) => (
      <TableRow key={idx}>
        {/* Generate cell (TableCell) for name / index */}
        <TableCell>{row.Name}</TableCell>
        <TableCell>{row.Index}</TableCell>
      </TableRow>
    ))}
  </TableBody>
</Table></TableContainer>

导出数据

¥Exporting Data

SheetJS table_to_book 方法 [^3] 可以从 DOM 元素解析数据。MUI Table 元素实际上是一个 HTML TABLE 元素。附加到 Table 元素的 ref 可以由 table_to_book 处理。

¥The SheetJS table_to_book method[^3] can parse data from a DOM element. The MUI Table element is really a HTML TABLE element under the hood. A ref attached to the Table element can be processed by table_to_book.

以下代码片段使用 writeFileXLSX 方法 [^4] 生成并下载 XLSX 工作簿：

¥The following snippet uses the writeFileXLSX method[^4] to generate and download a XLSX workbook:

Skeleton Component for exporting a Material UI Table

import { utils, writeFileXLSX } from "xlsx";
import { useRef } from "react";

export default function MUITableSheetJSExport() {
  /* This ref will be attached to the <Table> component */
  const tbl = useRef<HTMLTableElement>(null);

  const xport = () => {
    /* the .current field will be a TABLE element */
    const table_elt = tbl.current;
    /* generate SheetJS workbook */
    const wb = utils.table_to_book(table_elt);
    /* export to XLSX */
    writeFileXLSX(wb, "SheetJSMaterialUI.xlsx");
  };

  return ( <>
    <button onClick={xport}>Export</button>
    <TableContainer>
      <Table ref={tbl}>{/* ... */}</Table>
    </TableContainer>
  <>);
}

MUI 表格演示

¥MUI Table Demo

测试部署

该演示在以下部署中进行了测试：

¥This demo was tested in the following deployments:

Material 用户界面	情感	日期
5.15.20	11.11.4	2024-06-12

1. 使用 vite 创建一个新应用：

   ¥Create a new app using vite:

npm create vite@latest sheetjs-mui -- --template react-ts
cd sheetjs-mui

2. 安装依赖：

   ¥Install dependencies:

npm i -S https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz @mui/material@5.15.20 @emotion/react@11.11.4 @emotion/styled@11.11.5

3. 下载 App.tsx 并替换 src/App.tsx。

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

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

4. 启动开发服务器：

   ¥Start the development server:

npm run dev

该脚本应在 Web 浏览器中打开实时演示。单击 "导出" 按钮保存文件。在电子表格编辑器中打开生成的文件。

¥The script should open the live demo in a web browser. Click the "Export" button to save the file. Open the generated file in a spreadsheet editor.

Material UI 数据网格

¥Material UI Data Grid

下面包含一个完整的示例。

¥A complete example is included below.

行和列状态

¥Rows and Columns State

MUI 数据网格状态由列元数据数组和行对象数组组成。通常两者都在状态中定义：

¥MUI Data Grid state consists of an Array of column metadata and an Array of row objects. Typically both are defined in state:

import { DataGrid, GridColDef } from "@mui/x-data-grid";

export default function App() {
  const [rows, setRows] = useState([]);
  const [columns, setColumns] = useState([]);

  return ( <DataGrid columns={columns} rows={rows} /> );
}

最通用的数据表示是数组的数组。为了满足网格，列必须是其 field 属性是转换为字符串的索引的对象：

¥The most generic data representation is an array of arrays. To sate the grid, columns must be objects whose field property is the index converted to string:

import { WorkSheet, utils } from 'xlsx';
import { GridColDef } from "@mui/x-data-grid";

type Row = any[];
type RowCol = { rows: Row[]; columns: GridColDef[]; };

function ws_to_muidg(ws: WorkSheet): RowCol {
  /* create an array of arrays */
  const rows = utils.sheet_to_json(ws, { header: 1 });

  /* create column array */
  const range = utils.decode_range(ws["!ref"]||"A1");
  const columns = Array.from({ length: range.e.c + 1 }, (_, i) => ({
    field: String(i), // MUIDG will access row["0"], row["1"], etc
    headerName: utils.encode_col(i), // the column labels will be A, B, etc
    editable: true // enable cell editing
  }));

  return { rows, columns }; // these can be fed to setRows / setColumns
}

另一方面，可以使用 aoa_to_sheet 生成工作表：

¥In the other direction, a worksheet can be generated with aoa_to_sheet:

x-data-grid 未正确保留行数组对象，因此必须重新创建行数组。该代码片段定义了 arrayify 函数。

¥x-data-grid does not properly preserve row array objects, so the row arrays must be re-created. The snippet defines a arrayify function.

import { WorkSheet, utils } from 'xlsx';

type Row = any[];

function arrayify(rows: any[]): Row[] {
  return rows.map(row => {
    var length = Object.keys(row).length;
    for(; length > 0; --length) if(row[length-1] != null) break;
    return Array.from({length, ...row});
  });
}

function muidg_to_ws(rows: Row[]): WorkSheet {
  return utils.aoa_to_sheet(arrayify(rows));
}

编辑单元格

¥Editing Cells

processRowUpdate 回调 prop 接收新的行数据。事件处理程序可以改变状态：

¥The processRowUpdate callback prop receives the new row data. An event handler can mutate state:

import { GridRowModel } from "@mui/x-data-grid";

export default function App() {
  // ...

  const processRowUpdate = useCallback((rowNew: GridRowModel, rowOld: GridRowModel) => {
    /* scan each column and manually set state entries */
    for(var j = 0; j < columns.length; ++j) if(rowNew[j] != null) {
      rows[rowNew.id][j] = isNaN(+rowNew[j]) ? rowNew[j] : +rowNew[j];
    }
    /* force a state update */
    setRows(rows);
    /* commit the new row */
    return rowNew;
  }, [columns, rows]);

  return ( <DataGrid columns={columns} rows={rows} processRowUpdate={processRowUpdate} /> );
}

MUIDG 演示

¥MUIDG Demo

测试部署

该演示在以下部署中进行了测试：

¥This demo was tested in the following deployments:

数据网格	情感	日期
7.6.2	11.11.4	2024-06-12

1. 使用 vite 创建一个新应用：

   ¥Create a new app using vite:

npm create vite@latest sheetjs-muidg -- --template react-ts
cd sheetjs-muidg

2. 安装依赖：

   ¥Install dependencies:

npm i -S https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz @mui/x-data-grid@7.6.2 @emotion/react@11.11.4 @emotion/styled@11.11.5

3. 下载 App.tsx 并替换 src/App.tsx。

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

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

4. 启动开发服务器：

   ¥Start the development server:

npm run dev

当页面加载时，它将处理 https://xlsx.nodejs.cn/pres.numbers

¥When the page loads, it will process https://xlsx.nodejs.cn/pres.numbers

[^1]: 见 "Sheet 对象"

¥See "Sheet Objects"

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

¥See sheet_to_json in "Utilities"

[^3]: 见 "HTML" 实用程序中的 table_to_book

¥See table_to_book in "HTML" Utilities

[^4]: 见 writeFileXLSX 于 "写入文件"

¥See writeFileXLSX in "Writing Files"