前端下载并读取excel并转成对象数组array
目录
前端下载并读取excel并转成对象数组array
xlsx.js可以读取excel，可以使用前端下载下excel文件，并将其中内容转为对象数组，方便处理。
例如，在vue-cli3.0或4.0中，将excel作为静态文件，放到public下，通过路径可以访问到。
其中axios请求到的文件格式为arraybuffer
axios({
method: "get",
url: "文件名.xlsx",
responseType: "arraybuffer",
}).then((res) => {
var arraybuffer = res.data;
});将arraybuffer转为sheet
function tranfer2Sheet(arraybuffer,sheetNumber) {
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("");
var workbook = XLSX.read(bstr, { type: "binary" });
var sheetName = workbook.SheetNames[sheetNumber];
var sheet = workbook.Sheets[sheetName];
return sheet;
}将sheet转化为array
//第一个参数是sheet，由上个方法得到。
//第二个参数是转换标准，指定转换成对象的key与excel的列的对应关系，例如standard = { name:"A",age:"B" } 也就是 A列转为name，B列转为age，得到对象。
//第三个参数为开始的行号，用于避开标题。
function sheet2Array(sheet, standard, startRow) {
var rowNum = 1;
var resultList = [];
var total = sheet["!ref"];
var startEnd = total.split(":");
var start = startEnd[0].replace(/[^0-9]/ig, "");
var end = startEnd[1].replace(/[^0-9]/ig, "");
for (var rowNum = start; rowNum <= end; rowNum++) {
if (rowNum >= startRow) {
var rowObj = {};
Object.keys(standard).forEach((key) => {
let column = standard[key];
let value = sheet[column + "" + rowNum];
//去掉单元格中的空格和回车
rowObj[key] = (value && value.w ? value.w : "").replace(/\ +/g, "").replace(/[\r\n]/g, "");
})
resultList.push(rowObj)
}
}
return resultList;
}完整演示如下
import axios from "axios";
import XLSX from "xlsx";
axios({
method: "get",
url: "文件名.xlsx",
responseType: "arraybuffer",
}).then((res) => {
var arraybuffer = res.data;
var sheet = tranfer2Sheet(arraybuffer,0);
var result = sheet2Array(sheet,{name:"A",age:"B",gender:"C"},2);
console.log("get Array from Excel,array = ",result)
});
function tranfer2Sheet(arraybuffer,sheetNumber) {
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("");
var workbook = XLSX.read(bstr, { type: "binary" });
var sheetName = workbook.SheetNames[sheetNumber];
var sheet = workbook.Sheets[sheetName];
return sheet;
}
//第一个参数是sheet，由上个方法得到。
//第二个参数是转换标准，指定转换成对象的key与excel的列的对应关系，例如standard = { name:"A",age:"B" } 也就是 A列转为name，B列转为age，得到对象。
//第三个参数为开始的行号，用于避开标题。
function sheet2Array(sheet, standard, startRow) {
var rowNum = 1;
var resultList = [];
var total = sheet["!ref"];
var startEnd = total.split(":");
var start = startEnd[0].replace(/[^0-9]/ig, "");
var end = startEnd[1].replace(/[^0-9]/ig, "");
for (var rowNum = start; rowNum <= end; rowNum++) {
if (rowNum >= startRow) {
var rowObj = {};
Object.keys(standard).forEach((key) => {
let column = standard[key];
let value = sheet[column + "" + rowNum];
//去掉单元格中的空格和回车
rowObj[key] = (value && value.w ? value.w : "").replace(/\ +/g, "").replace(/[\r\n]/g, "");
})
resultList.push(rowObj)
}
}
return resultList;
}