Vue3中导入导出Excel

Vue2中导出Excel见： https://mp.csdn.net/mp_blog/creation/editor/111305117https://mp.csdn.net/mp_blog/creation/editor/111305117

安装xlsx

npm install -S xlsx

引入xlsx

import * as xlsx from 'xlsx' // Vue3 版本

打印看一下XLSX：

封装表头转换函数 

首先封装一个导入导出时都会用到的表头转换函数：

//表头数据切换
const changeTableHead = (
  tableData = [
    { index: 0, username: "张三", password: 333, age: 22 },
    { index: 1, username: "李四", password: 444, age: 23 },
  ],
  fieldName = {
    index: "索引",
    username: "用户名",
    password: "密码",
  }
) => {
  const list = tableData.map((item) => {
    const obj = {};
    for (const k in item) {
      if (fieldName[k]) {
        obj[fieldName[k]] = item[k];
      }
    }
    return obj;
  });
  return list;
};

转换效果1：

  let tableData = [
    { index: 0, username: "张三", password: 333, age: 22 },
    { index: 1, username: "李四", password: 444, age: 23 },
  ];
  let fieldNameObj = {
    index: "索引",
    username: "用户名",
    password: "密码",
  };
  console.log("转换前：", tableData);
  let data1 = changeTableHead(tableData, fieldNameObj);
  console.log("转换后：", data1);

转换效果2：

 

  let tableData = [
    {
      索引: 0,
      用户名: "张三",
      密码: 333,
      年龄: 23,
    },
    {
      索引: 1,
      用户名: "李四",
      密码: 444,
      年龄: 33,
    },
  ];
  let fieldNameObj = {
    索引: "index",
    用户名: "username",
    密码: "password",
  };
  console.log("转换前：", tableData);
  let data1 = changeTableHead(tableData, fieldNameObj);
  console.log("转换后：", data1);

 

 导出Excel

 

const exportExcel = (
  tableData = [
    { index: 0, username: "张三", password: 333, age: 22 },
    { index: 1, username: "李四", password: 444, age: 23 },
  ],
  fileName = "用户列表",
  pageName = "Sheet1"
) => {
  let fieldNameObj = {
    index: "索引",
    username: "用户名",
    password: "密码",
  };
  const list = changeTableHead(tableData, fieldNameObj);
  // 创建工作表
  const data = xlsx.utils.json_to_sheet(list);
  // 创建工作簿
  const wb = xlsx.utils.book_new();
  // 将工作表放入工作簿中
  xlsx.utils.book_append_sheet(wb, data, pageName);
  // 生成文件并下载
  xlsx.writeFile(wb, `${fileName}.xlsx`);
};

导入并解析Excel

const readFile = (file: any) => {
  return new Promise((resolve) => {
    let reader = new FileReader();
    reader.readAsBinaryString(file);
    reader.onload = (ev) => {
      resolve(ev.target?.result);
    };
  });
};
const uploadChange = async (file: any) => {
  let dataBinary = await readFile(file.raw);
  let workBook = xlsx.read(dataBinary, { type: "binary", cellDates: true });
  let workSheet = workBook.Sheets[workBook.SheetNames[0]];
  const data = xlsx.utils.sheet_to_json(workSheet);
  console.log("excel数据", data);
  // 转换表头
  let excelData = changeTableHead(data, {
    索引: "index",
    用户名: "username",
    密码: "password",
  });
  console.log("转换excel表头:", excelData);
};

    <el-upload
      action=""
      :auto-upload="false"
      :on-change="uploadChange"
      :limit="1"
    >
      <el-button type="primary">上传Excel</el-button>
    </el-upload>