dcat-admin导入20万行大型excel表格的方法
作者:总管理员
时间:2023-03-15 21:01:03
阅读数:735人阅读
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> 新建项目</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>
记录完毕。
本站所有文章、数据、图片均来自互联网,一切版权均归源网站或源作者所有。
如果侵犯了你的权益请来信告知我们删除。邮箱: 2554509967@qq.com