vue技巧:导入导出excel

作者: 党云龙 | 来源:发表于2020-11-28 10:24 被阅读0次

    安装


    导入导出excel这是前端做管理系统最常用的功能了,
    这里使用到了两个组件: file-saver (用于保存文件) xlsx (用于导入导出excel)

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

    2.建立文件


    打开你的vue项目,在src/js文件夹下新建 json2excel.js。

    import { saveAs } from 'file-saver'
    import XLSX from 'xlsx/dist/xlsx.full.min'
    
    
    // 将json数据处理为xlsx需要的格式
    function datenum(v, date1904) {
        if (date1904) v += 1462
        let epoch = Date.parse(v)
        return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000)
    }
    
    
    function data2ws(data) {
        const ws = {}
        const range = {s: {c: 10000000, r: 10000000}, e: {c: 0, r: 0}}
        for (let R = 0; R != data.length; ++R) {
            for (let 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
                const cell = { v: data[R][C] }
                if (cell.v == null) continue
                const 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
    }
    // 导出Excel
    function Workbook() {
        if (!(this instanceof Workbook)) return new Workbook()
        this.SheetNames = []
        this.Sheets = {}
    }
    
    
    function s2ab(s) {
        const buf = new ArrayBuffer(s.length)
        const view = new Uint8Array(buf)
        for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF
        return buf
    }
    
    
    /*
    * th => 表头
    * data => 数据
    * fileName => 文件名
    * fileType => 文件类型
    * sheetName => sheet页名
    */
    export default function toExcel ({th, data, fileName, fileType, sheetName}) {
        data.unshift(th)
        const wb = new Workbook(), ws = data2ws(data)
        sheetName = sheetName || 'sheet1'
        wb.SheetNames.push(sheetName)
        wb.Sheets[sheetName] = ws
        fileType = fileType || 'xlsx'
        var wbout = XLSX.write(wb, {bookType: fileType, bookSST: false, type: 'binary'})
        fileName = fileName || '列表'
        saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), `${fileName}.${fileType}`)
    }
    

    3.使用


    1.导出excel

    这里有两种使用方法,一种是加载为组件,一种是全局加载,我这里因为大部分页面都需要这个导出功能,所以我就直接在全局加载了。

    第一步:在main.js中全局挂载toExcel方法

    import toExcel from '../src/js/json2excel'
    Vue.prototype.$toExcel = toExcel
    

    第二步:创建下载表格的方法即可

    //下载表格
    downExcel() {
            const th = ['文件路径', '检测结果', '违规原因', '关键词'] //创建表头
            const filterVal = ['path', 'conclusion', 'msg', 'words'] //输出的内容
            const data = this.dialogData.error.map(v => filterVal.map(k => v[k]))//循环添加条数
            const [fileName, fileType, sheetName] = ['违规文件检测结果', 'xlsx', '违规文件目录']//文件名称,后缀,文件内的页名
            this.$toExcel({th, data, fileName, fileType, sheetName})
     },
    

    2.读取excel
    此方法应该配合一个file框使用

    importfxx(obj) {
            //读取excel
            let _this = this;
            let inputDOM = this.$refs.upload;
            // 通过DOM取文件数据
            this.file = event.currentTarget.files[0];
    
            var rABS = false; //是否将文件读取为二进制字符串
            var f = this.file;
    
            var reader = new FileReader();
            //if (!FileReader.prototype.readAsBinaryString) {
            FileReader.prototype.readAsBinaryString = function(f) {
                var binary = "";
                var rABS = false; //是否将文件读取为二进制字符串
                var pt = this;
                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]);
                    }
                    //此处引入,用于解析excel
                    if (rABS) {
                        wb = XLSX.read(btoa(fixdata(binary)), {
                        //手动转化
                        type: "base64"
                        });
                    } else {
                        wb = XLSX.read(binary, {
                        type: "binary"
                        });
                    }
                    //outdata = XLSX.utils.sheet_to_txt(wb.Sheets[wb.SheetNames[1]]);
                    //console.log("解析的文件内容为:" + outdata)
                    outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[1]]);
                    //outdata就是读取的数据(不包含标题行即表头,表头会作为对象的下标)
                    //此处可对数据进行处理
                    // let arr = [];
                    // outdata.map(v => {
                    //     let obj = {}
                    //     obj.code = v['Code']
                    //     obj.name = v['Name']
                    //     obj.pro = v['province']
                    //     obj.cit = v['city']
                    //     obj.dis = v['district']
                    //     arr.push(obj)
                    // });
                    // _this.da=arr;
                    // _this.dalen=arr.length;
                    //把结果转为json字符串
                    var jsonstr = JSON.stringify(outdata);
                    console.log("解析的文件内容为:" + jsonstr)
                    //console.log(jsonstr)
                    _this.text = jsonstr;
                    //return arr;
                };
                reader.readAsArrayBuffer(f);
            };
            if (rABS) {
                reader.readAsArrayBuffer(f);
            } else {
                reader.readAsBinaryString(f);
            }
          }
    

    相关文章

      网友评论

        本文标题:vue技巧:导入导出excel

        本文链接:https://www.haomeiwen.com/subject/gnsqwktx.html