el-upload + XLSX 实现前端上传解析excel数据

需求

前端导入excel, 解析成数组渲染到表格

实现步骤

1. el-upload 上传excel, 获取文件内容
2. xlsx解析文件,获取到原始数组
3. 配置表头字段, 转换数据
4. 绑定表格数据, 渲染表格

实现代码

页面组件

    <!-- 上传excel -->
    <el-upload :show-file-list="false" class="box-upload" accept=".xlsx, .xls" drag
        :auto-upload="false" :on-change="uploadChange">
        <div class="el-upload__text">
            <em>点击上传</em> / 拖拽到此区域
        </div>
    </el-upload>


/**
 * 上传change
 * @param {type} 参数
 * @returns {type} 返回值
 */
const uploadChange = (file) => {
    analysisExcel(file.raw, (fileName, dataJson) => {
        if (dataJson.length) {
            tableData.value = dataJson.map(item => zh2en(item)).slice(1)
        }
    })
}


/**
 * 中午转英文
 * @param {type} 参数
 * @returns {type} 返回值
 */
const zh2en = (item) => {
    const dict = {
        '首端站点名称': 'startStationName',
        '首端机房编号': 'startRoomNo',
        '首端设备编号': 'startDeviceNo',
        '首端设备端口号': 'startDevicePortNo',
        '业务名称': 'businessName',
        '业务类型': 'businessType',
        '业务等级': 'businessLevel',
        '所属子网': 'subnet',
        '尾端站点名称': 'endStationName',
        '尾端机房编号': 'endRoomNo',
        '尾端设备编号': 'endDeviceNo',
        '尾端设备端口号': 'endDevicePortNo',
    }
    const newUser = {}
    for (const key in item) {
        const enKey = dict[key]
        newUser[enKey] = item[key]
    }
    return newUser
}

工具代码

import * as XLSX from 'xlsx/xlsx.mjs'
    /**
     * 解析excel
     */
    analysisExcel(f, callback) {
        const that = this
        const reader = new FileReader();
        reader.onload = function (e: any) {
            const data = e.target.result;
            const workbook = XLSX.read(data, { type: 'binary' });
            if (callback) {
                const worksheet = workbook.Sheets[workbook.SheetNames[0]]
                const header = that.getHeaderRow(worksheet)
                const opt = { header: header, type: "string" }
                if (workbook.SheetNames.length == 1) {
                    const xlsxJson = XLSX.utils.sheet_to_json(worksheet, opt);
                    callback(f.name, xlsxJson);
                } else {
                    const xlsxJson: any = []
                    workbook.SheetNames.map(item => {
                        xlsxJson.push(XLSX.utils.sheet_to_json(workbook.Sheets[item], opt))
                    })
                    callback(f.name, xlsxJson);
                }
            }
        };
        reader.readAsBinaryString(f);
    },
    
        /**
     * 获取excel行头部
     * @param {type} 参数
     * @returns {type} 返回值
     */
    getHeaderRow(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 */
            const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })]
            /* find the cell in the first row */
            let hdr = 'UNKNOWN ' + C // <-- replace with your desired default
            if (cell && cell.t) hdr = XLSX.utils.format_cell(cell)
            headers.push(hdr)
        }
        return headers
    },