上传Excel文件

Element 组件

1、常用MIME类型
后缀名       MIME名称
*.3gpp    audio/3gpp, video/3gpp
*.ac3    audio/ac3
*.asf       allpication/vnd.ms-asf
*.au           audio/basic
*.css           text/css
*.csv           text/csv
*.doc    application/msword   
*.dot    application/msword   
*.dtd    application/xml-dtd   
*.dwg    image/vnd.dwg   
*.dxf      image/vnd.dxf
*.gif            image/gif   
*.htm    text/html   
*.html    text/html   
*.jp2            image/jp2   
*.jpe       image/jpeg
*.jpeg    image/jpeg
*.jpg          image/jpeg   
*.js       text/javascript, application/javascript   
*.json    application/json   
*.mp2    audio/mpeg, video/mpeg   
*.mp3    audio/mpeg   
*.mp4    audio/mp4, video/mp4   
*.mpeg    video/mpeg   
*.mpg    video/mpeg   
*.mpp    application/vnd.ms-project   
*.ogg    application/ogg, audio/ogg   
*.pdf    application/pdf   
*.png    image/png   
*.pot    application/vnd.ms-powerpoint   
*.pps    application/vnd.ms-powerpoint   
*.ppt    application/vnd.ms-powerpoint   
*.rtf            application/rtf, text/rtf   
*.svf           image/vnd.svf   
*.tif         image/tiff   
*.tiff       image/tiff   
*.txt           text/plain   
*.wdb    application/vnd.ms-works   
*.wps    application/vnd.ms-works   
*.xhtml    application/xhtml+xml   
*.xlc      application/vnd.ms-excel   
*.xlm    application/vnd.ms-excel   
*.xls           application/vnd.ms-excel   
*.xlt      application/vnd.ms-excel   
*.xlw      application/vnd.ms-excel   
*.xml    text/xml, application/xml   
*.zip            aplication/zip   
*.xlsx     application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
　　

<el-button type="primary" @click="uploadDialog=true" icon="el-icon-upload">上传</el-button>

        <el-dialog title="上传文件" :visible.sync="uploadDialog" class="eldialog">
          <el-form ref="addForm" class="eldialogForm" id="addForm">
            <el-form-item label >
              <el-upload
                class="upload-demo"
                drag
                :before-upload="beforeUpload"
                :on-exceed="handleExceed"
                :limit="1"       //只允许一次上传一个文件
                :http-request="uploadFile"
                multiple
                ref="upload"
                action
              >
                <i class="el-icon-upload"></i>
                <div class="el-upload__text">
                  将文件拖到此处，或
                  <em>点击上传</em>
                </div>
                <div class="el-upload__tip" slot="tip">只能上传Excel文件，且不超过500kb</div>
              </el-upload>
            </el-form-item>
          </el-form>
          <div slot="footer" class="dialog-footer">
            <el-button @click="closeFile()">取 消</el-button>
            <el-button type="primary" @click="postFile()" :disabled="uploading">确 定</el-button>
          </div>
        </el-dialog>

data（）{
return{
      //上传excel表格
      uploadDialog: false,
      file: [],
}
}，
  methods: {
      //上传excel表格
    beforeUpload(file) {
      if (file.type == "" || file.type == null || file.type == undefined) {
        const FileExt = file.name.replace(/.+\./, "").toLowerCase();
        if (
          FileExt == "xls" ||
          FileExt == "xlsx" ||
          FileExt == "XLS" ||
          FileExt == "XLSX"
        ) {
          return true;
        } else {
          this.$message.error("上传文件必须是Excel格式!");
          return false;
        }
      } else {
        const isText = file.type === "application/vnd.ms-excel";
        const isTextComputer =
          file.type ===
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        if (!isText && !isTextComputer) {
          this.$message.error("上传文件必须是Excel格式!");
        }
        return isText || isTextComputer;
      }
    },
    // 上传文件个数超过定义的数量
    handleExceed(files, fileList) {
      this.$message.warning(`当前限制选择 1 个文件，请删除后继续上传`);
    },
    uploadFile(item) {
      this.file = item.file;
    },
    postFile() {
      const fileObj = this.file;
      var fileData = new FormData();
      fileData.append("file", fileObj);
      let headers = {
        "Content-Type": "multipart/form-data"
      };
      this.uploading = true;
      this.$axios({
        method: "post",
        url: url + "/upload",    //填写上传的接口
        headers: headers,
        data: fileData
      }).then(res => {
        if (res.data.code === 200) {
          this.$message.success(res.data.msg);
          this.uploadDialog = false;
        } else {
          this.$message.error(res.data.msg);
        }
      });
      setTimeout(function() {
        this.uploading = false;
      }, 1500);
    },
    colseFile() {
      this.uploadDialog = false;
    },
  }

下载后端返回来的EXcel文件路径，是url不是二进制流文件（get方式）

   <el-button type="info" icon="el-icon-download" @click="download()">下载Excel模板</el-button>
   
    download() {
      this.$message.info("开始下载模板");
      let url = “后端给的路径”;   //填写后端的路径
      let iframe = document.createElement("iframe");
      iframe.style.display = "none";
      iframe.src = url;
      iframe.onload = function() {
        document.body.removeChild(iframe);
      };
      document.body.appendChild(iframe);   //第一种方式效果较好
      // window.open( “后端给的路径”);        //   第二种方式,采用打开新窗口，会出现屏幕快闪一下，效果不好
    },

element 导出table当前页数据或后端返回的所有数据

1.安装3个依赖
file-saver， xlsx，script-loader

npm install -S file-saver xlsx
npm install -D script-loader

2.导入两个JS

下载Blob.js和Export2Excel.js，在src目录下新建Excel文件夹，里面放入Blob.js和Export2Excel.js两个JS文件

