vue后台管理项目常见的Excel导入导出功能

2,893 阅读2分钟
安装依赖包
npm install -S file-saver xlsx
npm install -D script-loader

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

1624692383(1).jpg

webpack.base.conf.js做如下修改:

image.png

重点:在你项目的工具js里面加上下面本人封装好的导入导出的方法

导入方法:

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, needAll) {

    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', cellDates: true });
                let opt = {
                    defval: '',         // 设置默认值为null, 没设置的话,单元格为空的时候会缺少相应的key
                }
                // outdata就是你想要的东西 excel导入的数据
                outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], opt);

                // excel 数据再处理
                let arr = []

                outdata.map(v => {

                    let obj = {}
                    for (let key in codeObj) {
                        obj[key] = v[codeObj[key]] || '';
                    }
                    if (JSON.stringify(obj) !== '{}') {
                        arr.push(obj)
                    }
                })
                if(needAll) {
                    resolve({data: [...arr], dataAll: outdata})
                }else {
                    resolve([...arr]);
                }
                
            }
            reader.readAsArrayBuffer(f);

        }

        reader.readAsBinaryString(file)

    })
}

导出的方法:

//导出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 list = exData;

    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]));
};

Blob.js文件

/* eslint-disable */
/* Blob.js
 * A Blob implementation.
 * 2014-05-27
 *
 * By Eli Grey, http://eligrey.com
 * By Devin Samarin, https://github.com/eboyjr
 * License: X11/MIT
 *   See LICENSE.md
 */

/*global self, unescape */
/*jslint bitwise: true, regexp: true, confusion: true, es5: true, vars: true, white: true,
 plusplus: true */

/*! @source http://purl.eligrey.com/github/Blob.js/blob/master/Blob.js */

