Read contents of Excel file into Sapui5 table

[Former Member]

Hello Experts,

Can someone please help me with the steps to read the contents of an Excel file into the table.

I am using file upload Ui element to browse the file.

But I am unable to read it directly from the Input stream.

is it compulsory to store the file on the server and then read from there?

Thanks a lot in advance.

Regards,

Archana.

[DK007 · Accepted solution]

Hi,

Recently I worked on in uploading a CSV file which has header and line items. My requirement is unique, so I had to write everything from scratch, but I used some of the lessons learned from SCN community.

I used sap.ui.unified.FileUploader UI element to upload. In onChange event I read the file content.

Parse this file as a CSV delimited string. My column headers in CSV file are same model attributes. In my case I had to upload header and lines. So the convention, I followed is first row would be header labels(model attributes) and second row is header data, third row is line labels(line attributes of model) and fourth row onwards they are line data. During upload based on the header/line labels you can do conversion also.

Let me know if you need any further help in this regard.

Thanks,

Dheeram

[saivellanki]

Hi Archana,

Check this Blog -

Also this thread -

Browsing and uploading csv/excel file in sap ui... | SCN

Regards,

Sai Vellanki.

[Former Member]

Thanks Sai. But I am not using HANA studio.

I need to get contents of a local file and display in my SAPUI5 table view. IDE I am using is NWDS and deploying on Portal server.

[Qualiture]

in that case, you can just upload to NetWeaver, and with https://poi.apache.org/ do some formatting before giving the results back to your UI5 frontend

[raman_sharapinski]

Hi Archana,
Please, look at Sai's second link. 

Example of uploading a CSV file and reading as a string without any service/NetWeaver/HANA Studio:
JS Bin - Collaborative JavaScript Debugging

Regards,
Raman

[Former Member]

Hello Raman,

I tried this code and it works fine. However, the file upload works only for the first time. When I try to upload the same file again, it does not work. If I upload a file with a different name, then it works fine. Same is the case in your jsbin example also.

Please let me know if you have a solution for this.

Thanks,

Charles

[Former Member]

Hi Archana,

You can download the XLSX.js from this link

GitHub - SheetJS/js-xlsx: XLSX / XLSM / XLSB / XLS / SpreadsheetML (Excel Spreadsheet) / ODS parser ...

and import it in your SAPUI5 Project only 1 file is enough.

you have to comment small piece of code in that file

then, in your fileUploader function, write this code in your controller

handleExcelUpload : function(e) {

       this._import(e.getParameter("files") && e.getParameter("files")[0]);

     },

     _import : function(file) {

       if(file && window.FileReader){

         var reader = new FileReader();

         var result = {}, data;

         reader.onload = function(e) {

           data = e.target.result;

         var wb = XLSX.read(data, {type: 'binary'});

         wb.SheetNames.forEach(function(sheetName) {

           var roa = XLSX.utils.sheet_to_row_object_array(wb.Sheets[sheetName]);

           if(roa.length > 0){

             result[sheetName] = roa;

           }

         });

         return result;

       }

       reader.readAsBinaryString(file);

     }

     },

in result variable you can get all the excel sheet data in JSON format, then bind it in your SAPUI5 table

Regards,

A. Vishnu

[Former Member]

Hi Vishnu ,

I saw your solution for reading the excel file . Can you please tell me step by step where to place the

xlsx.js file and how to access the same in the controller.

Thanks

Ritushree Saha

[Former Member]

Hi Ritushree,

Download xlsx.js and jszip.js from the above URL.

1. Place xlsx.js and jszip.js file in the same folder where you have your view and controller.

2. now, add this line in index.html=>

<script src="projectname/jszip.js"></script>

3. include this on top of your controller file.

jQuery.sap.require("projectname.xlsx");

jQuery.sap.require("projectname.jszip"); //not required

Then follow the above steps.

it will work fine.

you have to comment some block of codes in xlsx.js and jszip.js files.

Regards,

Vishnu

[Former Member]

Thanks Vishnu , Your Suggestion works . 

I have tried with .xls file and able to get the JSON formatted data .

I have some query :

1) Why do we need the "jszip.js".IS this used for reading .xlsx file

Can you please help me in sorting this?

Thanks

Ritushree Saha

[Former Member]

Hi Ritushree,

     Happy to know that you got the solution.

yes, jszip.js is used when you have any other format other than xls, I've used it to upload xlsx.

you can upload these data on a table using this code.

//write this where you are getting result

this.excelDetail(result);

excelDetail: function(result){

       var data = result[Object.keys(result)[0]];

       for(var i=0; i<data.length; i++){

         var excelRows = new sap.m.ColumnListItem({cells:[

           new sap.m.Text({text: data[i][Object.keys(data[i])[0]]}),

           new sap.m.Text({text: data[i][Object.keys(data[i])[1]]})

         ]});

         this.getView().byId("yourTableName").addItem(excelRows );

       }

     },

now after you get these data in a table, you can sort.

Hope I solved your problem.

Regards,

Vishnu

[Former Member]

Hi Vishnu ,

I have added jszip.js in the places that you have mentioned . But it is failing in the

XLSX.read  and giving an error 'Uncaught TypeError: Cannot read property '0' of undefined'

What should be the value of 'type' for xlsx. as for '.xls' file are giving 'type: binary'

