Laravel 上传excel，读取并写入数据库 （实现自动建表、存记录值

    

     

      

       

      
      

       

        <?php
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       

        namespace App\Http\Controllers;
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       

        use Illuminate\Foundation\Bus\DispatchesJobs;
       
      
     

      

       

      
      

       

        use Illuminate\Routing\Controller as BaseController;
       
      
     

      

       

      
      

       

        use Illuminate\Foundation\Validation\ValidatesRequests;
       
      
     

      

       

      
      

       

        use Illuminate\Foundation\Auth\Access\AuthorizesRequests;
       
      
     

      

       

      
      

       

        use Illuminate\Support\Facades\Input;
       
      
     

      

       

      
      

       

        use Maatwebsite\Excel\Facades\Excel;
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       

        use Illuminate\Database\Schema\Blueprint;
       
      
     

      

       

      
      

       

        use Illuminate\Support\Facades\Schema;
       
      
     

      

       

      
      

       

        use Illuminate\Support\Facades\DB;
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       

        class QueryController extends BaseController
       
      
     

      

       

      
      

       

        {
       
      
     

      

       

      
      

       
    use AuthorizesRequests, DispatchesJobs, ValidatesRequests;
       
      
     

      

       

      
      

       
	public function index(){
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       
		$file = Input::file('myfile');
       
      
     

      

       

      
      

       
		if($file){
       
      
     

      

       

      
      

       

        // $realPath = $file
       
      
     

      

       

      
      

       

        // $path = $file -> move(app_path().'/storage/uploads');
       
      
     

      

       

      
      

       
			$realPath = $file->getRealPath();
       
      
     

      

       

      
      

       
			$entension =  $file -> getClientOriginalExtension(); //上传文件的后缀.
       
      
     

      

       

      
      

       
			$tabl_name = date('YmdHis').mt_rand(100,999);
       
      
     

      

       

      
      

       
			$newName = $tabl_name.'.'.'xls';//$entension;
       
      
     

      

       

      
      

       
			$path = $file->move(base_path().'/uploads',$newName);
       
      
     

      

       

      
      

       
			$cretae_path = base_path().'/uploads/'.$newName;
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       
			//dd($cretae_path);
       
      
     

      

       

      
      

       
			//dd($file);
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       

        			Excel::load($cretae_path, function($reader) use($tabl_name){
       
      
     

      

       

      
      

       
				//$data = $reader->all();
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       
				//获取excel的第几张表
       
      
     

      

       

      
      

       
				$reader = $reader->getSheet(0);
       
      
     

      

       

      
      

       
				//获取表中的数据
       
      
     

      

       

      
      

       
				$data = $reader->toArray();
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       
				$result = $this->create_table($tabl_name,$data);
       
      
     

      

       

      
      

       

        				dd($result);
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       
				//dd($data);
       
      
     

      

       

      
      

       

        			});
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       

        		}
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       
		return view('query.index');
       
      
     

      

       

      
      

       

        	}
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       
	public function create_table($table_name,$arr_field)
       
      
     

      

       

      
      

       

        	{
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       
		$tmp = $table_name;
       
      
     

      

       

      
      

       
		$va = $arr_field;
       
      
     

      

       

      
      

       

        		Schema::create("$tmp", function(Blueprint $table) use ($tmp,$va)
       
      
     

      

       

      
      

       

        		{
       
      
     

      

       

      
      

       
			$fields = $va[0];  //列字段
       
      
     

      

       

      
      

       
			//$fileds_count = 0; //列数
       
      
     

      

       

      
      

       
			$table->increments('id');//主键
       
      
     

      

       

      
      

       
			foreach($fields as $key => $value){
       
      
     

      

       

      
      

       
				if($key == 0){
       
      
     

      

       

      
      

       
					$table->string($fields[$key]);//->unique(); 唯一
       
      
     

      

       

      
      

       

        				}else{
       
      
     

      

       

      
      

       
					$table->string($fields[$key]);
       
      
     

      

       

      
      

       

        				}
       
      
     

      

       

      
      

       
				//$fileds_count = $fileds_count + 1;
       
      
     

      

       

      
      

       

        			}
       
      
     

      

       

      
      

       

        		});
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       
		$value_str= array();
       
      
     

      

       

      
      

       
		$id = 1;
       
      
     

      

       

      
      

       
		foreach($va as $key => $value){
       
      
     

      

       

      
      

       
			if($key != 0){
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       
				$content = implode(",",$value);
       
      
     

      

       

      
      

       
				$content2 = explode(",",$content);
       
      
     

      

       

      
      

       
				foreach ( $content2 as $key => $val ) {
       
      
     

      

       

      
      

       
					$value_str[] = "'$val'";
       
      
     

      

       

      
      

       

        				}
       
      
     

      

       

      
      

       
				$news = implode(",",$value_str);
       
      
     

      

       

      
      

       
				$news = "$id,".$news;
       
      
     

      

       

      
      

       

        				DB::insert("insert into db_$tmp values ($news)");
       
      
     

      

       

      
      

       
				//$value_str = '';
       
      
     

      

       

      
      

       
				$value_str= array();
       
      
     

      

       

      
      

       
				$id = $id + 1;
       
      
     

      

       

      
      

       

        			}
       
      
     

      

       

      
      

       

        		}
       
      
     

      

       

      
      

       
		return 1;
       
      
     

      

       

      
      

       

        	}
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       
 
       
      
     

      

       

      
      

       

        }
       
      
     

      

       

      
      

       
 
       
      
    
   

 https://blog.csdn.net/a9925/article/details/51201405