Rajin Ngoding

<div dir="ltr" style="text-align: left;" trbidi="on"><div dir="ltr" style="text-align: left;" trbidi="on"><div dir="ltr" style="text-align: left;" trbidi="on"><div dir="ltr" style="text-align: left;" trbidi="on"><style type="text/css">
  @page { margin: 0.79in }
  p { margin-bottom: 0.1in; line-height: 115% }
  a:link { so-language: zxx }
 </style>   <br />
<div style="line-height: 100%; margin-bottom: 0in;">Assalamwalaikum Warohmatullahi Wabarokatuh.<br />
Hi.. sahabat rajin, kali ini kita akan membahas bagaimana cara Upload ke banyak table berelasi dengan 1 file excel pada package laravel excel.</div><div style="line-height: 100%; margin-bottom: 0in;">Pastikan mesin kamu sudah terinstall extentions php berikut :</div><div style="line-height: 100%; margin-bottom: 0in;"><br />
</div><div style="line-height: 100%; margin-bottom: 0in;">PHP extension php_zip enabled</div><div style="line-height: 100%; margin-bottom: 0in;">PHP extension php_xml enabled</div><div style="line-height: 100%; margin-bottom: 0in;">PHP extension php_gd2 enabled</div><div style="line-height: 100%; margin-bottom: 0in;"><br />
</div><style type="text/css">
  @page { margin: 0.79in }
  p { margin-bottom: 0.1in; line-height: 115% }
  a:link { so-language: zxx }
 </style>   <br />
<div style="line-height: 100%; margin-bottom: 0in;">disini saya menggunakan laravel versi 6 dan mysql sebagai databasenya.</div><div style="line-height: 100%; margin-bottom: 0in;">Oya tujuan tulisan ini bukan untuk manggurui teman-teman sekalian, tetapi untuk catatan saya ketika saya lupa / mentok ketika ada masalah. Rasulullah shallallaahu ‘alaihi wa sallam bersabda:</div><div style="line-height: 100%; margin-bottom: 0in;"><span style="font-variant: normal;"><span style="color: black;"><span style="letter-spacing: normal;">“</span></span></span><em><span style="color: black;"><span style="font-family: "lato" , sans-serif;"><span style="font-size: small;"><span style="letter-spacing: normal;"><span style="font-weight: normal;">Ikatlah ilmu dengan dengan menulisnya</span></span></span></span></span></em><span style="font-variant: normal;"><span style="color: black;"><span style="letter-spacing: normal;">”</span></span></span></div><div style="line-height: 100%; margin-bottom: 0in;"><br />
</div><div style="line-height: 100%; margin-bottom: 0in;"><span style="font-variant: normal;"><span style="color: black;"><span style="font-family: "lato" , sans-serif;"><span style="font-size: small;"><span style="letter-spacing: normal;"><span style="font-style: normal;"><span style="font-weight: normal;">Kasus :</span></span></span></span></span></span></span></div><div style="line-height: 100%; margin-bottom: 0in;"><br />
<ul style="text-align: left;"><li><span style="font-variant: normal;"><span style="color: black;"><span style="font-family: "lato" , sans-serif;"><span style="font-size: small;"><span style="letter-spacing: normal;"><span style="font-style: normal;"><b>Table Users</b></span></span></span></span></span></span></li>
<li><span style="font-variant: normal;"><span style="color: black;"><span style="font-family: "lato" , sans-serif;"><span style="font-size: small;"><span style="letter-spacing: normal;"><span style="font-style: normal;"><b>Table Mahasiswa</b></span></span></span></span></span></span></li>
<li><span style="font-variant: normal;"><span style="color: black;"><span style="font-family: "lato" , sans-serif;"><span style="font-size: small;"><span style="letter-spacing: normal;"><span style="font-style: normal;"><b>Table Profile</b></span></span></span></span></span></span></li>
</ul></div><div style="line-height: 100%; margin-bottom: 0in;"><span style="font-variant: normal;"><span style="color: black;"><span style="font-family: "lato" , sans-serif;"><span style="font-size: small;"><span style="letter-spacing: normal;"><span style="font-style: normal;"><span style="font-weight: normal;"><br />
</span></span></span></span></span></span></span><span style="font-variant: normal;"><span style="color: black;"><span style="font-family: "lato" , sans-serif;"><span style="font-size: small;"><span style="letter-spacing: normal;"><span style="font-style: normal;"><span style="font-weight: normal;">Dimana table mahasiswa dan profile masing-masing mempunyai field users_id</span></span></span></span></span></span></span></div><div style="line-height: 100%; margin-bottom: 0in;"><br />
</div><style type="text/css">
  @page { margin: 0.79in }
  p { margin-bottom: 0.1in; line-height: 115% }
  a:link { so-language: zxx }
 </style>   <br />
<div style="line-height: 100%; margin-bottom: 0in;"><span style="font-variant: normal;"><span style="color: black;"><span style="font-family: "lato" , sans-serif;"><span style="font-size: small;"><span style="letter-spacing: normal;"><span style="font-style: normal;"><span style="font-weight: normal;">Persiapan :</span></span></span></span></span></span></span></div><br />
<code><br />
composer require maatwebsite/excel<br />
</code><br />
<br />
<code><br />
'providers' => [<br />
/*<br />
* Package Service Providers...<br />
*/<br />
Maatwebsite\Excel\ExcelServiceProvider::class,<br />
]<br />
<br />
'aliases' => [<br />
...<br />
'Excel' => Maatwebsite\Excel\Facades\Excel::class,<br />
]<br />
</code><br />
<br />
<br />
<div style="font-weight: normal; line-height: 100%; margin-bottom: 0in;"><span style="font-variant: normal;"><span style="color: black;"><span style="font-family: "lato" , sans-serif;"><span style="font-size: small;"><span style="letter-spacing: normal;"><span style="font-style: normal;">Lalu Publish Vendor Providers dengan perintah</span></span></span></span></span></span></div><style type="text/css">
  @page { margin: 0.79in }
  p { margin-bottom: 0.1in; line-height: 115% }
  a:link { so-language: zxx }
 </style>    <br />
<div style="line-height: 100%; margin-bottom: 0in;"><br />
</div><div style="line-height: 100%; margin-bottom: 0in;"><pre><code>
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"
</code>
</pre></div><br />
<style type="text/css">
  @page { margin: 0.79in }
  p { margin-bottom: 0.1in; line-height: 115% }
  a:link { so-language: zxx }
 </style>   <br />
<div style="line-height: 100%; margin-bottom: 0in;"><span style="font-variant-east-asian: normal; font-variant-numeric: normal;"><span style="color: black;"><span style="font-family: "lato" , sans-serif;"><span style="font-size: 12pt;">Selanjutnya kita akan buat file import dengan melakukan perintah :</span></span></span></span></div><div style="line-height: 100%; margin-bottom: 0in;"><span style="font-variant-east-asian: normal; font-variant-numeric: normal;"><span style="color: black;"><span style="font-family: "lato" , sans-serif;"><span style="font-size: 12pt;"><br />
</span></span></span></span></div><div style="line-height: 100%; margin-bottom: 0in;"><br />
</div><pre><code>
php artisan make:import MhsImport
</code>
</pre>Jika Kita Cek ada folder baru di dalam app/Imports/MhsImport<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDoz5sWb8TZWpF27aLpT25pDbnnEt3oaa2M1CLy-PTAOliOXzguxJ1of88o34JYJPELl-wVWSxigv8h6eB4QeUFvB1IB_ijY-vVFAjCmIBOAUsPVXRpi6ClAN3r50BrxIqDfi3KO_GZyE/s1600/folder.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="258" data-original-width="190" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDoz5sWb8TZWpF27aLpT25pDbnnEt3oaa2M1CLy-PTAOliOXzguxJ1of88o34JYJPELl-wVWSxigv8h6eB4QeUFvB1IB_ijY-vVFAjCmIBOAUsPVXRpi6ClAN3r50BrxIqDfi3KO_GZyE/s320/folder.png" width="233" /></a></div><br />
Pertama kita akan menggunakan metode collection untuk membaca isi dari file excel yang akan kita buat :<br />
Untuk Template excel saya buat seperti berikut :<br />
<style type="text/css">
  body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Arial"; font-size:x-small }
  a.comment-indicator:hover + comment { background:#ffd; position:absolute; display:block; border:1px solid black; padding:0.5em;  } 
  a.comment-indicator { background:red; display:inline-block; border:1px solid black; width:0.5em; height:0.5em;  } 
  comment { display:none;  } 
 </style>      <br />
<table border="0" cellspacing="0"><colgroup width="92"></colgroup>  <colgroup width="69"></colgroup>  <colgroup width="167"></colgroup>  <colgroup width="89"></colgroup>  <colgroup width="84"></colgroup>  <colgroup width="75"></colgroup>  <colgroup width="90"></colgroup>  <colgroup width="77"></colgroup>  <colgroup width="80"></colgroup>  <colgroup width="61"></colgroup>  <colgroup width="63"></colgroup>  <colgroup width="71"></colgroup>  <tbody>
<tr>   <td align="center" height="17" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">NAMA</td>   <td align="center" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">NIM</td>   <td align="center" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">EMAIL</td>   <td align="center" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">PASSWORD</td>   <td align="center" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">ANGKATAN</td>   <td align="center" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">JURUSAN</td>   <td align="center" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">KURIKULUM</td>   <td align="center" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">TTL</td>   <td align="center" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">KELAMIN</td>   <td align="center" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">AGAMA</td>   <td align="center" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">STATUS</td>   <td align="center" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">DIBUAT</td>  </tr>
<tr>   <td align="left" height="17" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">aman</td>   <td align="right" sdnum="1033;" sdval="14050880" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">14050880</td>   <td align="left" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">aman@email.com</td>   <td align="right" sdnum="1033;" sdval="123" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">123</td>   <td align="right" sdnum="1033;" sdval="192" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">192</td>   <td align="left" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">Ekonomi</td>   <td align="left" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">Ekonomi</td>   <td align="left" sdnum="1033;0;@" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">1991-05-05</td>   <td align="left" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">LAKI-LAKI</td>   <td align="left" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">ISLAM</td>   <td align="right" sdnum="1033;" sdval="1" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">1</td>   <td align="left" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">admin</td>  </tr>
<tr>   <td align="left" height="17" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">asas</td>   <td align="right" sdnum="1033;" sdval="1405051" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">1405051</td>   <td align="left" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">asas@email.com</td>   <td align="right" sdnum="1033;" sdval="124" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">124</td>   <td align="right" sdnum="1033;" sdval="192" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">192</td>   <td align="left" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">Perbankan</td>   <td align="left" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">Perbankan</td>   <td align="left" sdnum="1033;0;@" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">1991-05-06</td>   <td align="left" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">LAKI-LAKI</td>   <td align="left" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">ISLAM</td>   <td align="right" sdnum="1033;" sdval="1" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">1</td>   <td align="left" style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000; border-top: 1px solid #000000;" valign="bottom">admin</td>  </tr>
</tbody></table><code><br />
<br />
namespace App\Imports;<br />
<br />
use Illuminate\Support\Collection;<br />
use Maatwebsite\Excel\Concerns\ToCollection;<br />
use Maatwebsite\Excel\Concerns\WithHeadingRow;<br />
use DB;<br />
use App\User;<br />
use Carbon\Carbon;<br />
<br />
</code></div><br />
<br />
Code Lengkap Isi Dari Class MhsImport<br />
<pre><code>
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use DB;
use App\User;
use Carbon\Carbon;
use App\Notifications\UserWelcome;

class MhsImport implements WithHeadingRow,ToCollection
{
/**
* @param Collection $collection
*/
public function collection(Collection $rows)
{

foreach ($rows as $key) {
$user=User::updateOrCreate(
['email'=>$key['email'],'nim'=>$key['nim']]
,[
'uuid'=>unik(),
'name'=>$key['nama'],

'password'=>bcrypt($key['password']),
'old_password'=>$key['password'],
'status'=>$key['status'],
'created_by'=>$key['dibuat']

]);
$selectuser=User::where('email',$key['email'])->get();
$jurusan=DB::table('jurusan')->where('name','LIKE','%'.$key['jurusan'].'%')->first();
$kurikulum=DB::table('kurikulum')->where('name','LIKE','%'.$key['kurikulum'].'%')->first();

foreach ($selectuser as $mhs ) {
$x=DB::table('mahasiswa')->updateOrInsert(['users_uuid'=>$mhs->uuid],[
'uuid'=>unik(),
'nim'=>$mhs->nim,
'angkatan'=>$key['angkatan'],
'jurusan_uuid'=>$jurusan->uuid,
'kurikulum_uuid'=>$kurikulum->uuid,
'created_by'=>'Import',
'created_at'=>Carbon::now(),

]);
}
foreach ($selectuser as $pro ) {
DB::table('profile')->updateOrInsert(['users_uuid'=>$pro->uuid],[
'uuid'=>unik(),
'nama_lengkap'=>$pro->name,
'religion'=>$key['agama'],
'dob'=>$key['ttl'],
'sex'=>$key['kelamin']
]);
}
}

}

}
</code>
</pre></div><br />
<br />
Lalu isi file Controller<br />
<pre><code>
use App\Imports\MhsImport;
use Excel;
use DB;

class TesController extends Controller
{

public function store(Request $request)
{
DB::beginTransaction();

try {
DB::commit();

$file = $request->file('file');
Excel::import(new MhsImport,$file);

echo "OK";
} catch (\Exception $e) {
DB::rollback();
//toastr()->error($e->getMessage());
echo $e->getMessage();
}

}
</code>
</pre></div><br />
Ok sekian wasalamwalikum..</div>

Upload Multi Table Relation Dengan Laravel Excel

Assalamwalaikum Warohmatullahi Wabarokatuh. Hi.. sahabat rajin, kali ini kita akan membahas bagaimana cara Upload ke banyak table berel...

Baca selengkapnya »

Rajin Ngoding

Upload Multi Table Relation Dengan Laravel Excel

Entri Populer

Arsip Blog

Labels