vue解析Excel

1.安装xlsx依赖

npm install xlsx --save

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

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) => {
    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、页面代码

import excel from "../../utils/excel";

<div class="container">
        <input
                    type="file"
                    accept=".xls,.xlsx"
                    class="upload_file"
                    @change="handleUpload($event)"
            />
</div>

4、js方法

handleUpload(e){
                console.log(e)
                const input = e.target;
                console.log(input)
                const files = e.target.files;
                let file=files[0];
                console.log(file)
                const fileExt = file.name.split('.').pop().toLocaleLowerCase()
                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
},
            // 读取文件转换json
readFile(file) {
                const reader = new FileReader();
                reader.readAsArrayBuffer(file);
                reader.onloadstart = (e) => {
                    this.tableLoading = 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 };
                    });
                    //这里的tableData就是拿到的excel表格中的数据
                    console.log(results)
                    console.log(tableTitle)
                    this.tableData = results;
                    this.tableTitle = tableTitle;
                    const map = new Map();
                    header.map((key) => {
                        let arr = new Array()
                        results.map((v) => {
                            arr.push(v[key])
                        });
                        map.set(key,arr)
                    });
                    // results.forEach(title=>{
                    //
                    // })
                    console.log(map)
                    console.log(map.get("ID"));
                    this.tableLoading = false;
                };
 },