(function (view) {
    "use strict";

    view.URL = view.URL || view.webkitURL;

    if (view.Blob && view.URL) {
        try {
            new Blob;
            return;
        } catch (e) {}
    }

    // Internally we use a BlobBuilder implementation to base Blob off of
    // in order to support older browsers that only have BlobBuilder
    var BlobBuilder = view.BlobBuilder || view.WebKitBlobBuilder || view.MozBlobBuilder || (function(view) {
            var
                get_class = function(object) {
                    return Object.prototype.toString.call(object).match(/^\[object\s(.*)\]$/)[1];
                }
                , FakeBlobBuilder = function BlobBuilder() {
                    this.data = [];
                }
                , FakeBlob = function Blob(data, type, encoding) {
                    this.data = data;
                    this.size = data.length;
                    this.type = type;
                    this.encoding = encoding;
                }
                , FBB_proto = FakeBlobBuilder.prototype
                , FB_proto = FakeBlob.prototype
                , FileReaderSync = view.FileReaderSync
                , FileException = function(type) {
                    this.code = this[this.name = type];
                }
                , file_ex_codes = (
                    "NOT_FOUND_ERR SECURITY_ERR ABORT_ERR NOT_READABLE_ERR ENCODING_ERR "
                    + "NO_MODIFICATION_ALLOWED_ERR INVALID_STATE_ERR SYNTAX_ERR"
                ).split(" ")
                , file_ex_code = file_ex_codes.length
                , real_URL = view.URL || view.webkitURL || view
                , real_create_object_URL = real_URL.createObjectURL
                , real_revoke_object_URL = real_URL.revokeObjectURL
                , URL = real_URL
                , btoa = view.btoa
                , atob = view.atob

                , ArrayBuffer = view.ArrayBuffer
                , Uint8Array = view.Uint8Array
                ;
            FakeBlob.fake = FB_proto.fake = true;
            while (file_ex_code--) {
                FileException.prototype[file_ex_codes[file_ex_code]] = file_ex_code + 1;
            }
            if (!real_URL.createObjectURL) {
                URL = view.URL = {};
            }
            URL.createObjectURL = function(blob) {
                var
                    type = blob.type
                    , data_URI_header
                    ;
                if (type === null) {
                    type = "application/octet-stream";
                }
                if (blob instanceof FakeBlob) {
                    data_URI_header = "data:" + type;
                    if (blob.encoding === "base64") {
                        return data_URI_header + ";base64," + blob.data;
                    } else if (blob.encoding === "URI") {
                        return data_URI_header + "," + decodeURIComponent(blob.data);
                    } if (btoa) {
                        return data_URI_header + ";base64," + btoa(blob.data);
                    } else {
                        return data_URI_header + "," + encodeURIComponent(blob.data);
                    }
                } else if (real_create_object_URL) {
                    return real_create_object_URL.call(real_URL, blob);
                }
            };
            URL.revokeObjectURL = function(object_URL) {
                if (object_URL.substring(0, 5) !== "data:" && real_revoke_object_URL) {
                    real_revoke_object_URL.call(real_URL, object_URL);
                }
            };
            FBB_proto.append = function(data/*, endings*/) {
                var bb = this.data;
                // decode data to a binary string
                if (Uint8Array && (data instanceof ArrayBuffer || data instanceof Uint8Array)) {
                    var
                        str = ""
                        , buf = new Uint8Array(data)
                        , i = 0
                        , buf_len = buf.length
                        ;
                    for (; i < buf_len; i++) {
                        str += String.fromCharCode(buf[i]);
                    }
                    bb.push(str);
                } else if (get_class(data) === "Blob" || get_class(data) === "File") {
                    if (FileReaderSync) {
                        var fr = new FileReaderSync;
                        bb.push(fr.readAsBinaryString(data));
                    } else {
                        // async FileReader won't work as BlobBuilder is sync
                        throw new FileException("NOT_READABLE_ERR");
                    }
                } else if (data instanceof FakeBlob) {
                    if (data.encoding === "base64" && atob) {
                        bb.push(atob(data.data));
                    } else if (data.encoding === "URI") {
                        bb.push(decodeURIComponent(data.data));
                    } else if (data.encoding === "raw") {
                        bb.push(data.data);
                    }
                } else {
                    if (typeof data !== "string") {
                        data += ""; // convert unsupported types to strings
                    }
                    // decode UTF-16 to binary string
                    bb.push(unescape(encodeURIComponent(data)));
                }
            };
            FBB_proto.getBlob = function(type) {
                if (!arguments.length) {
                    type = null;
                }
                return new FakeBlob(this.data.join(""), type, "raw");
            };
            FBB_proto.toString = function() {
                return "[object BlobBuilder]";
            };
            FB_proto.slice = function(start, end, type) {
                var args = arguments.length;
                if (args < 3) {
                    type = null;
                }
                return new FakeBlob(
                    this.data.slice(start, args > 1 ? end : this.data.length)
                    , type
                    , this.encoding
                );
            };
            FB_proto.toString = function() {
                return "[object Blob]";
            };
            FB_proto.close = function() {
                this.size = this.data.length = 0;
            };
            return FakeBlobBuilder;
        }(view));

    view.Blob = function Blob(blobParts, options) {
        var type = options ? (options.type || "") : "";
        var builder = new BlobBuilder();
        if (blobParts) {
            for (var i = 0, len = blobParts.length; i < len; i++) {
                builder.append(blobParts[i]);
            }
        }
        return builder.getBlob(type);
    };
}(typeof self !== "undefined" && self || typeof window !== "undefined" && window || this.content || this));

Export2Excel.js文件

