vue中上传、下载xlsx文件方法

1.xlsx依赖引入

npm install xlsx --save

2.downloadExcel模板下载（参数：file_Name、file_List）

var XLSX = require('xlsx');

// const sheetName = '模板';
// const file_Name = '模板.xlsx';
// const json = this.templateData;

const fileName = file_Name;
const json = file_List;
const type = 'xlsx';

// 导出到excel
let keyMap = []; // 获取键
for (let k in json[0]) {
    if (json[0].hasOwnProperty(k)) {
        keyMap.push(k)
    }
}
let tmpdata = []; // 用来保存转换好的json
json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
    v: v[k] || '',
    position: (j > 25 ? this.getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
}))).reduce((prev, next) => prev.concat(next)).forEach(function (v) {
    tmpdata[v.position] = {
        v: v.v
    }
})
console.log("tmpdata");
console.log(tmpdata);
let outputPos = Object.keys(tmpdata); // 设置区域,比如表格从A1到F3
let tmpWB = {
    SheetNames: ['sheet'], // 保存的表标题
    Sheets: {
        'sheet': Object.assign({},
            tmpdata, // 内容
            {
                '!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] // 设置填充区域
            })
    }
};
let blob = new Blob([this.sTbuff(XLSX.write(tmpWB,
    { bookType: (type || 'xlsx'), bookSST: false, type: 'binary' } // 这里的数据是用来定义导出的格式类型
))], {
    type: this.fileType
}); // 创建二进制对象写入转换好的字节流
var link = document.createElement('a');
document.body.appendChild(link);
link.href = window.URL.createObjectURL(blob);
link.download = fileName;   //下载的文件名，带格式
link.click();
//释放内存
// window.URL.revokeObjectURL(link.href);
window.URL.revokeObjectURL(link.href, 100); // 延时释放
document.body.removeChild(link);

sTbuff字符串转字符流（str参数：string）

// 字符串转字符流
var buff = new ArrayBuffer(str.length)
var view = new Uint8Array(buff)
for (var i = 0; i !== str.length; ++i) {
    view[i] = str.charCodeAt(i) & 0xFF
}
return buff

getCharCol将指定自然数转换为26进制表示（n参数：number）映射关系[0-25] —> [A-Z]

let s = '';
let m = 0;
while (n > 0) {
  m = n % 26 + 1;
  s = String.fromCharCode(64 + m) + s;
  n = (n - m) / 26;
}
return s

3.importFile导入excel

// 导入excel
this.fullscreenLoading = true;
let files = this.__getVueIns('1579073713062').$refs._op_formUpload_upload.uploadFiles;
if (!files) {
    this.fullscreenLoading = false;
    return;
}
var file = files[0];
const types = file.name.split('.');
const type = types[types.length - 1];
const fileType = ['xlsx', 'xls', 'XLSX', 'XLS'].some(item => item == type);
if (!fileType) {
    this.$message.error('格式错误，请重新上传文件！');
    return;
}
this.readFile(file).then((res)=>{
    let list = res[0];
    console.log('list',list);
    if (list.length <= 1) {
        this.$notify({
            title: '文件内容格式不对或不能为空！',
            type: 'error'
        });
        return;
    }
    let appBody = [];
    for (let i in list) {
        if (i == 0) {
            continue;
        }
        let data = {
            staffNo: list[i][0],
            merchantNo: list[i][2],
            operation: list[i][4]
        }
        appBody.push(data);
    }
});

readFile文件读取（参数：file）

var XLSX = require('xlsx');
const result = [];
return new Promise((resolve) =>{
    const reader = new FileReader();
    reader.onload = function(e) {
    const data = e.target.result;
    const wb = XLSX.read(data, {
        type: 'binary'
    });
    wb.SheetNames.forEach((sheetName) => {
        result.push(
        XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {header:1,defval:''})
        /* {
        sheet: XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {header:1,defval:''})
        } */
        )
    });
    console.log('result',result);
    resolve(result)
    };
    reader.readAsBinaryString(file.raw)
})