Extracting Excel Data into JSON object and display into sap.ui.table using Javascript and sheetJS

[abdulrazak]

Intro:

Hello,

We have some cases where we want to upload the excel file data into database and show this data to UI using table and for that Backend build query and convert excel file into readable format then upload it to table using query and then give GET service to UI and the UI fetch this service and then display this data.

I cam across with this case and for that I use xlsx library featured by sheetJS. It helps to solve this problem and we are able to extract the data from excel sheet to Json object and then we can show this data to sap ui table and also, able to post this data to backend using post service if needed also we are able to apply the validations.

Prerequisites:

• JavaScript FileReader object.
• Sap UI5 and UI elements.

let's get start.

1. create a sample application.

2. we need a xlsx library

3. add library path to manifest.

4. We create a simple UI where we are able to upload a file and a table where we can display data.

5. We have a sample data file for testing.

6.First select the file and then click on upload.

7.from here our controller comes into picture from here we use the file reader object of javascript and read the file.

What is File Reader?

The FileReader object lets web applications asynchronously read the contents of files (or raw data buffers) stored on the user's computer, using File or blob objects to specify the file or data to read.

onLoad event  is fired when a file has been read successfully. This event is not cancelable and does not bubble.

9. In this onload event we are building a code to extract the data in Json Object.

10. Now we want to confirm that the file we upload have the same columns as we mentioned so we will not lose any data

11. If our data is correct, we are now in a place to set this data into model and then send if required post it.

12.Result

13. Show proper messages so that user can be able to identify where the thing is getting wrong.

• e.g. if user press upload button without selecting file.

• if the column name is wrong.

• If the Mandatory data is missing

Limitations:

• Developer should pre known the column names means column names should be static
• column names and object count should be same if the columns in excel are more then we are not able to capture that data and if the column is missing then we will set the undefined values.
• validations to check the column names and mandatory field are manually from developer.

Conclusion:

As sometime user wanted to upload the excel data into system that time user is not required to depend on backend side as he has the application that build to post the data from excel to Database.

many case scenarios can be performed using this library.

It helps me and hope so it will help you to.

If you find this content informative, please share and give feedback.