SheetJS used in Angular


#1

Hi all,

I have installed Canvas v1.2.2 and tried to use SheetJS for my application.
My question is can i take whatever sample in below link and just apply? coz i seem to get some errors by doing that.

An example is:
in html:

  <tm1-ui-upload
    tm1-instance="FileUpload"
    tm1-path="{{page.user.Name}}"
    tm1-after-upload=FuncUpload(file)
    >
  </tm1-ui-upload>

in javascript:

	$scope.FuncUpload = function(file){
		$scope.tempfile = file;
		$scope.msg = "You have uploaded " + $scope.tempfile.name;
		$scope.path = $scope.tempfile.path;
		$scope.typecheck = $scope.path.substr($scope.path.length-4);

		if($scope.typecheck == "xlsx"){
			$scope.type="Excel";
		}else if($scope.typecheck == ".csv"){
			$scope.type="CSV";
		}else if($scope.typecheck == ".xls"){
			$scope.type="Excel";
		}

		/* set up XMLHttpRequest */
		var url = $scope.path;
		var oReq = new XMLHttpRequest();
		oReq.open("GET", url, true);
		oReq.responseType = "arraybuffer";

		oReq.onload = function(e) {
		var arraybuffer = oReq.response;

		/* convert data to binary string */
		var data = new Uint8Array(arraybuffer);
		var arr = new Array();
		for(var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
		var bstr = arr.join("");

		/* Call XLSX */
		var workbook = XLSX.read(bstr, {type:"binary"});

		/* DO SOMETHING WITH workbook HERE */
		}

		oReq.send();

	};

The later part in JS i copied from below link - Parsing workbooks - Browse download file (ajax).

The error is:

client.js?v=1.2.2:61 Uncaught ReferenceError: XLSX is not defined
    at Object.oReq.onload (http://localhost:8080/client-assets/client.js?v=1.2.2:61:23)
    at Object.c.(anonymous function) [as dispatchEvent] (http://localhost:8080/assets/sso/xdomain.canvas.js:3:1663)
    at h (http://localhost:8080/assets/sso/xdomain.canvas.js:3:4064)

Please let me know if you have any samples using SheetJS with Angular?
Thank you very much!


#2

Hi @twu,

This article should help you out on how to setup and use that:

https://code.cubewise.com/canvas-docs/configuring-sheet-js


Paul


#3

Hi @plim

Thanks i have enabled SheetJS now.
But i am actually trying to do something a bit different.

Can i combine the tm1-ui-upload function with SheetJS function, the ideal scenario is:
the upload button will get my Excel file to certain location, at the same time can return the sheet names to my JS.

Please advise.

Thanks
Tina


#4

Hi @twu,

Once you have the file path from the tm1-ui-upload,

<tm1-ui-upload tm1-instance="dev" tm1-after-upload="processFile(file)"></tm1-ui-upload>

you should be able to read the file using one of their utilities:

$scope.processFile = function(file){
  var workbook = XLSX.read(file.path);
  console.info('workbook %o', workbook);
};

You can find out more from the SheetJS documentation:


Paul


#5

Hi @plim

I was not able to get the correct sheet name using the script above.
Looks like XLSX.read inside post-upload function is not able to collect all the info that SheetJS button does.

Please find below 2 console.log:
1st one is what i got from SheetJS upload button;
2nd one is what i got from applying your code to post-upload function.


#6

Hi @twu,

Here is what I read and used, utilizing the example under “Browser download file (ajax)”:

Also note that you will need to have $http declared on your controller:

$scope.processFile = function(file){

      
   // need to retrieve an array buffer as per documentation 
   $http.get(file.path, {responseType: "arraybuffer"}).then(function(response){

      // convert data to binary string
          var data = new Uint8Array(response.data);
          var arr = new Array();
          for(var i = 0; i != data.length; ++i) 
            arr[i] = String.fromCharCode(data[i]);
          var bstr = arr.join("");

          /* read into XLSX */
          var workbook = XLSX.read(bstr, {type:"binary"});
          console.info('workbook %o', workbook);
      });

};

and here are the details of how the above code came to be

Had checked what was being returned by the original code in there and found out that it was an array buffer. From there, had checked how we can grab that via Angular. Had then found that an additional parameter named {responseType: "arraybuffer"}

needs to be passed into the $http.get() as per code above. The rest of it is similar to the examples already in the SheetJS documentation / site.

Running the above code with the tm1-ui-upload should now give you:

Cheers!
Paul