SheetJS pure front end reading excel js-xlsx.js framework

Posted by Skyphoxx on Sat, 01 Feb 2020 10:09:39 +0100

Article directory

introduce

According to the needs of the project, we need to do a pure front-end function to read excel files. After evaluating the framework, we choose xlsx.js of SheetJS.
SheetJS is powerful. Easy to use, detailed documents.
github address: SheetJS
reference: Use of JS xlsx

Compatibility

Usage method

Implementation ideas

Get the selected local excel file stream through < input type = "file" >. Then read the file through the FileReader object, use the FileReader.onload event to listen for the operation, and finally use js-xlsx.js to process the data

File reference

  • https://github.com/SheetJS/sheetjs/tree/master/dist

    Generally speaking, only xlsx.core.min.js is needed, and xlsx.full.mini.js contains all the functions.
    Just introduce it directly.
    <script lang="javascript" src="dist/xlsx.full.min.js"></script>
    npm:
    npm install xlsx
    bower:
    bower install js-xlsx

Code examples and details

import XLSX from ".../dist/js/sheetjs/xlsx.core.min.js"

if(!document.getElementById('readLocalFile')){    //If there is already a corresponding dom element to click directly, add a
	let inputFile =	document.createElement("input");
	inputFile.setAttribute("id","readLocalFile");
	inputFile.setAttribute("type","file");
	inputFile.setAttribute("accept","application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel");
	 inputFile.setAttribute("multiple", false);  //Multiple choices are allowed. Set to no here
	inputFile.click();
	document.body.appendChild(inputFile);
	console.log("Use ID by readLocalFile Of input dom Object to get the contents of the uploaded file json Format data return");
	document.getElementById('readLocalFile').addEventListener('change', function(e) {   //Execute after selecting file
		let  files = e.target.files;
		if(files.length == 0) return;
		let  file = files[0];
		let  reader = new FileReader();
		reader.readAsBinaryString(file);
		reader.onload = function(e) {   //Handle the load event. Triggered when the read operation completes.
			let  data = e.target.result;
			let  workbook = XLSX.read(data, {type: 'binary'}); //XLSX: / xlsx.core.min.js read excel through XLSX.read(data, {type: type}) method
			console.log(workbook ); 
			let  sheetNames = workbook.SheetNames; // Sheet name collection
			let  worksheet = workbook.Sheets[sheetNames[0]]; // Here we only read the first sheet 
			let  json = XLSX.utils.sheet_to_json(worksheet); //  Read the workbook, you can write your own methods to output the table. Here it is recommended to use XLSX.utils. Tool class to output the data. Here you can output the data in json format and other format codes
			if(typeof(callback) == "function") callback(json);   //Callback 
			 document.getElementById('readLocalFile').value = null; //Clear after reading
		};
	});
  }else{
	  document.getElementById('readLocalFile').click();  //If you have dom elements, click
  }

XLSX.read(data, {type: type}) method reads excel and returns the object of WorkBook. The main values of type are as follows:
base64: read in base64 mode;
binary: BinaryString format (byte n is data.charCodeAt(n))
string: UTF8 encoded string;
buffer: nodejs Buffer´╝Ť
array: Uint8Array, 8-bit unsigned array;
Common export formats of tool class:
Xlsx.utils.sheet ABCD CSV: generate CSV format
Xlsx.utils.sheet? To? TXT: generate plain text format
Xlsx.utils.sheet ABCD to ABCD HTML: generate HTML format
Xlsx.utils.sheet ABCD to ABCD JSON: generate JSON format

Example workBook object:

Development attention

FileReader is only used to read the contents of files from the user (remote) system in a safe way. It cannot be used to simply read files by pathname from the file system. To read files by path name in JavaScript, you should use a standard Ajax solution for server-side file reading, or CORS permission if reading across domains.

Generate excel file and download

Go to directory

Published 14 original articles, won praise 8, visited 20000+
Private letter follow

Topics: Excel JSON github Javascript