PHP Excel::load方法代码示例

 public function import($entity)
 {
     $appHelper = new libs\AppHelper();
     $className = $appHelper->getNameSpace() . $entity;
     $model = new $className();
     $table = $model->getTable();
     $columns = \Schema::getColumnListing($table);
     $key = $model->getKeyName();
     $notNullColumnNames = array();
     $notNullColumnsList = \DB::select(\DB::raw("SHOW COLUMNS FROM `" . $table . "` where `Null` = 'no'"));
     if (!empty($notNullColumnsList)) {
         foreach ($notNullColumnsList as $notNullColumn) {
             $notNullColumnNames[] = $notNullColumn->Field;
         }
     }
     $status = \Input::get('status');
     $filePath = null;
     if (\Input::hasFile('import_file') && \Input::file('import_file')->isValid()) {
         $filePath = \Input::file('import_file')->getRealPath();
     }
     if ($filePath) {
         \Excel::load($filePath, function ($reader) use($model, $columns, $key, $status, $notNullColumnNames) {
             $this->importDataToDB($reader, $model, $columns, $key, $status, $notNullColumnNames);
         });
     }
     $importMessage = $this->failed == true ? \Lang::get('panel::fields.importDataFailure') : \Lang::get('panel::fields.importDataSuccess');
     return \Redirect::to('panel/' . $entity . '/all')->with('import_message', $importMessage);
 }

 public function import(Request $request, UserRepository $userRepository, Bus $bus)
 {
     $filename = $request->file;
     $programId = $request->program_id;
     \Excel::load('storage/app/' . $filename, function ($reader) use($programId, $userRepository, $bus) {
         $sheets = $reader->all();
         $sheets->each(function ($sheet) use($programId, $userRepository, $bus) {
             $sheet->each(function ($row) use($programId, $userRepository, $bus) {
                 $student = Student::firstOrNew(['reg_no' => $row->nim]);
                 $student->name = $row->nama;
                 $student->entry_year = $row->tahun_masuk;
                 $student->program_id = $programId;
                 $user = $userRepository->findByUsername($student->reg_no);
                 if (!$user) {
                     $role = Role::where('name', 'Student')->first();
                     $roles = $role ? [$role->id] : [];
                     $desc = ['password' => bcrypt($student->reg_no), 'name' => $student->name, 'active' => true];
                     $bus->dispatch(new RegisterNewUser($student->reg_no, $student->reg_no . '@email.com', $roles, $desc));
                     $user = $userRepository->findByUsername($student->reg_no);
                 }
                 $student->user_id = $user->id()->value();
                 $student->save();
             });
         });
     });
     return $this->formSuccess(route('admin.student.student.index'), ['message' => 'Import mahasiswa berhasil']);
 }

 public function post()
 {
     $file = Input::file('grade');
     \Excel::load($file->getRealPath(), function ($reader) {
     })->get();
     return redirect()->back();
 }

 public function store(Request $request)
 {
     //dd('jajaja');
     $file = $request->file('file');
     //obtenemos el campo file obtenido por el formulario
     $nombre = $file->getClientOriginalName();
     //indicamos que queremos guardar un nuevo archivo en el disco local
     \Storage::disk('local')->put($nombre, \File::get($file));
     \Excel::load('/storage/public/files/' . $nombre, function ($archivo) use(&$falla) {
         $result = $archivo->get();
         //leer todas las filas del archivo
         foreach ($result as $key => $value) {
             $var = new Periodo();
             $datos = ['bloque' => $value->bloque, 'inicio' => $value->inicio, 'fin' => $value->fin];
             $validator = Validator::make($datos, Periodo::storeRules());
             if ($validator->fails()) {
                 Session::flash('message', 'Los Periodos ya existen o el archivo ingresado no es valido');
                 $falla = true;
             } else {
                 $var->fill($datos);
                 $var->save();
             }
         }
     })->get();
     if ($falla) {
         // Fallo la validacion de algun campus, retornar al index con mensaje
         return redirect()->route('Administrador.periodos.index');
     }
     \Storage::delete($nombre);
     Session::flash('message', 'Los Periodos fueron agregados exitosamente!');
     return redirect()->route('Administrador.periodos.index');
 }

 public function import(Request $request, UserRepository $userRepository, Bus $bus)
 {
     $filename = $request->file;
     $programId = $request->program_id;
     \Excel::load('storage/app/' . $filename, function ($reader) use($programId, $userRepository, $bus) {
         $sheets = $reader->all();
         $sheets->each(function ($sheet) use($programId, $userRepository, $bus) {
             $sheet->each(function ($row) use($programId, $userRepository, $bus) {
                 $lecturer = Lecturer::firstOrNew(['reg_no' => $row->nidn_nup_nidk]);
                 $lecturer->local_reg_no = $row->nip;
                 $lecturer->name = $row->nama;
                 $user = $userRepository->findByUsername($lecturer->reg_no);
                 if (!$user) {
                     $role = Role::where('name', 'Lecturer')->first();
                     $roles = $role ? [$role->id] : [];
                     $desc = ['password' => bcrypt($lecturer->reg_no), 'name' => $lecturer->name, 'active' => true];
                     $bus->dispatch(new RegisterNewUser($lecturer->reg_no, $lecturer->reg_no . '@email.com', $roles, $desc));
                     $user = $userRepository->findByUsername($lecturer->reg_no);
                 }
                 $lecturer->user_id = $user->id()->value();
                 $lecturer->save();
             });
         });
     });
     return $this->formSuccess(route('admin.employee.lecturer.index'), ['message' => 'Import dosen berhasil']);
 }

 public function Importprocess()
 {
     $uploaddata = array();
     $StudentAdmissionData = Input::all();
     $validation = Validator::make($StudentAdmissionData, ClassModel::$importrules);
     if ($validation->passes()) {
         if (!empty($StudentAdmissionData['importfile'])) {
             Input::file('importfile')->move('assets/uploads/grade/', 'grade' . Input::file('importfile')->getClientOriginalName());
             $importfile = 'grade' . Input::file('importfile')->getClientOriginalName();
         }
         $results = Excel::load('assets/uploads/grade/' . $importfile, function ($reader) {
         })->get()->toArray();
         if (count(array_filter($results)) == 1) {
             $finaldata = $results[0];
         } else {
             $finaldata = $results;
         }
         foreach ($finaldata as $final) {
             $GradeName = $final['grade'];
             if (!empty($GradeName)) {
                 $count = ClassModel::where('GradeName', '=', $GradeName)->count();
                 if ($count == 0) {
                     $ClassData['GradeName'] = $GradeName;
                     ClassModel::create($ClassData);
                 }
             }
         }
         return Redirect::to('class')->with('Message', 'Class Details Saved Succesfully');
     } else {
         return Redirect::to('class')->withInput()->withErrors($validation->messages());
     }
 }

 public function postshopdata()
 {
     $file = Input::file('shopdata');
     $destinationPath = 'importdata';
     // If the uploads fail due to file system, you can try doing public_path().'/uploads'
     $filename = 'imported-shoppinglistdata';
     //$filename = $file->getClientOriginalName();
     //$extension =$file->getClientOriginalExtension();
     $upload_success = Input::file('shopdata')->move($destinationPath, $filename . '.' . $file->getClientOriginalExtension());
     if ($upload_success) {
         $importedFile = public_path() . '/importdata/' . $filename . '.' . $file->getClientOriginalExtension();
         Excel::load($importedFile, function ($r) {
             $res = $r->all()->toArray();
             foreach ($res as $data) {
                 if ($data['kode'] != '' && $data['deskripsi'] != '' && $data['satuan'] != '' && $data['harga'] != '') {
                     $d = new ShoppingList();
                     $d->kode_sl = $data['kode'];
                     $d->deskripsi_pekerjaan = $data['deskripsi'];
                     $d->satuan = $data['satuan'];
                     $d->harga = $data['harga'];
                     $d->save();
                 }
             }
         });
         Session::flash('success', 'Shoppinglist Data imported to database');
         return Redirect::to('/import');
     } else {
         Session::flash('error', 'Error uploading files');
         return Redirect::to('/import');
     }
 }

 public function postUpload()
 {
     $file = Input::file($this->input_name);
     $rstring = str_random(15);
     $destinationPath = realpath($this->upload_dir) . '/' . $rstring;
     $filename = $file->getClientOriginalName();
     $filemime = $file->getMimeType();
     $filesize = $file->getSize();
     $extension = $file->getClientOriginalExtension();
     //if you need extension of the file
     $filename = str_replace(Config::get('kickstart.invalidchars'), '-', $filename);
     $uploadSuccess = $file->move($destinationPath, $filename);
     $sheets = Excel::load($destinationPath . '/' . $filename)->calculate()->toArray();
     $newsheets = array();
     foreach ($sheets as $name => $sheet) {
         $newrows = array();
         foreach ($sheet as $row) {
             if (implode('', $row) != '') {
                 $rstr = str_random(5);
                 $newrows[$rstr] = $row;
             }
         }
         $newsheets[$name] = $newrows;
     }
     file_put_contents(realpath($this->upload_dir) . '/' . $rstring . '.json', json_encode($newsheets));
     return Redirect::to(strtolower($this->controller_name) . '/preview/' . $rstring);
 }

 public function postBatch()
 {
     $file = Input::file('excel_file');
     $result = Excel::load($file, function ($reader) {
         $reader->take(500);
     })->toArray();
     $data = array_filter($result);
     if (isset($data[0][0]['vendor_id'])) {
         $data = $data[0];
     }
     $i = 0;
     foreach ($data as $key => $value) {
         $cakes = new Cakes();
         $cakes->vendor_id = $value['vendor_id'];
         $cakes->product_code = $value['product_code'];
         $cakes->title = $value['title'];
         $cakes->description = $value['description'];
         $cakes->type = $value['type'];
         $cakes->price = $value['price'];
         $cakes->availability = $value['availability'];
         $cakes->image = $value['image'];
         $cakes->city = $value['city'];
         if ($cakes->save()) {
         } else {
             $i++;
         }
     }
     if ($i) {
         return Redirect::to('/cake/index')->with('message', 'Error in cakes upload.');
     } else {
         return Redirect::to('/cake/index')->with('message', 'Cakes upload succesfull.');
     }
 }

 public function uploadFile()
 {
     $importfile = Input::file('upload_ex_cik');
     //$cik_no_change = array();
     Excel::load($importfile, function ($reader) {
         $result = $reader->all();
         $cik_no_change = array();
         //$i=0;
         foreach ($result as $in) {
             if ($in['cik'] != "") {
                 $ckj = $in['cik'];
                 $ckj_details = Cik::where('name', $ckj)->first();
                 if ($ckj_details) {
                     array_push($cik_no_change, $ckj);
                     //$cik_no_change[$i]=$ckj;
                     //$i=$i+1;
                 } else {
                     $ckj_details = new Cik();
                     $ckj_details->name = $ckj;
                     $ckj_details->status = 0;
                     $ckj_details->save();
                 }
             }
         }
         //print_r($cik_no_change);
     });
     //$response = Response::json($cik_no_change);
     //  return $response;
     $allUser = User::where('role_id', 1)->get();
     return View::make('admin.adminDashboard')->withUser($allUser);
 }

 public function store(Request $request)
 {
     //dd('jajaja');
     $file = $request->file('file');
     //obtenemos el campo file obtenido por el formulario
     $nombre = $file->getClientOriginalName();
     //indicamos que queremos guardar un nuevo archivo en el disco local
     \Storage::disk('local')->put($nombre, \File::get($file));
     $campus = $request->get('campus');
     $tipos = $request->get('tipos');
     \Excel::load('/storage/public/files/' . $nombre, function ($archivo) use($campus, $tipos) {
         $result = $archivo->get();
         //leer todas las filas del archivo
         foreach ($result as $key => $value) {
             $campus = Campus::whereNombre($value->campus_id)->pluck('id');
             $tipos = TipoSala::whereNombre($value->tipo_sala_id)->pluck('id');
             //echo $facultades."<br>";
             if (is_null($campus)) {
                 // El campus no existe, deberia hacer algo para mitigar esto, o retornarlo al usuario ...
             }
             if (is_null($tipos)) {
                 // El campus no existe, deberia hacer algo para mitigar esto, o retornarlo al usuario ...
             }
             //if(!Sala::whereNombre('campus_id',$campus)->whereNombre('tipo_sala_id',$tipos)->first()){
             if (!Sala::where('nombre', $value->nombre)->first()) {
                 $var = new Sala();
                 $var->fill(['nombre' => $value->nombre, 'descripcion' => $value->descripcion, 'capacidad' => $value->capacidad, 'campus_id' => $campus, 'tipo_sala_id' => $tipos]);
                 $var->save();
             }
         }
     })->get();
     \Storage::delete($nombre);
     Session::flash('message', 'Las Salas fueron agregadas exitosamente!');
     return redirect()->route('Encargado.salas.index');
 }

 public function index()
 {
     Excel::load('./excel/users.csv', function ($reader) {
         $results = $reader->get();
         $results = $reader->all();
         dd($results);
     });
 }

 /**
  * Show the form for creating a new resource.
  *
  * @return Response
  */
 public function create()
 {
     //
     $value = null;
     Excel::load(storage_path() . '/001.xls', function ($reader) use(&$value) {
         $value = $reader->get()->toArray();
         //object -> array
     });
     return Redirect::action('CandidateController@store_a', ['data' => $value]);
 }

 /**
  * @param FunctionalTester $I
  * @param Project          $project
  *
  * @return void
  */
 protected function _downloadExport(FunctionalTester $I, Project $project)
 {
     $response = $I->getJsonResponseContent();
     $file = $response->file;
     if ($response->ext !== 'csv') {
         \Excel::load(storage_path('exports/' . $response->file))->store('csv');
         unlink(storage_path('exports/' . $response->file));
         $file = str_replace('.' . $response->ext, '.csv', $response->file);
     }
     $I->amOnAction('ProjectController@getDownloadExport', ['project' => $project, 'file' => $file]);
 }

 public function LuuImportExcel(Request $req)
 {
     $v = \Validator::make($req->all(), ['fDiemExcel' => 'required|mimes:xls,xlsx']);
     if ($v->fails()) {
         return redirect()->back()->withErrors($v->errors());
     } else {
         $luuden = storage_path() . '/ImportExcel/';
         $taptin = Input::file('fDiemExcel');
         //$extension = Input::file('fDiemExcel')->getClientOriginalExtension();
         //Lấy tên và cả đuôi của tập tin
         $tenbandau = Input::file('fDiemExcel')->getClientOriginalName();
         $upload_success = $taptin->move($luuden, $tenbandau);
         // Khi upload thành công thì thông báo
         if ($upload_success) {
             //Import vào CSDL
             //$sheet = Excel::load($luuden.'/'.$tenbandau, function($reader){})->get();
             \Excel::load($luuden . '/' . $tenbandau, function ($reader) {
                 //return $reader->dump(); trả về một đối tượng (Object)
                 $results = $reader->toArray();
                 //Trả về mảng
                 //return var_dump($results);
                 $row = count($results);
                 $n = count($results, 1);
                 // Đếm từng phần tử trong mảng đa chiều
                 $col = ($n - $row) / $row;
                 //                   echo $col." :Số cột<br>".$row.": Số dòng<br>";
                 //                   echo $n." Số Phần tử của mảng 2 chiều (Gồm số phần tử của hàng và số phần tử của cột)";
                 for ($i = 1; $i < $row; $i++) {
                     for ($j = 2; $j < $col - 2; $j++) {
                         //echo $j.">";
                         //echo $results[$i][$j] . "  ";
                         if ($j >= 4 && $j < $col - 2) {
                             //echo $results[$i][2]."-".$results[$i][$j]."<br>";
                             //Lưu điểm
                             DB::table('chitiet_diem')->where('mssv', $results[$i][2])->update(['diem' => $results[$i][$j]]);
                         }
                     }
                     echo "<br>";
                 }
                 //Lưu nhận xét
                 for ($i = 1; $i < $row; $i++) {
                     DB::table('chia_nhom')->where('mssv', $results[$i][2])->update(['nhanxet' => $results[$i][$col - 2]]);
                     echo $results[$i][2] . " => " . $results[$i][$col - 2] . "<br>";
                 }
             });
             return Redirect::to('giangvien/nhapdiem')->with('BaoUpload', 'Import điểm từ tập tin Excel thành công!');
         } else {
             return $upload_success;
         }
     }
 }