[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 : https://blogit.bimosaurus.com/demo/excel_reader2.php.zip. 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 sehingga data yang kita upload akan langsung diparsing konversi ke bentuk 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 :

 



NipNamaHonorStatus Ambil



MasterFile Disini

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 : https://blogit.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 :

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:https://blogit.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 https://blogit.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 termasuk upload download excel. Namun tutorial ini berguna untuk semua server tanpa PEAR ataupun ada PEAR. Karena tidak selalu server berinstalkan PEAR.

Selamat Mencoba, semoga berguna

[Contoh 3] Membuat Laporan Export Data dari MySQL ke Excel terencoding dengan PHP (no-pear)

Posting lalu, https://blogit.bimosaurus.com/2012/04/09/contoh-1-membuat-laporan-export-data-dari-mysql-ke-excel-dengan-php-no-pear/ telah ditunjukkan bagaimana caranya membuat sebuah laporan download Excel dengan menggunakan php_writeexcel. Kali ini akan dilanjutkan dengan bagaimana menuangkan data dari MySQL database ke dalam file berformat XLS atau spreadsheet. Berikut sebuah contoh, misal kita memiliki sebuah tabel sebagai berikut, bernama tabel pegawai dalam sebuah database.

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

Tabel dalam database MySQL tersebut akan kita tuangkan dalam sebuah file Excel dengan nama : laporan-tanggal-dd-mm-yy.xls.
Maka kita akan membuat sebuah script seperti berikut:

require_once "libs/php_writeexcel-0.3.0/class.writeexcel_workbook.inc.php";
require_once "libs/php_writeexcel-0.3.0/class.writeexcel_worksheet.inc.php";

if(mysql_connect( "localhost","user","pass" )){
   mysql_select_db( "kepegawaian" );
}


$date = date('Y-m-d');
$fname = tempnam("/tmp", "laporan-tanggal-$date.xls"); //membuka sebuah file baru diletakkan dalam /tmp
$workbook = &new writeexcel_workbook($fname); //workbook diberinama
$worksheet = &$workbook->addworksheet(); //nambah worksheet

//tulis blok titel tabel

$worksheet->write(0, 0,  "Data Pegawai PT anu");
$worksheet->write(2, 0,  "NIP");
$worksheet->write(2, 1,  "Nama");
$worksheet->write(2, 2,  "Honor");
$worksheet->write(2, 3,  "Status Ambil");
//title selesai

//mari query
$sql = "SELECT * FROM `pegawai`";
$q = mysql_query( $sql );
$i = 3; //ingat bahwa kolom sebenarnya adalah nilai i ditambah 1, karena dimulai dengan nol
while( $r = mysql_fetch_array( $q ) ){
   $worksheet->write($i,0,$r['nip']);
   $worksheet->write($i,1,$r['nama']);
   $worksheet->write($i,2,$r['honor']);
   $worksheet->write($i,3,$r['status_ambil']);
   $i++;
}
$workbook->close(); //selesai tulis

//tuang data
header("Content-Type: application/x-msexcel; name=\"laporan-tanggal-$date.xls\"");
header("Content-Disposition: inline; filename=\"laporan-tanggal-$date.xls\"");
$fh=fopen($fname, "rb"); //membuka file namafile.xls , membacanya dan siap untuk mengolah dalam bentuk biner
fpassthru($fh); //menuangkan data yang ada di buffer
unlink($fname); //setelah terdownload, hapus file

Hasilnya kira-kira dapat diambil disini : https://blogit.bimosaurus.com/demo/export2.php. Format ini adalah setara dengan MS-Excel 2000/XP.
Posting selanjutnya adalah bagaimana memasukkan data dari XLS/Excel ke dalam database MySQL.. Tunggu posting tersebut
Semoga berguna.