vue解析excel文件(使用xlsx)

╰半夏微凉° 2021-08-28 11:16 777阅读 0赞

1.安装xlsx依赖

npm install xlsx --save

2.创建一个js作为外部引入,命名为excel.js

/* eslint-disable */
    import XLSX from 'xlsx';
    
    function auto_width(ws, data){
        /*set worksheet max width per col*/
        const colWidth = data.map(row => row.map(val => {
            /*if null/undefined*/
            if (val == null) {
                return {'wch': 10};
            }
            /*if chinese*/
            else if (val.toString().charCodeAt(0) > 255) {
                return {'wch': val.toString().length * 2};
            } else {
                return {'wch': val.toString().length};
            }
        }))
        /*start in the first row*/
        let result = colWidth[0];
        for (let i = 1; i < colWidth.length; i++) {
            for (let j = 0; j < colWidth[i].length; j++) {
                if (result[j]['wch'] < colWidth[i][j]['wch']) {
                    result[j]['wch'] = colWidth[i][j]['wch'];
                }
            }
        }
        ws['!cols'] = result;
    }
    
    function json_to_array(key, jsonData){
        return jsonData.map(v => key.map(j => { return v[j] }));
    }
    
    // fix data,return string
    function fixdata(data) {
        let o = ''
        let l = 0
        const w = 10240
        for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)))
        o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)))
        return o
    }
    
    // get head from excel file,return array
    function get_header_row(sheet) {
        const headers = []
        const range = XLSX.utils.decode_range(sheet['!ref'])
        let C
        const R = range.s.r /* start in the first row */
        for (C = range.s.c; C <= range.e.c; ++C) { /* walk every column in the range */
            var cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })] /* find the cell in the first row */
            var hdr = 'UNKNOWN ' + C // <-- replace with your desired default
            if (cell && cell.t) hdr = XLSX.utils.format_cell(cell)
            headers.push(hdr)
        }
        return headers
    }
    
    export const export_table_to_excel= (id, filename) => {
        const table = document.getElementById(id);
        const wb = XLSX.utils.table_to_book(table);
        XLSX.writeFile(wb, filename);
    }
    
    export const export_json_to_excel = ({data, key, title, filename, autoWidth}) => {
        const wb = XLSX.utils.book_new();
        data.unshift(title);
        const ws = XLSX.utils.json_to_sheet(data, {header: key, skipHeader: true});
        if(autoWidth){
            const arr = json_to_array(key, data);
            auto_width(ws, arr);
        }
        XLSX.utils.book_append_sheet(wb, ws, filename);
        XLSX.writeFile(wb, filename + '.xlsx');
    }
    
    export const export_array_to_excel = ({key, data, title, filename, autoWidth}) => {
        const wb = XLSX.utils.book_new();
        const arr = json_to_array(key, data);
        arr.unshift(title);
        const ws = XLSX.utils.aoa_to_sheet(arr);
        if(autoWidth){
            auto_width(ws, arr);
        }
        XLSX.utils.book_append_sheet(wb, ws, filename);
        XLSX.writeFile(wb, filename + '.xlsx');
    }
    
    export const read = (data, type) => {
        /* if type == 'base64' must fix data first */
        // const fixedData = fixdata(data)
        // const workbook = XLSX.read(btoa(fixedData), { type: 'base64' })
        const workbook = XLSX.read(data, { type: type });
        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];
        const header = get_header_row(worksheet);
        const results = XLSX.utils.sheet_to_json(worksheet);
        return {header, results};
    }
    
    export default {
      export_table_to_excel,
      export_array_to_excel,
      export_json_to_excel,
      read
    }

3.在需要的组件中添加方法，以iview为例（主要是拿到上传的file文件),这是action地址不需要后台提供，这里使用的是iview中的默认上传地址，在这个需要使用的组件中用import 引入上面新建的excel.js

4.beforeFile中可以拿到我们想要的file

beforeFile(file){
            const fileExt = file.name.split('.').pop().toLocaleLowerCase()
            this.uploadFile = file;
            if (fileExt === 'xlsx' || fileExt === 'xls') {
              this.readFile(file)
              this.file = file
            } else {
              this.$Notice.warning({
                title: '文件类型错误',
                desc: '文件：' + file.name + '不是EXCEL文件，请选择后缀为.xlsx或者.xls的EXCEL文件。'
              })
            }
            return false
          },

5.这是读取文件

// 读取文件
        readFile(file) {
          const reader = new FileReader()
          reader.readAsArrayBuffer(file)
          reader.onloadstart = e => {
            this.uploadLoading = true
            this.tableLoading = true
            this.showProgress = true
          }
          reader.onprogress = e => {
            this.progressPercent = Math.round(e.loaded / e.total * 100)
          }
          reader.onerror = e => {
            this.$Message.error('文件读取出错')
          }
          reader.onload = e => {
            const data = e.target.result
            const { header, results } = excel.read(data, 'array')
            const tableTitle = header.map(item => { return { title: item, key: item } })
            this.tableData = results  //这里的tableData就是拿到的excel表格中的数据
            this.tableTitle = tableTitle
            this.uploadLoading = false
            this.tableLoading = false
            this.showRemoveFile = true
          }
        },