Skip to Content
test
0 %
Export Data to OneDrive
Details
// Explore More Tutorials

Export Data to OneDrive

05/17/2019

Visualize data from the OData service in a table and export its content to Microsoft OneDrive

You will learn

  • How to integrate the Microsoft Authentication Library (MSAL) into an existing SAPUI5 application
  • How to interact with Microsoft Graph
  • How to send byte code to the OneDrive Storage service
  • How to open Excel Online documents in new tabs


Step 1: Load Microsoft Authentication Library (MSAL)

Copy the following line below the first script-tag in the index.html file.

<script src="https://secure.aadcdn.microsoftonline-p.com/lib/1.0.0/js/msal.min.js"></script>
Index.html

This <script> tag will load version 1.0.0 of the MSAL library.

You can find the most recent version of this library at npm and fetch it from this source.

Log on to answer question
Step 2: Implement the view

The following snippet of the view defines a page which includes a smart table control with a custom toolbar. Use this code to replace the content of the <pages> tag in the View1.view.xml file:

<mvc:View controllerName="sapcp.tutorial.cf.ui.controller.View1" xmlns="sap.m" xmlns:smartFilterBar="sap.ui.comp.smartfilterbar"
	xmlns:smartTable="sap.ui.comp.smarttable" xmlns:mvc="sap.ui.core.mvc" xmlns:semantic="sap.f.semantic">
	<Shell id="shell">
		<App id="app" class="sapUiSizeCompact">
			<pages>
				<Page id="page" title="{i18n>title}">
					<content>
						<smartTable:SmartTable entitySet="Orders" tableType="ResponsiveTable" enableAutoBinding="true" useExportToExcel="false"
							class="sapUiResponsiveContentPadding"
							initiallyVisibleFields="CustomerID,EmployeeID,Freight,OrderDate,OrderID,RequiredDate,ShipAddress,ShipCity,ShipCountry,ShipName,ShippedDate,ShipPostalCodeShipRegion,ShipVia">
							<smartTable:customToolbar>
								<OverflowToolbar>
									<ToolbarSpacer/>
									<OverflowToolbarButton id="uploadBtn" icon="sap-icon://upload-to-cloud" tooltip="Export Online" press="onUploadToOneDrive"/>
								</OverflowToolbar>
							</smartTable:customToolbar>
						</smartTable:SmartTable>
					</content>
				</Page>
			</pages>
		</App>
	</Shell>
</mvc:View>

view

Caution: The property controllerName (here sapcp.tutorial.cf.ui.controller.View1) corresponds to your namespace and project name you have defined in the wizard during the creation of the UI5 module. So make sure they fit together. If you have chosen a different namespace and project as shown in the screenshot, please adjust your code accordingly.

Log on to answer question
Step 3: Add the Northwind data model

The data we want to display in this application originates from the public Northwind OData service. Create a ODataModel client to connect the source to the application.

  1. Right-click on the UI module (not the project) and click New and OData Service to open the wizard.

    NewModel
  2. Select the source type Service URL (left box) and select the Northwind source from drop-down (top box) and enter the URL path /v2/Northwind/Northwind.svc in the bottom box. Click enter to check if the wizard can connect to the OData service and confirm with Next.

    Wizard
  3. Keep the selection of the default model and click Next.

    Model
  4. Add a new data source and a new default model to the application by clicking Next.

    Confirm
Log on to answer question
Step 4: Import supporting SAPUI5 libraries

The controller is the place where the program logic happens.
Replace the entire content of the View1.controller.js file with this snippet.

/* eslint-disable sap-no-hardcoded-url */
/* global Msal */

sap.ui.define([
	"sap/ui/core/mvc/Controller", "sap/ui/export/Spreadsheet", "sap/m/MessageToast"
], function (Controller, Spreadsheet, MessageToast) {
	"use strict";

	return Controller.extend("com.plain.controller.View1", {
      //Insert the code of the next step here
	});
});

The header defines eslint exceptions, so that the editor will suppress warnings. The first arguments of the sap.ui.define function lists the SAPUI5 dependencies.

newcontroller
Log on to answer question
Step 5: Add the MSAL client config

Add the configuration of the Microsoft Graph API to the View1.controller.js file. Don’t forget to insert your application id from the previous tutorial in the /YOUR SECRET APP ID/ placeholder!