/* eslint-disable */
require('script-loader!file-saver');
// require('script-loader!@excel/Blob');
import './Blob'
require('script-loader!xlsx/dist/xlsx.core.min');
function generateArray(table) {
    var out = [];
    var rows = table.querySelectorAll('tr');
    var ranges = [];
    for (var R = 0; R < rows.length; ++R) {
        var outRow = [];
        var row = rows[R];
        var columns = row.querySelectorAll('td');
        for (var C = 0; C < columns.length; ++C) {
            var cell = columns[C];
            var colspan = cell.getAttribute('colspan');
            var rowspan = cell.getAttribute('rowspan');
            var cellValue = cell.innerText;
            if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;

            //Skip ranges
            ranges.forEach(function (range) {
                if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
                    for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
                }
            });

            //Handle Row Span
            if (rowspan || colspan) {
                rowspan = rowspan || 1;
                colspan = colspan || 1;
                ranges.push({ s: { r: R, c: outRow.length }, e: { r: R + rowspan - 1, c: outRow.length + colspan - 1 } });
            }
            ;

            //Handle Value
            outRow.push(cellValue !== "" ? cellValue : null);

            //Handle Colspan
            if (colspan) for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
        }
        out.push(outRow);
    }
    return [out, ranges];
};

function datenum(v, date1904) {
    if (date1904) v += 1462;
    var epoch = Date.parse(v);
    return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}

function sheet_from_array_of_arrays(data, opts) {
    var ws = {};
    var range = { s: { c: 10000000, r: 10000000 }, e: { c: 0, r: 0 } };
    for (var R = 0; R != data.length; ++R) {
        for (var C = 0; C != data[R].length; ++C) {
            if (range.s.r > R) range.s.r = R;
            if (range.s.c > C) range.s.c = C;
            if (range.e.r < R) range.e.r = R;
            if (range.e.c < C) range.e.c = C;
            var cell = { v: data[R][C] };
            if (cell.v == null) continue;
            var cell_ref = XLSX.utils.encode_cell({ c: C, r: R });

            if (typeof cell.v === 'number') cell.t = 'n';
            else if (typeof cell.v === 'boolean') cell.t = 'b';
            else if (cell.v instanceof Date) {
                cell.t = 'n';
                cell.z = XLSX.SSF._table[14];
                cell.v = datenum(cell.v);
            }
            else cell.t = 's';

            ws[cell_ref] = cell;
        }
    }
    if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
    return ws;
}

function Workbook() {
    if (!(this instanceof Workbook)) return new Workbook();
    this.SheetNames = [];
    this.Sheets = {};
}

function s2ab(s) {
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
    return buf;
}

export function export_table_to_excel(id) {
    var theTable = document.getElementById(id);
    console.log('a')
    var oo = generateArray(theTable);
    var ranges = oo[1];

    /* original data */
    var data = oo[0];
    var ws_name = "SheetJS";
    console.log(data);

    var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);

    /* add ranges to worksheet */
    // ws['!cols'] = ['apple', 'banan'];
    ws['!merges'] = ranges;

    /* add worksheet to workbook */
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;

    var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' });

    saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), "test.xlsx")
}

function formatJson(jsonData) {
    console.log(jsonData)
}
export function export_json_to_excel(th, jsonData, defaultTitle) {

    /* original data */

    var data = jsonData;
    data.unshift(th);
    var ws_name = "SheetJS";

    var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);


    /* add worksheet to workbook */
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;

    var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' });
    var title = defaultTitle || '列表'
    saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), title + ".xlsx")
}

导入,导出使用记录(主要是怕自己忘了,嘿嘿,引用的代码已忽略)

// 引用的代码已忽略

 // 导入
<el-upload action :auto-upload="false" :show-file-list="false" accept=".xlsx,.xls" :on-change="uploadChange" style="display: inline-block">
  <el-button type="text" size="medium" v-tip.top="'导入'" icon="iconfont icon-daoru"></el-button>
