使用composer安装phpexcel
composer config -g repo.packagist composer https://mirrors.aliyun.com/composer/
composer require phpoffice/phpexcel
引入类文件:phpthink5.1以后可以使用这种方式引入vendor目录类文件
use PHPExcel_IOFactory;
//导入
public function import(){
$param = get_params();
if (request()->isAjax()) {
$objPHPExcel = new \PHPExcel();
//通过ajax方式上传文件后获得文件信息
/*读取excel文件,并进行相应处理*/
$file = "./storage/" . $param['filename'];
if(empty($file)){
return json(['info'=>'请选择上传文件!','status'=>0]);
}
$info = explode('.', $file);
$file_extension = $info[2];//获取文件扩展名
//实例化PHPExcel类
if ($file_extension == 'xlsx'){
$objReader =\PHPExcel_IOFactory::createReader('Excel2007');
} else if ($file_extension == 'xls') {
$objReader =\PHPExcel_IOFactory::createReader('Excel5');
} else if($file_extension=='.csv'){
$objReader =\PHPExcel_IOFactory::createReader('csv');
}
// $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
$obj_PHPExcel =$objReader->load($file, $encode = 'utf-8'); // 加载文件内容,编码utf-8
$excel_array=$obj_PHPExcel->getsheet(0)->toArray(); // 转换为数组格式
$highestRow = $obj_PHPExcel->getSheet(0)->getHighestRow() - 1; //取得总行数
array_shift($excel_array); // 删除第一个数组(标题);
$data = []; // 数据库需要的二维数组
foreach ($excel_array as $key => $value) {
// 正则去除多余空白字符
$data[$key]['name'] = preg_replace('/\s+/', '', $value['0']);
$data[$key]['source_id'] = 1;
$data[$key]['grade_id'] = 1;
$data[$key]['industry_id'] = 1;
$data[$key]['belong_uid'] = 0;
$data[$key]['create_time'] = time();
}
// 启动事务
Db::startTrans();
try {
$result=Db::name('customer')->insertAll($data); //批量插入数据
if (!$result) {
throw new \Exception('插入数据失败!');
}
// 提交事务
Db::commit();
unlink($file);
return json(['msg'=>'文件上传成功,已经导入'.$result.'条数据','status'=>200]);
} catch (\Exception $e) {
// 回滚事务
Db::rollback();
unlink($file);
return json(['msg'=>'导入数据失败,'.$e->getMessage(),'status'=>400]);
}
}else{
return view();
}
}