dcat-admin导入20万行大型excel表格的方法

Excel用为最常用的数据存储载体，在web后台中也常常需要用到导入功能。dcat-admin使用自带的excel组件也能非常简单地实现导入，不过如果表格太大，导入容易超时或内存不够导致退出。

我们要实现这样的功能：选择excel表时，先读取表格并分页显示出来；用户可以显示的内容进行核对修改，无误后再点击导入，传递到后台数据库中储存。

这实现这样的功能，我们在dcat-admin中使用了vue组件。

首先在数据表格的grid中添加一个按钮：

$grid->tools('<a href="/admin/vue"><button class="btn btn-primary filter-btn btn-outline btn-outline"><i class="feather icon-upload"></i>&nbsp;新建项目</button></a>');

后台路由中添加：

    $router->post('/addbgsx', 'BgsxController@addbgsx');//创建项目
    $router->post('/tablepost', 'BgsxController@tablepost');//创建项目后导入数据
    $router->get('/vue', 'BgsxController@vue');//创建项目界面

在控制器中添加如下方法：

    //项目属性管理
    public function vue(Content $content){
        return $content->header("创建查询项目")->body(admin_view('admin.bgsx'));
    }
    
    //创建项目
    public function addbgsx(Request $request){
        $code = 1;
        if(!$xmmc = trim($request->xmmc)){
            return response()->json(['code'=>$code,'message'=>'项目名称为空']);
        }
        $zd = array_keys($request->zd);
        if(!$zd || count($zd) == 0){
            return response()->json(['code'=>$code,'message'=>'字段为空']);
        }
        $zds = [];
        foreach($zd as $i=>$v){
            if(!$v || str_replace(' ','',$v) == ''){
                return response()->json(['code'=>$code,'message'=>$v.'为空']);
            }
            $zds['k'.$i] = str_replace(' ','',$v);
        }
        if(BgsxModel::where("xmmc",$xmmc)->first()){
            return response()->json(['code'=>$code,'message'=>'项目已存在']);
        }
        $bm = 'bm_'.substr(md5($xmmc),16);//生成唯一正式表名
        if (Schema::hasTable($bm))Schema::drop($bm);//如果数据表已存在，则删除
        Schema::create($bm, function(Blueprint $table) use ($zds){
            $table->increments('id');//主键
            foreach($zds as $k=>$v){
                $table->text($k)->nullable();
            }
            $table->timestamps();
        });
        $fh = BgsxModel::create(['bm'=>$bm,'xmmc'=>$xmmc,'zd'=>$zds,'cxtj'=>[],'kfcx'=>array_keys($zds)]);
        return response()->json(['code'=>0,'message'=>"创建成功",'result'=>['bm'=>$bm, 'zd'=>$zds,'id'=>$fh->id]]);
    }
    
    //创建项目后批量导入数据
    public function tablepost(Request $request){
        $code = 1;
        if(!$bm = $request->bm){
            return response()->json(['code'=>$code,'message'=>'表名为空']);
        }
        $data = $request->data;
        if(!$data || count($data) == 0){
            return response()->json(['code'=>$code,'message'=>'数据为空']);
        }
        foreach($data as $k=>$v){
            $data[$k]['created_at'] = now();
            $data[$k]['updated_at'] = now();
        }
        $fh = DB::table($bm)->insert($data);
        return response()->json(['code'=>0,'message'=>"创建成功",'result'=>$fh]);
    }

然后，在resources/views/admin目录中添加一个bgsx.blade.php文件：