</el-upload>
// methods里的使用
    uploadChange(file) {
      let obj = {
        name: "姓名",
        mobile: "手机号",
        email: "邮箱",
        identityCardNo: "身份证号",
        idcarddate: "身份证开始时间",
        expirationdate: "身份证结束时间",
        originPlace: "籍贯",
        nation: "民族",
        contactPersonName: "联系人姓名",
        relationShip: "联系人关系",
        contactPersonPhone: "联系人电话",
        liveAddress: "居住地址",
        education: "学历",
        school: "毕业院校",
        college: "专业",
        schoolTime: "毕业时间",
        entryDate: "入职时间",
        bankAccountNo: "银行账号",
        bankAccountName: "户名",
        status: "状态",
        time: "时间",
      };
      excel(file.raw, obj).then((res) => {
        if (!res.length) {
          this.$message.warning("请检查导入文件,未检测到符合模板的数据!");
          return;
        }

        res.map((item) => {
          item.mobile = item.mobile + "";
          item.idcarddate = item.idcarddate
            ? shortTime(item.idcarddate, "YYYY/MM/DD")
            : "";
          item.expirationdate = item.expirationdate
            ? shortTime(item.expirationdate, "YYYY/MM/DD")
            : "";
          item.contactPersonPhone = item.contactPersonPhone + "";
          item.schoolTime = item.schoolTime
            ? shortTime(item.schoolTime, "YYYY/MM/DD")
            : "";
          item.entryDate = item.entryDate
            ? shortTime(item.entryDate, "YYYY/MM/DD")
            : "";
          item.nation = this.getValue(this.nationList, item.nation);
          item.relationShip = this.getValue(
            this.relationList,
            item.relationShip
          );
          item.education = this.getValue(this.eduList, item.education);
          item.status = this.getValue(this.WorkStatusList, item.status);
        });

        if (res) {
          let params = {
            operatorId: this.$storage.userMessage("employeeId").employeeId,
            employeeList: res,
          };
          batchImportEmployee({ data: params }).then((result) => {
            if (result.code === 0) {
              this.tableData = result.data;
              this.message(result.message);
            } else {
              this.$message.error(result.message);
            }
          });
        }
      });
    },
    
// 导出
    outExe() {
      this.$confirm('此操作将导出excel文件, 是否继续?', '提示', {
        confirmButtonText: '确定',
        cancelButtonText: '取消',
        type: 'warning'
      }).then(() => {
        getCheckWorkAttendanceAggregateList({ data: this.filterData }).then(res => {
          if (res.code === 0) {
            if (res.data.length > 0) {
              const tHeader = ['工作日', '员工姓名', '所属部门', '岗位名称', '职级', '上班班次时间', '上班打卡时间', '上班打卡结果', '下班班次时间', '下班打卡时间', '下班打卡结果',]; // 导出的表头名
              const filterVal = ['workDate', 'employeeName', 'departmentName', 'positionName', 'rank', 'onDutyClassTime', 'onDutyUserCheckTime', 'onDutyTimeResult', 'offDutyClassTime', 'offDutyUserCheckTime', 'offDutyTimeResult',]; // 导出的表头字段名
              const list = this.filterTableData(res.data);
              export2Excel(tHeader, filterVal, list, "考勤信息");
            } else {
              this.$message.error("暂无数据可导出!");
            }

          } else {
            this.$message.error(res.message);
          }
        });
      }).catch(() => {

      });
    },
    filterTableData(data) {
      data.forEach(element => {
        element.workDate = this.$options.filters["shorttime"](element.workDate, "YYYY-MM-DD", true);
        element.rank = this.$options.filters["rankFormat"](element.rank);
        element.onDutyClassTime = this.$options.filters["time"](element.onDutyClassTime, "HH:mm", true);
        element.onDutyUserCheckTime = this.$options.filters["time"](element.onDutyUserCheckTime, "HH:mm", true);
        element.onDutyTimeResult = this.$options.filters["ReportStatus"](element.onDutyTimeResult);
        element.offDutyClassTime = this.$options.filters["time"](element.offDutyClassTime, "HH:mm", true);
        element.offDutyUserCheckTime = this.$options.filters["time"](element.offDutyUserCheckTime, "HH:mm", true);
        element.offDutyTimeResult = this.$options.filters["ReportStatus"](element.offDutyTimeResult);

      });
      return data;
    }