安装命令: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);
}