Skip to Content
Technical Articles

Export As… Spreadsheet to Microsoft OneDrive

It seems like pure madness to download an Excel or Word file from an online app, just to upload it seconds later to OneDrive. This blog post will show you how to upload byte arrays from your SAPUI5 app straight to Microsoft OneDrive.

Tables are the core UI element of all enterprise apps. We not only use them to display data, but they also allow easy sorting and filtering of that data. Tables become even greater when they are smart, e.g. there’s no need to configure the columns or to implement standard functionality such as paging, sorting or filtering. This is what makes the smart table control and the “List report” fiori element awesome, you can simply connect them to an OData model and voila. Your table with all standard features is complete. They also come with additional features such as “Export as Spreadsheet”. Often, people download the file and want to share it with colleagues over the Cloud. This means they upload the Spreadsheet to Office 365 and invite colleagues.

To me, this is where the shoe pinches: In the cloud era where we think cloud-first, it doesn’t make any sense to download a file, just to upload it seconds later. It’s time to change this!

You can implement this functionality easily in your web application with these five steps:

  1. Configure the Spreadsheet export
    First, you need to specify how the Spreadsheet should look. The columns property contains information like columns name, data type, and content. Here we simply copy all columns from the SAPUI5 control to the spreadsheet, but you could also merge columns or put them in hierarchies.

    var oSettings = {
      workbook: {
        columns: oSmartTable.getInitiallyVisibleFields()
                  .split(',').map(function (sKey) {
          return {
            label: sKey,
            property: sKey,
            type: 'string'
          };
        }),
        hierarchyLevel: 'level'
      },
      dataSource: {
        ...
      }
    };
  1. Intercept the beforeSave event to get the byte array
    The development team added the new beforeSave event to this Spreadsheets library with version 1.61 and made it publicly accessible with 1.63. This event is triggered right after the web worker finishes to build the byte array and attaches it to the callback.

    new Spreadsheet(oSettings)
      .attachBeforeSave({}, function (oEvent) {
        var byteArray = oEvent.getParameter('data')
      }, {})
      .build();

    It’s crucial to prevent the default action which usually follows this event: The download of the byte array to the local file system.
    The following command takes care of this

    oEvent.preventDefault();
  2. Get an access token for Microsoft OneDrive
    Microsoft Graph is the central API to integrate with Office 365 applications. You’ll need a personal access (JWT) token to access the API endpoints. This may seem complicated at first, but don’t worry. Microsoft offers SDKs for all major programming languages which take care of the retrieval of your tokens for you.
  1. Send the byte array via HTTP
    Uploading the byte array is as easy as sending an HTTP PUT request, but there are some pitfalls along the way.
    Make sure to specify the location where you want to upload the file to and add the suffix “:/content”. Don’t forget: HTTP PUT will override whatever resource was there before.
    Convert the raw data from a byte array into a file-like object Blob.
    Prevent the automatic conversion of data to strings with the processData flag.

    $.ajax({
      type: "PUT",
      headers: {
        "Authorization": "Bearer " + access_token
      },
      url: 'https://graph.microsoft.com/v1.0/me/drive/root:/UploadedFromWebApp/UploadedFile.xlsx:/content',
      data: new Blob([oEvent.getParameter('data')], {
        type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
      }),
      processData: false
    })
  2. Open the spreadsheet in Excel Online
    Once the promise is resolved and the file has been uploaded to OneDrive, we can fetch the URL of the document and open it in a new tab.
    Here you can refine the data or share your work with your colleagues.

    $.ajax({
      ...
    })
    .then(function (data) {
      window.open(data.webUrl, '_blank');
    }))

 

Are you curious how this can be implemented end-to-end in a SAPUI5 application? I’ve also created a tutorial which guides you through this step-by-step.

 

3 Comments
You must be Logged on to comment or reply to a post.
  • Hi Marius,

     

    Great blog. But would you recommend having the call to Microsoft server in the backend(probably using abap http client)? It may not be considered secure if you have the fetching of JWT token logic in the front end code.

     

    Best Regards

    Chandan Singh

    • Hi Chandan,

      thank you 🙂

      It is possible to fetch the token in the backend and depending on your individual situation this might be required. There are SDKs for many programming languages such as Java, JavaScript etc, unfortunately, ABAP is not among them 🙁

      I understand your concern about security. In UI projects everyone can see and try to abuse “secrets” and IDs. The described flow is in fact considered secure. When you register the application at Microsoft, you need to specify a redirect URL to which MS will redirect the token. Malicious attackers can see your application ID, but it’s useless to them (unless they are able to steal your domain/callback URL). Here’s a post that explains the registration flow