How to export to excel using UI5 and SheetJS
Introduction
Recently I got to work with making a UI5 application outside of the SAP landscape, which meant making a UI5 app using OpenUI5 rather than SAPUI5. While my client does have SAP licenses etc. I thought it best to make sure I avoid any license implications and go with OpenUI5 which is mostly the same as SAPUI5 minus a few things such as export to Excel.
I honestly have no idea why this functionality isn't included in OpenUI5 as for a framwork with 'Create Enterprise-Ready Web Apps with Ease' as a tagline this seems an odd omission.
Regardless we're using OpenUI5 and we're going to use an open source JSON to excel spreadsheet converter called SheetJS.
This blog also applies to SAPUI5 but I thought I'd mention why I'm using this library rather than the inbuilt functionality that comes with SAPUI5.
Application setup
As always we're going to start from a blank application with nothing really setup in it, I make use of ExpressUI5 (disclaimer I made this) to quickly create my new UI5 applications.
So the first thing I'm going to do with our very basic application is make a new folder in out webapp folder called 'util'. This is where I like to keep a number of utiliity files that I often create in my projects, and then inside of that folder we're going to create a new file which I'm going to call xlxs.js which is going to be a copy of 'xlsx.full.min.js' from the SheetJS library/ github repo.
Lastly we need to include the new file to load when we load our UI5 application, this can be done in two different ways which is the same as I pointed out in our post of 'How to include a third party barcode generator in ui5' but I'll include it again here.
in our controller
We can include/ import other files such as this is within our controller directly at the top of the file/ in the import section. This would look like the following:
You are likely very familiar with this process from when you import other commonly use functions such as Filter, Sorter, MessageToast etc.
Note: Do not import as XLSX in capitals/ the import name is not important as it will always use XLSX in capital letters.
In our manifest
Our manifest.json as we know is the configuration of our application, this is typically where we'd define our ODATA model, other local JSON models, our routing etc but we can also import other JS files to load at the start of our application. This looks something like the following:
So inside of the 'resources' section create a new section called 'js' and then populate that with an array of our files to load which in this case is just out xlsx.js.
Setting up for our excel sheet download
So we've loaded in our library, first we're gonna make use of some dummy data, make a function to trigger our download and then boom we're done! So first we're gonna make some data, more than likely you'll already have some data that you want to download but for the purposes of this blog we're just gonna make some dummy JSON data which I've setup as the following:
So this is our typical array of results that we might get from our ODATA service, sitting inside of an array called 'results' and very likely the same sort of array you'll want to access for your excel export.
Exporting our data to excel
Next we're going to make a small function to build up our excel file and then trigger a download, I'll attach this download to a button in this case but you can invoke your function call however you'd like.
Take a look at our function as below:
So having a look at this we do a few things, first we get our data from our model this will likely be done the exact same way for yourselves with a named local JSON model. One important thing to note is that the function json_to_sheet takes a parameter which MUST be an array.
So if you're exporting a single entity into an excel sheet then you'll want to go ahead and simply wrap it up in an array, otherwise you'll get the error of 'r.foreach is not a function'. (Yes I learned this the hard way)
The rest is pretty easy to understand, you create a workbook where you can append multiple sheets, the 3rd parameter in pending sheets is the name of the sheet.
The function .writeFile automatically triggers the download to our user and we're done.
Conclusion
This was actually really quite easy to implement, as soon as I found out that the standard export functionality didn't exist in OpenUI5 I was originally in a bit of a panic/ was dreading making use of some other library but this actually proved to be quite simple.
However while it was simple it took me an hour or two to figure this all out as I missed a link to their documentation! Entirely my own fault really by missing this fact. Please find their documentation here and hopefully the documentation can be made more prominent in the future.
···
