Mass Data Updates from Excel in UI5 and Fiori elements with this Open Source Project

[Marian_Zeis]

1. What Is the UI5 Spreadsheet Importer?

The UI5 Spreadsheet Importer is an open-source custom control that allows you to upload and download data in Excel files directly to and from SAPUI5/OpenUI5 applications. It automatically maps columns to OData properties, handles validation, and supports large datasets through batching. Whether you’re using SAPUI5 on-premise, SAP BTP, or even OpenUI5, this control simplifies Excel-based data maintenance. Its flexibility extends to various backend OData versions, enabling you to integrate with different systems without modifying the backend.

Key advantages include:

• Versatile OData support: Works seamlessly with OData V2 and V4 (the new Update feature is currently only for V4).
• Wide UI5 compatibility: Compatible with multiple UI5 versions, including older long-term maintenance ones.
• Easy setup: Allows zero-configuration usage if you simply need to upload data without customizing anything.
• Extensive configuration: Offers many settings for advanced scenarios, from field validation to translations and more.

This control is also used in real production scenarios to speed up data upload and reduce manual input errors. Many organizations rely on Excel as a data-handling tool, and the UI5 Spreadsheet Importer helps them bridge the gap between the Excel environment and SAP UI5 apps.

2. Introducing the New Update Feature

Up to this point, the UI5 Spreadsheet Importer primarily focused on creating new records. However, in real-world applications, updating existing data is just as important. The new Update feature covers exactly this need with the just released version v1.7.2

With the Update capability, you can:

• Export records from your UI5 application into an Excel spreadsheet.
• Modify the data in Excel, taking advantage of Excel’s familiar interface.
• Re-upload the changed data to your SAPUI5/OpenUI5 application to update the records in the backend.

This feature is especially beneficial in scenarios where you have to correct, adjust, or enrich multiple records at once. By leveraging Excel’s powerful manipulation tools, you can reduce repetitive tasks and expedite changes.

3. Upsides and Downsides of the Update Feature

Upsides:

• Improves data maintenance by enabling mass updates, which saves time over manual row-by-row editing.
• Works in tandem with the spreadsheet exporter, letting you download existing data (including keys) for a smooth round-trip.
• Defaults to partial updates (changing only modified properties), with an option to switch to full updates if necessary.
• Integrates with OData Draft logic, detecting if an object is in active or draft mode and adapting the update process accordingly.

Downsides / Considerations:

• Experimental: Currently, the update feature only works for OData V4 and is marked as experimental. More stability improvements are coming soon.
• Because it must retrieve existing records first, performance is lower than a simple create operation. The process also includes additional checks around draft states.
• If a mismatch between your Excel file’s IsActiveEntity column and the object’s real state occurs, the system will generate warnings or ask for confirmation.

4. Sample Usage – Including a Fiori elements List Report Example

To try out the update feature, the recommended approach is:

1. Download data (with keys) to an Excel file.
2. Modify the desired fields in Excel.
3. Use the UI5 Spreadsheet Importer to re-upload the file, thereby updating those records.

You can see an example in our Live Demo here: Live Demo for Orders (V4 Freestyle).

A sample setup with CAP and Fiori Elements here: GitHub: UI5 Spreadsheet Importer CAP/Fiori elements Update 

Below is a simple code snippet that demonstrates how to configure the UI5 Spreadsheet Importer to perform updates instead of creates. You only need to change the "action" property.

this.spreadsheetUploadUpdate = await this.editFlow.getView().getController().getAppComponent()
.createComponent({
    usage: "spreadsheetImporter",
    async: true,
    componentData: {
        context: this,
        tableId: "ui.v4.ordersv4fe::OrdersObjectPage--fe::table::Items::LineItem-innerTable",
        action: "UPDATE"
    }
});

The complete Documenation is here: Docs: Update

Below is a more detailed example showing how to integrate the UI5 Spreadsheet Importer in a Fiori elements List Report. Here, you typically configure everything through a manifest.json and a small spreadsheet.js extension.

manifest.json (excerpt):

{
  "sap.ui5": {
    "componentUsages": {
      "spreadsheetImporter": {
        "name": "cc.spreadsheetimporter.v1_7_2"
      }
    },
    "resourceRoots": {
      "cc.spreadsheetimporter.v1_7_2": "./thirdparty/customcontrol/spreadsheetimporter/v1_7_2"
    },
    "routing": {
      "targets": {
        "BooksList": {
          "type": "Component",
          "id": "BooksList",
          "name": "sap.fe.templates.ListReport",
          "options": {
            "settings": {
              "contextPath": "/Books",
              "controlConfiguration": {
                "@com.sap.vocabularies.UI.v1.LineItem": {
                  "actions": {
                    "update": {
                      "press": "book.ext.spreadsheet.spreadsheet.update",
                      "text": "Update Excel"
                    },
                    "download": {
                      "press": "book.ext.spreadsheet.spreadsheet.download",
                      "text": "Download Excel"
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

spreadsheet.js (controller extension for the List Report):

sap.ui.define([
    "sap/m/MessageToast"
], function(MessageToast) {
    'use strict';

    return {
        update: async function() {
            // Create component for update
            this.spreadsheetUploadUpdate = await this.editFlow.getView().getController().getAppComponent()
            .createComponent({
                usage: "spreadsheetImporter",
                async: true,
                componentData: {
                    context: this,
                    debug: true,
                    action: "UPDATE",
                    tableId: "book::BooksList--fe::table:‌‌:LineItem-innerTable",
                    columns: ["ID", "title", "description", "price", "stock", "author"]
                }
            });
            this.spreadsheetUploadUpdate.openSpreadsheetUploadDialog();
        },
        download: async function() {
            // Create component for download
            this.spreadsheetDownload = await this.editFlow.getView().getController().getAppComponent()
            .createComponent({
                usage: "spreadsheetImporter",
                async: true,
                componentData: {
                    context: this,
                    tableId: "book::BooksList--fe::table:‌‌:LineItem-innerTable"
                }
            });
            this.spreadsheetDownload.triggerDownloadSpreadsheet({
                deepLevel: 0,
                addKeysToExport: true,
                showOptions: false,
                columns: {
                    "ID": {},
                    "title": {},
                    "description": {},
                    "price": {},
                    "stock": {},
                    "author": {}
                }
            });
        }
    };
});

5. Behind the Scenes: How the Update Feature Works

When you click the upload button, the UI5 Spreadsheet Importer follows these steps internally:

• It fetches the current records from the backend to confirm states like draft or active. This is done by generating OData filters for all keys in your Excel file, querying both IsActiveEntity = true and IsActiveEntity = false (in the case of a draft-enabled service).
• It compares the object state in your Excel data with what the service returns. If there is a mismatch, a warning appears. The user can decide to continue with an adjusted state or stop the process.
• Each row that truly exists and has changes is updated. By default, only changed properties are sent to the server, minimizing the payload. If fullUpdate is set to true, then all columns from the spreadsheet are sent even if they weren’t changed.
• To prevent overloads, the system batches updates in groups of 100 rows at a time, performing multiple sequential requests if necessary.

Once the updates complete, your UI5 application typically refreshes its model bindings, immediately reflecting all changes. This process offers an efficient way to maintain large data sets without manual clicks in table rows.

If you have any questions or suggestions on the Update feature, please share them via GitHub Issues. Your feedback helps shape upcoming improvements, including broader OData V2 support, deeper draft handling, and extended performance options.