vue 读取csv文件，处理数据，并导出excel

安装xlsx依赖，读取文件用

npm i xlsx

安装vue-json-excel依赖，下载excel时候用

npm i vue-json-excel

新建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);

    /* the second way */
    // const table = document.getElementById(id);
    // const wb = XLSX.utils.book_new();
    // const ws = XLSX.utils.table_to_sheet(table);
    // XLSX.utils.book_append_sheet(wb, ws, filename);
    // 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
}

整体代码如下

有两种读取文件的方法

<template>
    <div class="home" style="overflow-x: hidden;">
    	// 文件上传
        <van-uploader :after-read="afterRead" />
        // 导出excel
        <JsonExcel :fields="jsonFields" :fetch="getexcelData" type="xls" name="转换后数据.xls">
            <button type="primary" icon="icon-download">导出</button>
        </JsonExcel>
    </div>
</template>
  
<script>
import * as XLSX from 'xlsx';
import excel from './excel';

import JsonExcel from "vue-json-excel";
export default {
    name: 'home',
    data() {
        return {
            jsonFields: {
                ID_: 'ID',
                CUST_NAME: 'CUST_NAME',
                GRP_CUST_CODE: 'GRP_CUST_CODE',
                COUNTY_CODE: 'COUNTY_CODE',
                ADDRESS: 'ADDRESS',
                LONGITUDE: 'LONGITUDE',
                LATITUDE: 'LATITUDE',
                CUST_MANAGER_PHONE: 'CUST_MANAGER_PHONE',
                CUST_MANAGER_NAME: 'CUST_MANAGER_NAME',
                CREATE_TIME: 'CREATE_TIME',
                IS_VALID: 'IS_VALID',
            },
            tableData: []
        }
    },
    components: {
        JsonExcel
    },
    computed: {
    },
    mounted() {

    },
    methods: {
       
        getexcelData() {
            var arr = []
            for (let i = 0; i < this.tableData.length; i++) {
                const els = this.tableData[i];
                var ada = this.tobdMap(els.LONGITUDE, els.LATITUDE)
                arr.push(
                    {
                        "ID": els['???"ID_"'],
                        "CUST_NAME": els.CUST_NAME,
                        "GRP_CUST_CODE": els.GRP_CUST_CODE,
                        "CITY_CODE": els.CITY_CODE,
                        "COUNTY_CODE": els.COUNTY_CODE,
                        "ADDRESS": els.ADDRESS,
                        "LONGITUDE": ada[0],
                        "LATITUDE": ada[1],
                        "CUST_MANAGER_PHONE": els.CUST_MANAGER_PHONE,
                        "CUST_MANAGER_NAME": els.CUST_MANAGER_NAME,
                        "CREATE_TIME": this.formatDate(els.CREATE_TIME, '/'),
                        "IS_VALID": els.IS_VALID
                    },
                )
            }
            // 获取api接口返回的结果，这里需要换成你自己的api接口方法
            // 返回数据，执行下载
            return arr
        },
        afterRead(file) {
            this.readFile(file.file)
        },
        formatDate(numb, format) {
        // excel单元格，格式化时间
            const time = new Date((numb - 1) * 24 * 3600000 + 1);
            let h = time.getHours() + 16;
            let yeraData = new Date(1900, 0, numb - 1)
            let year = yeraData.getFullYear();
            let month = yeraData.getMonth() + 1
            month = month < 10 ? '0' + month : month;
            let day = yeraData.getDate()
            day = day < 10 ? '0' + day : day;
            if (h > 23) {
                h = h - 24;
            }
            let m = time.getMinutes() < 10 ? "0" + time.getMinutes() : time.getMinutes();
            let s = time.getSeconds() < 10 ? "0" + time.getSeconds() : time.getSeconds();
            return `${year}/${month}/${day} ${h}:${m}`;
        },
        // 读取文件
        readFile(file) {
            console.log(file);
            const reader = new FileReader()
            console.log(reader);
            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 => {
            }
            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
                    }
                })
                console.log(results);
                this.tableData = results
                // 逻辑处理
            }
        },
        readExcel(file) {
            // 表格导入
            const fileReader = new FileReader()
            fileReader.onload = ev => {
                try {
                    const data = ev.target.result
                    const workbook = XLSX.read(data, {
                        type: "binary",
                        cellDates: true
                    })
                    const wsname = workbook.SheetNames[0] // 取第一张表
                    const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]) // 生成json表格内容
                    // 逻辑处理
                    console.log(ws);
                } catch (e) {
                    return false
                }
            }
            fileReader.readAsBinaryString(file)
        },
    }
}
</script>
<style lang="less" scoped></style>

如果直接使用csv格式读取的话，会乱码，可以在wps中直接另存为excel，再读取文件