AngularJS 站点中的工作表

该演示适用于旧版 AngularJS 框架（版本 1）。

¥This demo is for the legacy AngularJS framework (version 1).

"Angular" 现在通常指的是从版本 2 开始的新框架。"Angular" 演示涵盖了新框架。

¥"Angular" now commonly refers to the new framework starting with version 2. The "Angular" demo covers the new framework.

AngularJS 是一个用于构建用户界面的 JS 库。

¥AngularJS is a JS library for building user interfaces.

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

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

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

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

在线演示

¥Live Demo

测试部署

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

¥This demo was tested in the following environments:

浏览器	版本	日期
Chromium 125	`1.8.2`（最新）	2024-06-09
Chromium 125	`1.2.32`（旧版）	2024-06-09

单击此处查看实时独立集成演示。

¥Click here for a live standalone integration demo.

该演示使用对象数组作为其内部状态。它在加载时获取并显示数据。它还包括一个用于将数据导出到文件的按钮和一个用于加载用户提交的文件的文件输入元素。

¥The demo uses an array of objects as its internal state. It fetches and displays data on load. It also includes a button for exporting data to file and a file input element for loading user-submitted files.

安装

¥Installation

可以在 HTML 入口点页面的 SCRIPT 标记中引用 SheetJS 独立脚本。

¥The SheetJS Standalone scripts can be referenced in a SCRIPT tag from the HTML entry point page.

该脚本添加 XLSX 全局变量。

¥The script adds the XLSX global variable.

数据源

¥Data Sources

现代浏览器支持许多方便的 API 来接收文件并允许用户提交文件。

¥Modern browsers support a number of convenient APIs for receiving files and allowing users to submit files.

AngularJS 与 API 可用之前的时代相关，它提供了封装器来简化网络和文件处理。

¥AngularJS, relevant in an era before the APIs were available, provides wrappers to simplify network and file processing.

远程文件

¥Remote Files

要从远程位置下载文件，$http 服务可以执行 GET 请求 [^1]

¥To download files from a remote location, the $http service can perform GET requests[^1]

responseType 选项直接传递给 XMLHttpRequest。将属性设置为 "arraybuffer" 可确保结果是 ArrayBuffer 对象。

¥The responseType option is directly passed to XMLHttpRequest. Setting the property to "arraybuffer" ensures the result is an ArrayBuffer object.

SheetJS read 方法可以解析 ArrayBuffer 并返回 SheetJS 工作簿对象 [^2]。

¥The SheetJS read method can parse the ArrayBuffer and return a SheetJS workbook object[^2].

该控制器获取样本文件，将结果解析到工作簿中，提取第一个工作表，并使用 SheetJS sheet_to_html 方法生成 HTML 表：

¥This controller fetches a sample file, parses the result into a workbook, extracts the first worksheet, and uses the SheetJS sheet_to_html method to generate a HTML table:

Sample Controller
/* The controller function must accept a `$http` argument */
app.controller('sheetjs', function($scope, $http) {
  /* fetch https://xlsx.nodejs.cn/pres.xlsx */
  $http({
    method:'GET', url:'https://xlsx.nodejs.cn/pres.xlsx',
    /* ensure the result is an ArrayBuffer object */
    responseType:'arraybuffer'
  }).then(function(response) {
    var wb = XLSX.read(response.data);
    /* generate HTML from first worksheet*/
    var ws = wb.Sheets[wb.SheetNames[0]];
    var html = XLSX.utils.sheet_to_html(ws);
    /* assign to the `tbl` scope property */
    $scope.tbl = html;
  }, function(err) { console.log(err); });
});

用户提交的文件

¥User-Submitted Files

用户可以使用 HTML 文件输入元素提交文件。当用户选择文件时，会创建 DOM change 事件。

¥Users can submit files using HTML file input elements. A DOM change event is created when users select a file.

在 AngularJS 中，标准 DOM 事件处理程序是使用带有 link 选项 [^3] 的自定义指令创建的。

¥In AngularJS, standard DOM event handlers are created using custom directives with the link option[^3].

以下指令函数创建一个 change 事件处理程序，该处理程序将使用 FileReader 生成带有文件数据的 ArrayBuffer 对象，使用 SheetJS read 方法解析文件数据，使用 sheet_to_html 生成 HTML 表，并将结果存储在 tbl 属性中应用状态：

