UI5 站点中的表格

OpenUI5 是一个用于构建企业级 Web 应用的 JavaScript 框架。它与 SAPUI5 框架兼容。

¥OpenUI5 is a JavaScript framework for building enterprise-ready web applications. It is compatible with the SAPUI5 framework.

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

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

此演示展示了如何使用 SheetJS 处理 OpenUI5 应用中的电子表格数据。你将学习如何加载电子表格文件、处理其数据以及生成新的电子表格导出。

¥This demo shows how to handle spreadsheet data in OpenUI5 apps using SheetJS. You'll learn how to load spreadsheet files, process their data, and generate new spreadsheet exports.

文档问题 #20 包含从 OpenUI5 "工作列表应用教程" 开始的完整示例。

¥Docs Issue #20 includes a complete example starting from the OpenUI5 "Worklist App Tutorial".

安装

¥Installation

SheetJS 库符合 UI5 ECMAScript 要求 [^1]。SheetJS 库可以在应用生命周期的不同点加载到 UI5 站点中。

¥SheetJS libraries conform to the UI5 ECMAScript requirements[^1]. SheetJS libraries can be loaded in a UI5 site at different points in the app lifecycle.

UI5 模块

¥UI5 Module

SheetJS 独立脚本符合 AMD define 语义。它们开箱即用地支持 sap.ui.define。

¥The SheetJS Standalone scripts comply with AMD define semantics. They support sap.ui.define out of the box.

如果 SheetJS Standalone 脚本保存到 webapp/xlsx.full.min.js，则基本脚本 webapp/index.js 可以加载 ./xlsx.full.min 依赖：

¥If the SheetJS Standalone script is saved to webapp/xlsx.full.min.js, the base script webapp/index.js can load the ./xlsx.full.min dependency:

webapp/index.js (loading the SheetJS dependency)
sap.ui.define([
  "./xlsx.full.min", // relative path to script, without the file extension
  /* ... other libraries ... */
], function (
  _XLSX // !! NOTE: this is not XLSX! A different variable name must be used
  /* ... variables for the other libraries ... */,
) {
  alert(XLSX.version); // use XLSX in the callback
});

在某些部署中，函数参数是 undefined。

¥In some deployments, the function argument was undefined.

独立脚本添加了 window.XLSX，因此建议在函数参数中使用 _XLSX 并在回调中使用 XLSX 访问库。

¥The standalone scripts add window.XLSX, so it is recommended to use _XLSX in the function arguments and access the library with XLSX in the callback.

HTML

UI5 通常在 webapp/index.html 中的 SCRIPT 标签中加载。类似地，SheetJS 独立脚本可以在同一个 HTML 页面中使用 SCRIPT 标签加载：

¥UI5 is typically loaded in a SCRIPT tag in webapp/index.html. Similarly, SheetJS Standalone scripts can be loaded with a SCRIPT tag in the same HTML page:

index.html (add in the HEAD block before UI5 scripts)
<script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>

这将公开 XLSX 全局对象，其中包括文档 "API 参考" 部分中列出的函数。

¥This will expose the XLSX global object, which includes the functions listed in the "API Reference" section of the documentation.

SheetJS Standalone 脚本必须在 UI5 引导脚本之前加载：

¥The SheetJS Standalone script must be loaded before the UI5 bootstrap script:

webapp/index.html (loading the SheetJS standalone script)


<head>
  <meta charset="utf-8">
  <title>UI5 Walkthrough</title>

  <!-- The SheetJS Standalone script must be loaded before the UI5 bootstrap -->
  <script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>
  
  <!-- UI5 bootstrap script -->
  <script
    id="sap-ui-bootstrap"
    src="resources/sap-ui-core.js"
    ...(other attributes)...
  ></script>
</head>

内部状态

¥Internal State

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

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

JSON 模型

¥JSON Model

UI5 JSONModel[^2] 是 JavaScript 对象数据的客户端模型实现。可以将其想象成一个包含电子表格数据的容器。

¥The UI5 JSONModel[^2] is a client-side model implementation for JavaScript object data. Think of it like a container that holds your spreadsheet data.

JSONModel 提供强大的双向数据绑定功能。每当数据发生变化时，UI5 都会自动更新你的网页。当用户与网页中的组件交互时，它还会响应更改。

¥JSONModel provides powerful two-way data binding capabilities. UI5 will automatically updates your webpage whenever the data changes. It will also respond to changes when users interact with components in the webpage.

状态

¥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, where the keys are specified in the first row:

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

这是初始化模型的基本示例。稍后将显示更完整的实现。