<div class="vue" id="vue" v-cloak>
    <el-card class="box-card" header="表格第一行为字段名，不可为空；输入项目名称，上传表格后并核对数据无误后，再点击确认提交">
        <el-form label-position="right" label-width="110px">
            <el-form-item label="项目名称" prop="xmmc">
                <el-input v-model="xmmc" placeholder="输入项目名称，不可重复" autocomplete="off"></el-input>
            </el-form-item>
            
            <el-form-item label="上传excel" prop="file" v-if="maxh == 0">
                <el-upload style="line-height:25px;" drag accept=".xlsx" :limit="1" :before-upload="readxlsx" action="">
                    <i class="el-icon-upload"></i>
                    <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
                    <div class="el-upload__tip" style="margin-top:0px;" slot="tip">只能上传xlsx后缀的表格文件，且不超过20万行</div>
                </el-upload>
            </el-form-item>
        </el-form>
        
        <el-progress v-if="maxh > 0 && qdloading" :text-inside="true" :stroke-width="20" :percentage="percent"></el-progress>
        
        <el-table ref="singleTable" v-if="maxh > 0" :data="tempjson.slice((currentPage-1)*pageSize,currentPage*pageSize)" highlight-current-row style="width: 100%;margin-top:19px;" border v-on:current-change="handlelieChange" :row-style="{height:'55px'}" :cell-style="{padding:'0px'}">
            <el-table-column type="index" width="50" label='序号'></el-table-column>
            <el-table-column v-for="(v,k) in tempjson[0]" :prop="k" :label="k" v-if="k != 'isEdit'">
                <template slot-scope="scope">
                    <div v-if="!scope.row.isEdit">${ scope.row[k] }</div>
                    <div v-else>
                        <el-input v-model="scope.row[k]"></el-input>
                    </div>
                </template>
            </el-table-column>
            
            <el-table-column label="操作">
                <template slot-scope="scope">
                    <el-button v-on:click="handleClick(scope.row)">${ scope.row.isEdit ? '完成' : '编辑' }</el-button>
                </template>
            </el-table-column>
        </el-table>
        <!-- 分页器 -->
        <div class="block" style="margin-top:15px;" v-if="maxh > 0">
            <el-pagination
                align='center' 
                v-on:current-change="handleCurrentChange"
                v-on:size-change="handleSizeChange"
                :current-page="currentPage" 
                :page-sizes="[1,5,10,20]" 
                :page-size="pageSize" 
                layout="total, sizes, prev, pager, next, jumper" 
                :total="tempjson.length">
            </el-pagination>
        </div>
        <div style="margin:2rem;text-align:center;" v-if="qdloading && maxh > 0">
            <el-button type="primary" v-on:click="submitTabel()">确认提交</el-button>
        </div>
    </el-card>
</div>
<style>
    /*加载完毕才显示vue*/
    [v-cloak]{
        display: none;
    }
    .tableAuto.el-table .cell {
      white-space: nowrap;
    }
</style>

{!! admin_css(['/static/css/element.css']) !!}
{!! admin_js(['/static/js/vue.min.js']) !!}
{!! admin_js(['/static/js/element.js']) !!}
{!! admin_js(['/static/js/vue-axios-plugin.js']) !!}
{!! admin_js(['/static/js/xlsx.full.min.js']) !!}
{!! admin_js(['/static/js/polyfill.js']) !!}

