Skip to Content
0

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

Nov 21, 2017 at 10:02 AM

217

avatar image
Former Member

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);
		};
	}
}
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

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

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>
Share
10 |10000 characters needed characters left characters exceeded