vue导出/导入Excel文件

1、在vue中使用导入导出，需要下载3个依赖包和2个js包：

npm install -S file-saver xlsx（这里其实安装了2个依赖）

npm install -D script-loader

2、在src目录下新建一个excel文件夹引入Blob.js和expor2Excal.js

3、在main.js中全局引入

import Blob from ‘./excel/Blob.js’
import Export2Excel from ‘./excel/Export2Excel.js’

4、webpack.base.conf.js做如下修改：

resolve: {
    extensions: ['.js', '.vue', '.json'],
    alias: {
      'vue$': 'vue/dist/vue.esm.js',
      '@': resolve('src'),
      'scss_vars': '@/styles/vars.scss',
      //新增加一行
      'excel': path.resolve(__dirname, '../src/excel'),
    }
  },

5、导出Excel

导出方法

//导出excel
//_tHeader :['名称','性别']
//_filterVal:['name','sex']
//_exData:需导出内容
//_fileName:文件名称
export const export2Excel = function (_tHeader, _filterVal, _exData, _fileName) {
    require.ensure([], () => {
        const { export_json_to_excel } = require('../excel/Export2Excel'); //这里必须使用绝对路径
        const tHeader = _tHeader; // 导出的表头名
        const filterVal = _filterVal; // 导出的表头字段名

        const data = formatJson(filterVal, _exData);//转换格式
        export_json_to_excel(tHeader, data, _fileName);// 导出的表格名称，根据需要自己命名
    })
}
export const formatJson = function (filterVal, jsonData) {
    return jsonData.map(v => filterVal.map(j => v[j]))
}

调用

methods：{
	outExe(){
		let tHeader = ['姓名', '部门', '岗位']
        let filterVal = ['createName', 'departmentName', 'posName']
        const list = this.data;
        if (list.length) {
          export2Excel(tHeader, filterVal, list, `人员信息`);
        } else {
          this.$message.error('暂无数据可导出')
        }
     }
}

6、导入

方法

import XLSX from 'xlsx'
/**
 * 
 * @param {File} file 文件
 * @param {Object} codeObj 每个对象所包含的属性，属性名
 * @return {Promise} 读取文件为异步的，
 * 调用例如excel(file.raw, obj).then(res => {console.log(res)})，res为最终得到的数组数据
 */
export default function (file, codeObj) {

    return new Promise(resolve => {

        var reader = new FileReader();

        FileReader.prototype.readAsBinaryString = function (f) {

            var binary = "";

            var wb; //读取完成的数据

            var outdata;

            var reader = new FileReader();

            reader.onload = function (e) {

                var bytes = new Uint8Array(reader.result);
                
                var length = bytes.byteLength;

                for (var i = 0; i < length; i++) {

                    binary += String.fromCharCode(bytes[i]);

                }

                wb = XLSX.read(binary, { type: 'binary' });

                // outdata就是你想要的东西 excel导入的数据
                outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);

                // excel 数据再处理

                let arr = []

                outdata.map(v => {

                    let obj = {}

                    for (let key in codeObj) {
                        obj[key] = v[codeObj[key]]
                    }
                    arr.push(obj)

                })

                resolve([...arr]);
            }
            reader.readAsArrayBuffer(f);

        }

        reader.readAsBinaryString(file)

    })
}

调用

<el-upload action :auto-upload="false" :show-file-list="false" :on-change="uploadChange" >
	<el-button type="success" plain icon="el-icon-upload">导入</el-button>
</el-upload>

methods：{
	uploadChange(file) {
      let obj = {
        name: "姓名",
        mobile: "手机号",
        email: "邮箱",
      }
      excel(file.raw, obj).then(res => {
        res.map(item => {
          item.mobile = item.mobile + '';
        })
        this.tableData = res;          
      })
    },
}

导入的时间格式异常转换
console.log(formatDate(43320, ‘-’)) // 2018-8-7

formatDate(numb, format = "/") {
      if (!numb) return;
      const time = new Date((numb - 1) * 24 * 3600000 + 1)
      time.setYear(time.getFullYear() - 70)
      const year = time.getFullYear() + ''
      const month = time.getMonth() + 1 + ''
      const date = time.getDate() - 1 + ''
      if (format && format.length === 1) {
        return year + format + month + format + date
      }
      return year + (month < 10 ? '0' + month : month) + (date < 10 ? '0' + date : date)
    },