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: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
}); // 创建二进制对象写入转换好的字节流
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)
})

4.exportExcel模板下载（参数：data、columns、fileName）带样式

安装插件：npm install xlsx-style
修改源码文件：.\node_modules\xlsx-style\dist\cpexcel.js
将：var cpt = require('./cpt' + 'able');修改成：var cpt = cptable;

var XLSX = require('xlsx');
var XLSXStyle = require('xlsx-style');
const type = 'xlsx';
let xlsxParam = { raw: true }
//定义表格样式
const styleAmount = {
  alignment: { vertical: "center", horizontal: "right" },
  numFmt: '#,##0.00'
}
const styleDate = {
  alignment: { vertical: "center", horizontal: "right" },
  numFmt: 'yyyy/m/d'
}
//格式化数据
const newData = this.formatData(data, columns);
//创建工作簿
const wb = XLSX.utils.book_new();
//创建工作表
const ws = XLSX.utils.json_to_sheet(newData, xlsxParam);
//设置列宽，wpx字段存储像素宽度，wch存储字符宽度，MDW字段存储最大数字宽度。
const colWidth = columns.map(column => ({ wch: column.cn_name.length * 3 + 2 }));
ws['!cols'] = colWidth;
//设置表格样式
for (let i = 1; i <= data.length; i++) {
  for (let j = 0; j < columns.length; j++) {
    const cell = ws[XLSX.utils.encode_cell({ r: i, c: j })]
    if (cell && cell.v === null) {
      cell.t = 's';
      cell.v = '';
    } else if (cell && cell.v !== null && columns[j].control_type === 'TEXT_WITH_AMOUNT') {
      cell.t = 'n';
      cell.s = styleAmount;
    } else if (cell && cell.v !== null && columns[j].control_type === 'DATEBOX') {
      cell.t = 'd';
      cell.s = styleDate;
    }
  }
}
console.log('ws2', ws);
//将工作表添加到工作簿
console.log('XLSX', XLSX);
console.log('XLSXStyle', XLSXStyle);
XLSX.utils.book_append_sheet(wb, ws, fileName);
//导出表格
let blob = new Blob([this.sTbuff(XLSXStyle.write(wb,
  { bookType: (type || 'xlsx'), bookSST: false, type: 'binary' } // 这里的数据是用来定义导出的格式类型
))], {
  type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
}); // 创建二进制对象写入转换好的字节流
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, 100); // 延时释放
document.body.removeChild(link);

formatData表格数据格式化(参数：data、columns)

const newData = []
//遍历数据
data.forEach(item => {
    const obj = {}
    //遍历列名
    columns.forEach(column => {
        obj[column.cn_name] = item[column.en_name]
    })
    newData.push(obj)
})
return newData

data数据格式为标准key-value的json格式，columns数据格式为与data一样，具体如下：

"columns": [{
    "cn_name": "编号",
    "en_name": "no",
    "control_type": "TEXT"
}, {
    "cn_name": "金额",
    "en_name": "amount",
    "control_type": "TEXT_WITH_AMOUNT"
}, {
    "cn_name": "日期",
    "en_name": "date",
    "control_type": "DATEBOX"
}, {
    "cn_name": "名称",
    "en_name": "name",
    "control_type": "TEXT"
}]
//下拉框取值
let fileName = this.options.find(item => item.tableENName === this.tableENName).tableCNName;

let fileName = this.options.find(item => item.value === this.tableCNName).label;

 5.压缩下载的Excel文件，注意：压缩之后各种格式都没了

// 导出表格
const XLSX = require('xlsx');
const XLSXStyle = require('xlsx-style');
const JSZip = require('jszip');
const FileSaver = require('file-saver');

// 格式化数据
const newData = this.formatData(data, columns);
// 创建工作簿
const wb = XLSX.utils.book_new();
// 创建工作表
const ws = XLSX.utils.json_to_sheet(newData);

// 将工作表添加到工作簿
XLSX.utils.book_append_sheet(wb, ws, fileName.substring(0, 30));
// 生成.xlsx文件的二进制数据
const excelData = XLSX.write(wb, { type: 'array', bookType: 'xlsx' });
// 创建一个新的JSZip实例
const zip = new JSZip();
// 将.xlsx文件添加到ZIP文件中，并设置文件名称
zip.file(fileName + '.xlsx', excelData, { binary: true });

// 生成zip文件
zip.generateAsync({
  type: 'blob',
  compression: 'DEFLATE', // STORE: 默认不压缩，DEFLATE：需要压缩
  compressionOptions: {
    level: 1 // 压缩等级 1~9   1：压缩速度最快，9：最优压缩方式
  }
}).then((compressedData) => {
  FileSaver.saveAs(compressedData, fileName + '.zip'); // 使用FileSaver.saveAs保存文件，文件名可自定义
});

 6.前端Vue下载后端传输过来Excel文件的二进制流数据

const byteCharacters = atob(res.obj.result);
const byteNumbers = new Array(byteCharacters.length);
for (let i = 0; i < byteCharacters.length; i++) {
  byteNumbers[i] = byteCharacters.charCodeAt(i);
}
const arrayBuffer = new Uint8Array(byteNumbers);
// // 将二进制流转换为ArrayBuffer
// const arrayBuffer = new Uint8Array(res.obj.result).buffer;
// 通过ArrayBuffer创建一个Blob对象
const blob = new Blob([arrayBuffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// 创建一个文件名
const fileName = name + '.xlsx';
// 创建一个虚拟链接
const url = window.URL.createObjectURL(blob);
// 创建一个<a>标签并设置下载属性
const link = document.createElement('a');
link.href = url;
link.download = fileName;
// 模拟点击下载链接，触发下载
link.click();
// 释放虚拟链接
window.URL.revokeObjectURL(link.href, 100); // 延时释放

 

参考：https://www.cnblogs.com/J-Luck/p/15066768.html、https://blog.csdn.net/sumimg/article/details/124927392