PHP Excel::selectSheets方法代码示例

 public function readExcel()
 {
     $excels = \Excel::selectSheets($this->class_name)->load(storage_path('excel/' . $this->class_name . '.xlsx'))->get();
     foreach ($excels as $excel) {
         $this->datas[] = $excel->toArray();
     }
     return $this->datas;
 }

 public function importExcel()
 {
     $fileInfo = $this->uploadFile(request()->file('file'), 'import');
     $pathToFile = $fileInfo['path'] . '/' . $fileInfo['name'];
     Excel::selectSheets('Sheet1')->load($pathToFile, function ($reader) {
         $rows = $reader->get();
         foreach ($rows as $key => $value) {
             // b1: insert Order
             $order = $this->excelForOrder($value, 'imei');
             $newOrder = new OrderRepository(new Order());
             $t = $newOrder->create($order);
             // b2: insert ship from order
             foreach ($t->phones as $index => $phone) {
                 $input = $this->excelForShip($value, $phone->id);
                 $newShip = new ShipRepository(new Ship());
                 $newShip->create($input);
             }
         }
     });
     return redirect()->back();
 }

 public function silentLoad()
 {
     //used in order to reload the spreadsheet and immediately go back to the save ( in sessions ) query.
     Excel::selectSheets('Sheet1')->load(storage_path() . '\\items.xlsx', function ($reader) {
         // Loop through all sheets
         $reader->each(function ($row) {
             // Loop through all rows
             //     $sheet->each(function ($row)  {
             if ($row->cmmf != null) {
                 //row in cmmf input is not blank so add/edit item
                 $item = Item::where('cmmf', '=', $row->cmmf)->first();
                 if ($item == null) {
                     //item doesn't exist yet
                     $item = new Item();
                     $item->cmmf = $row->cmmf;
                     $item->case = $row->case_pack;
                     $item->weight = $row->weight_lb;
                     $item->size = $row->size;
                     $item->cartonsperpallet = $row->ctnspallet;
                     $item->save();
                     unset($item);
                     //      $itemCount++;
                 } else {
                     //cmmf already exists..just update it
                     $item->cmmf = $row->cmmf;
                     $item->case = $row->case_pack;
                     $item->weight = $row->weight_lb;
                     $item->size = $row->size;
                     $item->cartonsperpallet = $row->ctnspallet;
                     $item->save();
                     unset($item);
                     //   $itemCount++;
                 }
             }
             //  });
         });
     });
     //    $data['itemCount']=$itemCount;
     //   return View::make('load-list-done', $data);
 }

 public function run()
 {
     $folderpath = 'database/seeds/seed_files';
     $folders = File::directories($folderpath);
     $latest = '11232015';
     foreach ($folders as $value) {
         $_dir = explode("/", $value);
         $cnt = count($_dir);
         $name = $_dir[$cnt - 1];
         $latest_date = DateTime::createFromFormat('mdY', $latest);
         $now = DateTime::createFromFormat('mdY', $name);
         if ($now > $latest_date) {
             $latest = $name;
         }
     }
     $file_path = $folderpath . "/" . $latest . "/Category Tagging.xlsx";
     echo (string) $file_path, "\n";
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     Excel::selectSheets('Sheet1')->load($file_path, function ($reader) {
         $records = $reader->get();
         $records->each(function ($row) {
             if (!is_null($row->category)) {
                 $form_category = FormCategory::where('category', $row->category)->first();
                 // dd($form_category);
                 if (!empty($form_category)) {
                     $form_category->sos_tagging = $row->sos;
                     $form_category->osa_tagging = $row->osa;
                     $form_category->secondary_display = $row->secondary_display;
                     $form_category->update();
                 }
             }
         });
     });
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     Model::reguard();
 }

        // $reader->createWriter($reader, 'Excel2007');
        // $reader->create('Nimit New.xlsx')->store('xlsx');
    })->setFileName('achmadi_BSSSSghfhfhfh')->save('xlsx');
    // ->export('xlsx');
    // ->get();//tidak bisa dipakai xxx
    // dd($dataexcel);
    // echo dd($dataexcel->take(10));//xx
    // echo $dataexcel->getTitle();
    // foreach($dataexcel as $sheet)
    // {
    //     // get sheet title
    //   echo $sheet->getTitle();
    //   // dd($sheet);
    // }
    // dd($dataexcel);
    $dataExcel = Excel::selectSheets('2008-2009')->load('storage\\app\\php6EA5.tmp.xlsx', function ($reader) {
        echo $reader->setActiveSheetIndex(0)->getCell('B7')->getValue();
    })->setFileName('achmadi_B')->save('xlsx');
    // })->store('xlsx','E:\xampp 1.8.3\htdocs\laravel5\storage/exports/phpD3.xlsx');
    // ->convert('csv');
    // dd($dataExcel->getFileName());
    // Storage::disk('local')->put('simpan.xlsx',  File::get($dataexcel));//xxx
    //
    // Excel::create('sasa')->store('xlsx');//xxx
});
Route::get('m', function () {
    $u = new Urusan();
    $u->tahun = 2018;
    $u->kd_urusan = 65;
    $u->nm_urusan = 'Nama Urusan 09999999';
    $u->save();

 public function run()
 {
     $start_date = '';
     $end_date = '';
     $folderpath = 'database/seeds/seed_files';
     $folders = File::directories($folderpath);
     $latest = '11232015';
     foreach ($folders as $value) {
         $_dir = explode("/", $value);
         $cnt = count($_dir);
         $name = $_dir[$cnt - 1];
         $latest_date = DateTime::createFromFormat('mdY', $latest);
         $now = DateTime::createFromFormat('mdY', $name);
         if ($now > $latest_date) {
             $latest = $name;
         }
     }
     $file_path = $folderpath . "/" . $latest . "/Store Mapping.xlsx";
     echo (string) $file_path, "\n";
     // dd($file_path);
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     DB::table('audit_templates')->truncate();
     Excel::selectSheets('Sheet1')->load($file_path, function ($reader) {
         $records = $reader->get();
         $records->each(function ($row) {
             if (!is_null($row->template)) {
                 $template = AuditTemplate::where('template', $row->template)->first();
                 if (count($template) == 0) {
                     $newtemplate = new AuditTemplate();
                     $newtemplate->template_code = $row->channel_code;
                     $newtemplate->template = $row->template;
                     $newtemplate->save();
                 }
             }
         });
     });
     DB::table('grade_matrixs')->truncate();
     Excel::selectSheets('Sheet1')->load($file_path, function ($reader) {
         $records = $reader->get();
         $records->each(function ($row) {
             if (!is_null($row->enrollment_type)) {
                 $matrix = GradeMatrix::where('desc', $row->enrollment_type)->first();
                 if (count($matrix) == 0) {
                     $newmatrix = new GradeMatrix();
                     $newmatrix->desc = $row->enrollment_type;
                     $newmatrix->save();
                 }
             }
         });
     });
     DB::table('users')->truncate();
     DB::table('role_user')->truncate();
     Excel::selectSheets('Sheet1')->load($file_path, function ($reader) {
         $records = $reader->get();
         $records->each(function ($row) {
             if (!is_null($row->fullname)) {
                 $userlist = explode("/", $row->fullname);
                 $emaillist = explode("/", $row->email);
                 // dd($row);
                 for ($i = 0; $i < count($userlist); $i++) {
                     $user = User::where('username', $row->username)->first();
                     if (count($user) == 0) {
                         if (empty($emaillist[$i])) {
                             $email = strtolower($row->username . "@unilever.com");
                         } else {
                             $email = strtolower($emaillist[$i]);
                         }
                         $newuser = User::create(array('name' => strtoupper($userlist[$i]), 'email' => $email, 'username' => $row->username, 'password' => Hash::make('password')));
                         $newuser->roles()->attach(3);
                     } else {
                         // $user->name = strtoupper($row->fullname);
                         // $user->username = $row->username;
                         // $user->email = strtolower($row->email);
                         // $user->update();
                         // if(!$user->hasRole('field')){
                         // 	$user->roles()->attach(3);
                         // }
                         // echo $user->hasRole('field');
                     }
                 }
             }
         });
     });
     DB::table('accounts')->truncate();
     Excel::selectSheets('Sheet1')->load($file_path, function ($reader) {
         $records = $reader->get();
         $records->each(function ($row) {
             if (!is_null($row->account)) {
                 $account = Account::where('account', $row->account)->first();
                 if (count($account) == 0) {
                     $newaccount = new Account();
                     $newaccount->account = $row->account;
                     $newaccount->save();
                 }
             }
         });
     });
     DB::table('customers')->truncate();
     Excel::selectSheets('Sheet1')->load($file_path, function ($reader) {
//.........这里部分代码省略.........

});
Route::get('/databases', function () {
    return View::make('layouts.databases');
});
Route::get('/datawarehouse', function () {
    return View::make('layouts.datawarehouse');
});
// MySQL
Route::resource('products', 'ProductsController');
Route::resource('product_brand_types', 'ProductBrandTypesController');
Route::resource('product_statuses', 'ProductStatusesController');
Route::resource('product_distributions', 'ProductDistributionsController');
Route::resource('product_orders', 'ProductOrdersController');
Route::resource('product_distribution_dates', 'ProductDistributionDatesController');
Route::resource('product_order_dates', 'ProductOrderDatesController');
// PostreSQL
Route::resource('sales', 'SalesController');
Route::resource('branches', 'BranchesController');
Route::resource('cashier_sales', 'CashierSalesController');
Route::resource('cashier_infos', 'CashierInfosController');
Route::get('/original', function () {
    return View::make('hello');
});
Route::get('test', function () {
    if (DB::connection('pgsql')->getDatabaseName()) {
        echo "conncted sucessfully to database " . DB::connection('pgsql')->getDatabaseName();
    }
});
Route::get('xlsx', function () {
    $reader = Excel::selectSheets('customer')->load('/Users/brynzky/Sites/Projects/datawarehouse_etl/public/excel/excel.xlsx')->get()->toArray();
});