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

Posted on by bimosaurus

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.

Artikel terkait

40 thoughts on “[Contoh] Cara Mengexport Data SQL ke File Excel 2007 ( xlsx ) dari MySQL dengan PHP”

  1. gan thx atas atikelnya…
    tapi saya coba dengan database oracle kok isinya kosong ya hanya
    ada header nya aja….

    Reply

    1. Sebenarnya intinya selama querynya menghasilkan array yang dapat difetch oleh PHP, tentunya bisa… Mungkin cara paling enak adalah bagian 

      $writer->save("$datetime.xlsx");

      di comment dulu

      menjadi

      //$writer->save("$datetime.xlsx");

      Dan arraynya dikeluarkan. kEbetulan untuk Oracle saya kurang tahu proses fetch_arraynya. Kalau di dalam kasus ini saya bisa melakukan seperti ini :

      
$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 ) ){

print_r($r); // menambahkan ini untuk check apakah query berhasil.
   $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++;
}

      Silakan dicoba mas…

      Reply

  2. thx gan tadi dah saya coba …
    ini script yang saya modif :

    $sql = oci_parse($connect, "SELECT id as idpel, nama as nama, trf_kd as tarif, daya as daya 
				FROM pel.plg_pelanggan where id =411020206925");
								oci_execute($sql);
								
								
$i = 2; //Dimulai dengan baris ke dua, baris pertama digunakan oleh titel kolom
while( $r = oci_fetch_array( $sql ) ){
	print_r($r);
   $sheet->setCellValue( "A" . $i, $row["Idpel"] );
   $sheet->setCellValue( "B" . $i, $row["Nama"] );
   $sheet->setCellValue( "C" . $i, $row["Tarif"] );
   $sheet->setCellValue( "D" . $i, $row["Daya"] );
   $i++;
}
 
 
 $writer = new PHPExcel_Writer_Excel2007($excel);
 $writer->save("$datetime.xlsx");

    n hasil dari fetch array nya :

    Array ( [0] => 411020206925 [IDPEL] => 411020206925 [1] => KANDATEL AMBON [NAMA] => KANDATEL AMBON [2] => B3 [TARIF] => B3 [3] => 555000 [DAYA] => 555000 ) Your File in : Here

    gimana menurut agan ?? apa ada query saya yang keliru ??

    Reply

    1. Maaf mas, pada while nya anda gunakan $r, sementara pada setCellValue anda masih menggunakan $row. MUngkin bisa diteliti disitu lebih dulu.. 🙂

      while($r = oci_fetch_array( $sql ) ){
print_r($r);
$sheet->setCellValue( “A” . $i, $row["Idpel"] );
$sheet->setCellValue( “B” . $i, $row["Nama"] );
$sheet->setCellValue( “C” . $i, $row["Tarif"] );
$sheet->setCellValue( “D” . $i, $row["Daya"] );
$i++;
}
      Reply

  3. hmmmm wah iya tu lpa >.<…
    tpi kok masi ga ada isinya juga ya ….
    cma ada titel kolomnya aja….
    🙁

    Reply

    1. Saya kurang tahu kasusnya ya mas, jika tidak menghadapi langsung :). Namun kira-kira seperti ini penjelasan saya

      • Proses Export jika telah menghasilkan Title, berarti plotting ke Excel sukses. Jika tidak kemungkinan masih ada masalah di setting php.ini
      • Query ketika pengambilan parameter array telah dapat di Print_r, maka telah sukses

      Untuk itu cobalah periksa kembali pada bagian looping pengambilan datanya. 🙂

      Reply

    1. Kalau berkenan, silakan dishare disini saja, apa saja errornya dan bagaimana proses yang dibangun. Semoga team disini bisa membantu :). Banyak sekali langkah yang bisa digunakan untuk melakukan deteksi kesalahan yang nantinya juga untuk sempurnanya sistem.

      Reply

  6. gan gimana cara buat bila kita melakukan pencarian data berdasarkan tanggal baru kita export ke format excel, mohon contohnya gan biar jelas coz masih belajar, thx before… 🙂

    Reply

    1. Siap bung… Jadi Clue nya adalah seperti ini : Query akan menghasilkan Array. Array tersebutlah yang akan kita export ke dalam bentuk file excel. Untuk contoh adalah misal saya memiliki tabel `biodata` seperti berikut:

      nama tanggal_lahir
      Abu 1999-12-01
      Banu 1999-08-13
      Chandra 1999-06-09

      Dengan perkiraan tabel adalah kolom NAMA dengan format varchar(255) dan Tanggal Lahir dengan format date. Maka Query adalah memanfaatkan fasilitas pengurutan DESC (urutan terbalik) atau ASC (urutan normal), selanjutnya akan di array dalam bentuk excel. Querynya sendiri akan seperti berikut:

       $sql = "SELECT * FROM `biodata` ORDER BY `tanggal_lahir` DESC"

      Perkiraan susunan program lengkapnya kira kira sebagai berikut : 

      
