zhxy-desk/pages/studentRanked/components/students-change-excel.vue

<template>
    <el-popover placement="top-start" title="表格批量替换" width="280" trigger="hover">
        <view>
            下载当前座位表格模板，更改后点击表格导入批量替换座位
            <view class="txt-red">请确保表格格式完整，切勿存在缺漏或重复</view>
            <view class="ui-flex-row ui-flex-center ui-mt20">
                <el-button type="" plain class="ui-mr20" @click="makeExcelFile">下载模板</el-button>

                <el-upload class="upload-demo" action="#" :before-upload="()=>{return false}" :auto-upload="false"
                    :on-change="onStuChangeByExcel" :file-list="fileList">
                    <!--  @click="onStuChangeByExcel" -->
                    <el-button type="primary" plain>上传导入</el-button>
                </el-upload>
            </view>
        </view>
        <text slot="reference" class="txt-link">表格导入</text>
    </el-popover>
</template>

<script>
    import xlsx from '@/common/xlsx.core.min.js'
    export default {
        data() {
            return {
                fileList: [] //excel上传的数据
            }
        },
        props: {
            stuList: Array
        },
        methods: {
            makeExcelFile() { //下载表格模板
                let title = ['1组', '2组', '3组', '4组', '5组', '6组', '7组', '8组'];
                let list = [];
                this.stuList.forEach((item, index) => {
                    item.nodes.forEach((el, ii) => {
                        if (el.id) {
                            if (!list[ii]) {
                                list[ii] = [];
                            }
                            list[ii][index] = el.title;
                        }
                    })
                })
                list = [title].concat(list);
                console.log(list);
                // return;
                var sheet = xlsx.utils.aoa_to_sheet(list);
                this.openDownloadDialog(this.sheet2blob(sheet), '导出座位表.xlsx');
            },
            // 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
            sheet2blob(sheet, sheetName) {
                sheetName = sheetName || 'sheet1';
                var workbook = {
                    SheetNames: [sheetName],
                    Sheets: {}
                };
                workbook.Sheets[sheetName] = sheet;
                // 生成excel的配置项
                var wopts = {
                    bookType: 'xlsx', // 要生成的文件类型
                    bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
                    type: 'binary'
                };
                var wbout = xlsx.write(workbook, wopts);
                var blob = new Blob([s2ab(wbout)], {
                    type: "application/octet-stream"
                });
                // 字符串转ArrayBuffer
                function s2ab(s) {
                    var buf = new ArrayBuffer(s.length);
                    var view = new Uint8Array(buf);
                    for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
                    return buf;
                }
                return blob;
            },
            openDownloadDialog(url, saveName) {
                if (typeof url == 'object' && url instanceof Blob) {
                    url = URL.createObjectURL(url); // 创建blob地址
                }
                var aLink = document.createElement('a');
                aLink.href = url;
                aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
                var event;
                if (window.MouseEvent) event = new MouseEvent('click');
                else {
                    event = document.createEvent('MouseEvents');
                    event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0,
                        null);
                }
                aLink.dispatchEvent(event);
            },
            stuLength(){
                let count = 0;
                this.stuList.forEach(item => {
                    item.nodes.forEach(el => {
                        if(el.id){
                            count ++;
                        }
                    })
                })
                return count;
            },
            onStuChangeByExcel(e) {
                let fileRaw = e.raw;
                this.fileList = [];
                var reader = new FileReader();
                reader.onload = (e) => {
                    var data = e.target.result;
                    var workbook = xlsx.read(data, {
                        type: 'binary'
                    });
                    console.log(workbook)

                    var sheetNames = workbook.SheetNames; // 工作表名称集合
                    var worksheet = workbook.Sheets[sheetNames[0]]; // 这里我们只读取第一张sheet
                    var csv = xlsx.utils.sheet_to_csv(worksheet);
                    console.log(csv);
                    let json = csv.split("\n").map(item => {
                        return item.split(',');
                    })
                    
                    let header = json[0];
                    json.splice(0,1);
                    console.log(json);
                    // console.log(json, xlsx.utils.sheet_to_json(worksheet));
                    // return;
                    let list = [];
                    let maxLen = json.length;
                    let insertList = []; //插入判断用于判断是否有重复的名字
                    let error = null;
                    json.forEach((item, index) => {
                        item = Object.values(item);
                        item.forEach((name, ii) => {
                            if (name) {
                                if(insertList.includes(name)){
                                    error = name + '学生异常';
                                }
                                insertList.push(name);
                                
                                if (!list[ii]) {
                                    list[ii] = {
                                        id: ii + 1,
                                        title: header[ii],
                                        nodes: []
                                    };
                                }
                                list[ii].nodes[index] = name;
                            }
                        })
                    });
                    if (error) {
                        this.$alert(error, '表格异常', {
                            confirmButtonText: '确定',
                        });
                        return;
                    }
                    if(insertList.length != this.stuLength()){
                        this.$alert('表格中人数异常，请检查', '表格异常', {
                            confirmButtonText: '确定',
                        });
                        return;
                    }
                    
                    list.forEach((item, index) => {
                        item.nodes.forEach((name, ii) => {
                            list[index].nodes[ii] = this.findStuItem(name);
                        })
                    });

                    list = Object.values(list);
                    console.log(list)
                    this.$emit('change', uni.$u.deepClone(list))
                };
                reader.readAsBinaryString(fileRaw);
            },
            findStuItem(name) {
                let findOne = null;
                this.stuList.some(item => {
                    return item.nodes.some(el => {
                        if (el.title == name) {
                            findOne = el;
                        }
                        return !!findOne;
                    })
                })
                return findOne;
            },
        }
    }
</script>

<style>
</style>