how to upload excel file and convert it into JSON ...

Hi Expert,

i need to upload excel file and convert it into JSON format.

Screen

Thanks & Regards,

Ayushi

SAP Managed Tags:
SAPUI5

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (2)

that demo gives everything you need. just check the source code

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

take a look at this thread

html - Excel to JSON javascript code? - Stack Overflow

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Thanks for reply rajeesh

i am using xlsx.js lib but its not working.

i am creating project using JS-XLSX Live Demo that sample.

Thanks & Regards,

Ayushi

How does your xlsx file format look like?

XLSX.js file- https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js

jszip.js file- https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js

I meant the file you are trying to convert (.xslx extension file).Are you developing a tool to convert excel to json using these libraries?

No..i am developing a project only...do you have a project where we can convert excel to json format?...bcoz i am lil confused bcz of so many errors

Thanks & Regards

Ayushi,

hope this helps

JS Bin - Collaborative JavaScript Debugging

HI Rajeesh

here is my code

view part-

<l:VerticalLayout>

<u:FileUploader id="fileUploader" name="myFileUpload"

class="sapUiSmallMarginEnd" uploadUrl="upload/" width="400px"

tooltip="Upload your file to the local server" uploadComplete="handleUploadComplete"

change="handleExcelUpload" placeholder="Please Select File" />

Controller part-

onInit: function() {

var oModel = new sap.ui.model.json.JSONModel();

this.getView().setModel(oModel);

sap.ui.getCore().setModel(oModel);

handleExcelUpload : function(e) {

this._import(e.getParameter("files")

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

_import : function(file) {

if (file && window.FileReader) {

var reader = new FileReader();

that = this;

//result = {};

//var data;

reader.onload = function(evt) {

var data = evt.target.result;

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

var arr = String.fromCharCode.apply(null, new Uint8Array(data));

var xlsx = XLSX.read(btoa(arr), {type: 'base64'});

result = xlsx.Strings;

result = {};

xlsx.SheetNames.forEach(function(sheetName) {

var rObjArr = XLSX.utils.sheet_to_row_object_array(xlsx.Sheets[sheetName]);

if(rObjArr.length > 0){

result[sheetName] = rObjArr;

}

});

return result;

that.b64toBlob(xlsx, "binary");

};

reader.readAsArrayBuffer(file);

};

b64toBlob : function(b64Data, contentType) {

contentType = contentType || '';

var sliceSize = 512;

b64Data = b64Data.replace(/^[^,]+,/, '');

b64Data = b64Data.replace(/\s/g, '');

var byteCharacters = Base64.decode(b64Data);

var byteArrays = [];

for (var offset = 0; offset < byteCharacters.length;offset += sliceSize){

var slice = byteCharacters.slice(offset, offset + sliceSize);

var byteNumbers = new Array(slice.length);

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

byteNumbers[i] = slice.charCodeAt(i);

}

var byteArray = new Uint8Array(byteNumbers);

byteArrays.push(byteArray);

}

var blob = new Blob(byteArrays, {

type : contentType

});

}

index.htmt

<html>

<head>

id="sap-ui-bootstrap"

data-sap-ui-libs="sap.m"

data-sap-ui-theme="sap_bluecrystal">

</script>

sap.ui.localResources("excel_file2");

var app = new sap.m.App({initialPage:"idexcel_file1"});

var page = sap.ui.view({id:"idexcel_file1", viewName:"excel_file2.excel_file", type:sap.ui.core.mvc.ViewType.XML});

app.addPage(page);

app.placeAt("content");

</script>

</head>

</body>

</html>

output screen

Note- i have added xlxs.js and jszip.js files

Regards,

Ayushi

Hi take a look at this,

I just checked the source code as jun wu said and tweaked a little

JS Bin - Collaborative JavaScript Debugging

HI Rajeesh,

i tried that code also but its not working for me.

if you developed so can you share your code?

Regards,

Ayushi

Hi Ayushi,

I shared the code in jsbin in my previous reply.try uploading .xlsx file in my code sample.

If you are not able to see this is my controller code

handleExcelUpload: function(e) {

var result = {};

rABS = false;//read as binary string

that = this;

var files = e.getParameter("files");

var f = files[0];

{

var reader = new FileReader();

var name = f.name;

reader.onload = function(e) {

if(typeof console !== 'undefined') console.log("onload", new Date(), rABS, false);

var data = e.target.result;

var wb;

if(rABS) {

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

} else {

var arr = that._fixdata(data);

wb = XLSX.read(btoa(arr), {type: 'base64'});

}

result = that._process_wb(wb);

alert(result);

};

if(rABS) reader.readAsBinaryString(f);

else reader.readAsArrayBuffer(f);

}

_process_wb:function(wb){

var output = "";

output = JSON.stringify(this._to_json(wb), 2, 2);

return output;

_to_json:function(workbook){

var result = {};

workbook.SheetNames.forEach(function(sheetName) {

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

if(roa.length > 0){

result[sheetName] = roa;

}

});

return result;

_fixdata:function(data){

var o = "", l = 0, w = 10240;

for(; l<data.byteLength/w; ++l) o+=String.fromCharCode.apply(null,new Uint8Array(data.slice(l*w,l*w+w)));

o+=String.fromCharCode.apply(null, new Uint8Array(data.slice(l*w)));

return o;

}

Regards

Hi Rajeesh,

in a view part you had taken file upload button only?

You have added XLXS.js and jszip.js lib?

Regards,

Ayushi

thanks rajeesh code is working fine

regards,

Ayushi

Hi Rajees,

Can you add CSV format also because i have two radio buttons when i select csv then data should be shown in csv format.

Regards,

Ayushi

Ask a Question

Top Q&A Solution Author

User

Count