getProperties()->setCreator("Bimosaurus");
$excel->getProperties()->setLastModifiedBy("Bimosaurus");
$excel->getProperties()->setTitle("Urutan tanggal lahir");
$excel->removeSheetByIndex(0);
 
 
$sheet = $excel->createSheet();
$sheet->setTitle('sheet_1');
 $sheet->setCellValue("A1", "Nama");
 $sheet->setCellValue("B1", "Tanggal Lahir");
 
$sql = "SELECT * FROM `biodata` ORDER BY `tanggal_lahir` DESC";
$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['nama'] );
   $sheet->setCellValue( "B" . $i, $r['tanggal_lahir'] );

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

      Nah perkiraan kasar demikian Gan… silakan dipelajari 🙂 barangkali ada yang masih bisa didiskusikan, share saja disini

      Reply

  7. thx ya gan atas prifatnya yang geratis ini 😀 akan saya coba praktekan dulu, tp kl ada yang gak mudeng mohon pencerahan dari suhu lagi ya, thx… 😀

    Reply

  8. thanksss gan…. artikelnya bagus…oiya kalo jumlah recordnya lebih dari 100.000 record koq gk bs ya
    ada muncul “Maximum file size, 7087104, exceeded”…
    mungkin ada yg bs membantu…..

    Reply

    1. Itu adalah karena PHP tidak dapat menerima file sebesar itu. Untuk mengubahnya dapat dilakukan pengubahan PHP.INI dengan melakukan pengubahan value untuk

      upload_max_filesize = 10M

      dapat juga melakukannya lewat awal script PHP njenengan. Misal :
      Reply

      1. saat di proses di dalam exel datanya terisi “Maximum file size, 7087104, exceeded” di php.ini upload_max_filesize’,’10M’ sudah saya setting tapi masih terjadi maximu file size… bisa minta tolong

        Reply

        1. Coba bagaimana jika diletakkan dalam awal script?

          ini_set('upload_max_filesize','10M');

          oh ya untuk setting di php.ini baru akan berpengaruh efeknya ketika apache telah direstart

          Reply

    1. Maksudnya apakah tidak disimpan di server ? Sepertinya format tersebut diatas sudah dapat langsung di download di komputer. Atau bisa dijelaskan maksud detailnya?

      Reply

  10. gan di sini malah terdapat kasus Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/smpnka/public_html/adminpanel/export_siswa.php on line 48

    File Anda: Unduh di sini

    sedang line 48 isinya :
    while( $r = mysql_fetch_array( $q ) ){
    mohon pencerahannya

    Reply

  11. mas, sy ada buat db pake phpmysql trus pake framework yii. sudah bisa dionlinekan, eh tau2 si bos nyuruh supaya dibuat link download supaya isi2 database nya bisa di eksport ke excel.

    jadi klo pake framwork yii, itu script nya ditaruh dimana, atau di gimanain ya?

    Reply

  13. bagaimana jika export berdasarkan kriteria tertentu;

    contoh

    $tahun = $_POST[‘tahun’];
    $sql = “SELECT * FROM `biodata` where tahun=’$tahun’ ORDER BY `tanggal_lahir` DESC”

    terimakasih, mohon informasinya.

    Reply

    1. Intinya sama saja mas. Kriteria persyaratan where kan hanya untuk memfilter saja. Jika dengan query biasa (tanpa where) bisa jadi,pasti dengan where juga bisa,selama sintax benar dan datanya ada

      Reply

  14. Mas bimo saya mau tanya kalo saya sudah punya file excel
    dan saya hanya ingin mengisi baris”nya yang diambil dari database gmna caranya yah mas bimo?
    terimakasih mas bimo, mohon bimbingannya.
    Salam kenal

    Reply

    1. maaf mas Luqman atas reply terlambat ini karena kebetulan posisi saya mobile on-project.
      Untuk permasalahan mas Luqman sudah ada di posting yang mas Luqman baca. Namun memang itu adalah EXPORT database ke EXCEL. Untuk mengsi dari EXCEL ke database, bisa digunakan sebaliknya IMPORT database from Excel. Ada di posting saya bagian yang bertag ini:

      https://blogit.bimosaurus.com/tag/excel/

      Namun jika ingin tanpa coding PHP mas Luqman bisa menggunakan SQL Yog atau navicat secara manual

      Reply

  15. pak bimo
    muncul error gini kenapa pak?

    Warning: mysql_connect() [function.mysql-connect]: Access denied for user ‘user’@’localhost’ (using password: YES) in C:\xampp\htdocs\phpexcel\eksport.php on line 2

    Warning: mysql_query() [function.mysql-query]: Access denied for user ‘ODBC’@’localhost’ (using password: NO) in C:\xampp\htdocs\phpexcel\eksport.php on line 27

    Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in C:\xampp\htdocs\phpexcel\eksport.php on line 27

    Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\phpexcel\eksport.php on line 29
    Your File in : Here

    Reply

    1. Mas Tofan, kesalahan pertama adalah : script php mas Tofan tidak terhubung dengan database.

      Tentunya setiap script PHP yang akan melakukan akses database harus memiliki koneksi ke dalam database. Cara untuk melakukan koneksi database cukup mudah, yaitu dengan sebelumnya memberikan perintak koneksi dan perintah hubung database

      mysql_connect( 'localhost','namauser','password_database' );
mysql_select_db( 'namadatabase' );

      Selain perintah diatas ada opsi perintah lain untuk melakukan koneksi. Apakah sudah mencobanya mas? Atau jika mas Tofan sudah memiliki file konfig database, mas belum lakukan 

      include file_konfigurasi_db.php

      Selamat memeriksa ya

      Reply

      1. heeheeeeee iya pak kelupaan.
        makasi pak sudah jalan dengan normal….

        sekarang yang saya tanyakan bagaimana caranya jika nama workbook dan worksheet nya sudah ada atau kita pilih??

        terima kasih

        Reply

  16. mas trima kasih bwt artikelnya, sangat berguna bwt saya…untuk proses sebaliknya, import data excel ke mysql itu gmna ya mas, masih tetap pakai class php excel sperti proses diatas itu gk ???

    Reply

    1. Mas Rangga dapat melihat pada artikel saya yang lain di https://blogit.bimosaurus.com/tag/excel untuk segala yang berkaitan dengan excel. Untuk input ke dalam database berarti diperlukan pembaca excel, dan selanjutnya menginput ke dalam database. Untuk saya, saya menggunakan excel_reader seperti pada posting ini

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

      selamat mencoba ya

      Reply

  18. Mas, mohon bantuannya ya,,gimana kalau data yang di database ditampilankan dulu dalam bentuk table,,jadi di buatkan tombol untuk export ke excel,,itu gimana mas??
    Mohon pencerahannya mas..terima kasih banyak..

    Reply

  19. Script tsb telah saya implementasikan pada server saya (XAMPP) dan berjalan dengan baik sesuai harapan. Sayangnya waktu dieksekusi pada server yang sebenarnya (Internet), script tesebut gagal. Ada pesan “faultCode1faultStringFatal error:Class ‘ZipArchive’ not found in /usr/home/smkn2/html/reg_siswa_files/Classes/PHPExcel/Writer/Excel2007.php on line 227”. Menurut hemat saya server tsb yang tidak dapat mengeksekusi script diatas. Boleh jadi server tsb sudah tidak up to date. Bagaimana cara mengatasinya mas? Agak merepotkan saya karena saya tidak punya akses penuh ke Server tsb dan komputer client rata2 minimal menggunakan Excel 2007 atau versi yg lebih tinggi. Mohon pencerahannya mas!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *