Skip to main content

Angular 站点中的工作表

Angular 是一个用于构建用户界面的 JS 库。[^1]

¥Angular is a JS library for building user interfaces.[^1]

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

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

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

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

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

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

Angular 工具使用原生 NodeJS 模块。尝试使用不同的 NodeJS 版本运行 Angular 项目时会出现许多问题。这些问题应该针对 Angular 项目。

¥Angular tooling uses native NodeJS modules. There are a number of issues when trying to run Angular projects with different NodeJS versions. These issues should be directed to the Angular project.

遥测

Angular CLI 默认启用遥测。使用最新版本时，请在创建新项目之前通过 CLI 工具全局禁用分析：

¥Angular CLI enables telemetry by default. When using a recent version, disable analytics globally through the CLI tool before creating a new project:

npx @angular/cli analytics disable -g

（如果提示共享数据，请输入 N 并按 Enter 键）

¥(If prompted to share data, type N and press Enter)

安装​

¥Installation

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

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

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

¥The library can be imported directly from JS or TS 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.

Angular 17 破坏了与使用 Angular 2 的项目的向后兼容性 - 16.

¥Angular 17 broke backwards compatibility with projects using Angular 2 - 16.

尽管 Angular 出现了混乱，但 SheetJS 与 Angular 的每个版本都能很好地配合。

¥Despite the Angular turmoil, SheetJS plays nice with each version of Angular.

相关时，Angular 17 和 Angular 2 的代码片段 - 包括 16。"角度 2-16" 和 "角 17+" 选项卡更改显示的代码块

¥When relevant, code snippets for Angular 17 and Angular 2 - 16 are included. The "Angular 2-16" and "Angular 17+" tabs change the displayed code blocks

对象数组​

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

该数据通常存储为组件类中的对象数组：

¥This data is typically stored as an array of objects in the component class:

import { Component } from '@angular/core';

@Component({ /* ... component configuration options ... */ })
export class AppComponent {
  /* the component state is an array of objects */
  rows: any[] = [ { Name: "SheetJS", Index: 0 }];
}

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

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

import { Component } from '@angular/core';

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

@Component({ /* ... component configuration options ... */ })
export class AppComponent {
  /* the component state is an array of presidents */
  rows: President[] = [ { Name: "SheetJS", Index: 0 }];
}

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

¥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 函数简化了状态更新。它们最好用在 ngOnInit[^2] 的函数体和事件处理程序中。

¥The SheetJS read and sheet_to_json functions simplify state updates. They are best used in the function bodies of ngOnInit[^2] and event handlers.

当用户加载站点时，ngOnInit 方法可以下载并更新状态：

¥A ngOnInit method can download and update state when a person loads the site:

import { Component } from '@angular/core';
import { read, utils } from 'xlsx';

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

@Component({ /* ... component configuration options ... */ })
export class AppComponent {
  rows: President[] = [ { Name: "SheetJS", Index: 0 }];
  ngOnInit(): void { (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 workbook */
    const wb = read(ab);

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

    /* update data */
    this.rows = data;
  })(); }
}

渲染数据​

¥Rendering Data

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

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

角 2 - 16.推荐使用 ngFor[^3]。Angular 17 不再支持故事语法，而是选择让人想起 JavaScript[^4] 的 @for 块。

¥Angular 2 - 16 recommended using ngFor[^3]. Angular 17 no longer supports the storied syntax, instead opting for a @for block reminiscent of JavaScript[^4].

Example Template for displaying arrays of objects (Angular 2-16)
<div class="content" role="main">

<table>
  <thead><tr><th>Name</th><th>Index</th></tr></thead>
  <tbody>
    <tr *ngFor="let row of rows">
      

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


      

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


    </tr>
  </tbody>
</table>

</div>

导出数据​

¥Exporting Data

writeFile 和 json_to_sheet 功能简化了数据导出。它们最好用在附加到按钮或其他元素的事件处理程序的函数体中。

¥The writeFile and json_to_sheet functions simplify exporting data. They are best used in the function bodies of event handlers attached to button or other elements.

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

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

src/app/app.component.ts
import { Component } from '@angular/core';
import { utils, writeFileXLSX } from 'xlsx';

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

@Component({ /* ... component configuration options ... */ })
export class AppComponent {
  rows: President[] = [ { Name: "SheetJS", Index: 0 }];
  /* get state data and export to XLSX */
  onSave(): void {
    /* generate worksheet from state */
    const ws = utils.json_to_sheet(this.rows);
    /* create workbook and append worksheet */
    const wb = utils.book_new();
    utils.book_append_sheet(wb, ws, "Data");
    /* export to XLSX */
    writeFileXLSX(wb, "SheetJSAngularAoO.xlsx");
  }
}

完整组件​

¥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/app/app.component.ts
import { Component } from '@angular/core';
import { read, utils, writeFileXLSX } from 'xlsx';

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

@Component({
  selector: 'app-root',
  template: `
<div class="content" role="main">

<table>
  <thead><tr><th>Name</th><th>Index</th></tr></thead>
  <tbody>
    <tr *ngFor="let row of rows">
      

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


      

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


    </tr>
  </tbody><tfoot>
    <button (click)="onSave()">Export XLSX</button>
  </tfoot>
</table>

</div>
`
})
export class AppComponent {
  rows: President[] = [ { Name: "SheetJS", Index: 0 }];
  ngOnInit(): void { (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 workbook */
    const wb = read(ab);

    /* update data */
    this.rows = utils.sheet_to_json<President>(wb.Sheets[wb.SheetNames[0]]);

  })(); }
  /* get state data and export to XLSX */
  onSave(): void {
    const ws = utils.json_to_sheet(this.rows);
    const wb = utils.book_new();
    utils.book_append_sheet(wb, ws, "Data");
    writeFileXLSX(wb, "SheetJSAngularAoO.xlsx");
  }
}
How to run the example (click to hide)
测试部署

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

¥This demo was tested in the following environments:

Angular日期
17.3.02024-03-13
16.2.122024-03-13

  1. 禁用遥测：

    ¥Disable telemetry:

npx @angular/cli analytics disable -g

  1. 创建一个新项目：

    ¥Create a new project:

npx @angular/cli@17.3.0 new --minimal --defaults --no-interactive sheetjs-angular

@angular/cli 版本控制 Angular 的项目版本。例如，以下命令使用 Angular 16.2.12：

¥The @angular/cli version controls the project version of Angular. For example, the following command uses Angular 16.2.12:

npx @angular/cli@16.2.12 new --minimal --defaults --no-interactive sheetjs-angular

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

    ¥Install the SheetJS dependency and start the dev server:

cd sheetjs-angular
npm i
npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz
npx @angular/cli analytics disable
npm start

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

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

  2. 在前面的 src/app/app.component.ts 代码片段中，选择相应版本的 Angular（"角度 2-16" 或 "角 17+"）选项卡，复制代码内容并替换项目中的 src/app/app.component.ts。

    ¥In the previous src/app/app.component.ts code snippet, select the tab for the appropriate version of Angular ("Angular 2-16" or "Angular 17+"), copy the code contents and replace src/app/app.component.ts in the project.

该页面将刷新并显示带有“导出”按钮的表格。单击该按钮，页面将尝试下载 SheetJSAngularAoO.xlsx。使用电子表格编辑器打开该文件。

¥The page will refresh and show a table with an Export button. Click the button and the page will attempt to download SheetJSAngularAoO.xlsx. Open the file with a spreadsheet editor.

  1. 停止开发服务器并构建站点：

    ¥Stop the dev server and build the site:

npm run build

要测试生成的站点，请启动 Web 服务器：

¥To test the generated site, start a web server:

npx -y http-server dist/sheetjs-angular/

使用 Web 浏览器访问显示的 URL（通常为 http://localhost:8080）以测试打包的站点。

¥Access the displayed URL (typically http://localhost:8080) with a web browser to test the bundled site.

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。生成的 HTML 不包含任何 <script> 标签，因此应该可以安全地传递给 innerHTML 绑定变量，但强烈建议使用 DomSanitizer 方法 [^6]：

¥The sheet_to_html function generates HTML that is aware of merges and other worksheet features. The generated HTML does not contain any <script> tags, and should therefore be safe to pass to an innerHTML-bound variable, but the DomSanitizer approach[^6] is strongly recommended:

src/app/app.component.ts
import { Component, ElementRef, ViewChild } from '@angular/core';
import { DomSanitizer, SafeHtml } from '@angular/platform-browser';
import { read, utils, writeFileXLSX } from 'xlsx';

@Component({
  selector: 'app-root',
  template: `<div class="content" role="main" [innerHTML]="html" #tableau></div>
    <button (click)="onSave()">Export XLSX</button>`
})
export class AppComponent {
  constructor(private sanitizer: DomSanitizer) {}
  html: SafeHtml = "";
  @ViewChild('tableau') tabeller!: ElementRef<HTMLDivElement>;
  ngOnInit(): void { (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 workbook */
    const wb = read(ab);

    /* update data */
    const h = utils.sheet_to_html(wb.Sheets[wb.SheetNames[0]]);
    this.html = this.sanitizer.bypassSecurityTrustHtml(h);
  })(); }
  /* get live table and export to XLSX */
  onSave(): void {
    const elt = this.tabeller.nativeElement.getElementsByTagName("TABLE")[0];
    const wb = utils.table_to_book(elt);
    writeFileXLSX(wb, "SheetJSAngularHTML.xlsx");
  }
}
How to run the example (click to hide)
测试部署

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

