Excel remains one of the most critical and widely used tools in the enterprise world, thanks to its versatility and flexibility. Therefore, whenever building new solutions, it is essential to integrate spreadsheet files into digital processes, especially if Excel cannot be fully eliminated.
To simplify this process, I have developed an open-source component that enables you to create entries from spreadsheet files, called UI5 Spreadsheet Importer, and more recently, also allows you to update multiple entries in bulk. The big advantage of this component is that it can be integrated into standard SAP applications. I have previously demonstrated how to do this with the “Manage Banks” application in an earlier blog post (How to add Excel Upload to any Standard SAP Fiori App (tutorial with App “Manage Banks”).
In the meantime, many updates have been introduced both to the Spreadsheet Importer component and to how adaptation projects are handled with the Fiori tools. Currently, the UI5 Spreadsheet Importer supports the mass update feature only with OData V4. Therefore, in this blog post, we will use the new “Manage Banks” app, which uses an OData V4 service. Please note that creating new records works with OData V2 and the spreadsheet importer as well.
Deploy Spreadsheet Importer
To use the component, it must be deployed to the ABAP stack. This process has also improved since the last blog post and is documented here.
You can clone this GitHub repository and follow the instructions in the README.md. This repository contains a UI5 application that allows you to import all versions of the Spreadsheet Importer into your system. After deployment, you can reuse the Spreadsheet Importer component in any UI5 apps and adaptation projects.
Create Adaptation Project
The complete example described here can be found in a my GitHub repository.
The creation of adaptation projects has also been improved in the last two years. You can find an excellent tutorial here, and the official documentation is available here .
Here is how it can look like:
Add the Component
Adaptation projects can now also be created in VS Code. The following steps are shown in VS Code, but they do not differ much from SAP Business Application Studio. The general procedure is similar to the one in my previous blog post, although the UI might look a little different now.
First, right-click on the “webapp” folder and choose Add SAPUI5 Component Usages.
In the dialog, provide a Usage ID, for example “customer.upload”, and set the component name to the version deployed on your system, for instance “cc.spreadsheetimporter.v1_7_3”.
This process will create a file similar to id_1739737700495_addComponentUsages.change.
Adding Custom XML and Custom Controller
To add a button in the toolbar and an extension to trigger the Spreadsheet Importer with JavaScript, you can open the Adaptation Editor by right-clicking on “webapp” and selecting “Open Adaptation Editor”, or by running npm run start-editor from the terminal (as defined in your package.json).
Add Button to Action Toolbar
Hover over the table until it is highlighted with a blue frame, then use the editor’s button to add a new fragment to the Action Toolbar.
In the dialog, choose the “actions” aggregation with the fragment name “excelupload”. This creates a new XML file named “excelupload.fragment.xml” under the “fragments” folder, which you can now edit as needed.
This will create a change file and the XML Fragment.
Add Controller Extension
To open the Spreadsheet Importer, you need a Controller Extension. Again, hover over the table and click the button “Extend with Controller”.
Provide a name for the extension file, for example “excelupload“. A new file named “excelupload.js“ is generated under the “coding” folder, containing boilerplate code for extending the List Report with JavaScript.
Make sure to always save the changes. This is highlighted in the Adaption Editor:
This will create a change file and the javascript file.
Add JavaScript Method to Open Spreadsheet Importer
Next, add a method in the controller that will be invoked by our new button. This method calls the Spreadsheet Importer component using the usage ID “customer.upload” we defined earlier.
The Configuration Options are documented here, and these options allow you to tailor the component to your requirements.
In our example, we want to give users the ability to update data in mass. Therefore, we set the property action to “UPDATE”. The parameter tableId indicates which table to use for updates. Usually, the best approach is to download your existing data first, so that key fields for identification are automatically included in the spreadsheet. Hence, we enable a download button by setting showDownloadButton to true.
We also configure deepDownloadConfig properties:
deepLevel: 0, because we only need the root level entity.addKeysToExport: true, so that the spreadsheet will contain key fields for identification.setDraftStatus: true, to handle drafts properly if the OData V4 service uses draft features.showOptions: false, to skip any additional dialogs and immediately download the spreadsheet.
The final method looks like this:
async onUpload(event) {
const spreadsheetUploadComponent = await this.base.getAppComponent().createComponent({
usage: “customer.upload”,
async: true,
componentData: {
context: this,
action: “UPDATE”,
tableId: “bf.bk.bank.manage::BankList–fe::table::LineItem-innerTable”,
showDownloadButton: true,
deepDownloadConfig: {
deepLevel: 0,
addKeysToExport: true,
setDraftStatus: true,
showOptions: false
}
}
});
spreadsheetUploadComponent.openSpreadsheetUploadDialog();
}
Add Button to Call Method
Finally, we add a button in the XML fragment to call our onUpload method. An example XML fragment could look like this:
<core:FragmentDefinition xmlns:core=’sap.ui.core’ xmlns=’sap.m’>
<Button
id=”excelUpload”
text=”Excel Upload”
press=”.extension.customer.app.variant1.excelupload.onUpload”
/>
</core:FragmentDefinition>
Note that in the press event, the namespace of your extension must be included. For example, if your extension namespace is “customer.app.variant1” and the controller extension is named “excelupload”, the method is “onUpload”.
The Button now appears in the Toolbar:
Preview
You can now open the preview from the context menu (right-click on webapp and select “Preview Application”) or start it from the terminal with npm start. This will launch the application, and when you click on your new “Excel Upload” button, the Spreadsheet Importer dialog will appear.
From there, you can click “Download Data as Spreadsheet” to edit and then re-upload your data.
In this example, you might add a new value (like a BIC code) for all banks in the spreadsheet and upload it.
The “Show Preview” button lets you see the data before finalizing the upload.
Clicking the “Upload” button then updates the data in the background.
Further Changes
A quick note of caution: Not every app can be adapted with the Adaptation Project, and each standard app may behave differently. Also, for the update feature, the underlying standard app must use an OData V4 service. Creating new records, however, is also possible with OData V2. Simply change the action to “CREATE” or add a separate button for creation.
Additionally, there are many other configuration options and events you can leverage to customize the component to your specific needs.
Deployment
For deployment of the adaptation project, you can follow this documentation .
Further Links
Central Deployment of the Spreadsheet Importer GitHub Repo for Packed Deployment to ABAP Configuration Options for Spreadsheet Importer SAPUI5 Adaptation Tutorial Official Documentation for Adaptation Projects Deployment Instructions for Adaptation Projects
Excel remains one of the most critical and widely used tools in the enterprise world, thanks to its versatility and flexibility. Therefore, whenever building new solutions, it is essential to integrate spreadsheet files into digital processes, especially if Excel cannot be fully eliminated.To simplify this process, I have developed an open-source component that enables you to create entries from spreadsheet files, called UI5 Spreadsheet Importer, and more recently, also allows you to update multiple entries in bulk. The big advantage of this component is that it can be integrated into standard SAP applications. I have previously demonstrated how to do this with the “Manage Banks” application in an earlier blog post (How to add Excel Upload to any Standard SAP Fiori App (tutorial with App “Manage Banks”).In the meantime, many updates have been introduced both to the Spreadsheet Importer component and to how adaptation projects are handled with the Fiori tools. Currently, the UI5 Spreadsheet Importer supports the mass update feature only with OData V4. Therefore, in this blog post, we will use the new “Manage Banks” app, which uses an OData V4 service. Please note that creating new records works with OData V2 and the spreadsheet importer as well.Deploy Spreadsheet ImporterTo use the component, it must be deployed to the ABAP stack. This process has also improved since the last blog post and is documented here.You can clone this GitHub repository and follow the instructions in the README.md. This repository contains a UI5 application that allows you to import all versions of the Spreadsheet Importer into your system. After deployment, you can reuse the Spreadsheet Importer component in any UI5 apps and adaptation projects.Create Adaptation ProjectThe complete example described here can be found in a my GitHub repository.The creation of adaptation projects has also been improved in the last two years. You can find an excellent tutorial here, and the official documentation is available here .Here is how it can look like:Add the ComponentAdaptation projects can now also be created in VS Code. The following steps are shown in VS Code, but they do not differ much from SAP Business Application Studio. The general procedure is similar to the one in my previous blog post, although the UI might look a little different now.First, right-click on the “webapp” folder and choose Add SAPUI5 Component Usages.In the dialog, provide a Usage ID, for example “customer.upload”, and set the component name to the version deployed on your system, for instance “cc.spreadsheetimporter.v1_7_3”.This process will create a file similar to id_1739737700495_addComponentUsages.change.Adding Custom XML and Custom ControllerTo add a button in the toolbar and an extension to trigger the Spreadsheet Importer with JavaScript, you can open the Adaptation Editor by right-clicking on “webapp” and selecting “Open Adaptation Editor”, or by running npm run start-editor from the terminal (as defined in your package.json).Add Button to Action ToolbarHover over the table until it is highlighted with a blue frame, then use the editor’s button to add a new fragment to the Action Toolbar.In the dialog, choose the “actions” aggregation with the fragment name “excelupload”. This creates a new XML file named “excelupload.fragment.xml” under the “fragments” folder, which you can now edit as needed. This will create a change file and the XML Fragment. Add Controller ExtensionTo open the Spreadsheet Importer, you need a Controller Extension. Again, hover over the table and click the button “Extend with Controller”. Provide a name for the extension file, for example “excelupload”. A new file named “excelupload.js” is generated under the “coding” folder, containing boilerplate code for extending the List Report with JavaScript. Make sure to always save the changes. This is highlighted in the Adaption Editor:This will create a change file and the javascript file. Add JavaScript Method to Open Spreadsheet ImporterNext, add a method in the controller that will be invoked by our new button. This method calls the Spreadsheet Importer component using the usage ID “customer.upload” we defined earlier.The Configuration Options are documented here, and these options allow you to tailor the component to your requirements.In our example, we want to give users the ability to update data in mass. Therefore, we set the property action to “UPDATE”. The parameter tableId indicates which table to use for updates. Usually, the best approach is to download your existing data first, so that key fields for identification are automatically included in the spreadsheet. Hence, we enable a download button by setting showDownloadButton to true.We also configure deepDownloadConfig properties:deepLevel: 0, because we only need the root level entity.addKeysToExport: true, so that the spreadsheet will contain key fields for identification.setDraftStatus: true, to handle drafts properly if the OData V4 service uses draft features.showOptions: false, to skip any additional dialogs and immediately download the spreadsheet.The final method looks like this: async onUpload(event) {
const spreadsheetUploadComponent = await this.base.getAppComponent().createComponent({
usage: “customer.upload”,
async: true,
componentData: {
context: this,
action: “UPDATE”,
tableId: “bf.bk.bank.manage::BankList–fe::table::LineItem-innerTable”,
showDownloadButton: true,
deepDownloadConfig: {
deepLevel: 0,
addKeysToExport: true,
setDraftStatus: true,
showOptions: false
}
}
});
spreadsheetUploadComponent.openSpreadsheetUploadDialog();
} Add Button to Call MethodFinally, we add a button in the XML fragment to call our onUpload method. An example XML fragment could look like this: <core:FragmentDefinition xmlns:core=’sap.ui.core’ xmlns=’sap.m’>
<Button
id=”excelUpload”
text=”Excel Upload”
press=”.extension.customer.app.variant1.excelupload.onUpload”
/>
</core:FragmentDefinition> Note that in the press event, the namespace of your extension must be included. For example, if your extension namespace is “customer.app.variant1” and the controller extension is named “excelupload”, the method is “onUpload”.The Button now appears in the Toolbar: PreviewYou can now open the preview from the context menu (right-click on webapp and select “Preview Application”) or start it from the terminal with npm start. This will launch the application, and when you click on your new “Excel Upload” button, the Spreadsheet Importer dialog will appear.From there, you can click “Download Data as Spreadsheet” to edit and then re-upload your data. In this example, you might add a new value (like a BIC code) for all banks in the spreadsheet and upload it.The “Show Preview” button lets you see the data before finalizing the upload. Clicking the “Upload” button then updates the data in the background. Further ChangesA quick note of caution: Not every app can be adapted with the Adaptation Project, and each standard app may behave differently. Also, for the update feature, the underlying standard app must use an OData V4 service. Creating new records, however, is also possible with OData V2. Simply change the action to “CREATE” or add a separate button for creation.Additionally, there are many other configuration options and events you can leverage to customize the component to your specific needs.DeploymentFor deployment of the adaptation project, you can follow this documentation .Further LinksCentral Deployment of the Spreadsheet Importer GitHub Repo for Packed Deployment to ABAP Configuration Options for Spreadsheet Importer SAPUI5 Adaptation Tutorial Official Documentation for Adaptation Projects Deployment Instructions for Adaptation Projects Read More Technology Blogs by Members articles
#SAP
#SAPTechnologyblog