¥Here is a basic example of initializing a model. A more complete implementation will be shown later.

UI5 JSONModel for rows of data
sap.ui.define(["sap/ui/model/json/JSONModel"], function (JSONModel) {
  const oModel = new JSONModel({ presidents: [] });
});

更新状态

¥Updating State

从电子表格文件开始，SheetJS read 方法将数据解析为 SheetJS 工作簿对象 [^3]。选择工作表后，sheet_to_json 方法会生成可分配给模型的行对象。

¥Starting from a spreadsheet file, the SheetJS read method parses the data into a SheetJS workbook object[^3]. After selecting a worksheet, the sheet_to_json method generates row objects that can be assigned to the model.

以下是下载工作簿、从第一个工作表生成行以及更新 UI5 JSONModel 的示例流程图和方法：

¥Here is a sample flow diagram and method for downloading a workbook, generating rows from the first worksheet, and updating a UI5 JSONModel:

Download workbook, extract data from first worksheet, and update JSONModel
_loadExcelFile: async function () {
  /* Download from https://xlsx.nodejs.cn/pres.xlsx */
  const f = await (await fetch("https://xlsx.nodejs.cn/pres.xlsx")).arrayBuffer();

  /* parse */
  const wb = XLSX.read(f); // parse the array buffer
  /* generate array of objects from first worksheet */
  const ws = wb.Sheets[wb.SheetNames[0]]; // get the first worksheet
  const data = XLSX.utils.sheet_to_json(ws); // generate objects

  /* update JSONModel */
  this.getView().getModel().setProperty("/presidents", data);
}

渲染数据

¥Rendering Data

在 UI5 中，"模型-视图-控制器"[^4] 模式用于组织代码和分离关注点。视图定义 UI 结构，控制器处理逻辑，模型管理数据。

¥In UI5, the "Model-View-Controller"[^4] pattern is used to organize code and separate concerns. The view defines the UI structure, the controller handles the logic, and the model manages the data.

以下示例使用 Table 组件 [^5] 显示数据。

¥The following example uses the Table component[^5] to display data.

Example View XML for displaying an array of objects
<mvc:View>
  <Page>
    <!-- The Table component binds to the presidents array -->
    <Table width="300px" items="{/presidents}">
      <!-- Column definitions specify the table structure -->
      <columns>
        <Column><header><Text text="Name" /></header></Column>
        <Column><header><Text text="Value" /></header></Column>
      </columns>
      <!-- ColumnListItem template defines how each row should be rendered -->
      <items>
        <ColumnListItem>
          <cells>
            <Text text="{Name}" />
            <Text text="{Index}" />
          </cells>
        </ColumnListItem>
      </items>
    </Table>
  </Page>
</mvc:View>

导出数据

¥Exporting Data

JSONModel 的 getProperty 方法 [^6] 从 UI5 模型中提取数据。如果使用 setProperty 推送了对象数组，则 getProperty 方法将返回对象数组。

¥The getProperty method[^6] of the JSONModel pulls data from the UI5 model. If an array of objects was pushed with setProperty, the getProperty method will return an array of objects.

SheetJS json_to_sheet 函数将从数组中的数据创建 SheetJS 工作表对象 [^7]。book_new 方法将创建一个包含新工作表的 SheetJS 工作簿对象。writeFile 将尝试生成文件并启动下载。

¥The SheetJS json_to_sheet function will create a SheetJS worksheet object[^7] from the data in the array. The book_new method will create a SheetJS workbook object that includes the new worksheet. writeFile will attempt to generate a file and initiate a download.

以下是将数据从 UI5 JSONModel 导出到 XLSX 的示例方法：

¥Here is a sample method for exporting data from the UI5 JSONModel to XLSX:

Fetch data from JSONModel and export to XLSX
/* get model data and export to XLSX */
onExport: function () {
  const data = this.getView().getModel().getProperty("/presidents");
  /* generate worksheet from model data */
  const ws = XLSX.utils.json_to_sheet(data);
  /* create workbook and append worksheet */
  const wb = XLSX.utils.book_new(ws, "Data");
  /* export to XLSX */
  XLSX.writeFileXLSX(wb, "SheetJSOpenUI5AoO.xlsx");
}

此方法可以绑定到 sap.m.Button 控件的 press 事件：

¥This method can be bound to the press event of a sap.m.Button control:

Example View XML for exporting an array of objects to a workbook
<mvc:View>
  <Page>
    <!-- The `onExport` method is bound to the `press` event -->
    <Button text="Export Data" press=".onExport" />
  </Page>
</mvc:View>

完整组件

¥Complete Component

此完整组件示例获取测试文件并在表中显示内容。单击导出按钮时，事件处理程序将导出文件：

¥This complete component example fetches a test file and displays the contents in a table. When the export button is clicked, an event handler will export a file:

视图实现

¥View Implementation

webapp/view/Main.view.xml
<mvc:View controllerName="sheetjs.openui5.controller.Main" displayBlock="true" xmlns="sap.m" xmlns:mvc="sap.ui.core.mvc" xmlns:core="sap.ui.core" core:require="{formatter: 'sheetjs/openui5/model/formatter'}">
  <Page>
    <VBox width="auto" alignItems="Start">
      <Table width="300px" items="{/presidents}">
        <columns>
          <Column><header><Text text="Name" /></header></Column>
          <Column><header><Text text="Index" /></header></Column>
        </columns>
        <items><ColumnListItem><cells>
          <Text text="{Name}" />
          <Text text="{Index}" />
        </cells></ColumnListItem></items>
      </Table>
      <Button text="Export XLSX" press=".onExport" />
    </VBox>
  </Page>
</mvc:View>

控制器实现

¥Controller Implementation

webapp/controller/Main.controller.js
sap.ui.define(
  ["./BaseController", "sap/ui/model/json/JSONModel"],
  function (BaseController, JSONModel) {
    "use strict";

    return BaseController.extend("com.demo.xlsx.controller.Main", {
      onInit: function () {
        /* initialize model */
        const oModel = new JSONModel({ presidents: [] });
        this.getView().setModel(oModel);
        /* load data */
        this._loadExcelFile();
      },

      _loadExcelFile: async function () {
        /* fetch and parse file */
        const f = await (await fetch("https://xlsx.nodejs.cn/pres.xlsx")).arrayBuffer();
        const wb = XLSX.read(f);
        /* extract data from first worksheet */
        const ws = wb.Sheets[wb.SheetNames[0]];
        const data = XLSX.utils.sheet_to_json(ws);
        /* update state model */
        this.getView().getModel().setProperty("/presidents", data);
      },

      onExport: function () {
        /* fetch data from model */
        const data = this.getView().getModel().getProperty("/presidents");
        /* generate workbook */
        const ws = XLSX.utils.json_to_sheet(data);
        const wb = XLSX.utils.book_new(ws, "Data");
        /* export to XLSX */
        XLSX.writeFileXLSX(wb, "SheetJSOpenUI5AoO.xlsx");
      },
    });
  }
);

How to run the example (click to hide)

测试部署

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

¥This demo was tested in the following environments:

OpenUI5	日期
`1.132.1`	2025-01-24

创建一个新站点：

¥Create a new site:

npm i --global generator-easy-ui5
npx yo easy-ui5 app

出现提示时，输入以下选项：

¥When prompted, enter the following options:

Enter your application id (namespace)?：输入 sheetjs.openui5 并按 Enter

¥Enter your application id (namespace)?: Type sheetjs.openui5 and press Enter
Which framework do you want to use?：按 Enter（OpenUI5 应为默认值）

¥Which framework do you want to use?: Press Enter (OpenUI5 should be the default)
Which framework version do you want to use?：输入 1.132.1 并按 Enter

¥Which framework version do you want to use?: Type 1.132.1 and press Enter
Who is the author of the application?：按 Enter（使用默认作者）

¥Who is the author of the application?: Press Enter (use the default author)
Would you like to create a new directory for the application?：输入 Y 并按 Enter

¥Would you like to create a new directory for the application?: Type Y and press Enter
Would you like to initialize a local git repository for the application?：输入 N 并按 Enter

¥Would you like to initialize a local git repository for the application?: Type N and press Enter

Expected output

安装依赖并启动服务器：

¥Install the dependencies and start server:

cd sheetjs.openui5
npm install
npm start