¥This demo was tested in the following environments:

Angular日期
17.3.02024-03-13
16.2.122024-03-13

  1. 禁用遥测：

    ¥Disable telemetry:

npx @angular/cli analytics disable -g

  1. 创建一个新项目：

    ¥Create a new project:

npx @angular/cli@17.3.0 new --minimal --defaults --no-interactive sheetjs-angular

@angular/cli 版本控制 Angular 的项目版本。例如，以下命令使用 Angular 16.2.12：

¥The @angular/cli version controls the project version of Angular. For example, the following command uses Angular 16.2.12:

npx @angular/cli@16.2.12 new --minimal --defaults --no-interactive sheetjs-angular

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

    ¥Install the SheetJS dependency and start the dev server:

cd sheetjs-angular
npm i
npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz
npx @angular/cli analytics disable
npm start

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

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

  2. 在前面的 src/app/app.component.ts 代码片段中，选择相应版本的 Angular（"角度 2-16" 或 "角 17+"）选项卡，复制代码内容并替换项目中的 src/app/app.component.ts。

    ¥In the previous src/app/app.component.ts code snippet, select the tab for the appropriate version of Angular ("Angular 2-16" or "Angular 17+"), copy the code contents and replace src/app/app.component.ts in the project.

该页面将刷新并显示带有“导出”按钮的表格。单击该按钮，页面将尝试下载 SheetJSAngularHTML.xlsx。使用电子表格编辑器打开该文件。

¥The page will refresh and show a table with an Export button. Click the button and the page will attempt to download SheetJSAngularHTML.xlsx. Open the file with a spreadsheet editor.

  1. 停止开发服务器并构建站点：

    ¥Stop the dev server and build the site:

npm run build

要测试生成的站点，请启动 Web 服务器：

¥To test the generated site, start a web server:

npx -y http-server dist/sheetjs-angular/

使用网络浏览器访问 http://localhost:8080 以测试打包站点。

¥Access http://localhost:8080 with a web browser to test the bundled site.

行和列​

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

ngx-datatable 使用 prop 作为键，使用 name 作为列标签：

¥ngx-datatable uses prop as the key and name for the column label:

/* rows are generated with a simple array of arrays */
this.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");
this.columns = Array.from({ length: range.e.c + 1 }, (_, i) => ({
  /* for an array of arrays, the keys are "0", "1", "2", ... */
  prop: String(i),
  /* column labels: encode_col translates 0 -> "A", 1 -> "B", 2 -> "C", ... */
  name: XLSX.utils.encode_col(i)
}));

旧版本​

¥Older Versions

此演示适用于旧部署。与不同的 NodeJS 和其他生态系统版本存在不兼容性。应向 Google 和 Angular 团队提出问题。

¥This demo is included for legacy deployments. There are incompatibilities with different NodeJS and other ecosystem versions. Issues should be raised with Google and the Angular team.

最新版本的 NodeJS 不适用于较旧的 Angular 项目！

¥The newest versions of NodeJS will not work with older Angular projects!

Angular 工具不提供在版本之间切换的命令！

¥The Angular tooling does not provide a command to switch between versions!

这是一个已知的 Angular 问题。

¥This is a known Angular problem.

为了解决这个问题，SheetJSAngular.zip 是一个框架项目，旨在与每个 Angular 版本很好地配合。

¥To work around this, SheetJSAngular.zip is a skeleton project designed to play nice with each Angular version.

策略​

¥Strategies

内部状态​

¥Internal State

该演示使用数组的数组作为内部状态：

¥This demo uses an array of arrays as the internal state:

export class SheetJSComponent {
  data: any[][] = [ [1, 2], [3, 4] ];
  // ...
}

模板中嵌套的 ngFor 可以跨行和单元格循环：

¥Nested ngFor in a template can loop across the rows and cells:



