Excel is and will remain for a long time one of the most important tools for companies. It is not for nothing that it is called the Swiss army knife of business tools.
Therefore, it is also very important to integrate Excel into SAP.
This alone shows the large number of blog posts in the SAP community.
Accordingly, there are also open source projects that specialize in integration. The most popular one for ABAP is by far abap2xlsx.
Even if the adaptation is difficult and copy paste code from ten year old blog posts is preferred again and again, it is the most popular ABAP open source project on Github after abapGit with over 500 stars on GitHub.
For the frontend, the integration in UI5 was already completed years ago with the Excel download from tables.
Unfortunately, the possibility to upload Excel files easily is missing here.
UI5 Excel Upload Custom Control
Here the Custom Control UI5 Excel Upload fills the gap and offers a plug and play possibility to accomplish the demand as an Open Source Custom Control.
To ensure universal integration, this does not send the file itself to the backend, but reads the data from the Excel file and uses the standard UI5 APIs.
This allows for simplified use in Fiori Elements App, but can also be used in all other UI5 scenarios, including RAP and CAP Backends.
Getting Started
Since the control is open source, the complete code and examples can be viewed on GitHub:
https://github.com/marianfoo/ui5-cc-excelUpload
Another focus is as simple as possible use and implementation by the developer.
By using the Application Generator from SAP and the generator from the Excel Upload Control, a UI5 app with Excel upload function can be generated without writing a line of code and in under 2 minutes.
An example of this is this short video that shows the creation of an app, the integration of the Excel Upload Control and the upload of an Excel file.
The official documentation and infos how to start with it can be found here:
https://marianfoo.github.io/ui5-cc-excelUpload/
Features
The focus is currently still on universal integration, but nevertheless there are already some features that can be used. The following features are available:
– Automatic generation of a template file for later upload
– define the columns to be uploaded and displayed in the template file
– define columns that should be present at least
– automatic activation of draft versions (e.g. in the list report)
– Event: check the data when uploading the file and display errors (e.g. check if a price exceeds a threshold)
– Event: manipulate data before sending it to the backend (e.g. round numbers)
All options and events can be found in the documentation
What features are planned for the future can be found here:
https://github.com/users/marianfoo/projects/2
Of course, you can also continue to make suggestions.
Supported Scenarios and Versions
The component is also tested in many scenarios and UI5 versions with wdi5 (currently 11 scenarios!).
Currently the scenarios Fiori Elements V2/V4 Draft and non draft are tested with the current Long-term Maintenance versions (108,96,84,71).
An overview of all versions can be found here: Supported Versions
Since the component is tested with wdi5, it also needs apps.
The example apps can be found in the GitHub repo under examples.
It also supports the new version of UI5 Tooling V3 with which the initial configuration is easier.
wdi5 Tests with GitHub Actions
Central / Decentral Deployment
The basic implementation behind it is a reuse component.
This results in two different deployment scenarios in the Fiori Launchpad scenario.
It is possible to deploy the component directly with the app itself, i.e. “in-app”.
However, the component can also be deployed directly, e.g. on an on-premise system, and then used by individual apps. More about the central deployment here: Central Deployment
Multi Version Namespace
Another feature is the Multi Version Namespace, which allows to use single versions of the Excel Upload Control. This is especially useful in a central deployment, as each app can decide for itself which version it wants to use, and thus when updating the Excel Upload Reuse component, not every app that uses it needs to be tested.
From a technical point of view, the namespace is marked with a placeholder during development and is then replaced with the current version during publish, which can then be consumed via npm.
How it works
Even though the name “Custom Control” is somewhat misleading, the basis is a reusable component. The advantages are the generation of preload files, i18n, and deployment as a central component.
As described in the documentation, it is important that the context is provided. Only then can the correct table be found in the view and the UI5 APIs be accessed to send the data to the backend.
A UI5 table is then searched for in the view in order to access the binding. If multiple tables are found, they must be defined in the options.
With the binding also the metadata can be read to know which columns should be displayed in the template and with which label.
For reading the data from the Excel file and generating the template, the library SheetJS is used.
After reading the data is available as an array. In addition, the data is converted into the correct format (i.e. Excel Date to Edm.Date).
This payload can be sent to the backend with the ODataModel API, depending on the OData version either with create or createEntry.
Feedback
This is an open source project in which everyone can contribute and help.
Helping is also very easy, just try the Excel Upload Control and test it for your use cases.
Feedback can also be given to the documentation if something is unclear.
Of course also further ideas which features can be implemented.
I would be very happy to receive feedback.
My preferred way is via Github issues, but as an alternative you can reach me via email, twitter, mastodon or linkedin.
You also more then welcome to leave a comment here in the blog
Conclusion
In conclusion, Excel is a critical tool for many companies, and integrating it with SAP can be a challenge. The UI5 Excel Upload Custom Control offers a plug and play solution that simplifies the process of uploading Excel files to SAP. It’s an open source project with many useful features, including the ability to generate a template file, define columns to upload and display, and check data for errors. The control has been tested in many scenarios and UI5 versions, and it can be deployed either directly with the app or as a central component. With the Multi Version Namespace feature, each app can decide which version of the control to use, making updates easier. Overall, the UI5 Excel Upload Custom Control is a powerful tool that can make integrating Excel with SAP much simpler and more efficient.
Further Links