¥The following directive function creates a change event handler that will use a FileReader to generate an ArrayBuffer object with the file data, parse the file data using the SheetJS read method, generate a HTML table using sheet_to_html, and store the result in the tbl property of the app state:

function SheetJSImportDirective() { return {
  scope: false,
  /* $elm will be a reference to the file input DOM element */
  link: function ($scope, $elm) {
    /* add a `change` event handler */
    $elm.on('change', function (changeEvent) {
      /* use a FileReader to read the file */
      var reader = new FileReader();
      reader.onload = function (e) {
        /* this event handler will be called once the data is read */
        var wb = XLSX.read(e.target.result);

        /* generate HTML from first worksheet*/
        var ws = wb.Sheets[wb.SheetNames[0]];
        var html = XLSX.utils.sheet_to_html(ws);

        /* assign to the `tbl` scope property */
        $scope.apply(function() { $scope.tbl = html; });
      };
      /* read */
      reader.readAsArrayBuffer(changeEvent.target.files[0]);
    });
  }
}; }

可以通过两个步骤将此功能添加到应用中：

¥This functionality can be added to the app in two steps:

添加具有属性 import-sheet-js="" 的 INPUT 元素：

¥Add an INPUT element with attribute import-sheet-js="":

<input type="file" import-sheet-js="" multiple="false"  />

定义附加 SheetJSImportDirective 的 importSheetJs 指令：

¥Define the importSheetJs directive that attaches SheetJSImportDirective:

app.directive("importSheetJs", [SheetJSImportDirective]);

AngularJS 将连字符属性 import-sheet-js 规范化为 importSheetJs 驼峰式指令名称。

¥AngularJS normalizes the hyphenated attribute import-sheet-js to the importSheetJs camel-case directive name.

内部状态

¥Internal State

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

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

对象数组

¥Array of Objects

示例总统表有一个标题行，其中包含 "名称" 和 "索引" 列。自然的 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:

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

SheetJS sheet_to_json 方法从工作表生成行对象。以下控制器解析远程文件，生成行对象，并将数组存储在状态中：

¥The SheetJS sheet_to_json method generates row objects from worksheets. The following controller parses a remote file, generates row objects, and stores the array in the state:

app.controller('sheetjs', function($scope, $http) {
  $http({
    url:'https://xlsx.nodejs.cn/pres.xlsx',
    method:'GET', responseType:'arraybuffer'
  }).then(function(response) {
    var wb = XLSX.read(response.data);
    $scope.data = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
  }, function(err) { console.log(err); });
});

组件通常会使用 ng-repeat 循环数据。以下模板生成一个表，其中每个总统都有一行：

¥A component will typically loop over the data using ng-repeat. The following template generates a TABLE with a row for each President:

<table id="sjs-table">
  <tr><th>名称</th><th>索引</th></tr>
  <tr ng-repeat="row in data track by $index">
    
<td>{{row.Name}}</td>

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

  </tr>
</table>

json_to_sheet 方法可以根据数据生成工作表：

¥The json_to_sheet method can generate a worksheet from the data:

/* assuming $scope.data is an array of objects */
$scope.exportSheetJS = function() {
  /* generate a worksheet */
  var ws = XLSX.utils.json_to_sheet($scope.data);
  /* add to workbook */
  var wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "Presidents");
  /* write workbook and force a download */
  XLSX.writeFile(wb, "SheetJSAngularJSAoO.xlsx");
};

How to run the example (click to hide)

Save the following to index.html:

index.html
<!DOCTYPE html>
<html ng-app="s5s">
<head>
  <title>SheetJS + AngularJS</title>
  <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.8.2/angular.min.js"></script>
  <script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/shim.min.js"></script>
  <script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>
</head>
<body>
<h3><a href="https://sheetjs.com">SheetJS + AngularJS demo</a></h3>

<div ng-controller="sheetjs">
  <button ng-click="exportSheetJS()">Export Table</button>
  <table id="s5s-table">
    <tr><th>Name</th><th>Index</th></tr>
    <tr ng-repeat="row in data track by $index">
      <td>{{row.Name}}</td>
      <td>{{row.Index}}</td>
    </tr>
  </table>
</div>

