使用xlsx插件读取Excel表格数据为JSON数据
一、使用步骤
1.引入库
import xlsx from 'xlsx'
2.后端node使用
const workbook = xlsx.readFile(file, { type: 'binary' })
const arr = xlsx.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]]);
const restul = []
for (const item of arr) {
const userdata: any = item
const keys = lodash.keys(userdata)
const obj: any = {}
for (const key of keys) {
const value = userdata[key]
switch (key) {
case '姓名(必填)': {
obj.name = String(value).trim()
break
}
case 'ID(必填)': {
obj.userId = String(value).trim()
break
}
case '性别(必填)': {
obj.sex = String(value).trim()
break
}
case '编号(必填)': {
obj.box_code = String(value).trim()
break
}
}
}
restul.push(obj)
}
3.前端使用
1.封装
import XLSX from "xlsx";
const fixdata = (data) => {
var o = ''
var l = 0
var 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
}
const praseExecltoJson = (file) => {
let promise = new Promise((resolve, reject) => {
let wb
let rABS = false
let reader = new FileReader()
if (rABS) {
reader.readAsArrayBuffer(file)
} else {
reader.readAsBinaryString(file)
}
let arr = []
reader.onload = function (e) {
let data = e.target.result
if (rABS) {
wb = XLSX.read(btoa(fixdata(data)), {
type: 'base64'
})
} else {
wb = XLSX.read(data, {
type: 'binary'
})
}
arr = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]])
resolve(arr)
}
reader.onerror = function (e) {
reject(arr)
}
})
return promise
}
self.onmessage = function (evt) {
let file = evt.data.file;
praseExecltoJson(file).then(res => {
postMessage(res);
self.close();
})
}
2.调用
import toJsonWorker from 'src/common/execlToJson.worker.js'
let worker = new toJsonWorker();
worker.postMessage({file: this.uploadfile});
worker.onmessage = (res)=>{
let data = res.data;
let importdata = this.formatData(data);
}
formatData(data) {
if (!data) {
return [];
}
let arr = [];
for (let i = 0; i < data.length; i++) {
let item = data[i];
let obj = {};
for (let key in item) {
if (item.hasOwnProperty(key)) {
let value = item[key];
if (String(value).indexOf('例:') > -1) {
break;
}
switch (key) {
case '名称*': {
obj.name = String(value);
break;
}
}
}
}
if (!_.isEmpty(obj)) {
arr.push(obj)
}
}
return arr
}