config: {
  msalConfig: {
    auth: {
      clientId: "/YOUR SECRET APP ID/"
    },
    cache: {
      cacheLocation: 'localStorage',
      storeAuthStateInCookie: true
    }
  },
  graphBaseEndpoint: "https://graph.microsoft.com/v1.0/",
  scopeConfig: {
    scopes: [ 'Files.ReadWrite.All']
  }
},
config
Log on to answer question
Step 6: Initialize the MSAL client

Add the following onInit hook to the controller.

onInit: function () {
  this.oMsalClient = new Msal.UserAgentApplication(this.config.msalConfig);
  //check if the user is already signed in
  if (!this.oMsalClient.getAccount()) {
    this.oMsalClient.loginPopup(this.config.scopeConfig);
  }
},
oninit

The onInit hook redirects the user to the Microsoft authentication page if the (Microsoft) users is not logged in.

Log on to answer question
Step 7: Parse the table configuration

The onUploadToOneDrive function will be invoked by the export event of the view. This function reads the current filter settings of the table, defines the columns which should be part of the Spreadsheet and defines additional metadata.

Insert this function after the onInit function of the previous step.

onUploadToOneDrive: function () {
		var oSmartTable = this.getView().findAggregatedObjects(true, function (oAggregate) {
			return oAggregate instanceof sap.ui.comp.smarttable.SmartTable;
		})[0];
		var oTable = oSmartTable.getTable();
		var oRowBinding = oTable.getBinding("items");
		var aCols = oSmartTable.getInitiallyVisibleFields().split(',').map(function (sKey) {
			return {
				label: sKey,
				property: sKey,
				type: 'string'
			};
		});
		var oModel = oRowBinding.getModel();
		var oModelInterface = oModel.getInterface();
		var oSettings = {
			workbook: {
				columns: aCols,
				hierarchyLevel: 'level'
			},
			dataSource: {
				type: "oData",
				dataUrl: oRowBinding.getDownloadUrl ? oRowBinding.getDownloadUrl() : null,
				serviceUrl: oModelInterface.sServiceUrl,
				headers: oModelInterface.getHeaders ? oModelInterface.getHeaders() : null,
				count: oRowBinding.getLength ? oRowBinding.getLength() : null,
				sizeLimit: oModelInterface.iSizeLimit
			}
		};
		new Spreadsheet(oSettings).attachBeforeSave({}, function (oEvent) {
			oEvent.preventDefault();
			this.putToGraph('me/drive/root:/UploadedFromWebApp/' + oSmartTable.getEntitySet() + '.xlsx:/content',
				new Blob([oEvent.getParameter('data')], {
					type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
				}),
				function (data) {
					window.open(data.webUrl, '_blank');
				});
		}.bind(this), {}).build();
},
uploadcode
Log on to answer question
Step 8: Send the bytearray to Microsoft OneDrive

This putToGraph function takes the bytecode of the export worker and sends it to the Microsoft OneDrive API via an HTTP POST request. It also checks if the Microsoft user has been authenticated and attached the corresponding authorization header to the request.

Insert this function after the onUploadToOneDrive function of the previous step.

putToGraph: function (sEndpoint, payload, fnCb) {
	this.oMsalClient.acquireTokenSilent(this.config.scopeConfig)
		.then(function (oTokenInfo) {
			$.ajax({
					headers: {
						"Authorization": "Bearer " + oTokenInfo.accessToken
					},
					data: payload,
					processData: false,
					url: this.config.graphBaseEndpoint + sEndpoint,
					type: "PUT"
				})
				.then(fnCb)
				.fail(function (error) {
					MessageToast.show("Error, please check the log for details");
					$.sap.log.error(JSON.stringify(error.responseJSON.error));
				});
		}.bind(this))
		.catch($.sap.log.error);
}
puttograph
Log on to answer question
Step 9: Finalize the controller

Make sure you entire MainController.js file has all required methods and looks like this display. You can use the snippet below the screenshot to replace the entire file if needed.

You can collapse function in the Web IDE when you click on the small triangle next to the line numbers.

finishController
/* eslint-disable sap-no-hardcoded-url */
/* global Msal */

sap.ui.define([
	"sap/ui/core/mvc/Controller", "sap/ui/export/Spreadsheet", "sap/m/MessageToast"
], function (Controller, Spreadsheet, MessageToast) {
	"use strict";

	return Controller.extend("com.plain.controller.View1", {

		config: {
			msalConfig: {
				auth: {
					clientId: "/YOUR SECRET APP ID/"
				},
				cache: {
					cacheLocation: 'localStorage',
					storeAuthStateInCookie: true
				}
			},
			graphBaseEndpoint: "https://graph.microsoft.com/v1.0/",
			scopeConfig: {
				scopes: ['Files.ReadWrite.All']
			}
		},

		onInit: function () {
			this.oMsalClient = new Msal.UserAgentApplication(this.config.msalConfig);
			//check if the user is already signed in
			if (!this.oMsalClient.getAccount()) {
				this.oMsalClient.loginPopup(this.config.scopeConfig);
			}
		},

		onUploadToOneDrive: function () {
			var oSmartTable = this.getView().findAggregatedObjects(true, function (oAggregate) {
				return oAggregate instanceof sap.ui.comp.smarttable.SmartTable;
			})[0];
			var oTable = oSmartTable.getTable();
			var oRowBinding = oTable.getBinding("items");
			var aCols = oSmartTable.getInitiallyVisibleFields().split(',').map(function (sKey) {
				return {
					label: sKey,
					property: sKey,
					type: 'string'
				};
			});
			var oModel = oRowBinding.getModel();
			var oModelInterface = oModel.getInterface();
			var oSettings = {
				workbook: {
					columns: aCols,
					hierarchyLevel: 'level'
				},
				dataSource: {
					type: "oData",
					dataUrl: oRowBinding.getDownloadUrl ? oRowBinding.getDownloadUrl() : null,
					serviceUrl: oModelInterface.sServiceUrl,
					headers: oModelInterface.getHeaders ? oModelInterface.getHeaders() : null,
					count: oRowBinding.getLength ? oRowBinding.getLength() : null,
					sizeLimit: oModelInterface.iSizeLimit
				}
			};
			new Spreadsheet(oSettings).attachBeforeSave({}, function (oEvent) {
				oEvent.preventDefault();
				this.putToGraph('me/drive/root:/UploadedFromWebApp/' + oSmartTable.getEntitySet() + '.xlsx:/content',
					new Blob([oEvent.getParameter('data')], {
						type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
					}),
					function (data) {
						window.open(data.webUrl, '_blank');
					});
			}.bind(this), {}).build();
		},

		putToGraph: function (sEndpoint, payload, fnCb) {
			this.oMsalClient.acquireTokenSilent(this.config.scopeConfig)
				.then(function (oTokenInfo) {
					$.ajax({
							headers: {
								"Authorization": "Bearer " + oTokenInfo.accessToken
							},
							data: payload,
							processData: false,
							url: this.config.graphBaseEndpoint + sEndpoint,
							type: "PUT"
						})
						.then(fnCb)
						.fail(function (error) {
							MessageToast.show("Error, please check the log for details");
							$.sap.log.error(JSON.stringify(error.responseJSON.error));
						});
				}.bind(this))
				.catch($.sap.log.error);
		}
	});
});
Log on to answer question
Step 10: Re-deploy the application

Re-deploy the application to see the changes in production.

  1. Trigger another build process with a right-click on the project and select Build twice.

    ReBuild
  2. Once the build process has completed successfully, right-click on the project and select Deploy and Deploy to SAP Cloud Platform.

    ReDeploy
Log on to answer question
Step 11: Login to the application

The token of your previous deployment might have expired. If necessary, login with the credentials of your SAP ID one more time.

idp
Log on to answer question
Step 12: Sign in with your Microsoft account

The application will recognize that no Microsoft user has been signed in to it and redirect your to the Microsoft login page.

  1. Enter your Office 365 or your personal @outlook.com email address here and click Next to proceed.

    msLogin
  2. When logging in for the first time, you need to grant the defined privileges and click yes.

    msPermissions

You (and the users of your application) can revoke the given permissions at https://myapps.microsoft.com [for Microsoft Office 365 users] or https://account.live.com/consent/Manage [for outlook.com users]

Log on to answer question
Step 13: Download the data via the UI

Congrats, you logged in successfully! Now you can export the data you see in the table.

  1. Click the export button above the table.

    export
  2. Notice the progress popover during the process.

    progress
  3. Once the process has completed, a new browser window/tab should open and display the data in Excel online.

    online

Make sure to unlock the file before exporting it again.

How many rows can you see in the Spreadsheet (when exporting the table without filters)
×

Next Steps

Back to top