[Contoh] Cara Mengexport Data SQL ke File Excel 2007 ( xlsx ) dari MySQL dengan PHP

Berdasarkan komentar saudara Dahlan di https://blogit.bimosaurus.com/2012/04/09/contoh-3-membuat-laporan-export-data-dari-mysql-ke-excel-terencoding-dengan-php-no-pear, maka saya coba paparkan tentang melakukan export konversi data dari SQL ke Excel 2007 dengan menggunakan PHP. Sehingga data yang berasal dari SQL dapat di download ke dalam bentuk Excel 2007

STRUKTUR XLSX / Excel 2007

Jika kita periksa sebuah file excel 2007 adalah sebuah file berformat kompresi zip. Jika anda rename, mengganti extensi file xlsx dengan zip, dan anda extract ke suatu tempat, maka file tersebut akan berisi beberapa direktori dan file berformat xml. Ya, Microsoft Office 2007 memang merancang format Office menjadi format yang mudah dikembangkan terutama untuk mendukung DotNet dan networking.

Lantas, apakah kita akan gunakan koding PHP bertele-tele untuk membuat aplikasi untuk memodifikasi XML tersebut? Untuk hal itu, kita tidak perlu koding sendiri, hanya perlu belajar dan menggunakan sebuah fungsi dari http://www.phpexcel.net/ atau http://phpexcel.codeplex.com/. Silakan download paket aplikasi fungsi PHP-Excel tersebut, dan extract dalam direktori DocumentRoot yang anda gunakan. Kira-kira struktur data akan berformat seperti demikian :

1.7.6/changelog.txt

1.7.6/Classes/

1.7.6/Documentation/

1.7.6/install.txt

1.7.6/license.txt

1.7.6/Tests/

Kita hanya membutuhkan sesuatu di dalam directori Classes. Untuk kasus yang saya pakai, saya melakukan pengkopian direktori Classes ke aplikasi web yang saya butuhkan menjadi

{direktori_aplikasi}libs/phpexcel/

 

BAGAIMANA MENGEKSPOR DATA KE DALAM XLSX?

Berikut satu script aplikasi PHP untuk melakukan Export ke dalam XLSX

 


getProperties()->setCreator("Bimosaurus");
$excel->getProperties()->setLastModifiedBy("Bimosaurus");
$excel->getProperties()->setTitle("Coba");
$excel->removeSheetByIndex(0);


$sheet = $excel->createSheet();
$sheet->setTitle('sheet_1');
 $sheet->setCellValue("A1", "Kolom Satu");
 $sheet->setCellValue("B1", "Kolom Dua");
 $sheet->setCellValue("C1", "Kolom Tiga");
 $sheet->setCellValue("D1", "Kolom Empat");
 $sheet->setCellValue("E1", "Kolom Lima");
 $sheet->setCellValue("F1", "Kolom Enam");

 $sheet->setCellValue("A2", "Isi Satu");
 $sheet->setCellValue("B2", "Isi Dua");  
 $sheet->setCellValue("C2", "Isi Tiga");
 $sheet->setCellValue("D2", "Isi Empat"); 
 $sheet->setCellValue("E2", "Isi Lima"); 
 $sheet->setCellValue("F2", "Isi Enam");
 
 $writer = new PHPExcel_Writer_Excel2007($excel);
 $writer->save("excel.xlsx");
 
 
?>
Your File in : Here

Cobalah anda lakukan dalam percobaan anda. Nah, sekarang bagaimana apabila kita akan melakukan pengambilan data dari MySQL Server dengan PHP dan direstore ke dalam XLSX?

Berikut satu contoh programnya di bawah ini. Saya masih menggunakan table lama saya yaitu tabel pegawai dari database kepegawaian.

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

getProperties()->setCreator("Bimosaurus");
$excel->getProperties()->setLastModifiedBy("Bimosaurus");
$excel->getProperties()->setTitle("Data Pegawai PT Anu");
$excel->removeSheetByIndex(0);


