2024-12-26 16:51:25 +00:00
---
2025-01-06 02:51:20 +00:00
title: Sheets in UI5 Sites
sidebar_label: OpenUI5 / SAPUI5
2024-12-26 16:51:25 +00:00
description: Build enterprise-grade applications with OpenUI5. Seamlessly integrate spreadsheets into your app using SheetJS. Bring Excel-powered workflows and data to the modern web.
pagination_prev: demos/index
pagination_next: demos/grid/index
sidebar_position: 10
---
import current from '/version.js';
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import CodeBlock from '@theme/CodeBlock';
2025-01-06 02:51:20 +00:00
[OpenUI5 ](https://openui5.org/ ) is a JavaScript framework for building
enterprise-ready web applications. It is compatible with the SAPUI5 framework.
2024-12-26 16:51:25 +00:00
[SheetJS ](https://sheetjs.com ) is a JavaScript library for reading and writing
data from spreadsheets.
This demo uses OpenUI5 and SheetJS to process and generate a spreadsheets. We'll
explore how to load SheetJS in an OpenUI5 app and handle data binding with the
Model-View-Controller pattern.
2025-01-06 02:51:20 +00:00
:::info pass
[Docs Issue #20 ](https://git.sheetjs.com/sheetjs/docs.sheetjs.com/issues/20 )
includes a complete example starting from the OpenUI5 "Worklist App Tutorial".
:::
2024-12-26 16:51:25 +00:00
## Installation
2025-01-06 02:51:20 +00:00
SheetJS libraries conform to the UI5 ECMAScript limitations[^1]. SheetJS
libraries can be loaded in a UI5 site at different points in the app lifecycle.
2024-12-26 16:51:25 +00:00
2025-01-06 02:51:20 +00:00
**HTML**
2024-12-26 16:51:25 +00:00
2025-01-06 02:51:20 +00:00
UI5 is typically loaded in a `SCRIPT` tag in `webapp/index.html` . Similarly,
[SheetJS Standalone scripts ](/docs/getting-started/installation/standalone )
can be loaded in the same HTML page:
< CodeBlock language = "html" value = "html" > {`\
< script src = "https://cdn.sheetjs.com/xlsx-${current}/package/dist/xlsx.full.min.js" > < / script >
`}
< / CodeBlock >
This will expose the `XLSX` global object, which includes the functions listed
in the ["API Reference" ](/docs/api/ ) section of the documentation.
:::caution pass
The SheetJS Standalone script must be loaded before the UI5 bootstrap script:
< CodeBlock language = "html" value = "html" title = "webapp/index.html" > {`\
< head >
< meta charset = "utf-8" >
< title > UI5 Walkthrough< / title >
< script src = "https://cdn.sheetjs.com/xlsx-${current}/package/dist/xlsx.full.min.js" > < / script >
< script
id="sap-ui-bootstrap"
src="resources/sap-ui-core.js"
...(other attributes)...
>
< / script >
< / head >
`}
2024-12-26 16:51:25 +00:00
< / CodeBlock >
2025-01-06 02:51:20 +00:00
:::
**UI5 Module**
The [SheetJS Standalone scripts ](/docs/getting-started/installation/standalone )
comply with AMD `define` semantics. They support `sap.ui.define` out of the box.
If the SheetJS Standalone script is saved to `webapp/xlsx.full.min.js` , the base
script `webapp/index.js` can load the library at `./xlsx.full.min` .
```js title="webapp/index.js"
sap.ui.define([
// highlight-next-line
"./xlsx.full.min", // relative path to script, without the file extension
/* ... other libraries ... */
], function(
// highlight-next-line
_XLSX // !! NOTE: this is not XLSX! A different variable name must be used
/* ... variables for the other libraries ... */,
) {
// highlight-next-line
alert(XLSX.version); // use XLSX in the callback
});
```
2024-12-26 16:51:25 +00:00
2025-01-06 02:51:20 +00:00
:::info pass
In some deployments, the function argument was `undefined` .
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.
:::
2024-12-26 16:51:25 +00:00
## Internal State
The various SheetJS APIs work with various data shapes. The preferred state
depends on the application.
### JSON Model
The OpenUI5 JSON Model provides ideal integration for spreadsheet data, enabling direct UI control binding.
#### State
The example [presidents sheet ](https://docs.sheetjs.com/pres.xlsx ) 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:
< table >
< thead > < tr > < th > Spreadsheet< / th > < th > State< / th > < / tr > < / thead >
< tbody > < tr > < td >
< / td > < td >
```js
[
{ 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 }
]
```
< / td > < / tr > < / tbody > < / table >
2025-01-06 02:51:20 +00:00
The OpenUI5 `JSONModel` [^2] is a client-side model implementation that stores data as JSON.
2024-12-26 16:51:25 +00:00
Here's a basic example of initializing a model, with a more complete implementation shown later:
< Tabs groupId = "lang" >
< TabItem name = "JS" value = "JavaScript" >
```js
sap.ui.define(["sap/ui/model/json/JSONModel"], function (JSONModel) {
const oModel = new JSONModel({ presidents: [] });
});
```
< / TabItem >
< / Tabs >
#### Updating State
The SheetJS [`read` ](/docs/api/parse-options ) and [`sheet_to_json` ](/docs/api/utilities/array#array-output )
functions simplify state updates. OpenUI5's JSONModel provides powerful two-way data binding
capabilities.
```mermaid
flowchart LR
url[(Remote\nFile)]
ab[(Data\nArrayBuffer)]
wb(SheetJS\nWorkbook)
ws(SheetJS\nWorksheet)
aoo(array of\nobjects)
model((JSON\nModel))
url --> |fetch\n\n| ab
ab --> |read\n\n| wb
wb --> |wb.Sheets\nselect sheet| ws
ws --> |sheet_to_json\n\n| aoo
aoo --> |setProperty\nfrom model| model
```
< Tabs groupId = "lang" >
< TabItem name = "JS" value = "JavaScript" >
```js
_loadExcelFile: async function () {
/* Download from https://docs.sheetjs.com/pres.xlsx */
const f = await (await fetch("https://docs.sheetjs.com/pres.xlsx")).arrayBuffer();
// highlight-start
/* 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);
// highlight-end
}
```
< / TabItem >
< / Tabs >
#### Rendering Data
2025-01-06 02:51:20 +00:00
In OpenUI5, the `Model-View-Controller` [^3] pattern is used to organize code and separate concerns.
2024-12-26 16:51:25 +00:00
The view defines the UI structure, the controller handles the logic, and the model manages the data.
```xml title="Example XML for displayin 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 >
<!-- ColumnItem template defines how each row should be rendered -->
// highlight-start
< items >
< ColumnItem >
< cells >
< Text text = "{Name}" / >
< Text text = "{Index}" / >
< / cells >
< / ColumnItem >
< / items >
// highlight-end
< / Table >
< / Page >
< / mvc:View >
```
#### Exporting Data
The [`writeFile` ](/docs/api/write-options ) and [`json_to_sheet` ](/docs/api/utilities/array#array-of-objects-input )
functions simplify exporting data. They are typically used in event handlers attached to buttons or other elements.
A button press handler can generate a local file when clicked:
```mermaid
flowchart LR
state((oModel\ngetProperty))
ws(SheetJS\nWorksheet)
wb(SheetJS\nWorkbook)
file[(XLSX\nexport)]
state --> |json_to_sheet\n\n| ws
ws --> |book_new\nbook_append_sheet| wb
wb --> |writeFile\n\n| file
```
```js
/* get model data and export to XLSX */
onExport: function() {
const data = this.getView().getModel().getProperty("/presidents");
/* generate worksheet from model data */
// highlight-next-line
const ws = XLSX.utils.json_to_sheet(data);
/* create workbook and append worksheet */
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "Data");
/* export to XLSX */
XLSX.writeFileXLSX(wb, "SheetJSOpenUI5AoO.xlsx");
}
```
#### 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:
```xml title="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 = "Value" / >
< / 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 >
```
```js title="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 when component is initialized */
this._loadExcelFile();
},
_loadExcelFile: async function () {
const f = await (await fetch("https://docs.sheetjs.com/pres.xlsx")).arrayBuffer();
const wb = XLSX.read(f);
const ws = wb.Sheets[wb.SheetNames[0]];
const data = XLSX.utils.sheet_to_json(ws);
this.getView().getModel().setProperty("/presidents", data);
},
onExport: function () {
const data = this.getView().getModel().getProperty("/presidents");
const ws = XLSX.utils.json_to_sheet(data);
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "Data");
XLSX.writeFileXLSX(wb, "SheetJSOpenUI5AoO.xlsx");
},
});
}
);
```
< details open >
< summary > < b > How to run the example< / b > (click to hide)< / summary >
:::note Tested Deployments
This demo was tested in the following environments:
| OpenUI5 | generator-easy-ui5 | Date |
|:--------|---------|---------|
| `1.131.1` | `3.8.1` | 2024-12-24 |
:::
1) Create a new site:
```bash
npm i -g generator-easy-ui5
npx yo easy-ui5 app
```
:::note Use the provided defaults:
- Application id: `sheetjs.openui5`
- Framework: `OpenUI5`
- Version: `1.131.1`
- Author: `SheetJS`
- Create new directory: `Y`
- Initialize git: `N`
:::
2) Install the dependencies and start server:
```bash
cd sheetjs.openui5
npm install
npm start
```
3) Open a web browser and access the displayed URL (`http://localhost:8080`)
4) Add SheetJS to your project by including this script tag in `webapp/index.html` :
< CodeBlock language = "html" value = "html" title = "webapp/index.html" >
{`< script src = "https://cdn.sheetjs.com/xlsx-${current}/package/dist/xlsx.full.min.js" > < / script > `}
< / CodeBlock >
5) Replace `webapp/view/Main.view.xml` with the complete implementation above.
6) Replace `webapp/controller/Main.controller.js` with the complete implementation above.
The page will refresh and show a table with an Export button. Click the button and the page will
attempt to download `SheetJSOpenUI5AoO.xlsx` .
7) Build the site:
```bash
npm run build
```
The generated site will be placed in the `dist` folder.
8) Start a local web server:
```bash
npm run start:dist
```
Access the displayed URL (typically http://localhost:8080) with a web browser and test the page.
When the page loads, the app will fetch https://docs.sheetjs.com/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.
< / details >
### 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.
2025-01-06 02:51:20 +00:00
However, this does not handle merge cells[^4] well!
2024-12-26 16:51:25 +00:00
The [`sheet_to_html` ](/docs/api/utilities/html#html-table-output ) function generates HTML that is aware of merges and other worksheet
2025-01-06 02:51:20 +00:00
features. Using OpenUI5's `core:HTML` [^5] control, we can render this HTML directly. During export, we extract the table element from the
2024-12-26 16:51:25 +00:00
rendered HTML and use [`table_to_book` ](/docs/api/utilities/html#html-table-input ) to create a workbook that maintains all the worksheet
features.
2025-01-06 02:51:20 +00:00
In this example, the component directly renders the HTML table in the model through OpenUI5's `core:HTML` [^5] control. For export, we extract
2024-12-26 16:51:25 +00:00
the inner table from the rendered HTML using `getElementsByTagName("table")[1]` , then pass it to [`table_to_book` ](/docs/api/utilities/html#html-table-input )
to create a workbook that preserves all features.
```xml title="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 content = "{/tableHTML}" / >
< Button text = "Export XLSX" press = ".onExport" / >
< / content >
< / Page >
< / mvc:View >
```
```js title="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 when component is initialized */
this._loadExcelFile();
},
_loadExcelFile: async function () {
const f = await (await fetch("https://docs.sheetjs.com/pres.xlsx")).arrayBuffer();
const wb = XLSX.read(f); // parse the array buffer
const ws = wb.Sheets[wb.SheetNames[0]]; // get the first worksheet
const opts = {
header: `<table id="excel-table">` ,
footer: `</table>`
};
const tableHTML = XLSX.utils.sheet_to_html(ws, opts); // generate HTML
this.getView().getModel().setProperty("/tableHTML", tableHTML); // update state
},
/* get live table and export the XLSX */
onExport: function () {
const tableHTML = this.getView().getModel().getProperty("/tableHTML"); // get HTML string from the model
const div = document.createElement("div"); // create temporary div to parse HTML
div.innerHTML = tableHTML; // insert HTML into div
const table = div.getElementsByTagName("table")[1]; // get inner table (bypasses outer wrapper)
const wb = XLSX.utils.table_to_book(table); // convert table element to workbook
XLSX.writeFileXLSX(wb, "SheetJSOpenUI5HTML.xlsx");
},
});
}
);
```
< details open >
< summary > < b > How to run the example< / b > (click to hide)< / summary >
:::note Tested Deployments
This demo was tested in the following environments:
| OpenUI5 | generator-easy-ui5 | Date |
|:--------|---------|---------|
| `1.131.1` | `3.8.1` | 2024-12-24 |
:::
1) Create a new site:
```bash
npm i -g generator-easy-ui5
npx yo easy-ui5 app
```
:::note Use the provided defaults:
- Application id: `sheetjs.openui5`
- Framework: `OpenUI5`
- Version: `1.131.1`
- Author: `SheetJS`
- Create new directory: `Y`
- Initialize git: `N`
:::
2) Install the dependencies and start server:
```bash
cd sheetjs.openui5
npm install
npm start
````
3) Open a web browser and access the displayed URL (`http://localhost:8080`)
4) Add SheetJS to your project by including this script tag in `webapp/index.html` :
< CodeBlock language = "html" value = "html" title = "webapp/index.html" >
{`< script src = "https://cdn.sheetjs.com/xlsx-${current}/package/dist/xlsx.full.min.js" > < / script > `}
< / CodeBlock >
5) Replace `webapp/view/Main.view.xml` with the complete implementation above.
6) Replace `webapp/controller/Main.controller.js` with the complete implementation above.
The page will refresh and show a table with an Export button. Click the button and the page will
attempt to download `SheetJSOpenUI5HTML.xlsx` .
7) Build the site:
```bash
npm run build
```
The generated site will be placed in the `dist` folder.
8) Start a local web server:
```bash
npm run start:dist
```
Access the displayed URL (typically http://localhost:8080) with a web browser and test the page.
When the page loads, the app will fetch https://docs.sheetjs.com/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.
< / details >
2025-01-06 02:51:20 +00:00
[^1]: See ["ECMAScript Support" ](https://sdk.openui5.org/topic/0cb44d7a147640a0890cefa5fd7c7f8e.html#loio0cb44d7a147640a0890cefa5fd7c7f8e/section_UI5Mod ) for more details about OpenUI5 compatibility.
[^2]: See [`JSONModel` ](https://sdk.openui5.org/1.38.62/docs/api/symbols/sap.ui.model.json.JSONModel.html ) in the OpenUI5 documentation.
[^3]: See OpenUI5's [MVC Documentation ](https://sdk.openui5.org/topic/91f233476f4d1014b6dd926db0e91070 ) for detailed explanation of the pattern implementation.
[^4]: See ["Merged Cells" in "SheetJS Data Model" ](/docs/csf/features/merges ) for more details.
[^5]: See [`core:HTML` ](https://sdk.openui5.org/1.38.62/docs/api/symbols/sap.ui.core.HTML.html ) in the OpenUI5 documentation.
