public function import() { $path = 'C:/Filename.xls'; Excel::selectSheetsByIndex(0)->load($path, function ($reader) { DB::table('members')->insert($reader->toArray()); }); }
public function proses_import(Request $request) { ini_set('max_execution_time', 300); $rules = ['excel' => 'required']; $pesan = ['excel.required' => 'File excel harus diisi']; $validasi = Validator::make($request->all(), $rules, $pesan); if ($validasi->fails()) { return Redirect::back()->withErrors($validasi)->withInput(); } $excel = $request->file('excel'); //ambil sheet pertama $excels = Excel::selectSheetsByIndex(0)->load($excel, function ($reader) { //options jika ada })->get(); //digunakan untuk menghitung total siswa yang masuk $counter = 0; $rowRules = ['no_peserta' => 'required', 'nisn' => 'required', 'nama' => 'required', 'jk' => 'required', 'rombel' => 'required']; foreach ($excels as $row) { //membuat validasi untuk row di excel //jangan lupa mengubah $row menjadi array $validasi = Validator::make($row->toArray(), $rowRules); //skip baris ini jika tidak valid, langsung ke baris berikutnya if ($validasi->fails()) { continue; } $cek = Siswa::where('nis', $row['nisn'])->count(); if ($cek > 0) { continue; } $pass = mt_rand() . '@'; $data = array('nis' => $row['nisn'], 'nama' => $row['nama'], 'kd_kelas' => $row['rombel'], 'jk' => $row['jk'], 'tmp_lahir' => $row['tempat'], 'tgl_lahir' => $row['tanggal'], 'nik' => $row['nik'], 'agama' => $row['agama'], 'alamat' => $row['alamat'], 'rt' => $row['rt'], 'rw' => $row['rw'], 'dusun' => $row['dusun'], 'kelurahan' => $row['kelurahan'], 'kecamatan' => $row['kecamatan'], 'kode_pos' => $row['kode_pos'], 'no_skhun' => $row['skhun'], 'nm_ayah' => $row['ayah'], 'nm_ibu' => $row['ibu'], 'no_peserta' => $row['no_peserta'], 'password' => Hash::make($pass), 'password_asli' => $pass, 'status' => 0); DB::table('siswa')->insert($data); $counter++; } Session::flash('pesan', "Berhasil mengimport " . $counter . " siswa"); return Redirect::to('admin/siswa'); }
public function import(Request $request) { exit; $path = $request->input('path'); set_time_limit(600); // Excel::filter('chunk')->selectSheetsByIndex(0)->load($path)->noHeading()->chunk(1000, function($result){ // $data = $result->toArray(); // unset($data[0]); // if ($data) { // foreach ($data as $k=>$v) { // $in[] = array( // 'user_name'=>$v[0], // 'sno'=>$v[1], // 'gender'=>$v[2], // 'id_card'=>$v[3], // 'birthday'=>$v[4], // 'nation'=>$v[5], // 'grade'=>$v[6], // 'degree'=>$v[7], // 'major'=>$v[8], // 'phone'=>$v[9], // 'learn_center'=>$v[10], // 'roll_status'=>$v[11], // 'account_status'=>$v[12], // 'roll_filled'=>$v[13], // 'graduation_filled'=>$v[14], // 'degree_filled'=>$v[15], // 'fee'=>$v[16], // 'fee_ed'=>$v[17], // 'fee_status'=>$v[18], // 'teaching_way'=>$v[19], // 'ordered'=>$v[20], // ); //// $in[] = array( //// 'user_name'=>$v[0], //// 'sno'=>$v[1], //// 'gender'=>$v[2], //// 'id_card'=>$v[3], //// 'class_name'=>$v[4], //// 'grade'=>$v[5], //// 'major'=>$v[6], //// 'school'=>$v[7], //// 'site'=>$v[8], //// 'degree'=>$v[9], //// 'course_name'=>$v[10], //// 'note'=>$v[11], //// 'ordered'=>$v[12], //// ); // } // DB::table('t1')->insert($in); // } // }); // exit; Excel::selectSheetsByIndex(0)->load($path, function ($reader) { $data = $reader->noHeading()->toArray(); unset($data[0]); if ($data) { foreach ($data as $k => $v) { $in[] = array('user_name' => $v[0], 'sno' => $v[1], 'gender' => $v[2], 'id_card' => $v[3], 'birthday' => $v[4], 'nation' => $v[5], 'grade' => $v[6], 'degree' => $v[7], 'major' => $v[8], 'phone' => $v[9], 'learn_center' => $v[10], 'roll_status' => $v[11], 'account_status' => $v[12], 'roll_filled' => $v[13], 'graduation_filled' => $v[14], 'degree_filled' => $v[15], 'fee' => $v[16], 'fee_ed' => $v[17], 'fee_status' => $v[18], 'teaching_way' => $v[19], 'ordered' => $v[20]); // $in[] = array( // 'user_name'=>$v[0], // 'sno'=>$v[1], // 'gender'=>$v[2], // 'id_card'=>$v[3], // 'class_name'=>$v[4], // 'grade'=>$v[5], // 'major'=>$v[6], // 'school'=>$v[7], // 'site'=>$v[8], // 'degree'=>$v[9], // 'course_name'=>$v[10], // 'note'=>$v[11], // 'ordered'=>$v[12], // ); } DB::table('t1')->insert($in); } // DB::table('t2')->insert($reader->toArray()); }); exit; Excel::filter('chunk')->selectSheetsByIndex(0)->load($path)->chunk(500, function ($results) { // DB::enableQueryLog(); // $results = $results->slice(1); foreach ($results->toArray() as $v) { print_r($v); } // DB::table('t2')->insert($results->toArray()); // exit; // foreach ($results as $k=>$v) { //// $data = $v->toArray();//->values() // // $in[] = array( // 'user_name'=>$v[0], // 'sno'=>$v[1], // 'gender'=>$v[2], // 'id_card'=>$v[3], // 'class_name'=>$v[4], // 'grade'=>$v[5], // 'major'=>$v[6], // 'school'=>$v[7], // 'site'=>$v[8], // 'degree'=>$v[9], // 'course_name'=>$v[10], // 'note'=>$v[11], // 'ordered'=>$v[12], // ); // //// //t3 //// $in[] = array( //// 'degree'=>$v[1], //// 'major'=>$v[2], //// 'admit'=>$v[3], //// 'user_name'=>$v[4], //// 'sno'=>$v[5], //// 'phone'=>$v[6], //// 'id_card'=>$v[8], //// 'gender'=>$v[9], //// 'grade'=>$v[10], //// 'ordered'=>$v[11], //// ); // // // // } // var_dump($in); // DB::table('t2')->insert($in); }); // var_dump(DB::getQueryLog()); }
protected function injectInsertAndUpdateData() { return Excel::selectSheetsByIndex(0)->filter('chunk')->load($this->callback->getData()['realpath'])->skip(HoneyBaby::SKIP_LARAVEL_EXCEL_CHUNK_BUG_INDEX)->chunk(HoneyBaby::CHUNK_SIZE, $this->getChunkProcess()); }
public function postUpload(Request $request) { //return dd($request->all()); if (!$request->hasFile('file') || !$request->file('file')->isValid()) { return redirect('/remittance/philhealth')->withErrors('File is corrupted! Try again.'); } if (strtolower($request->file->getClientOriginalExtension()) !== 'xls') { return redirect('/remittance/philhealth')->withErrors('Invalid file extension! Use .xls file extension.'); } $filename = $request->file->getClientOriginalName(); $destinationPath = public_path('uploads' . DS . 'remittance'); $request->file->move($destinationPath, $filename); $file = $destinationPath . DS . $filename; try { $e = Excel::selectSheetsByIndex(0)->load($file); } catch (\Exception $e) { return redirect('/remittance/philhealth')->withErrors('Something went wrong! ' . $e->getMessage()); } try { $company = $this->company->find($request->input('company_id')); } catch (\Exception $e) { return redirect('/remittance/philhealth')->withErrors('Invalid company!'); } if (is_null($company)) { return redirect('/remittance/philhealth')->withErrors('Company not found!'); } $fields = ['phealth_no', 'bracket', 'e_status', 'date_hired', 'birthday']; $rs = $e->select($fields)->get(); $data = []; $data[0] = ['MEM_PHIC_NO', 'MO_SAL', 'EE_STAT', 'EFF_DATE', 'DATE_OF_BIRTH']; foreach ($rs as $key => $row) { $idx = $key + 1; foreach ($fields as $key => $value) { switch ($value) { case 'e_status': if ($row->{$value} == 'NH') { $data[$idx][$key] = 'NH'; $data[$idx][$key + 1] = $row->date_hired->format('Y-m-d'); } else { $data[$idx][$key] = 'A'; $data[$idx][$key + 1] = null; } break; case 'date_hired': break; default: $data[$idx][$key] = $row->{$value}; break; } } } $month = Carbon::parse($request->input('date'))->endOfMonth(); $fname = 'PLH-' . strtoupper($company->code) . '-' . $month->format('Ym'); try { Excel::create($fname, function ($excel) use($fname, $data) { $excel->sheet($fname, function ($sheet) use($data) { $sheet->fromArray($data); }); })->store('csv', public_path('downloads' . DS . 'remittance')); } catch (\Exception $e) { return redirect('/remittance/philhealth')->withErrors('Something went wrong! ' . $e->getMessage()); } $companies = $this->company->all(['code', 'descriptor', 'id']); $dl = public_path('downloads' . DS . 'remittance') . DS . $fname . '.csv'; if (File::exists($dl)) { return redirect('remittance/philhealth')->with('companies', $companies)->with('dl', $fname . '.csv'); } else { return redirect('remittance/philhealth')->with('companies', $companies)->withErrors('Something went wrong!'); } }