纯前端实现excel中的信息批量导入和批量导出

1、引入工具

SheetJS官网

<script lang="javascript" src="https://cdn.sheetjs.com/xlsx-0.19.2/package/dist/xlsx.full.min.js"></script>

2、按钮及表格

<el-upload class="upload" action="" :multiple="false" :show-file-list="false" accept=".xlsx"
            :http-request="importData">
            <el-button type="primary">批量导入</el-button>
        </el-upload>
        <el-button @click="exportData">批量导出</el-button>
        <el-table ref="multipleTable" :data="tableData" tooltip-effect="dark" style="width: 100%" height="860"
            @selection-change="handleSelectionChange">
            <el-table-column type="selection" width="55">
            </el-table-column>
            <el-table-column prop="imgUrl" label="图片地址">
            </el-table-column>
            <el-table-column prop="imgUrl" label="图片展示">
                <template slot-scope="scope">
                    <img :src="scope.row.imgUrl" style="width: 200px;height:200px;" />
                </template>
            </el-table-column>
            <el-table-column prop="remark" label="备注">
            </el-table-column>
        </el-table>

3、批量导入

importData(e) {
                let file = e.file // 文件信息
                if (!file) {   // 没有文件
                    return false
                } else if (!/\.(xls|xlsx)$/.test(file.name.toLowerCase())) {
                    // 格式根据自己需求定义
                    this.$message.error('上传格式不正确，请上传xls或者xlsx格式')
                    return false
                }

                const fileReader = new FileReader()
                fileReader.onload = (ev) => {
                    try {
                        const data = ev.target.result
                        const workbook = XLSX.read(data, {
                            type: 'binary'  // 以字符编码的方式解析
                        })
                        const exlname = workbook.SheetNames[0]   // 取第一张表
                        const exl = XLSX.utils.sheet_to_json(workbook.Sheets[exlname]) //生成json表格内容
                        // 将 JSON 数据挂到 data 里
                        this.tableData = exl
                        // document.getElementsByName('file')[0].value = '' 
                        // 根据自己需求，可重置上传value为空，允许重复上传同一文件
                    } catch (e) {
                        console.log('出错了：：')
                        return false
                    }
                }
                fileReader.readAsBinaryString(file)
            },

4、批量导出

 handleSelectionChange(val) {
                this.selectedData = val;
            },
async exportData() {
                const  rows = this.selectedData;
                /* generate worksheet and workbook */
                const worksheet = XLSX.utils.json_to_sheet(rows);
                const workbook = XLSX.utils.book_new();
                XLSX.utils.book_append_sheet(workbook, worksheet, "Dates");

                /* fix headers */
                XLSX.utils.sheet_add_aoa(worksheet, [["imgUrl", "remark"]], { origin: "A1" });

                /* calculate column width */
                const max_width = rows.reduce((w, r) => Math.max(w, r.remark.length), 10);
                worksheet["!cols"] = [{ wch: max_width }];

                /* create an XLSX file and try to save to Presidents.xlsx */
                XLSX.writeFile(workbook, "imgUrlExport.xlsx", { compression: true });
            }