链接：https://pan.baidu.com/s/1wUvD0A3hrBD4K7dplHnNRg 
提取码：a3ch 

打开Export2Excel.js修改成正确的引入路径

3.main.js 引入文件

//引入
import Blob from '@/excel/Blob'
import Export2Excel from '@/excel/Export2Excel.js'

4.组件中使用

//template中添加按钮点击export2Excel()下载事件
  <el-button type="primary" icon="el-icon-download" @click="export2Excel()">导出Excel</el-button>
  data（）{
  return{
  tableDataexcel:[],        //后端接口返回来的table数据
  }
  }
 // methods中添加
 //导出excel
    export2Excel() {
      this.$axios.get("与后端请求数据的url接口").then(res => {
        if (res.data.code == 200) {
          this.tableDataexcel = res.data.data;       //后端接口返回的list数据赋值到tableDataexcel上
          require.ensure([], () => {
            let time = new Date();
            let year = time.getFullYear();
            let month = time.getMonth() + 1;
            let day = time.getDate();
            let name = year + "." + month + "." + day;               //获取当前的时间添加到文件名上，可以知道是什么时候导出的文件
            const {
              export_json_to_excel
            } = require("./../../../excel/Export2Excel.js");            //注意修改这里请求的路径，当前页面与Export2Excel.js的位置
            const tHeader = ["序号", "名称","地址"]; // 上面设置Excel的表格第一行的标题
            const filterVal = ["id", "name","address"]; // 上面的id、name、address是tableDataexcel里对象的属性
            const list = this.tableDataexcel; //把data里的tableDataexcel存到list
            const data = this.formatJson(filterVal, list);
            export_json_to_excel(tHeader, data, name + "excel列表");             //表头，数据，文件名（格式为：2019.7.9excel列表 ，可修改）
          });
        } else {
          this.$message.info(res.data.msg);
        }
      });
    },
      formatJson(filterVal, jsonData) {
      return jsonData.map(v => filterVal.map(j => v[j]));
    },

如果是导出当前页面的数据（一开始页面就加载好的当前页面数据）

//template中添加按钮点击export2Excel()下载事件
  <el-button type="primary" icon="el-icon-download" @click="export2Excel()">导出Excel</el-button>
  data（）{
  return{
  tableDataexcel:[],        //后端接口返回来的table数据（一开始页面就加载好的当前页面数据）
  }
  }
 // methods中添加
 //导出excel
    export2Excel() {
          require.ensure([], () => {
            let time = new Date();
            let year = time.getFullYear();
            let month = time.getMonth() + 1;
            let day = time.getDate();
            let name = year + "." + month + "." + day;               //获取当前的时间添加到文件名上，可以知道是什么时候导出的文件
            const {
              export_json_to_excel
            } = require("./../../../excel/Export2Excel.js");            //注意修改这里请求的路径，当前页面与Export2Excel.js的位置
            const tHeader = ["序号", "名称","地址"]; // 上面设置Excel的表格第一行的标题
            const filterVal = ["id", "name","address"]; // 上面的id、name、address是tableDataexcel里对象的属性
            const list = this.tableDataexcel; //把data里的tableDataexcel存到list
            const data = this.formatJson(filterVal, list);
            export_json_to_excel(tHeader, data, name + "excel列表");             //表头，数据，文件名（格式为：2019.7.9excel列表 ，可修改）
          });
    },
      formatJson(filterVal, jsonData) {
      return jsonData.map(v => filterVal.map(j => v[j]));
    },

利用SheetJS js-xlsx导出多个sheet的excel（接口请求后端返回的json数据前端导出）

1.vue安装依赖

npm install xlsx
或
npm install --save xlsx

2.组件内引入

import XLSX from "xlsx";

3.完整示例代码
1.示例中用了element ui组件的按钮

<template>
  <el-container>
    <el-main>
      <el-button type="primary" icon="el-icon-download" @click="export2Excel()">导出</el-button>
    </el-main>
  </el-container>
</template>


<script>
import XLSX from "xlsx";
export default {
  name: "FuncFormsJStest",
  
  mounted: function() {},
  watch: {},
  data() {
    return {
      tabledata1: [
        { Sheet1: "111", 名称: "6800", 数量: "6800", 昵称: "广告主网" },
        { Sheet1: "433", 名称: "6800", 数量: "6800", 昵称: "广告主网" }
      ],

      tabledata2: [
        { Sheet2: "100", 名称: "6800", 数量: "6800", 昵称: "广告主网" },
        { Sheet2: "433", 名称: "6800", 数量: "6800", 昵称: "广告主网" }
      ]
    };
  },
  methods: {
    export2Excel() {
      const ws1 = XLSX.utils.json_to_sheet(this.tabledata1); //第一个Sheet2的数据
      const ws2 = XLSX.utils.json_to_sheet(this.tabledata2); //第二个Sheet2的数据
      /* generate workbook and add the worksheet */
      const wb = XLSX.utils.book_new();
      XLSX.utils.book_append_sheet(wb, ws1, "Sheet1");
      XLSX.utils.book_append_sheet(wb, ws2, "Sheet2"); //声明两个就是一个excel文件包含两个Sheet，‘Sheet2’为自己自定义名
      /* workbook now has 2 worksheets */

      /* save to file */
      XLSX.writeFile(wb, "SheetJS.xlsx");   // "SheetJS.xlsx"文件命名必须为字符串且含。xlsx后缀，不然导出的文件无法打开，可以自定义文件名，写法为：yourname+‘.xlsx’,例如 var yourname=‘多个sheet的excel’；XLSX.writeFile(wb,yourname+".xlsx");最后的文件名就是：多个sheet的excel.xlsx
    }
  }
};
</script>
<style scoped>
</style>