reader.onload = function(e) {

           data = e.target.result;

           var wb = XLSX.read(data, {type: 'binary'});

          wb.SheetNames.forEach(function(sheetName) {

            var roa = XLSX.utils.sheet_to_row_object_array(wb.Sheets[sheetName]);

            if(roa.length > 0){

              result[sheetName] = roa;

            }

          });

         return result;

};

reader.readAsBinaryString(file);

Thanks

Ritushree Saha

[Former Member]

Hi Ritushree,

I am sure, for xlsx, the type will be binary.

most probably the problem is in jszip.js file.

have you modified jszip.js file?

If you are not able to modify the file, I can send it.

Regards,

Vishnu

[Former Member]

Hi Vishnu ,

I have not modified the jszip.js. Can you please send the same .

Thanks

Ritushree Saha

[Former Member]

HI Ritushree,

open jszip.js file and replace these line of codes at first(you can find these same code on top, it's not syntactically correct)

!function(e){ if(typeof exports==="object"&& typeof module!=="undefined"){module.exports=e();}

else if(typeof define==="function"&&define.amd){define([],e);}else{var f;typeof window!=="undefined"?f=window:"undefined"!=typeof global?f=global:"undefined"!=typeof self

&&(f=self),f.JSZip=e();}}(function(){

return (function e(t,n,r){function s(o,u){if(!n[o]){if(!t[o]){var a=typeof require==="function"&&require;if(!u&&a){return a(o,!0);}

if(i){return i(o,!0);}

throw new Error("Cannot find module'"+o+"'");}var f=n[o]={exports:{}};

t[o][0].call(f.exports,function(e){var n=t[o][1][e];

return s(n?n:e);},f,f.exports,e,t,n,r);}return n[o].exports;}var i=typeof require==="function"&&require;for(var o=0;o<r.length;o++){s(r[o]);}

return s;})({1:[function(_dereq_,module,exports){

'use strict';

// private property

var _keyStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=";

now, test whether it is working fine.

Regards,

Vishnu

[Former Member]

Hi Vishnu ,

After replacing the code in jszip.js with your code I am getting the below error - at this line

in xlsx.js

Uncaught TypeError: jszip is not a function

and failing in xlsx.js. Seems it is not able to access jszip.js

function read_zip(data, opts) {

  var zip, d = data;

  var o = opts||{};

  if(!o.type) o.type = (has_buf && Buffer.isBuffer(data)) ? "buffer" : "base64";

  switch(o.type) {

  case "base64": zip = new jszip(d, { base64:true }); break;

  case "binary": case "array": zip = new jszip(d, { base64:false }); break;

  case "buffer": zip = new jszip(d); break;

  case "file": zip=new jszip(d=_fs.readFileSync(data)); break;

  default: throw new Error("Unrecognized type " + o.type);

  }

  return parse_zip(zip, o);

}

I have put the js files in lib folder and added them as includes in manifest.json.

Thanks

Ritushree

[Former Member]

Hi Vishnu ,

I added "<script src="lib/jszip.js"></script>" this line in index.html

and it worked.

Thanks for your help and support.

Thanks

Ritushree Saha

[Former Member]

Hi Ritushree,

I mentioned it before to include the jszip.js in index file.

Anyway.. happy to know that it worked.

Regards,

Vishnu

[Former Member]

Hi Vishnu,

What code needs to be commented in XLSX.js and jszip.js?

[Former Member]

Hi Majid,

for jszip.js, I have written the correct code above, it was syntactically incorrect, so just replace those line.

in xlsx.js, copy paste your code in WebIDE, and see where it is showing error. Then comment those lines.

Try these fixes, let me know if you need the xlsx.js file via email

Regards,

Vishnu

[vijay_kumar49]

My Requirement is How to read the data from the .xlsx file (Examp: “VijayTest. .xlsx) using sap/ui/unified/FileUploader

What code needs to be commented in XLSX.js and jszip.js?

I used below code

Hi Archana,

You can download the XLSX.js from this link
GitHub - SheetJS/js-xlsx: XLSX / XLSM / XLSB / XLS / SpreadsheetML (Excel Spreadsheet) / ODS parser ...
and import it in your SAPUI5 Project only 1 file is enough.

you have to comment small piece of code in that file
then, in your fileUploader function, write this code in your controller

handleExcelUpload : function(e) {
       this._import(e.getParameter("files") && e.getParameter("files")[0]);
     },
     _import : function(file) {
       if(file && window.FileReader){
         var reader = new FileReader();
         var result = {}, data;
         reader.onload = function(e) {
           data = e.target.result;
         var wb = XLSX.read(data, {type: 'binary'});
         wb.SheetNames.forEach(function(sheetName) {
           var roa = XLSX.utils.sheet_to_row_object_array(wb.Sheets[sheetName]);
           if(roa.length > 0){
             result[sheetName] = roa;
           }
         });
         return result;
       }
       reader.readAsBinaryString(file);
     }
     },

in result variable you can get all the excel sheet data in JSON format, then bind it in your SAPUI5 table

Regards,
A. Vishnu

[Former Member]

Hi Vishnu,

I did not find any error in xlsx.js file. while using the same i am getting the error that read is undefined. Can you please tell me what need to be changed in xlsx.js file ?
or if u can share the corrected file ?

[former_member618871]

thanks for the good contribution

[Former Member]

Hello Experts,

Can someone please help here?

Thanks & Regards,

Archana