Svelte Sites 中的工作表

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

¥Svelte is a JavaScript library for building user interfaces.

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

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

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

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

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

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

安装

¥Installation

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

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

该库可以直接从 Svelte 文件导入：

¥The library can be imported directly from Svelte files 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

通常，一些用户会创建一个电子表格，其中包含应加载到网站中的源数据。该工作表将具有已知的列。例如，"名称" 和 "索引" 用于 pres.xlsx：

¥Typically, some users will create a spreadsheet with source data that should be loaded into the site. This sheet will have known columns. For example, "Name" and "Index" are used in pres.xlsx:

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

这自然会映射到类型化对象的数组，如下面的 TS 示例所示：

¥This naturally maps to an array of typed objects, as in the TS example below:

import { read, utils } from 'xlsx';

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

const f = await (await fetch("https://xlsx.nodejs.cn/pres.xlsx")).arrayBuffer();
const wb = read(f);
const data = utils.sheet_to_json<President>(wb.Sheets[wb.SheetNames[0]]);
console.log(data);

组件通常会映射数据。以下示例生成一个表，其中为每位总统生成一行：

¥A component will typically map over the data. The following example generates a TABLE with a row for each President:

src/SheetJSSvelteAoO.svelte
<script>
import { onMount } from 'svelte';
import { read, utils, writeFileXLSX } from 'xlsx';

/* the component state is an array of presidents */
let pres = [];

/* Fetch and update the state once */
onMount(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
  pres = utils.sheet_to_json(ws); // generate objects and update state
});

/* get state data and export to XLSX */
function exportFile() {
  const ws = utils.json_to_sheet(pres);
  const wb = utils.book_new();
  utils.book_append_sheet(wb, ws, "Data");
  writeFileXLSX(wb, "SheetJSSvelteAoO.xlsx");
}
</script>

<main>
  

<table><thead><tr><th>Name</th><th>Index</th></tr></thead><tbody>
  {#each pres as p}<tr>
    

<td>{p.Name}</td>


    

<td>{p.Index}</td>


  </tr>{/each}
  </tbody><tfoot><td colSpan={2}>
  <button on:click={exportFile}>Export XLSX</button>
  </td></tfoot></table>


</main>

How to run the example (click to hide)

测试部署

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

¥This demo was tested in the following environments:

SvelteJS	ViteJS	日期
`4.2.18`	`5.2.13`	2024-06-07

创建一个新项目：

¥Create a new project:

npm create vite@latest sheetjs-svelte -- --template svelte-ts

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

¥Install the SheetJS dependency and start the dev server:

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

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

¥Open a web browser and access the displayed URL (http://localhost:5173)
将 src/App.svelte 替换为 src/SheetJSSvelteAoO.svelte 示例。

¥Replace src/App.svelte with the src/SheetJSSvelteAoO.svelte example.

该页面将刷新并显示带有“导出”按钮的表格。单击该按钮，页面将尝试下载 SheetJSSvelteAoA.xlsx。由于 Vite 会尝试即时优化 SheetJS 库，因此可能会有延迟。

¥The page will refresh and show a table with an Export button. Click the button and the page will attempt to download SheetJSSvelteAoA.xlsx. There may be a delay since Vite will try to optimize the SheetJS library on the fly.

建立网站：

¥Build the site:

npm run build

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

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

启动本地网络服务器：

¥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.

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[^1] well!

sheet_to_html 函数生成可识别合并和其他工作表功能的 HTML。Svelte @html 标签允许原始 HTML 字符串：

¥The sheet_to_html function generates HTML that is aware of merges and other worksheet features. Svelte @html tag allows raw HTML strings:

src/SheetJSSvelteHTML.svelte
<script>
import { onMount } from 'svelte';
import { read, utils, writeFileXLSX } from 'xlsx';

let html = "";
let tbl;

/* Fetch and update the state once */
onMount(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
  html = utils.sheet_to_html(ws); // generate HTML and update state
});

/* get state data and export to XLSX */
function exportFile() {
  const elt = tbl.getElementsByTagName("TABLE")[0];
  const wb = utils.table_to_book(elt);
  writeFileXLSX(wb, "SheetJSSvelteHTML.xlsx");
}
</script>

<main>
  <button on:click={exportFile}>Export XLSX</button>
  <div bind:this={tbl}>{@html html}</div>
</main>

How to run the example (click to hide)

测试部署

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

¥This demo was tested in the following environments:

SvelteJS	ViteJS	日期
`4.2.18`	`5.2.13`	2024-06-07

创建一个新项目：

¥Create a new project:

npm create vite@latest sheetjs-svelte -- --template svelte-ts

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

¥Install the SheetJS dependency and start the dev server:

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

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

¥Open a web browser and access the displayed URL (http://localhost:5173)
将 src/App.svelte 替换为 src/SheetJSSvelteHTML.svelte 示例。

¥Replace src/App.svelte with the src/SheetJSSvelteHTML.svelte example.

该页面将刷新并显示带有“导出”按钮的表格。单击该按钮，页面将尝试下载 SheetJSSvelteHTML.xlsx。由于 Vite 会尝试即时优化 SheetJS 库，因此可能会有延迟。

¥The page will refresh and show a table with an Export button. Click the button and the page will attempt to download SheetJSSvelteHTML.xlsx. There may be a delay since Vite will try to optimize the SheetJS library on the fly.

建立网站：

¥Build the site:

npm run build

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

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

启动本地网络服务器：

¥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.

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

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

安装​

内部状态​

对象数组​

HTML​

安装

内部状态

对象数组

HTML