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;
