Skip to Content
avatar image
Former Member

How to parse xlsx file using javascript and send it to backend JSON

I can not parse a xlsx file in sap ui5 using javascript and I can not send it to the backend to store it into db

UploadFiles.view.xml

<u:FileUploader id="fileUploader" name="myFileUpload" uploadUrl="/" tooltip="Upload your xlsx file" uploadComplete="handleUploadComplete" change="handleExcelUpload" typeMissmatch="handleTypeMissmatch" style="Emphasized" fileType="xlsx" placeholder="Choose a file for Upload."/> 			    

<Button text="upload file" icon="sap-icon://upload-to-cloud" press="handleUploadPress" type="Emphasized"/>

UploadFiles.controller.js

handleUploadPress: function(oEvent) {
	var oFileUploader = this.getView().byId("fileUploader");
	if (!oFileUploader.getValue()) {
		MessageToast.show("Choose a xlsx file first");
		return;
	} else {
		var oResourceBundle = this.getView().getModel("i18n").getResourceBundle();
		var url = oResourceBundle.getText("UploadExcelFile").toString();
		var oReq = new XMLHttpRequest();
		oReq.open("POST", url, true);
		oReq.responseType = "arraybuffer";
		
		oFileUploader.addHeaderParameter(new sap.ui.unified.FileUploaderParameter({
			name: "slug",
			value: oFileUploader.getValue()
		}));


		oFileUploader.addHeaderParameter(new sap.ui.unified.FileUploaderParameter({
			name: "x-csrf-token",
			value: this.getView().getModel().getSecurityToken()
    }));
		oFileUploader.setUploadUrl(url);
		oFileUploader.setSendXHR(true);
		oReq.onload = function(e) {
			var arraybuffer = oReq.response;
			var data = new Uint8Array(arraybuffer);
			var arr = {};
			for (var i = 0; i !== data.length; ++i) {
				arr[i] = String.fromCharCode(data[i]);
				var bstr = arr.join("");
				var workbook = XLSX.read(bstr, {
					type: "binary"
				});
				var FirstSheetName = workbook.SheetNames[0];
				var worksheet = workbook.Sheets[FirstSheetName];			        var json = XLSX.utils.sheet_to_json(worksheet);
				var params = "json=" + json;
		}
		oReq.send(params);
		};
	}
}
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Dec 13, 2017 at 04:57 PM

    Here is the answer:

    In the controller file of your own (JS Controller) add the following

    onUpload: function(e) {
    	var fU = this.getView().byId("idfileUploader");
    	var domRef = fU.getFocusDomRef();
    	var file = domRef.files[0];
    	var reader = new FileReader();
    	reader.onload = function(oEvent) {
    		var strCSV = oEvent.target.result;
    		var arrCSV = strCSV.match(/[\w .]+(?=,?)/g);
    		var noOfCols = 3; // amend that to match your file columns #
    		var headerRow = arrCSV.splice(0, noOfCols);
    		var data = [];
    		while (arrCSV.length > 0) {
    			var obj = {};
    			var row = arrCSV.splice(0, noOfCols);
    			for (var i = 0; i < row.length; i++) {
    				obj[headerRow[i]] = row[i].trim();
    			}
    			data.push(obj);
    		}
    	};
    	reader.readAsBinaryString(file);
    }
    

    In the view file of your own (XML View) add the following

    <VBox>
    <sap.ui.unified:FileUploader id="idfileUploader" width="50%" buttonText="Browse" fileType="CSV" placeholder="Choose CSV file" style="Emphasized"/>
    <Button text="Upload your file." press="onUpload" type="Emphasized"/>
    </VBox>
    
    Add comment
    10|10000 characters needed characters exceeded