vue项目实现导入/导出Excel

前端方案

首先安装依赖包

npm install xlsx --save

前端实现方案

<template>
  <div>
    <label for="file">
      <div>导入Excel</div>
    </label>
    <input type="file" name="file" id="file" ref="file" @change="onChange" />
    <div @click="exportExcel">导出Excel</div>
  </div>
</template>
<script>
import * as XLSX from "xlsx";
export default {
  data() {
    return {
      tableData: [
        { index: 1, time: "2008-01-01", state: "禁用", power: "200MW" },
        { index: 2, time: "2009-01-01", state: "禁用", power: "300MW" },
        { index: 3, time: "2010-01-01", state: "可用", power: "400MW" },
        { index: 4, time: "2011-01-01", state: "可用", power: "500MW" },
        { index: 5, time: "2012-01-01", state: "禁用", power: "600MW" },
      ],
    };
  },
  mounted() {},
  methods: {
    exportExcel() {
      const fdArrayList = this.tableData;
      const fdArray = [];
      fdArrayList.forEach(function (data, index) {
        var obj = {
          序号: index + 1,
          时段: data.time,
          "电力(MW)": data.power,
          状态: data.state,
        };
        fdArray.push(obj);
      });
      // 新建一个excel.xlsx
      var wb = XLSX.utils.book_new();
      // //封装JSON 数据
      var fdXslxws = XLSX.utils.json_to_sheet(fdArray);
      XLSX.utils.book_append_sheet(wb, fdXslxws, "sheet");
      XLSX.writeFile(wb, "机组固定出力" + ".xlsx");
      this.$vMessage({ type: "success", message: "导出成功！" });
    },
    onChange(file) {
      let files = file.target.files[0];
      if (!files) {
        // 如果没有文件
        return false;
      } else if (!/\.(xls|xlsx)$/.test(files.name.toLowerCase())) {
        alert("上传格式不正确，请上传xls或者xlsx格式");
        return false;
      }
      const fileReader = new FileReader();
      fileReader.onload = (e) => {
        try {
          const data = e.target.result;
          const workbook = XLSX.read(data, {
            type: "binary",
          });
          const wsname = workbook.SheetNames[0]; // 取第一张表
          const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]); // 生成json表格内容
          console.log("ws", ws);
          let tempList = [];
          for (var i = 0; i < ws.length; i++) {
            if (
              ws[i].hasOwnProperty("序号") &&
              ws[i].hasOwnProperty("时段") &&
              ws[i].hasOwnProperty("状态") &&
              ws[i].hasOwnProperty("电力(MW)")
            ) {
              let sheetData = {
                // 键名为绑定 el 表格的关键字，值则是 ws[i][对应表头名]
                index: ws[i]["序号"],
                time: ws[i]["时段"],
                state: ws[i]["状态"],
                power: ws[i]["电力(MW)"],
              };
              tempList.push(sheetData);
            } else {
              alert("导入Excel格式不正确");
              return;
            }
          }
          this.tableData = tempList;
          alert("导入成功");
        } catch (e) {
          return false;
        }
      };
      fileReader.readAsBinaryString(files);
      this.$refs.file.value = "";
    },
  },
};
</script>
<style>
#file {
  opacity: 0;
  position: absolute;
  z-index: -1;
}
</style>

后端处理导出

前端通过字节流或者url实现导出，字节流方式导出的文件方式可以通过前端实现文件名称的修改，url导出方式则不能修改导出的文件名（文件名由后端提供）。

<template>
  <div class="export-file-box">
    <div class="title">导出文件</div>
    <el-button plain class="el-btn" @click="exportFileExcel"
      >导出Excel文件(字节流方式)</el-button
    >
    <el-button plain class="el-btn" @click="exportFilePdf"
      >导出PDF文件(url方式)</el-button
    >
  </div>
</template>

<script>
import axios from "axios";
export default {
  data() {
    return {};
  },
  methods: {
    exportFileExcel() {
      let sendData = {};
      axios({
        method: "GET",
        url: "user/schedule/other/exportScheduleList",
        params: sendData,
        responseType: "blob",
      })
        .then((res) => {
          let blob = new Blob([res.data], {
            type: "application/vnd.ms-excel",
          });
          let url = window.URL.createObjectURL(blob);
          var a = document.createElement("a");
          document.body.appendChild(a);
          a.style = "display: none";
          a.href = url;
          a.download = "日程信息表";
          a.click();

          this.close();
        })
        .catch((err) => {});
    },
    exportFilePdf() {
      var a = document.createElement("a");
      document.body.appendChild(a);
      a.style = "display: none";
      a.href =
        "https://aym-1300578978.cos.ap-beijing.myqcloud.com/unstructured/ismilework/article/content/PDF%E6%A8%A1%E6%9D%BF.pdf";
      a.click();
    },
  },
};
</script>

<style lang="scss" scoped>
</style>