Related tutorials: Pure front end using JS-XLSX (3) merging cell pure front end using JS-XLSX Excel file import and export function example (2) HandsonTable combined with JS-XLSX editable XLSX import and export function (see) JS-XLSX asynchronous file stream reading example
1. Implement the import function
Download JS-xlsx to dist copy xlsx.full.min.js into the page and then read the file from the FileReader object using JS-XlsX converted to JSON data code (==> example <==)
<! DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title></title> <script src="http://oss.sheetjs.com/js-xlsx/xlsx.full.min.js"></script> </head> <body> <input /> <div id="demo"></div> <script> /* FileReader: Read the file as an ArrayBuffer. ReadAsBinaryString (file) : reads the file as a binary string 3. ReadAsDataURL (file) : reads the file as a DataURL 4. ReadAsText (file, [encoding]) : Read the file as text. Encoding Defaults to 'utF-8' */ var wb; Var rABS = false; Function importf(obj) {// Import if(! obj.files) { return; } var f = obj.files[0]; var reader = new FileReader(); reader.onload = function(e) { var data = e.target.result; If (rABS) {wb = xlsx.read (btoa(fixData (data)), {// manually convert type: 'base64'}); } else { wb = XLSX.read(data, { type: 'binary' }); } //wb.SheetNames[0] get the name of the first Sheet in Sheets //wb.Sheets[name] get the data of the first Sheet document.getelementById ("demo").innerhtml = JSON.stringify( XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]) ); }; if(rABS) { reader.readAsArrayBuffer(f); } else { reader.readAsBinaryString(f); }} function fixData (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; } </script> </body> </html>Copy the code2. Implement the export function
Also introduce jS-XLSX code
<! DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title></title> <script src="http://oss.sheetjs.com/js-xlsx/xlsx.full.min.js"></script> </head> <body> <button Onclick ="downloadExl(jsono)" </button> <! <a href="" download=" XLSX "id="hf"></a> <script> var jsono = [{// test data" expiration warning (days)": "AdventLifecycle "," Product Title ": "title", "Suggested Retail Price" : "defaultPrice"," high (CM)": "Height "," Product Description" : "Description", "Shelf life Lock-up (days)": "LockupLifecycle", "name of commodity" : "skuName", "product introduction" : "brief", "width (cm)" : "width", "Ada" : "asdz", "article number" : "goodsNo", "commodity bar code" : "SkuNo "," product brand" : "brand", "netVolume (cm^3)": "netVolume", "whether shelf life management ": "isShelfLifeMgmt"," whether serial number management ": "isSNMgmt", "product color ": "Color", "size" : "size", "whether the batch management" : "isBatchMgmt", "product id" : "skuCode", "goods" : "shortName", "gross weight (g)" : "GrossWeight", "long (cm)" : "length", "English name" : "englishName", "net weight (g)" : "netWeight", "classification of goods" : "categoryId", "over here" : 1111.0, "date ": "expDate"}]; var tmpDown; Function downloadExl(json, type) {var tmpData = json[0]; json.unshift({}); var keyMap = []; // Get keys for (var k in tmpdata) {keymap.push (k); json[0][k] = k; } var tmpdata = []; Json. Map ((v, I) => keymap. map((k, j) => Object.assign({}, {v: v[k], position: (j > 25? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1) }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = { v: v.v }); var outputPos = Object.keys(tmpdata); Var tmpWB = {SheetNames: ['mySheet'], // Save SheetNames: {'mySheet': Object.assign({}, tmpData, // contents {'! Ref ': outputPos[0] + ':' + outputPos[outputpos.length - 1] // Set padding})}}; tmpDown = new Blob([s2ab(XLSX.write(tmpWB, {bookType: (type == undefined ? 'xlsx':type),bookSST: false, type: 'binary'}// The data is used to define the format type of the export))], {type: ""}); Var href = url.createObjecturl (tmpDown); Document.getelementbyid ("hf").href = href; // Bind a tag document.getelementById ("hf").click(); SetTimeout (function() {revokeObjectURL(tmpDown); RevokeObjectURL () to release the object URL}, 100); } function s2ab(s) {var buf = new ArrayBuffer(s.length); var view = new Uint8Array(buf); for (var i = 0; i ! = s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF; return buf; } // Converts the specified natural number to a base 26 representation. Mapping: [0-25] -> [a-z]. function getCharCol(n) { let temCol = '', s = '', m = 0 while (n > 0) { m = n % 26 + 1 s = String.fromCharCode(m + 64) + s n = (n - m) / 26 } return s } </script> </body> </html>Copy the code3. Use Python to convert Excel into Json to create test data
code
import sys
import xlrd
import json
file =sys.argv[1]
data = xlrd.open_workbook(file)
table=data.sheets()[0]
def haveNoIndex(table):
returnData=[]
keyMap=table.row_values(0)
for i in range(table.nrows):#row
tmpmp={}
tmpInd=0
for k in table.row_values(i):
tmpmp[keyMap[tmpInd]]=k
tmpInd=tmpInd+1
returnData.append(tmpmp);
return json.dumps(returnData,ensure_ascii=False,indent=2)
returnJson= haveNoIndex(table)
fp = open(file+".json","w",encoding='utf-8')
fp.write(returnJson)
fp.close()
Copy the codeValue =key ({key:key}); value=key ({key:key}); Node read and write Excel files explore practice XCel project summary – Electron and Vue performance optimization