打开 Web 浏览器并访问显示的 URL (http://localhost:8080)。

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

在文件列表中，单击 index.html 启动应用。

¥In the file listing, click index.html to launch the app.

在 title 标记后向 webapp/index.html 添加 SheetJS 独立脚本：

¥Add the SheetJS Standalone script to webapp/index.html after the title tag:

webapp/index.html (add highlighted lines)
    <title>UI5 Application: sheetjs.openui5</title>
    <script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>

用 "视图实现" 节中的 Main.view.xml 代码片段替换 webapp/view/Main.view.xml。

¥Replace webapp/view/Main.view.xml with the Main.view.xml snippet in the "View Implementation" section.
用 "控制器实现" 节中的 Main.controller.js 示例替换 webapp/controller/Main.controller.js。

¥Replace webapp/controller/Main.controller.js with the Main.controller.js example in the "Controller Implementation" section.
切换回浏览器窗口。

¥Switch back to the browser window.

该页面将刷新并显示带有“导出”按钮的表格。

¥The page will refresh and show a table with an Export button.

单击该按钮，页面将尝试下载 SheetJSOpenUI5AoO.xlsx。可以使用电子表格编辑器检查此文件。

¥Click the button and the page will attempt to download SheetJSOpenUI5AoO.xlsx. This file can be inspected with a spreadsheet editor.

建立网站：

¥Build the site:

npm run build:opt

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

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

SAP 推荐 npm run build。这不会生成正确的独立站点！使用 npm run build 构建的站点必须使用 npm run start:dist 提供服务。

¥SAP recommends npm run build. This does not generate a proper standalone site! Sites built with npm run build must be served with npm run start:dist.

此演示使用 build:opt 目标来确保生成正确的静态站点。此演示中的 dist 文件夹可以部署在静态主机上。

¥This demo uses the build:opt target to ensure that a proper static site is generated. The dist folder in this demo can be deployed on a static host.

启动本地网络服务器：

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

页面加载时，应用将获取 https://xlsx.nodejs.cn/pres.xlsx 并在表中显示第一个工作表中的数据。"导出 XLSX" 按钮将生成一个可以在电子表格编辑器中打开的工作簿。

¥When the page loads, the app will fetch https://xlsx.nodejs.cn/pres.xlsx and display the data from the first worksheet in a TABLE. The "Export XLSX" button will generate a workbook that can be opened in a spreadsheet editor.

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

sheet_to_html 函数生成可识别合并和其他工作表功能的 HTML。

¥The sheet_to_html function generates HTML that is aware of merges and other worksheet features.

要从模型呈现 HTML 字符串，模型中的属性应绑定到 UI5 core:HTML[^9] 控件的 content 属性。

¥To render the HTML string from the model, the property from the model should be bound to the content property of a UI5 core:HTML[^9] control.

在导出时，table_to_book 方法会从呈现的 HTML 表中创建一个 SheetJS 工作簿对象。

¥On export, the table_to_book method creates a SheetJS workbook object from the rendered HTML table.

视图实现

¥View Implementation

webapp/view/Main.view.xml
<mvc:View controllerName="sheetjs.openui5.controller.Main" displayBlock="true" xmlns="sap.m" xmlns:mvc="sap.ui.core.mvc" xmlns:core="sap.ui.core" xmlns:html="http://www.w3.org/1999/xhtml">
  <Page>
    <content>
      <core:HTML id="tbl" content="{/tableHTML}" />
      <Button text="Export XLSX" press=".onExport"/>
    </content>
  </Page>
</mvc:View>

控制器实现

¥Controller Implementation

webapp/controller/Main.controller.js
sap.ui.define([
    "sap/ui/core/mvc/Controller",
    "sap/ui/model/json/JSONModel"
  ], function (Controller, JSONModel) {
    "use strict";

    return Controller.extend("sheetjs.openui5.controller.Main", {
      onInit: function () {
        /* the component state is an HTML string */
        const oModel = new JSONModel({ tableHTML: "", });
        this.getView().setModel(oModel);
        /* load data */
        this._loadExcelFile();
      },

      _loadExcelFile: async function () {
        /* fetch and parse file */
        const f = await (await fetch("https://xlsx.nodejs.cn/pres.xlsx")).arrayBuffer();
        const wb = XLSX.read(f);
        /* generate HTML table from first worksheet */
        const ws = wb.Sheets[wb.SheetNames[0]];
        const opts = { header: `<table>`, footer: `</table>` };
        const tableHTML = XLSX.utils.sheet_to_html(ws, opts);
        /* update state model */
        this.getView().getModel().setProperty("/tableHTML", tableHTML);
      },

      onExport: function () {
        /* Get reference to the `TABLE` element in the model */
        const table = this.getView().byId("tbl").getDomRef();

        /* Generate workbook */
        const wb = XLSX.utils.table_to_book(table);

        /* Export to XLSX */
        XLSX.writeFileXLSX(wb, "SheetJSOpenUI5HTML.xlsx");
      },
    });
  }
);

How to run the example (click to hide)

测试部署

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

¥This demo was tested in the following environments:

OpenUI5	日期
`1.132.1`	2025-01-24

创建一个新站点：

¥Create a new site:

npm i --global generator-easy-ui5
npx yo easy-ui5 app

出现提示时，输入以下选项：

¥When prompted, enter the following options:

Enter your application id (namespace)?：输入 sheetjs.openui5 并按 Enter

¥Enter your application id (namespace)?: Type sheetjs.openui5 and press Enter
Which framework do you want to use?：按 Enter（OpenUI5 应为默认值）

¥Which framework do you want to use?: Press Enter (OpenUI5 should be the default)
Which framework version do you want to use?：输入 1.132.1 并按 Enter

¥Which framework version do you want to use?: Type 1.132.1 and press Enter
Who is the author of the application?：按 Enter（使用默认作者）

¥Who is the author of the application?: Press Enter (use the default author)
Would you like to create a new directory for the application?：输入 Y 并按 Enter

¥Would you like to create a new directory for the application?: Type Y and press Enter
Would you like to initialize a local git repository for the application?：输入 N 并按 Enter

¥Would you like to initialize a local git repository for the application?: Type N and press Enter

Expected output

安装依赖并启动服务器：

¥Install the dependencies and start server:

cd sheetjs.openui5
npm install
npm start

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

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

在文件列表中，单击 index.html 启动应用。

¥In the file listing, click index.html to launch the app.

在 title 标记后向 webapp/index.html 添加 SheetJS 独立脚本：

¥Add the SheetJS Standalone script to webapp/index.html after the title tag:

webapp/index.html (add highlighted lines)
    <title>UI5 Application: sheetjs.openui5</title>
    <script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>

用 "视图实现" 节中的 Main.view.xml 代码片段替换 webapp/view/Main.view.xml。

¥Replace webapp/view/Main.view.xml with the Main.view.xml snippet in the "View Implementation" section.
用 "控制器实现" 中的 Main.controller.js 示例替换 webapp/controller/Main.controller.js。

¥Replace webapp/controller/Main.controller.js with the Main.controller.js example in "Controller Implementation".
切换回浏览器窗口。

¥Switch back to the browser window.

该页面将刷新并显示带有“导出”按钮的表格。

¥The page will refresh and show a table with an Export button.

单击该按钮，页面将尝试下载 SheetJSOpenUI5HTML.xlsx。可以使用电子表格编辑器检查此文件。

¥Click the button and the page will attempt to download SheetJSOpenUI5HTML.xlsx. This file can be inspected with a spreadsheet editor.

建立网站：

¥Build the site:

npm run build:opt

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

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

SAP 推荐 npm run build。这不会生成正确的独立站点！使用 npm run build 构建的站点必须使用 npm run start:dist 提供服务。

¥SAP recommends npm run build. This does not generate a proper standalone site! Sites built with npm run build must be served with npm run start:dist.

此演示使用 build:opt 目标来确保生成正确的静态站点。此演示中的 dist 文件夹可以部署在静态主机上。

¥This demo uses the build:opt target to ensure that a proper static site is generated. The dist folder in this demo can be deployed on a static host.

启动本地网络服务器：

¥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]: 有关 OpenUI5 兼容性的更多详细信息，请参阅 "ECMAScript 支持"。

¥See "ECMAScript Support" for more details about OpenUI5 compatibility.

[^2]: 请参阅 OpenUI5 文档中的 JSONModel。

¥See JSONModel in the OpenUI5 documentation.

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

¥See "SheetJS Data Model"

[^4]: 有关模式实现的详细说明，请参阅 OpenUI5 的 MVC 文档。

¥See OpenUI5's MVC Documentation for detailed explanation of the pattern implementation.

[^5]: 请参阅 OpenUI5 文档中的 "列表、列表项和表格"。

¥See "List, List Item, and Table" in the OpenUI5 documentation.

[^6]: 请参阅 OpenUI5 文档中的 sap.ui.model.json.JSONModel 类的 getProperty。

¥See getProperty of class sap.ui.model.json.JSONModel in the OpenUI5 documentation.

[^7]: 详细信息请参见 "工作表对象" 于 "SheetJS 数据模型"。

¥See "Worksheet Object" in "SheetJS Data Model" for more details.

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

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

[^9]: 请参阅 OpenUI5 文档中的 core:HTML。

¥See core:HTML in the OpenUI5 documentation.

安装​

UI5 模块​

HTML​

内部状态​

JSON 模型​

状态​

更新状态​

渲染数据​

导出数据​

完整组件​

视图实现​

控制器实现​

HTML​

视图实现​

控制器实现​

安装

UI5 模块

HTML

内部状态

JSON 模型

状态

更新状态

渲染数据

导出数据

完整组件

视图实现

控制器实现

HTML

视图实现

控制器实现