--- title: Sheets in UI5 Sites sidebar_label: OpenUI5 / SAPUI5 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'; [OpenUI5](https://openui5.org/) is a JavaScript framework for building enterprise-ready web applications. It is compatible with the SAPUI5 framework. [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. :::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". ::: ## Installation 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. **HTML** 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: {`\ `} 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: {`\ UI5 Walkthrough `} ::: **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 }); ``` :::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. ::: ## 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:
SpreadsheetState
![`pres.xlsx` data](pathname:///pres.png) ```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 } ] ```
The OpenUI5 `JSONModel`[^2] is a client-side model implementation that stores data as JSON. Here's a basic example of initializing a model, with a more complete implementation shown later: ```js sap.ui.define(["sap/ui/model/json/JSONModel"], function (JSONModel) { const oModel = new JSONModel({ presidents: [] }); }); ``` #### 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 ``` ```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 } ``` #### Rendering Data In OpenUI5, the `Model-View-Controller`[^3] 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. ```xml title="Example XML for displayin array of objects"
// highlight-start // highlight-end
``` #### 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"