<table class="sjs-table">
  <tr *ngFor="let row of data">
    <td *ngFor="let val of row">{{val}}</td>
  </tr>
</table>


读取数据​

¥Reading Data

对于旧部署，最好的入口是标准 HTML INPUT 文件元素：

¥For legacy deployments, the best ingress is a standard HTML INPUT file element:

<input type="file" (change)="onFileChange($event)" multiple="false" />

在组件中，该事件是标准文件事件。与现代 Blob#arrayBuffer 方法相比，使用 FileReader 得到了广泛的支持：

¥In the component, the event is a standard file event. Using a FileReader has broad support compared to the modern Blob#arrayBuffer approach:

  onFileChange(evt: any) {
    /* wire up file reader */
    const target: DataTransfer = <DataTransfer>(evt.target);
    if (target.files.length !== 1) throw new Error('Cannot use multiple files');
    const reader: FileReader = new FileReader();
    reader.onload = (e: any) => {
      /* read workbook */
      const ab: ArrayBuffer = e.target.result;
      const wb: WorkBook = read(ab);

      /* grab first sheet */
      const wsname: string = wb.SheetNames[0];
      const ws: WorkSheet = wb.Sheets[wsname];

      /* save data */
      this.data = <AOA>(utils.sheet_to_json(ws, {header: 1}));
    };
    reader.readAsArrayBuffer(target.files[0]);
  }

写入数据​

¥Writing Data

该演示在模板中使用了 HTML5 按钮：

¥The demo uses an HTML5 button in the template:

<button (click)="export()">Export!</button>

在组件中，aoa_to_sheet 用于生成工作表：

¥In the component, aoa_to_sheet is used to generate the worksheet:

  export(): void {
    /* generate worksheet */
    const ws: WorkSheet = utils.aoa_to_sheet(this.data);

    /* generate workbook and add the worksheet */
    const wb: WorkBook = utils.book_new();
    utils.book_append_sheet(wb, ws, 'Sheet1');

    /* save to file */
    writeFile(wb, "SheetJS.xlsx");
  }

SystemJS​

默认 angular-cli 配置不需要额外配置。

¥The default angular-cli configuration requires no additional configuration.

某些部署使用 SystemJS 加载器，这确实需要配置。SystemJS 演示 包括所需的设置。

¥Some deployments use the SystemJS loader, which does require configuration. The SystemJS demo includes the required settings.

旧版演示​

¥Legacy Demo

  1. 下载并解压 SheetJSAngular.zip：

    ¥Download and unzip SheetJSAngular.zip:

curl -LO https://xlsx.nodejs.cn/angular/SheetJSAngular.zip
unzip SheetJSAngular.zip
cd SheetJSAngular

  1. 下载所需 Angular 版本的文件：

    ¥Download the files for the desired Angular version:

curl -o package.json -L https://xlsx.nodejs.cn/angular/versions/package.json-ng2
curl -o src/polyfills.ts -L https://xlsx.nodejs.cn/angular/versions/polyfills.ts-ng2

  1. 安装项目和依赖：

    ¥Install project and dependencies:

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

  1. 启动本地服务器

    ¥Start a local server with

npm start

传统站点 URL 是 http://localhost:4200/ 。 使用网络浏览器打开页面并打开控制台。在 "元素" 选项卡中，app-root 元素将具有 ng-version 属性。

¥The traditional site URL is http://localhost:4200/ . Open the page with a web browser and open the console. In the "Elements" tab, the app-root element will have an ng-version attribute.

  1. 构建应用

    ¥Build the app with

npm run build

[^1]: Angular 版本 2-16 的主要网站是 https://angular.io/ 。 该项目在 Angular 17 发布期间转移到新域 https://angular.dev/。

¥The main website for Angular versions 2-16 is https://angular.io/ . The project moved to a new domain https://angular.dev/ during the Angular 17 launch.

[^2]: 请参阅 Angular 2-16 文档 或 Angular 17 文档 中的 OnInit

¥See OnInit in the Angular 2-16 docs or Angular 17 docs

[^3]: 请参阅 Angular 2-16 文档中的 ngFor。

¥See ngFor in the Angular 2-16 docs.

[^4]: 请参阅 Angular 17 文档中的 @for。

¥See @for in the Angular 17 docs.

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

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

[^6]: 请参阅 Angular 2-16 文档 或 Angular 17 文档 中的 DomSanitizer

¥See DomSanitizer in the Angular 2-16 docs or Angular 17 docs