首先安装xlsx依赖
npm install xlsx --save
项目中引入
import * as XLSX from "xlsx";
一、导出excel
方法一:(最简单导出)
const data = XLSX.utils.json_to_sheet(tableData.value)//此处tableData.value为表格的数据
const wb = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(wb, data, 'test-data')//test-data为自定义的sheet表名
XLSX.writeFile(wb,'test.xlsx')//test.xlsx为自定义的文件名
方法二(自定义表头中文名):
此方法用于对表头有要求的需求,可以用来自定义设置表头中文名
1.封装一个exportExcel方法
function exportExcelTable(json, name, titleArr, sheetName,fields) {
let data = new Array();
if (!Array.isArray(json)) return console.warn('数据请传入数组');
if (!Array.isArray(titleArr)) return console.warn('标题请传入数组');
if (!Array.isArray(fields)) return console.warn('字段请传入数组');
data=json.map(obj=>{
return fields.map(field=>{
return obj[field]
})
})
data.splice(0, 0, titleArr);
// fields为英文字段表头,一般不需要,需要直接把下面注释打开即可
// data.splice(0, 0, fields);
const ws = XLSX.utils.aoa_to_sheet(data);
const wb = XLSX.utils.book_new();
// 此处隐藏英文字段表头
let wsrows = [{ hidden: true }];
ws['!rows'] = wsrows; // ws - worksheet
XLSX.utils.book_append_sheet(wb, ws, sheetName);
/* generate file and send to client */
XLSX.writeFile(wb, name + '.xls');
}
2.在需要导出的地方调用封装的exportExcel方法
const titleArr = ['任务编号', '物料名称', '物料编码', '规格型号', '验证产品编码', '验证产品名称', "验证产品规格", "验证数量", "验证天数", "验证原因"]//表头第一行从左到右
const fields = ["taskCode", "materialName", "materialCode", "materialSpec", "productCode", "productName", "productSpec", "verifyQty", "verifyDays", "verifyReason"]//标题对应的字段名
exportExcel(tableList.value, 'test', titleArr, 'sheetName',fields ); //列表变量名、文件名、第一行标题、表名、字段名
举个例子
let table = [
{
"taskId": 25,
"taskCode": "202308020020",
"materialId": 812,
"materialName": "单片机",
"materialCode": "A0800150",
"materialSpec": "M483KIDAE"
},
{
"taskId": 24,
"taskCode": "202308020019",
"materialId": 1011,
"materialName": "排针",
"materialCode": "A1000076",
"materialSpec": "排针2*3"
},
{
"taskId": 23,
"taskCode": "202308020018",
"materialId": 962,
"materialName": "直插电容",
"materialCode": "A1000027",
"materialSpec": "105℃ 470UF/16V"
}
]
let titleArr=["任务编号","物料名称","物料编码"]
let fields = [
"taskCode", "materialName", "materialCode"
]
exportExcelTable(table , '验证任务', titleArr, 'sheetname', fields);
那么最终显示是这样的:
二、上传excel转成数组
import axios from 'axios'; //没安装先安装
async function importExcel(url){
let res = await axios.get(url, { responseType: 'arraybuffer' })
const data = new Uint8Array(res.data);
const workbook = XLSX.read(data, { type: 'array' });
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];
const result = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
// 获取属性名数组
let keys = result[0];
// 将属性名数组和数据数组转换为对象数组
let arr = result.slice(1).map(row => {
let obj = {};
keys.forEach((key, index) => {
obj[key] = row[index];
});
return obj;
});
return arr
}
调用函数即可,注意:得到的数组是个promise对象
const tableFn=async ()=>{
let url="http://xxx.cn.excel.xlsx"
let arr= await importExcel(url)
console.log(arr)
}
如有遇到相关问题欢迎评论区交流!!!