Introducing Deep Export for Multi-Level Data Extraction in UI5 and Fiori Elements

[Marian_Zeis]

1. Why Did I Create That?

When working with UI5 and Fiori Elements applications, the default options for exporting data to a spreadsheet may not be flexible enough for your needs. You might require special formatting, custom logic, or the ability to handle complex datasets that include multiple related entities. This is where the Spreadsheet Importer comes in. It’s a UI5-based solution that enhances your data handling capabilities beyond standard exports, allowing you to retrieve and organize extensive multi-level data structures with ease.

For example, consider a scenario with Orders, their associated OrderItems, and possibly ShippingInfos. With the Spreadsheet Importer’s deep export feature, you can pull all of this interconnected data in one go. This comprehensive approach makes it simple to review, refine, and then reimport updates, delivering a more efficient and controlled data management workflow.

2. How Do You Use It?

Using the deep download feature is straightforward. All configuration and usage details are documented here:

https://docs.spreadsheet-importer.com/pages/spreadsheetdownload/

In essence, you enable the deep export configuration in the component data and define how many levels of sub-entities you want to include, along with which columns and properties should appear in your exported spreadsheet.

Note: This new feature is experimental and currently only available for OData V4. If you deep download data, the OData Service must support $expand.

In a Fiori Elements List Report Extension, the code can look like this:

this.spreadsheetUpload = await this.editFlow
  .getView()
  .getController()
  .getAppComponent()
  .createComponent({
    usage: 'spreadsheetImporter',
    async: true,
    componentData: {
      context: this,
      action: 'UPDATE',
      createActiveEntity: true,
      i18nModel: this.getModel('i18n'),
      debug: true,
      showDownloadButton: true,
      deepDownloadConfig: {
        deepLevel: 1,
        deepExport: false,
        addKeysToExport: true,
        showOptions: true,
        filename: 'Orders123',
        columns: {
          OrderNo: {
            order: 1,
          },
          buyer: {
            order: 3,
          },
          Items: {
            quantity: {
              order: 2,
            },
            title: {
              order: 4,
            },
          },
          Shipping: {
            address: {
              order: 5,
            },
          },
        },
      },
    },
  });
this.spreadsheetUpload.triggerDownloadSpreadsheet();

This example shows the configuration options available:

• deepLevel: How many levels should the export go into the entities? If the value is ‘0’, only the main entity is exported.
• deepExport: This activates or deactivates the deep export. When switched off, only the main entity is exported.
• addKeysToExport: Normally, fields marked as ‘hidden’ (like GUIDs) are not included. With this setting, they are included, which is also important for a later re-import when data is updated.
• filename: The name of the Excel file when it is exported.
• columns: An object that determines which columns are exported. In debug mode, all possible columns are logged in the console.
• showOptions: Allows changing some settings via a dialog when the export is triggered.

Excel File can then look like this:

2.1 How to Trigger the Export

2.1.2. Directly in Code

You can easily trigger the export directly in code by using the triggerDownloadSpreadsheet method. With this method, it’s also possible to pass other configuration parameters. The settings dialog can be displayed here as well.

this.spreadsheetUpload.triggerDownloadSpreadsheet({
  deepLevel: 1,
  deepExport: true,
  addKeysToExport: true,
  showOptions: true,
  filename: 'Orders_TEST',
  columns: {
    OrderNo: {
      order: 1,
    },
    buyer: {
      order: 3,
    },
    Items: {
      quantity: {
        order: 2,
      },
    }
  },
});

2.1.2 In the Spreadsheet Upload Dialog

With the ‘showDownloadButton’ option, the export button is displayed directly in the upload dialog. You can try this in the Live Demo for my OData V4 Fiori Elements List Report Sample:

https://livedemo.spreadsheet-importer.com/launchpad.html?sap-language=EN#ordersv4fe-120

2.1.3. Without Code in the XML View as a Button

For uploading, you can also integrate the button directly into the XML view without any JavaScript code. This is also available for exporting and could then look like this:

<core:ComponentContainer
  id="test2"
  width="100%"
  usage="spreadsheetImporter"
  propagateModel="true"
  async="true"
  settings="{
    componentContainerData:{uploadButtonPress:'uploadButtonPress',buttonText:'Excel Download with Dialog',buttonId:'downloadButtonDialog',buttonIcon:'sap-icon://download',downloadButton:true},
    deepDownloadConfig:{
      deepLevel: 2,
      deepExport: true,
      addKeysToExport: true,
      showOptions: true,
      filename: 'Orders_Dialog',
      columns : {
        'OrderNo':{
          'order': 1
        },
        'buyer': {
          'order': 3
        },
        'Items': {
          'quantity' : {
            'order': 2
          }
        },
        'Shipping': {
          'address' : {
            'order': 5
          }
        }
      }
    }
  }"
/>

You can check out the sample in my live demo here:

https://livedemo.spreadsheet-importer.com/launchpad.html?sap-language=EN#ordersv4freestyle-120

The code for this app can be found here.
The Documentation for it is here.

