Native Excel migration tool for Fiori (semi-dynamic)

[TheGokke]

Introduction

Recently I got a request from my client if it was possible to upload an Excel file in Fiori to the backend. I received the technical specs more or less as described in my previous sentence... So I knew I had to do some freewheeling but being able to upload multiple sheets in one go was a requirement/nice to have. The purpose was to update Allocation table data to begin with but it should be possible to upload other data too (without telling me what it was). Therefore, it was time to grab a coffee and to descend into the development cave...

Approach

Of course I did some research and googling because a good developer is a lazy developer and saw some interesting stuff:

• The File uploader example at the UI5 SDK: UI5 SDK


• An existing Fiori application already built to upload Excel data


• A blog by vishalkumar1305 https://blogs.sap.com/2017/05/17/sapui5-ms-excel-file-upload/

All of this was certainly helpful but what was bothering me was that this was a fixed format (where I needed dynamic input) and that the Excel file has to be saved in a temporary Z table.

 

Solution

The result is an application that previews native Excel files before there is a call to the backend. This results into a quick overview of the uploaded data where the user can verify that everything seems to be in order.

If you upload the following Excel file with two sheets:

This is what the Fiori application shows after the upload:

As you can see, the two sheets are merged together into one table and shown in preview mode onto the screen before anything is send to the backend! First, the user needs to specify the type of data to upload via the dropdown and this loads a fragment with additional input parameters for the backend:

After selecting the type and filling in all the required parameters the user presses the Save button and the data is send to the backend system. A return message will inform him if the upload was successful or not.

The code

One view with a fragment for the file picker and type of data, a container for additional parameters and a fragment to show the uploaded data into a table:

Main view

<mvc:View xmlns:html="http://www.w3.org/1999/xhtml" xmlns:mvc="sap.ui.core.mvc" xmlns="sap.m" xmlns:semantic="sap.m.semantic"

	xmlns:core="sap.ui.core" xmlns:l="sap.ui.layout" controllerName="XXXX.UploadExcelData.controller.MainView" displayBlock="true">

	<semantic:FullscreenPage busyIndicatorDelay="{appView>/delay}" enableScrolling="false" class="sapFioriListReportPageOverflow">

		<semantic:content>

			<VBox fitContainer="true">

				<core:Fragment fragmentName="XXXX.UploadExcelData.fragment.UploadFile" type="XML"/>

				<l:HorizontalLayout id="additionalParamaters"/>

				<core:Fragment fragmentName="XXXX.UploadExcelData.fragment.UploadSmartTable" type="XML"/>

			</VBox>

		</semantic:content>

		<semantic:customFooterContent>

			<Button text="{i18n>btnSave}" press="handleSaveData" icon="sap-icon://save" type="Emphasized" enabled="{appView>/saveEnabled}"/>

		</semantic:customFooterContent>

	</semantic:FullscreenPage>

</mvc:View>

Upload fragment

<core:FragmentDefinition xmlns="sap.m" xmlns:core="sap.ui.core" xmlns:l="sap.ui.layout" xmlns:u="sap.ui.unified">

	<Panel>

		<l:VerticalLayout width="100%" id="fileUploaderLayout">

			<l:content>

				<HBox alignItems="Center" justifyContent="Start">

					<Label text="{i18n>uploadLabel}" id="fileUploaderLabel" class="sapMH5FontSize sapUiSmallMarginEnd" labelFor="fileUploader" required="true" width="15em"/>

					<u:FileUploader sameFilenameAllowed="true" id="fileUploader" sendXHR="true" tooltip="{i18n>uploadTooltip}" change="onUpload"

						fileType="xls,xlsx" useMultipart="false" width="400px" typeMissmatch="handleTypeMissmatch" placeholder="{i18n>uploadPlaceHolder}"/>

				</HBox>

				<HBox alignItems="Center" justifyContent="Start">

					<Label text="{i18n>uploadType}" class="sapMH5FontSize sapUiSmallMarginEnd" labelFor="uploadType" required="true" width="15em"/>

					<Select id="uploadType" items="{ path: '/UploadTypeSet', sorter: { path: 'TypeText' } }" enabled="{appView>/typeEnabled}" forceSelection="false" change="onTypeSelected">

						<core:ListItem key="{Type}" text="{TypeText}"/>

					</Select>

				</HBox>

			</l:content>

		</l:VerticalLayout>

	</Panel>

</core:FragmentDefinition>

Table fragment with factory functions for the items & columns since this is dynamic

<core:FragmentDefinition xmlns="sap.m" xmlns:core="sap.ui.core">

	<ScrollContainer horizontal="true" vertical="true" height="85%">

		<Table items="{path: 'localModel>/items', factory: '._populateItems'}" columns="{path: 'localModel>/columns', factory: '._populateColumns'}"

			fixedLayout="false">

			<headerToolbar>

				<Toolbar>

					<Title text="{i18n>tableTitle}"/>

				</Toolbar>

			</headerToolbar>

		</Table>

	</ScrollContainer>

</core:FragmentDefinition>

Libraries

I added https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.0/jszip.js & https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.0/xlsx.js as a local library to handle the upload of the Excel file:

 

The upload without backend call

			/**

			 * Initialize model for content

			 * @author      Andy Goris

			 * @version     1.0.0

			 * @since       1.0.0

			 * @memberOf    XXXX.UploadExcelData.controller.MainView

			 * @public

			 * @param {object} oEvent the event of the fileuploader

			 */

			onUpload: function(oEvent) {

				let oExcelData = {};

				const oFile = oEvent.getParameter("files") && oEvent.getParameter("files")[0];

				if (oFile && window.FileReader) {

					let oReader = new FileReader();

					let aData = [];

					let aColumns = [];

					oReader.onload = function(oExcel) {

						const sData = oExcel.target.result;

						const oWorkbook = XLSX.read(sData, {

							type: "binary"

						});

						let bColumnsAdded = false;

						oWorkbook.SheetNames.forEach(function(sSheetName) {

							// Here is your object for every sheet in workbook

							oExcelData = XLSX.utils.sheet_to_row_object_array(oWorkbook.Sheets[sSheetName], {

								defval: ""

							});

							//Transform data to A/B/C/D/E/F columns

							oExcelData.forEach(function(oRow) {

								let oNewRow = {};

								let iIndex = 0;

								for (let sProperty in oRow) {

									let sColumn = String.fromCharCode(65 + iIndex);

									let oNewColumn = {};

									oNewRow[sColumn] = oRow[sProperty].toString().replace(/\s/g, '');

									if (!bColumnsAdded) { //Only for the first row

										oNewColumn.columnId = sProperty;

										aColumns.push(oNewColumn);

									}

									iIndex++;

								}

								aData.push(oNewRow);

								bColumnsAdded = true;

							});

						});



						// Setting the data to the local model 

						let oLocalModel = this.getModel("localModel");

						oLocalModel.setProperty("/items", aData);

						oLocalModel.setProperty("/columns", aColumns);

						this.getModel("appView").setProperty("/typeEnabled", true);

					}.bind(this);

					oReader.onerror = function(oException) {

						this.getModel("appView").setProperty("/typeEnabled", false);

						MessageBox.error(oException);

					};

					oReader.readAsBinaryString(oFile);

				}

			},

 

Conclusion

When uploading the data to the backend everything is transferred into one string to be able to upload the complete table in one call. The additional parameters are added to the entity call as a complex type so that I was able to use a simple create of the oData model.