前端下载并读取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;
}