3. Events for Interjecting into the Process

You can manipulate the data at various steps. Two events give you the opportunity to influence the final result.

3.1. Event beforeDownloadFileProcessing

This event is fired before the data is converted to a spreadsheet file. Use this event to manipulate the data before conversion. You can change the data parameter of the event directly, as this is a reference.

Documentation: https://docs.spreadsheet-importer.com/pages/Events/#event-beforedownloadfileprocessing

onDownload: async function () {
    // init your spreadsheet upload component
    this.spreadsheetUpload.attachBeforeDownloadFileProcessing(this.onBeforeDownloadFileProcessing, this);
    this.spreadsheetUpload.triggerDownloadSpreadsheet();
},
onBeforeDownloadFileProcessing: function (event) {
    const data = event.getParameters().data;
    // change buyer of first row of the root entity
    data.$XYZData[0].buyer = "Customer 123";
    // change quantity of first row of the Items entity
    data.Items.$XYZData[0].quantity = 4
}

3.2. Event beforeDownloadFileExport

This event is fired just before the file is downloaded. Use this event to manipulate the filename or other parameters before the file is downloaded.

The great flexibility here is that you can use the workbook object of SheetJS and all possible APIs before it is downloaded.

Documentation: https://docs.spreadsheet-importer.com/pages/Events/#event-beforedownloadfileexport

onDownload: async function () {
    // init your spreadsheet upload component
    this.spreadsheetUpload.attachBeforeDownloadFileExport(this.onBeforeDownloadFileExport, this);
    this.spreadsheetUpload.triggerDownloadSpreadsheet();
},
onBeforeDownloadFileExport: function (event) {
    const workbook = event.getParameters().workbook;
    event.getParameters().filename = filename + "_modified";
}

4. Try It in the Live Demo

If you want to see the deep download feature in action before integrating it into your own application, you can test it out in our live demo environment:

https://livedemo.spreadsheet-importer.com/launchpad.html#ordersv4freestyle-120

This demo showcases how to trigger a deep export, configure columns, and retrieve deeply nested data structures from the backend. This is an OData V4 Freestyle App with UI5 Version 1.120.

5. How Does It Work Internally?

Under the hood, the deep download feature relies on OData V4’s $expand capabilities to fetch nested entities in a single request. The Spreadsheet Importer constructs a metadata model that maps out all related entities and their navigation properties. It then dynamically builds a set of expands to retrieve all linked data efficiently.

To dive deeper into the technical details and understand the internals, please visit:

https://docs.spreadsheet-importer.com/pages/Development/SpreadsheetDownload/

There you will find comprehensive documentation on metadata handling, batch fetching, and the steps involved in generating the final spreadsheet.

6. e2e Tests

Of course, no new feature can be developed without testing.
The reliable wdi5 comes into play again for this, and it can also be used to simply check the download of Excel files. Currently, only UI5 version 1.120 is being tested. Other versions will follow.
Various scenarios are tested, including starting from exporting the code and from the button in the XML view.

Here is a run of the test: https://github.com/spreadsheetimporter/ui5-cc-spreadsheetimporter/actions/runs/12206743460/job/34056... 
Here is the test file for wdi5 that runs from now on every PR: https://github.com/spreadsheetimporter/ui5-cc-spreadsheetimporter/blob/main/examples/test/specs/down... 

7. What Also Happened in the Last Versions (Since Version 1.0)

The Spreadsheet Importer has been actively developed and improved over time. Since the release of version 1.0, several changes and enhancements have been introduced:

• New Configuration excludeColumns: You can specify columns to exclude from the import and the template instead of specifying all columns you want.
• New event preFileProcessing: An event fired right after the file is uploaded to the app and before further processing.
• New LLM-translated language: Dutch
• Drag & Drop support: Easily drag your spreadsheet file onto the upload dialog.
• Custom i18n text overwrites: All translated texts can now be overwritten by providing your own i18n model, allowing more flexibility in localization.

For the full list of changes, please refer to our detailed Changelog.

8. What Is Happening in the Future?

The journey doesn’t end here. Future improvements include:

• Support OData V2 for Deep Export: Currently, only OData V4 is supported.
• Update Support: Currently, only Create is possible via the spreadsheet importer. We plan to enable full Update capabilities so you can round-trip your data effortlessly. The data export is a prerequisite for making updates easily possible.
• Support Deep Insert: This is a major challenge, but one of my main goals is to integrate this functionality.

Stay tuned for upcoming releases.

Previous Announcements & Background

For those who missed it, we celebrated the version 1.0 release of the UI5 Spreadsheet Importer earlier this year. The response has been tremendous, and we extend our thanks to everyone who contributed, tested, and provided valuable feedback.

If you have any questions and suggestions, feel free to reach out at marian@marianzeis.de.

For more background and feature highlights, you can read our previous blog post and check out:

• Landing Page: https://spreadsheet-importer.com/
• GitHub Repo: https://github.com/spreadsheetimporter/ui5-cc-spreadsheetimporter