vue+element UI Excel导入导出

1.终端执行命令

npm i xlsx -s 

2.script中导入

 import FileSaver from "file-saver";
 import * as XLSX from "xlsx";  

3.单表头效果图

4.html代码：

   <div>
        <el-upload 
            action="/上传文件的接口" 
            :on-change="onChange" 
            :auto-upload="false" 
            :show-file-list="false"
             accept=".xls, .xlsx" 
             ref="upload" 
            :multiple="true">
           <el-button 
                type="warning" 
                icon="el-icon-folder-add">
            导入
            </el-button>
         </el-upload>
         <el-button 
            @click="exportClick" 
            type="primary" 
            icon="el-icon-folder-opened" 
            class="export">
            导出
         </el-button>
    </div>
    <div class="table">
         <el-table 
            :data="tableData" 
            border 
            style="width: 100%" 
            id="mainTable">
              <el-table-column 
                    v-for="item in tableHeader" 
                    :key="item.id" 
                    :prop="item.prop" 
                    :label="item.label"
                    align="center" 
                    width="180">
               </el-table-column>
               <el-table-column 
                    label="操作" 
                    align="center" 
                    min-width="200px" 
                    fixed="right">
                        <template slot-scope="scope">
                            <span 
                                @click="edit(scope.row.id)" 
                                class="inspector-operate"> 
                            修改
                            </span>
                            <span 
                                @click="dele(scope.row.id)" 
                                class="inspector-operate"> 
                            删除
                            </span>
                            <span 
                                @click="see(scope.row.id)" 
                                class="inspector-operate"> 
                            查看
                            </span>
                        </template>
                    </el-table-column>
                </el-table>
            </div>

5.js代码

  import FileSaver from "file-saver";
  import * as XLSX from "xlsx";    
  export default {
    data() {
        return {
            // 表格数据
            tableData: [],
            // 表头
            tableHeader: [
                {
                    id: 1,
                    // 中文名
                    label: "公司",
                    // 对应数据属性
                    prop: "company",
                },
                {
                    id: 2,
                    label: "姓名",
                    prop: "name",
                },
                {
                    id: 3,
                    label: "所在部门",
                    prop: "department",
                },
                //....
            ],
        }
    },
    methods: {
        //导入
        onChange(file, fileList) {
            this.readExcel(file); // 调用读取数据的方法
        },
        // 读取数据
        readExcel(file) {
            let that = this;
            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 = async (ev) => {
                try {
                    const data = ev.target.result;
                    const workbook = XLSX.read(data, {
                        type: "binary",
                    });
                    if (workbook.SheetNames.length >= 1) {
                        this.$message({
                            message: "导入数据表格成功",
                            showClose: true,
                            type: "success",
                        });
                    }
                    const wsname = workbook.SheetNames[0]; //导入excel的第一张表
                    // 导入的信息
                    const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]); //生成json表格内容
                    let params = [];
                    //如果一行表头，则i从0开始，如果是两行则从1开始以此类推
                    for (var i = 0; i < ws.length; i++) {
                        let sheetData = {
                            // 键名为绑定 el 表格的关键字，值则是 ws[i][对应表头名]
                            company: ws[i]["公司"],
                            name: ws[i]["姓名"],
                            department: ws[i]["所在部门"],
                            nowInspection: ws[i]["现任纪检职务"],
                            departmentPost: ws[i]["所在部门岗位"],
                            postGradeSystem: ws[i]["岗位等级体系"],
                            //....
                        };
                        params.push(sheetData)
                    }
                    //数组对象传值 一次添加多条数据的新增接口  请求新增接口
                    let res = await InspectorSaveAll(params)
                    if (res.status == 200) {
                        //请求分页接口刷新数据
                    }
                    this.$refs.upload.value = "";
                } catch (e) {
                    return false;
                }
            };
            // 如果为原生 input 则应是 files[0]
            fileReader.readAsBinaryString(file.raw);
        },

        //导出
        exportClick(test) {
            //第一个参数是导后文件名，第二个table元素的id
            this.exportExcel(test, "mainTable");
        },
        //转换数据
        exportExcel(filename, tableId) {
            var xlsxParam = { raw: true }; // 导出的内容只做解析，不进行格式转换
            var table = document.querySelector("#" + tableId).cloneNode(true);
            table.removeChild(table.querySelector(".el-table__fixed-right"));
            var wb = XLSX.utils.table_to_book(table, xlsxParam);
            /* 获取二进制字符进行输出 */
            var wbout = XLSX.write(wb, {
                bookType: "xlsx",
                bookSST: true,
                type: "array",
            });
            try {
                FileSaver.saveAs(
                    new Blob([wbout], { type: "application/octet-stream" }),
                    filename + ".xlsx"
                );
            } catch (e) {
                if (typeof console !== "undefined") {
                    console.log(e, wbout);
                }
            }
            return wbout;
        },
}

6.多级表头效果图

7.多级表头的html代码

<div class="btn">
    <el-button 
        @click="add" 
        icon="el-icon-plus" 
        class="color add">
    新增
    </el-button>
    <el-upload 
        action="/上传文件的接口" 
        :on-change="onChange" 
        :auto-upload="false" 
        :show-file-list="false"
        accept=".xls, .xlsx" 
        ref="upload" 
        :multiple="true">
        <el-button 
            type="warning" 
            icon="el-icon-folder-add">
        导入
        </el-button>
    </el-upload>
    <el-button 
        @click="exportClick" 
        type="primary" 
        icon="el-icon-folder-opened" 
        class="export">
    导出
    </el-button>
</div>
<!-- 表格 -->
<div class="table">
    <el-table 
        :data="tableData" 
        border style="width: 100%" 
        id="mainTable">
    <template v-for="item in tableHeader">
        <el-table-column 
            v-if="item.label !== '在处理处分影响期内'" 
            :prop="item.prop" 
            :label="item.label"
            align="center" 
            width="180">
        </el-table-column>
        <template v-else>
             <el-table-column 
                :prop="item.prop" 
                :label="item.label" 
                align="center" 
                width="180">
                    <el-table-column 
                        v-for="item1 in item.item" 
                        :key="item1.id" 
                        :prop="item1.prop"
                        :label="item1.label" 
                        align="center" 
                        width="180">
                     </el-table-column>
              </el-table-column>
         </template>
    </template>
    <el-table-column 
        label="操作" 
        align="center" 
        min-width="200px" 
        fixed="right">
        <template slot-scope="scope">
            <span 
                @click="edit(scope.row.id)" 
                class="inspector-operate"> 
            修改
            </span>
            <span 
                @click="dele(scope.row.id)" 
                class="inspector-operate"> 
             删除
            </span>
            <span 
                @click="see(scope.row.id)" 
                class="inspector-operate"> 
             查看
            </span>
         </template>
    </el-table-column>
     </el-table>
 </div>

8.多级表头导入数据格式配置与单级表头不同，其他js部分相同

ifMyCriticize: ws[i]["是否在民主生活会或组织生活会进行自我批评"],
ifReport: ws[i]["是否按规定向上级部门报告处分决定执行情况"],
ifAppraising: ws[i]["在处理处分影响期内"],//第一项对应Excel表头的总表头
ifPromotion: ws[i]["__EMPTY"],//__EMPTY为分内容的第二个，__EMPTY_1为分内容的第3个，__2为第4个以此类推，第一个不用写
ifLengthenFormal: ws[i]["__EMPTY_1"],
otherHandleTest: ws[i]["其他对处理处分执行不到位的情况"],
reformMeasureTest: ws[i]["对存在处理、处分执行不到位情况的整改措施"],
reformTime: ws[i]["对存在处理、处分执行不到位情况的整改时限"],