$sheet = $excel->createSheet();
$sheet->setTitle('sheet_1');
 $sheet->setCellValue("A1", "NIP");
 $sheet->setCellValue("B1", "Nama");
 $sheet->setCellValue("C1", "Honor");
 $sheet->setCellValue("D1", "Status Ambil");


$sql = "SELECT * FROM `pegawai`";
$q = mysql_query( $sql );
$i = 2; //Dimulai dengan baris ke dua, baris pertama digunakan oleh titel kolom
while( $r = mysql_fetch_array( $q ) ){
   $sheet->setCellValue( "A" . $i, $r['nip'] );
   $sheet->setCellValue( "B" . $i, $r['nama'] );
   $sheet->setCellValue( "C" . $i, $r['honor'] );
   $sheet->setCellValue( "D" . $i, $r['status_ambil'] );
   $i++;
}


 $writer = new PHPExcel_Writer_Excel2007($excel);
 $writer->save("$datetime.xlsx");
 
 
?>
Your File in : Here

Nah bandingkan juga penulisan format export ke kolom excel, dengan artikel lalu https://blogit.bimosaurus.com/2012/04/09/contoh-3-membuat-laporan-export-data-dari-mysql-ke-excel-terencoding-dengan-php-no-pear. Pastikan juga bahwa direktori tujuan Export file tersebut, writeable untuk Webserver anda.

Selamat mencoba, silakan melakukan respon jika perlu direspon. Saya siapkan juga satu artikel untuk pembacaan file excel 2007 untuk diupload ke dalam webserver dan akan terkonversi menjadi SQL database. Segera.

Operasional File Melalui Perintah SQL (SQL Command for File Operation)

MySQL adalah salah satu bentuk database server yang bersifat Opensource. MySQL ini menjadi banyak pilihan karena sifatnya yang opensource, updated dan mudah dicari resource / literatur belajarnya. Biasanya para operator SQL menggunakan sintaks-sintaks SQL ini untuk keperluan: DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language) dan untuk beberapa jenis database terdapat TCL (Transaction Control Language).

Apakah itu?Hal itu akan dijelaskan pada bagian sub bab di bawah.

Namun SQL untuk MySQL juga memiliki command sintaks yang cukup ampuh sekaligus berbahaya, yaitu sintaks untuk berhubungan dengan file tertentu. Dua sintaks penting diantaranya adalah:

LOAD_FILE('path_file');

dan

INTO OUTFILE('path_tujuan');

 

Contoh penggunaannya adalah :

SELECT LOAD_FILE('/home/web/test.txt');

untuk keperluan membaca file ‘/home/web/test.txt’. Contoh lainnya dapat dikombinasikan seperti berikut:

INSERT INTO `table` (`id`,`text`) VALUES ('2',LOAD_FILE('/home/web/test.txt'));

 

Sedangkan satunya dapat digunakan seperti berikut:

SELECT `text` FROM `table` WHERE `id`='2' INTO OUTFILE('/home/web/test.txt');

biasanya digunakan untuk mengambil data dan dijadikan file text.

 

 

Selamat Mencoba. Perlu diingat bahwa sebagai seorang sistem administrator, maka kita harus baik-baik mengatur permission dari MySQL. Jangan sampai perintah-perintah mudah MySQL ini menjadi masalah di kemudian hari, karena cukup banyak hacker atau program hacker menggunakan perintah-perintah ini untuk menyusup masuk ke dalam server. Semoga bermanfaat

 

DDL, DML, DCL, TCL

DDL Adalah singkatan dari Data Definition Language. Biasanya digunakan untuk menentukan struktur data tabel dan skemanya. Sintaks yang termasuk DDL antara lain : CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT

DML adalah singkatan dari Data Manipulation Language. Digunakan untuk operasional record data. Sintaks yang termasuk diantaranya antara lain: LOCK TABLE, UNLOCK TABLE, SELECT, INSERT, UPDATE, DELETE, MERGE

DCL adalah singkatan dari Data Control Language. Digunakan untuk melakukan kontrol database. Sintaks yang biasa digunakan adalah GRANT