<script init=".vue">
    const speak = (msg) => {
        let ssu = new window.SpeechSynthesisUtterance();
        ssu.lang = 'zh-CN';
        ssu.text = msg;
        window.speechSynthesis.speak(ssu);
    };
    new Vue({
        el: '#vue',
        delimiters: ['${', '}'],
        data: function () {
            return {
                headers: {'X-CSRF-TOKEN': Dcat.token},
                flielist: [],
                maxh: 0, 
                percent: 0,
                tempjson: [],
                id:'',
                bm: '',
                xmmc: '',
                zd: {},
                qdloading: true,
                currentPage: 1,//当前是第几页
                pageSize: 10,//一页多少条
                currentRow: null,//选中行
            }
        },
        
        methods: {
            //选中指定行
            setCurrent(row) {
                this.$nextTick(()=>{
                    this.$refs.singleTable.setCurrentRow(row);
                    this.$set(row, 'isEdit', true);
                });
            },
            //编辑数据
            handleClick(row) {
                if (row.isEdit) {
                    this.$delete(row, 'isEdit')
                } else {
                    this.$set(row, 'isEdit', true);
                }
            },
            //每页条数改变时触发 选择一页显示多少行
            handleSizeChange(val) {
                this.currentPage = 1;
                this.pageSize = val;
            },
            //当前页改变时触发 跳转其他页
            handleCurrentChange(val) {
                this.currentPage = val;
            },
            handlelieChange(val){
                this.currentRow = val;//选中行
            },
            
            //读取表格
            readxlsx(file){
                if(!file)return;
                var name = file.name;
                this.$message.success('正在读取表格');
                var reader = new FileReader();
                reader.onload = (e)=> {
                    var data = e.target.result;
                    var workbook = XLSX.read(data, {type: 'binary', cellText:false, cellDates: true});
                    var sheetNames = workbook.SheetNames; 
                    var worksheet = workbook.Sheets[sheetNames[0]]; 
                    var json = XLSX.utils.sheet_to_json(worksheet,{raw:false,dateNF:'yyyy-mm-dd'});
                    this.tempjson = [];
                    console.log(json);
                    //过滤空行
                    for(let v of json){
                        if(Object.values(v).length > 0 && Object.values(v)[0] != '') {
                            this.tempjson.push(v);
                        }
                    }
                    this.maxh = this.tempjson.length;
                    speak('读取成功，表格共有' + json.length + '条数据');
                    this.$message.success('读取成功，表格共有' + json.length + '条数据');
                };
                reader.readAsBinaryString(file);
            },
            
            //提交
            async submitTabel(){
                this.percent = 0;
                this.maxh = this.tempjson.length;
                if(!this.xmmc){
                    speak('项目名称为空');
                    return this.$message.error('项目名称为空');
                }
                if(this.maxh < 1){
                    speak('表格内容为空');
                    return this.$message.error('表格最少要有两行数据');
                }
                if(Object.keys(this.tempjson[0]).length  == 0){
                    speak('表格第一行为空');
                    return this.$message.error('表格第一行为空');
                }
                //先创建项目，返回bm zd
                this.addbgsx({xmmc: this.xmmc, zd: this.tempjson[0]});
            },
            
            //创建项目
            async addbgsx(data){
                await this.$http.post('/admin/addbgsx', data, {
                    emulateJSON: true,
                    headers: this.headers
                }).then((response) => {
                    this.bm = response.data.result.bm;
                    this.zd = response.data.result.zd;
                    this.id = response.data.result.id;
                    this.postsj();
                });
            },
            
            //批量导入数据
            async postsj(){
                let zd = this.zd;//字段
                var p = 600;let temp = [];let i = 1;let ii = 1; let newvalue = new Object();
                this.qdloading = false; this.percent = 1;
                //导入数据
                for (const value of this.tempjson) {
                    newvalue = {}; 
                    for (let k in zd) {
                        var v = String(value[zd[k]]);
                        if (v == null || v == 'undefined') {
                            v = '';
                        }
                        newvalue[k] = v; 
                    }
                    temp.push(newvalue);
                    if (i > p) {
                        await this.$http.post('/admin/tablepost', {bm: this.bm, data: temp}, {
                            emulateJSON: true,headers: this.headers
                        }).then(response=>{
                            if (response.data.code != 0) {
                                speak(response.data.message);
                                return this.$message.error(response.data.message);
                            }
                        });
                        i = 1;
                        temp = [];
                    }
                    this.percent = parseInt((ii / this.maxh) * 100);
                    i++;
                    ii++;
                }
                if (temp.length > 0) {
                    await this.$http.post('/admin/tablepost', {bm: this.bm, data:temp }, {
                        emulateJSON: true,headers: this.headers
                    }).then(response=>{
                        if (response.data.code != 0) {
                            speak(response.data.message);
                            return this.$message.error(response.data.message);
                        }
                    });
                    this.$message.success('数据导入完成');
                    this.percent = 100;
                }
                speak('数据导入完成');
                this.qdloading = true;
                window.location.href = "{{url('/admin/cxbgsj?id=')}}" + this.id;
                return;
            },
            
        }
    });
</script>

记录完毕。