Skip to Content
Technical Articles
Author's profile photo Andy Goris

Native Excel migration tool for Fiori (semi-dynamic)

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:

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.

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      Very nice post, thanks for sharing! I have some follow-up questions.

      1. I'm a bit confused by the use of two separate sheets in this case. If data can be merged, it implies that data structure/model is the same. Why is it on two different sheets then? And if it's not the same then I'm even more confused by the implementation... How would the users know which data belongs to which sheet? And wouldn't this correspond to two different data objects in the backend?
      2. Regarding this statement: "When uploading the data to the backend everything is transferred into one string to be able to upload the complete table in one call." I'm confused here as well... We can transfer structured data in "one call" afaik, so why one string? Also did you really mean "everything"? What if there are thousands of rows in Excel spreadsheet?

      Thank you!

      Author's profile photo Andy Goris
      Andy Goris
      Blog Post Author
      1. Good point, it was asked by the client because their upload data comes from legacy systems. I guess the export tool they use might split them in sheets, but it's indeed supposed to be the same layout. So I agree with you that it's not a really usefull feature (if it was not possible the client would just have to merge everything into one worksheet manually, not a big deal).
      2. You can transfer structured data in one call, but this is one row of the table. You cannot do an "entity create" with a whole table. So the option here is to either group the odata calls into one big batch call or as I did, copy the whole table into one string and resplit the string into a table in the backend. So yes I really meant everything. I don't want an upload program where 500 of the 1000 rows are uploaded and the rest is in error. It's all or nothing in this case.

      Thanks for the feedback 🙂