layui php phpexcel导出,Yii2 基于 layui 的 Excel 上传并导入数据（含分页）

安装命令：composer require phpoffice/phpexcel

引入layui包，我这里用的是2.4.5的版本，请自行下载对应版本

layui前台页面

导入文件

layui.use(['form','element','upload','table'], function(){

var element = layui.element;

var upload = layui.upload;

var table = layui.table;

upload.render({

elem:'#file',

url:'=Url::to(["sales/re-charge-upload"])?>',

accept: 'file',

size: 5120,

exts:'xlsx',

//上传前的回调

before: function(obj){

layer.load(2);

},

//上传成功的回调

done: function(res, index, upload){

layer.closeAll('loading');

//执行渲染

$('#ImportList').show();

table.render({

elem: '#ImportExcel' //指定原始表格元素选择器(推荐id选择器)

,cols: [[

{field: 'agentId', title: '分销商编号',width:100,unresize:true},

{field: 'vcOrderId', title: '销售单号',width:120,unresize:true},

{field: 'productName', title: '产品名称',width:100,unresize:true},

{field: 'vcUnitName', title: '产品单位',width:100,unresize:true},

{field: 'nFXRate', title: '产品汇率',width:100,unresize:true},

{field: 'cardId', title: '卡号',width:'',unresize:true},

{field: 'initBalance', title: '初始值',width:100,unresize:true},

{field: 'cardBalance', title: '当前余额',width:100,unresize:true},

{field: 'cardStatus', title: '卡状态',width:80,unresize:true}

]] //设置表头

,data:res.data

,page:true

,limit:10

,skin:'line'

,text:'导入数据异常，请重新导入'

});

table.render();

},

//上传错误的回调

error: function(res,index, upload){

layer.closeAll('loading');

}

});

});

Yii2 后台代码

//导入excel文件并解析数据

public function actionReChargeUpload(){

$request = Yii::$app->request;

if ($request->isAjax){

$params = $_FILES['file'];

if (!empty($params)) {

$file_name = $params['name'];

$temp_name = $params['tmp_name'];

$error = $params['error'];

$arr = pathinfo($file_name);

$ext_suffix = $arr['extension'];

$allow_suffix = ['xlsx'];

if (!in_array($ext_suffix, $allow_suffix)) {

return $this->asJson(['msg' => '上传的文件类型只能是xlsx', 'code' => 400]);

}

if (!file_exists('uploads')) {

mkdir('uploads');

}

$new_filename = date('YmdHis', time()) . rand(100, 1000) . '.' . $ext_suffix;

if (move_uploaded_file($temp_name, 'uploads/' . $new_filename)) {

$excelData = json_decode($this->actionImportExcel('uploads/' . $new_filename),true);

if (!empty($excelData)) {

return $this->asJson(['msg' => '导入成功', 'data' => $excelData, 'code' => 200]);

}else{

return $this->asJson(['msg' => '导入失败', 'data' => [], 'code' => 200]);

}

} else {

return $this->asJson(['msg' => '导入失败,错误码：' . $error, 'code' => 400]);

}

}else{

return $this->asJson(['msg' => '文件导入失败,没有找到数据流', 'code' => 400]);

}

}else{

return $this->asJson(['请求错误','code'=>400]);

}

}

//解析excel文件数据

public function actionImportExcel($filename='uploads/20190920105001627.xlsx'){

$dataArray = [];

$Sheets = \PHPExcel_IOFactory::load($filename);

$dataArray = $Sheets->getSheet(0)->toArray();

array_shift($dataArray);

$excel_array = [];

foreach($dataArray as $k=>$v) {

$excel_array[$k]['cardId'] = $v[0];

$excel_array[$k]['cardStatus'] = $v[1];

$excel_array[$k]['initBalance'] = $v[2];

$excel_array[$k]['cardBalance'] = $v[3];

$excel_array[$k]['agentId'] = $v[4];

$excel_array[$k]['cardNo'] = $v[5];

$excel_array[$k]['vcOrderId'] = '-';

$excel_array[$k]['nFXRate'] = '-';

$excel_array[$k]['vcUnitName'] = '-';

$excel_array[$k]['productName'] = '-';

}

return json_encode($excel_array);

}