<script>
var app = angular.module('s5s', []);
app.controller('sheetjs', function($scope, $http) {
  $scope.exportSheetJS = function() {
    var ws = XLSX.utils.json_to_sheet($scope.data);
    var wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, "Presidents");
    XLSX.writeFile(wb, "SheetJSAngularJSAoO.xlsx");
  };
  $http({
    url:'https://xlsx.nodejs.cn/pres.xlsx',
    method:'GET', responseType:'arraybuffer'
  }).then(function(response) {
    var wb = XLSX.read(response.data);
    var data = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
    $scope.data = data;
  }, function(err) { console.log(err); });
});
</script>
</body>
</html>

Start a local web server with npx http-server . and access the displayed URL with a web browser (typically http://localhost:8080)

When the page loads, the app will fetch https://xlsx.nodejs.cn/pres.xlsx and store an array of objects in state. When the "Export Table" button is clicked, a worksheet is created and exported to XLSX.

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

sheet_to_html 函数生成可识别合并和其他工作表功能的 HTML。生成的 HTML 不包含任何 <script> 标记，因此应该可以安全地传递到 ng-bind-html 绑定。此方法需要 ngSanitize 插件 [^5]。

¥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 ng-bind-html binding. This approach requires the ngSanitize plugin[^5].

<div ng-controller="sheetjs">
  <div ng-bind-html="data" id="tbl"></div>
</div>

<script>
var app = angular.module('s5s', ['ngSanitize']);
app.controller('sheetjs', function($scope, $http) {
  $http({
    url:'https://xlsx.nodejs.cn/pres.xlsx',
    method:'GET', responseType:'arraybuffer'
  }).then(function(response) {
    var wb = XLSX.read(response.data);
    $scope.data = XLSX.utils.sheet_to_html(wb.Sheets[wb.SheetNames[0]]);
  }, function(err) { console.log(err); });
});
</script>

HTML 表格可以直接用 table_to_book 导出：

¥The HTML table can be directly exported with table_to_book:

  $scope.exportSheetJS = function() {
    /* export table element */
    var tbl = document.getElementById("tbl").getElementsByTagName("TABLE")[0];
    var wb = XLSX.utils.table_to_book(tbl);
    XLSX.writeFile(wb, "SheetJSAngularJSHTML.xlsx");
  };

How to run the example (click to hide)

Save the following to index.html:

index.html
<!DOCTYPE html>
<html ng-app="s5s">
<head>
  <title>SheetJS + AngularJS</title>
  <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.8.2/angular.min.js"></script>
  <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.8.2/angular-sanitize.js"></script>
  <script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/shim.min.js"></script>
  <script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>
</head>
<body>
<h3><a href="https://sheetjs.com">SheetJS + AngularJS demo</a></h3>

<div ng-controller="sheetjs">
  <button ng-click="exportSheetJS()">Export Table</button>
  <div ng-bind-html="data" id="tbl"></div>
</div>

<script>
var app = angular.module('s5s', ['ngSanitize']);
app.controller('sheetjs', function($scope, $http) {
  $scope.exportSheetJS = function() {
    var tbl = document.getElementById("tbl").getElementsByTagName("TABLE")[0];
    var wb = XLSX.utils.table_to_book(tbl);
    XLSX.writeFile(wb, "SheetJSAngularJSHTML.xlsx");
  };
  $http({
    url:'https://xlsx.nodejs.cn/pres.xlsx',
    method:'GET', responseType:'arraybuffer'
  }).then(function(response) {
    var wb = XLSX.read(response.data);
    $scope.data = XLSX.utils.sheet_to_html(wb.Sheets[wb.SheetNames[0]]);
  }, function(err) { console.log(err); });
});
</script>
</body>
</html>

Start a local web server with npx http-server . and access the displayed URL with a web browser (typically http://localhost:8080)

When the page loads, the app will fetch https://xlsx.nodejs.cn/pres.xlsx and store the HTML string in state. When the "Export Table" button is clicked, a worksheet is created and exported to XLSX.

[^1]: 请参阅 AngularJS 文档中的 $http。

¥See $http in the AngularJS documentation.

[^2]: 见 "工作簿对象"

¥See "Workbook Object"

[^3]: 请参阅 AngularJS 文档中的 "创建指令"。

¥See "Creating Directives" in the AngularJS documentation.

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

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

[^5]: 请参阅 AngularJS 文档中的 ngSanitize。

¥See ngSanitize in the AngularJS documentation.

在线演示​

安装​

数据源​

远程文件​

用户提交的文件​

内部状态​

对象数组​

HTML​