Skip to content

[Contoh] Input Database MySQL dari File Berformat Excel dengan Excel Reader (no-pear)

April 10, 2012

http://bimosaurus.com/2012/04/10/contoh-input-database-mysql-dari-file-berformat-excel-dengan-excel-reader-no-pear/

 

Jika pada tiga posting yang lalu ( ini dan ini ) telah dipost tentang tutorial penggunaan PHP untuk keperluan export data ke file berformat Excel, maka kini sebaliknya. Kita akan coba lakukan import data dari Excel ke dalam tabel MySQL. Cara lama yang biasa digunakan untuk melakukan import data dari excel ke dalam MySQL adalah dengan menyimpan file excel tersebut sebagai file berformat CSV ( Comma Separated Value ). Namun diyakini bahwa format CSV ini tidak akan valid karena batas antar sel dan batas antar baris masih menggunakan karakter huruf alfabet. Semisal, ditentukan sebuah cara bahwa batas sel adalah dengan tanda koma, maka jika dalam konten sel terdapat koma, maka akan terjadi kesalahan dalam pembacaan jumlah sel.

Cara paling baik yaitu membiarkan saja dalam bentuk XLS file yang dibuka encoding datanya, dan kemudian direstore dalam bentuk text yang bisa dibaca dengan normal oleh PHP. Untuk membuka encoding data ini telah disediakan sebuah tools yang terkenal yang dinamakan Excel Reader. Biasanya filenya bernama excel_reader2.php. Disini dapat di download di : http://bimosaurus.com/demo/excel_reader2.php. Silakan di download dan letakkan dalam direktori libs anda. Di dalam excel reader ini telah terdapat class dan fungsi-fungsi untuk melakukan pembacaan file excel. Kita selaku developer tinggal menggunakan. Bagaimana contoh penggunaan dan penjelasannya?

Langkah pertama adalah biasanya kita perlu bantuan sebuah form yang berfungsi sebagai upload data. Misal kita buat sebuah file bernama : import.php, kira-kira berisi :

 

 
<?php
if( mysql_connect("localhost","user","password") ){
   mysql_select_db( "kepegawaian" );
}else{
   echo "database gagal";
}
?>
<table border="1">
<tr><th>Nip</th><th>Nama</th><th>Honor</th><th>Status Ambil</th></tr>
 
<?php
$sql = "SELECT * FROM `pegawai`";
$query = mysql_query( $sql );
while( $res=mysql_fetch_array( $query ) ){
?>
<tr><td><?php echo $res['nip'];?></td><td><?php echo $res['nama'];?></td><td><?php echo $res['honor'];?></td><td><?php echo $res['status_ambil'];?></td></tr>
<?php
}
?>
 
</table><br />
<form action="import-do.php" method="post" enctype="multipart/form-data">
<input type="file" name="upfile"><br />
<input type="submit" value="IMPOR" name="submit_import">
</form><br />
<a href="http://bimosaurus.com/demo/master_excel.xls">MasterFile Disini</a>

Selanjutnya,dibuat juga file tujuan uploadnya yang pada form di atas diarahkan ke file : import-do.php. File tersebut akan berisi : menangkap file,dan memparsing data yang ada di dalamnya dengan tools excel_reader2.php, dan melakukan restore data dalam database MySQL.

Saya masih menggunakan tabel yang telah digunakan dalam posting sebelumnya yaitu tabel pegawai :

nip nama honor status_ambil
10001 Susanto Rp. 1000000 Belum
10002 Susanti Rp. 1000000 Sudah
10003 Marwoto Rp. 2000000 Sudah
10004 Gunadi Rp. 1500000 Belum
10005 Nur Eko Rp. 1000000 Belum

Selanjutnya saya mempersiapkan sebuah master file excel seperti berikut ini : http://bimosaurus.com/demo/master_excel.xls. Master file ini berfungsi untuk panduan user penginput data dalam melakukan inputing data. Sedangkan file import-do.php berisi demikian :

<?php
 
include "libs/excel_reader2.php";
if( mysql_connect("localhost","user","password") ){
   mysql_select_db( "kepegawaian" );
}else{
   echo "database gagal";
}
if( isset( $_POST['submit_import'] ) ){
 
$cell   = new Spreadsheet_Excel_Reader($_FILES['upfile']['tmp_name']);
$jumlah = $cell->rowcount($sheet_index=0);
 
$i = 2; // dimulai dari ke dua karena baris pertama berisi title
while( $i<=$jumlah ){
 
   //$cell->val( baris,kolom )
 
   $nip  = $cell->val( $i,1 );
   $nama = $cell->val( $i,2 );
   $honor= $cell->val( $i,3 );
   $status_ambil = $cell->val( $i,4 );
 
$sql ="INSERT INTO `pegawai` (`nip`,`nama`,`honor`,`status_ambil`) VALUES ('$nip','$nama','$honor','$status_ambil')";
mysql_query( $sql );
   $i++;
}
 
}
header("Location:http://bimosaurus.com/demo/import.php");
?>

Penjelasannya adalah sebagai berikut, file yang diupload hanya akan diletakkan dalam direktori temporary, dan mulai dibaca. Sheet_index merupakan nomer worksheet yang dibaca. 0 adalah worksheet pertama. Selanjutnya pembacaan baris dimulai dari nomer 2 (dua) karena baris pertama berisi header kolom. Pembacaan akan dimulai dengan proses looping yang dimulai dengan angka dua dan berakhir pada jumlah baris excel yang terbaca oleh php. Urutannya adalah $cell->val( baris,kolom ); Data diinisiasi dalam variabel masing-masing, dan dimasukkan dalam query, dan dieksekusi.

Nah file ini dapat dilihat di http://bimosaurus.com/demo/import.php. Kemudian perhatikan juga dalam posting sebelumnya, tentunya dalam demo terjadi perubahan, karena saya dalam hal ini menggunakan tabel yang sama. Dua script ini bisa saja disatukan jadi satu, dengan catatan bahwa form html diletakkan di bagian bawah dari aksi uploading, dan tentunya pengubahan arah form action. Permasalahan yang sering terjadi dalam proses uploading adalam masalah format cell, yang biasanya dapat bervariasi sekitar Text, Numeric, dan lain sebagainya. Oleh karena itu user perlu diarahkan untuk menggunakan format cell dengan benar.

Sebenarnya masih terdapat satu hal terkait excel ini, yaitu penggunaan PEAR. Dimana dalam PEAR telah lengkap untu segala library. Namun tutorial ini berguna untuk semua server tanpa PEAR ataupun ada PEAR. Karena tidak selalu server berinstalkan PEAR.

Selamat Mencoba, semoga berguna

From → Tak Berkategori

6 Komentar
  1. ferry permalink

    http://bimosaurus.com/demo/excel_reader2.php. tidak bisa di download, mohon berikan link nya lagi pak. thanks yah ifonya keren banget ini yg saya cari.

  2. adi permalink

    pak saya mau nanya
    untuk import file yg bapak buat
    jika ada satu baris data excel yg sama persis dengan di database
    maka data di excel tersebut tidak masuk ke database
    tapi gimana caranya agar data yg baru tersebut bisa niban data yg lama nya pak?
    mohon solusinya y pak
    terima kasih

  3. vera permalink

    kenapa ada eror
    The filename C:\xampp\tmp\phpFE8F.tmp is not readable

  4. hafid permalink

    bagai mana jika menyimpan foto yang ada di dalam excel ke mysql dengan excel_reader2.php ?

    • Maaf mas Hafid, baru sempat membalas. Untuk excel_reader kita, saya belum menemukan cara mengisikan gambar atau foto ke dalamnya, selain embed url, atau mengencodingnya menjadi data yang bisa dibaca oleh excel🙂

      Atau barangkali ada teman lain yang dapat menjelaskannya.

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: