Thinkphp6实现Excel导入功能
tp6实现Excel导入很简单，实现步骤如下：1、使用composer安装PHPExcel
composer require phpoffice/phpexcel
2、执行composer更新
composer update
3、代码实现
<?php
declare (strict_types = 1);
namespace app\union\controller;
use app\Request;
use app\union\model\Groups;
use think\facade\Cookie;
use think\facade\Db;
use think\facade\View;
use PHPExcel_IOFactory; //通过composer加载的第三方类，直接在头部引入一下就可以
class Group extends Base
{
public function import_save(Request $request){
if(!$request->param('excel')){
return returnJson('500','请上传excel文件');
}
$path = public_path().$request->param('excel');
//实例化PHPExcel类
$PHPExcel = new \PHPExcel();
//默认用excel2007读取excel，若格式不对，则用之前的版本进行读取
$PHPReader = new \PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($path)) {
$PHPReader = new \PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($path)) {
return returnJson('500','请上传excel文件');
}
}
//读取Excel文件
$PHPExcel = $PHPReader->load($path);
//读取excel文件中的第一个工作表
$sheet = $PHPExcel->getSheet(0);
//取得最大的列号
$allColumn = $sheet->getHighestColumn();
//取得最大的行号
$allRow = $sheet->getHighestRow();
//从第二行开始插入,第一行是列名
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
//获取B列的值
$data = [
'number'=>$PHPExcel->getActiveSheet()->getCell("A" . $currentRow)->getValue(),
'nickName'=>$PHPExcel->getActiveSheet()->getCell("B" . $currentRow)->getValue(),
'name'=>$PHPExcel->getActiveSheet()->getCell("C" . $currentRow)->getValue(),
'tel'=>$PHPExcel->getActiveSheet()->getCell("D" . $currentRow)->getValue(),
'money'=>$PHPExcel->getActiveSheet()->getCell("E" . $currentRow)->getValue(),
'time'=>self::get_date_by_excel($PHPExcel->getActiveSheet()->getCell("F" . $currentRow)->getValue()),
'is_pay'=>$PHPExcel->getActiveSheet()->getCell("G" . $currentRow)->getValue(),
'shop_name'=>$PHPExcel->getActiveSheet()->getCell("H" . $currentRow)->getValue(),
'remarks'=>$PHPExcel->getActiveSheet()->getCell("I" . $currentRow)->getValue(),
'status'=>0,
'created_at'=>date('Y-m-d')
];
if($data['number'] == ''){
$result = 1;
continue;
}else{
$find = Groups::where(array('number'=>$data['number']))->find();
if($find){
$result = 1;
continue;
}else{
$result=Db::name('group')->insert($data);
}
}
}
if($result){
return returnJson('200','导入成功');
}else{
return returnJson('500','导入失败');
}
}
public static function get_date_by_excel($date){
if (!$date || $date == '0000-00-00') return null;
$unix_time = \PHPExcel_Shared_Date::ExcelToPHP($date);
return gmdate('Y-m-d H:i',$unix_time);
}
}
?>
页:
[1]