vue导入与导出 excel 表格内容

导入

我们要实现的效果是点击<el-button>按钮，然后选择excel文件，读取excel文件中的内容，并将内容展示到<el-table>表格中

HTML中的代码

<div class="excel">
      <el-upload
        class="upload-demo"
        action=""
        :on-change="handleChange"
        :show-file-list="false"
        :auto-upload="false"
      >
        <el-button size="small" type="primary">
          读取excel文件
         </el-button>
      </el-upload>

      <el-table :data="tableData" style="width: 100%">
        <el-table-column
          v-for="item in tabp"
          :key="item"
          :prop="item"
          :label="item"
          width="180"
        ></el-table-column>
        <el-table-column label="操作">
          <template slot-scope="scope">
            <a @click="handleDelete(scope.row)">删除</a> |
            <a @click="handelEdit(scope.row)">修改</a>
          </template>
        </el-table-column>
      </el-table>

    </div>

js部分的代码

GitHub - SheetJS/sheetjs: 📗 SheetJS Community Edition -- Spreadsheet Data Toolkit

npm install xlsx

import * as XLSX from "xlsx/xlsx.mjs";

 data() {
    return {
tableData: [],
    fileContent: '',
    file: '',
    data: '',
    tabp: []
           }
        },


methods: {
    handleDelete(item) {
      console.log("handleDelete", item);
    },
    handelEdit(item) {
      console.log("handleDelete", item);
      this.input = item.姓名1;
    },
    // 核心部分代码(handleChange 和 importfile)
    handleChange(file, fileList) {
      this.fileContent = file.raw;
      const fileName = file.name;
      const fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
      if (this.fileContent) {
        if (fileType === "xlsx" || fileType === "xls") {
          this.importfile(this.fileContent);
        } else {
          this.$message({
            type: "warning",
            message: "附件格式错误，请重新上传！",
          });
        }
      } else {
        this.$message({
          type: "warning",
          message: "请上传附件！",
        });
      }
    },
    importfile(obj) {
      this.tableData = [];
      this.tabp = [];
      const reader = new FileReader();
      reader.readAsArrayBuffer(obj);
      reader.onload = () => {
        const buffer = reader.result;
        const bytes = new Uint8Array(buffer);
        const length = bytes.byteLength;
        let binary = "";
        for (let i = 0; i < length; i++) {
          binary += String.fromCharCode(bytes[i]);
        }
        const wb = XLSX.read(binary, {
          type: "binary",
        });
        const outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
        for (let i in outdata[0]) {
//表格栏信息
          this.tabp.push(i);
          //i是index，arr[i]是item
        }
        this.tableData = outdata;
      };
    },
}

如果表格头信息是中文也可以 vue可以直接xx.中文拿值

V3 vite 版 html一样

declare module 'xlsx/xlsx.mjs' {

  export default any

}

import XLSX from "xlsx/xlsx.mjs";
let tableData = ref([]);
let fileContent = ref("");
let file = ref("");
let data = ref("");
let tabp = ref<any[]>([]);




const handleDelete = function (item: any) {
  console.log("handleDelete", item);
};
const handelEdit = function (item: any) {
  console.log("handleDelete", item.姓名1);
 
};
// 核心部分代码(handleChange 和 importfile)
const handleChange = function (file: any, fileList: any) {
  fileContent.value = file.raw;
  const fileName = file.name;
  const fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
  if (fileContent.value) {
    if (fileType === "xlsx" || fileType === "xls") {
      importfile(fileContent.value);
    } else {
      //   this.$message({
      //     type: "warning",
      //     message: "附件格式错误，请重新上传！",
      //   });
    }
  } else {
    // this.$message({
    //   type: "warning",
    //   message: "请上传附件！",
    // });
  }
};
const importfile = function (obj: any) {
  tableData.value = [];
  tabp.value = [];
  const reader = new FileReader();
  reader.readAsArrayBuffer(obj);
  reader.onload = () => {
    const buffer = reader.result;
    const bytes = new Uint8Array(buffer as any);
    const length = bytes.byteLength;
    let binary = "";
    for (let i = 0; i < length; i++) {
      binary += String.fromCharCode(bytes[i]);
    }
    const wb = XLSX.read(binary, {
      type: "binary",

    });
    const outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
    for (let i in outdata[0]) {
      //表格栏信息
      tabp.value.push(i);
      //i是index，arr[i]是item
    }
    tableData.value = outdata;
  };
};

导出

v2

npm install vue-json-excel -S

main.js

import JsonExcel from 'vue-json-excel'

Vue.component('downloadExcel', JsonExcel) //全局注册

或者在vue文件里引入也可

 html代码

  <template>
              <download-excel
              class="export-excel-wrapper"
              :data="DetailsForm"
              :fields="json_fields"
              :header="title"
              name="需要导出的表格名字.xls"
            >
              <!-- 上面可以自定义自己的样式，还可以引用其他组件button -->
              <el-button type="success">导出</el-button>
              </download-excel>
              </template>

data

title: "xx公司表格",
 json_fields: {
        "排查日期":'date',
        "整改隐患内容":'details',
        "整改措施":'measure',
        "整改时限":'timeLimit',
        "应急措施和预案":'plan',
        "整改责任人":'personInCharge',
        "填表人":'preparer',
        "整改资金":'fund',
        "整改完成情况":'complete',
        "备注":'remark',
      },
    DetailsForm: [
        {
          date: "2022-3-10",
          details: "卸油区过路灯损坏",
          measure: "更换灯泡",
          timeLimit: "2022-3-21",
          plan: "先使用充电灯代替,贴好安全提醒告示",
          personInCharge: "王xx",
          preparer: "王xx",
          fund: "20元",
          complete: "已完成整改",
          remark: "重新更换了灯泡",
        },
       
      ],

• DetailsForm：需要导出的数据

• title：表格标题 导出时放在表格的第一条 和导入同时使用时建议不设置

• json_fields：里面的属性是excel表每一列的title的中文版,用多个词组组成的属性名(中间有空格的)要加双引号; 指定接口的json内某些数据下载,若不设置，默认导出源数据的英文对象名

• 一些常用参数

  

• V3

  npm install vue-json-excel3

declare module 'vue-json-excel3'

• main.ts

  import JsonExcel from "vue-json-excel3";
  const app = createApp(App)
  app.component("downloadExcel", JsonExcel)

• html

      <downloadExcel class="export-excel-wrapper" :data="DetailsForm" :fields="json_fields" name="需要导出的表格名字.xls">

        <el-button type="success">导出</el-button>

      </downloadExcel>

常用参数和v2一样