exls导入数据库 php_PHP Excel文件导入数据到数据库

1.php部分(本例thinkphp5.1)：

namespace app\admin\controller;

// 引入phpExcel

use Env;

use vendor\PHPExcel\PHPExcel;

class Upload

{

//上传Excel 导入数据

public function import(){

include_once(Env::get('root_path').'vendor/PHPExcel/PHPExcel.php');

// $objPHPExcel = new \PHPExcel();

$file = request()->file('file');

$info = $file->validate(['size'=>52428800])->move( '../public/uploads');

if($info){

$ext = $info->getExtension(); //获取文件后缀名

if(!in_array($ext,['xlsx','xls'])){

$msg=['code'=>-1,'msg'=>"文件格式有误"];

}else{

$path = $_SERVER['DOCUMENT_ROOT'].'/uploads/'.str_replace('\\', '/', $info->getSaveName()); //上传文件路径

if (!file_exists($path)) {

$msg['res'] = -1;

$msg['msg'] = "上传文件丢失!" . $file->getError();

}

if ($ext == 'xlsx') {

$objReader = \PHPExcel_IOFactory::createReader('Excel2007');

$objPHPExcel = $objReader->load($path, 'utf-8');

} elseif ($ext == 'xls') {

$objReader = \PHPExcel_IOFactory::createReader('Excel5');

$objPHPExcel = $objReader->load($path, 'utf-8');

}

$sheet = $objPHPExcel->getSheet(0);

$highestRow = $sheet->getHighestRow(); // 取得总行数

// $highestColumn = $sheet->getHighestColumn(); // 取得总列数

$i = 0;

$importRows = 0;

$addData = [];

for ($j = 2; $j <= $highestRow; $j++) {

$importRows++;

$realName = (string)$objPHPExcel->getActiveSheet()->getCell("A$j")->getValue();//需要导入的realName

$phone = (string)$objPHPExcel->getActiveSheet()->getCell("B$j")->getValue(); //需要导入的phone

$company = (string)$objPHPExcel->getActiveSheet()->getCell("C$j")->getValue(); //需要导入的company

$job = (string)$objPHPExcel->getActiveSheet()->getCell("D$j")->getValue(); //需要导入的job

$email = (string)$objPHPExcel->getActiveSheet()->getCell("E$j")->getValue(); //需要导入的email

$addData[] = ['name'=>$realName,'tel'=>$phone,'company'=>$company,'job'=>$job,'email'=>$email];

$i++;

}

halt($addData); //打印数据

//数据写入数据库

$result = model('')->insertAll($addData);

if($result){

$msg = ['code'=>0,'msg'=>'导入数据成功！'];

}else{

$msg = ['code'=>-1,'msg'=>'导入失败，请重试！'];

}

}

}

return $msg;

}

}

配置路由：

'import'=>'admin/Upload/import',

2.前端部分：layui传文件

导入数据 //点击开始上传文件操作

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

var upload = layui.upload;

upload.render({

elem:'.import_btn',

url:"{:url('admin/import')}",

accept:'file', //普通文件

exts:'xls', //允许的文件后缀

done:function(res){

//执行导入接口后操作

}

});

})

参考链接：https://www.cnblogs.com/52lnamp/p/9237704.html，https://blog.csdn.net/xgs736214763/article/details/78904780(感谢分享)