Free Ebook Tutorial from Rosihan Ari's Blog

Tutorial Javascript | Tutorial PHP | Tutorial AJAX | Tutorial CSS | Tutorial FPDF | Tutorial Wordpress | Tutorial Java SE


Ebook yang direkomendasikan untuk Anda
Ebook Panduan Praktis OOP di PHP


Script PHP Export Data MySQL Ke File Excel Multiple Sheet


September 23rd, 2012 | by rosihanari | Cetak Artikel Cetak Artikel


GampSMS SMS Gateway

Sebelumnya, saya pernah memposting artikel tentang export data ke Excel (.xls) di blog ini. Namun, dalam artikel tersebut, file Excel yang dihasilkan berupa single sheet atau single worksheet. Lantas, bagaimana jika kita ingin mengeksport beberapa data ke beberapa sheet sekaligus dalam satu file? Mudah-mudahan artikel yang saya buat ini bisa mencerahkan para pengunjung blog tutorial gratis ini yang ingin tahu caranya :-)

Untuk keperluan export data excel dengan multiple sheet ini, saya menggunakan class Spreadsheet_WriteExcel yang sudah tersedia. Silakan Anda mengunduh class tersebut di sini.

Namun, sayangnya manual atau petunjuk penggunaan class tersebut sangatlah minim sehingga terpaksa saya membedah class tersebut untuk mendapatkan method atau fasilitas apa saja yang bisa digunakan dalam class ini.

Dalam artikel ini, saya akan mencoba mengupas cara mengeksport data yang ada di MySQL ke dalam file Excel menggunakan class Spreadsheet_WriteExcel. Sebagai contoh misalkan kita memiliki data mahasiswa di MySQL seperti pada gambar berikut ini

Export MySQL ke Excel

Selanjutnya kita ingin mengeksport data mahasiswa tersebut ke file Excel dengan 2 sheet, yaitu sheet pertama untuk menampilkan data mahasiswa laki-laki, dan sheet kedua untuk data mahasiswa perempuan, sebagaimana tampak pada kedua gambar di bawah ini.

Export MySQL ke Excel

Export MySQL ke Excel

Berikut ini adalah script PHP yang nantinya akan menghasilkan file Excel dengan 2 sheet apabila script ini dijalankan di browser.

export.php

<?php
  require_once('Worksheet.php');
  require_once('Workbook.php');

  // koneksi ke mysql
  mysql_connect('dbhost', 'dbuser', 'dbpass');
  mysql_select_db('dbname');

  // function untuk membuat header file excel
  function HeaderingExcel($filename) {
      header("Content-type: application/vnd.ms-excel");
      header("Content-Disposition: attachment; filename=$filename" );
      header("Expires: 0");
      header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
      header("Pragma: public");
      }

  // membuat header file excel dan nama filenya
  HeaderingExcel('mhs.xls');

  // membuat workbook baru
  $workbook = new Workbook("");
  // membuat worksheet ke-1 (data laki-laki)
  $worksheet1 =& $workbook->add_worksheet('Laki-laki');

  // setting format header tabel data
  $format =& $workbook->add_format();
  $format->set_align('vcenter');
  $format->set_align('center');
  $format->set_color('white');
  $format->set_bold();
  $format->set_italic();
  $format->set_pattern();
  $format->set_fg_color('red');

  // membuat header tabel dengan format
  $worksheet1->set_row(0, 15);
  $worksheet1->set_column(0, 0, 10);
  $worksheet1->write_string(0, 0, "NIM", $format);
  $worksheet1->set_column(0, 1, 30);
  $worksheet1->write_string(0, 1, "NAMA", $format);
  $worksheet1->set_column(0, 2, 20);
  $worksheet1->write_string(0, 2, "TINGGI BADAN (cm)", $format);

  // menampilkan data mhasiswa laki-laki

  $query = "SELECT * FROM mhs WHERE jns_kelamin = 'L'";
  $hasil = mysql_query($query);
  $baris = 1;
  while ($data = mysql_fetch_array($hasil))
  {
        $worksheet1->write_string($baris, 0, $data['nim']);
        $worksheet1->write_string($baris, 1, $data['nama']);
        $worksheet1->write_number($baris, 2, $data['tinggi']);
        $baris++;
  }

  // membuat worksheet ke-2 untuk data mhs perempuan
  $worksheet2 =& $workbook->add_worksheet('Perempuan');

  // membuat header tabel
  $worksheet2->set_row(0, 15);
  $worksheet2->set_column(0, 0, 10);
  $worksheet2->write_string(0, 0, "NIM", $format);
  $worksheet2->set_column(0, 1, 30);
  $worksheet2->write_string(0, 1, "NAMA", $format);
  $worksheet2->set_column(0, 2, 20);
  $worksheet2->write_string(0, 2, "TINGGI BADAN (cm)", $format);

  // menampilkan data mhasiswa perempuan

  $query = "SELECT * FROM mhs WHERE jns_kelamin = 'P'";
  $hasil = mysql_query($query);
  $baris = 1;
  while ($data = mysql_fetch_array($hasil))
  {
        $worksheet2->write_string($baris, 0, $data['nim']);
        $worksheet2->write_string($baris, 1, $data['nama']);
        $worksheet2->write_number($baris, 2, $data['tinggi']);
        $baris++;
  }

  $workbook->close();
?>

Pada script di atas, ada beberapa perintah yang perlu saya jelaskan di sini yaitu:

$worksheet1 =& $workbook->add_worksheet('Laki-laki');

Perintah di atas digunakan untuk membuat sheet atau worksheet dengan nama ‘Laki-laki’ (worksheet1). Selanjutnya perintah yang terkait dengan $worksheet1 tersebut selalu didahului dengan $worksheet1->… Demikian juga ketika membuat worksheet ke dua (worksheet2).

Kelebihan dari class ini adalah bisa memformat tampilan teks maupun cellnya. Dalam contoh di atas, diberikan perintah sbb:

$format =& $workbook->add_format();
$format->set_align('vcenter');
$format->set_align('center');
$format->set_color('white');
$format->set_bold();
$format->set_italic();
$format->set_pattern();
$format->set_fg_color('red');

dengan perintah di atas, kita akan membuat format teks dengan properti: bold, italic, background warna merah, vertical centered, horizontal centerd, dan warna fontnya putih. Format tersebut nantinya akan diberikan pada cell yang merupakan header dari tabel data mahasiswanya. Sebagai contoh, misalkan kita ingin memformat pada cell header tabel ‘NAMA’ dengan properti di atas pada sheet 2, maka perintahnya:

$worksheet2->write_string(0, 1, "NAMA", $format);

di mana parameter 0 dan 1 merupakan koordinat cell nya.

Adapun method set_row(x, y) digunakan untuk mengatur lebar baris pada kolom ke-x, dengan lebar y pixel. Sedangkan method set_column(x, y, z) untuk mengatur lebar kolom sebesar z pixel pada kolom ke-y. Untuk menuliskan data ke dalam cell, bisa menggunakan method write_string() maupun write_number(). Beda antara keduanya adalah jika method write_string() akan dihasilkan string pada cell tersebut, sedangkan write_number() data yang ditulis dalam cell berupa bilangan.




Share ke Facebook Share ke Twitter
Baca Juga Artikel Terkait
Script PHP Import Data Dari File Excel Multiple Sheet Ke MySQL
Membuat Script PHP Import Data MS. Excel Ke MySQL
Export Data Dari MySQL ke XML dengan PHP
Script PHP untuk Membuat Report Format MS. Excel
Script Cari Data File Yang Terupload Ke MySQL

Kata kunci: export excel - export mysql - multiple sheet - script php -


Ada 38 komentar dalam artikel ini.



  1. iqbal says:

    Buku 67 TRIK dan Ide Brilian karangan Mas Rosihan sudah aku dapatkan , tapi saya mencari trik PHP mengenai cara membandingkan isi dari 2 tabel, contohnya : mahasiswa menginput data pada tabel1 atau dapat juga mencentang data yang sudah disiapkan pada tabel2, Selanjutnya ISI Data yang dimasukkan pada tabel 1 dibandingkan dengan isi data yang ada pada tabel 2. JIKA datanya ada pada tabel 2, maka akan ada kofirmasi dari program yang memberitahu bahwa data yang diinput tersedia di tabel 2 dan sebaliknya, JIKA TIDAK ADA maka program akan memberi tahu barang tersebut tidak tersedia & masih kosong.. TERIMA KASIH ATAS BANTUANNYA..

  2. Heru says:

    Assalamualaikum
    Pak mau nanya, Kalo menyisipkan data ke File Excel yang sudah ada gimana ya caranya

    Terima kasih
    Wassalamualaikum

  3. Andi says:

    Pak Rosihanari,
    saya sudah coba tutorial ini dan berhasil,
    tetapi bagaimana caranya jika hasil generate file excel tsb langsung tersimpan misal di C:\tmp
    tanpa ada kotak dialog download ?

    Terima Kasih.

    • Andi says:

      ah sudah ketemu

      1.chdir(“C:\\Temp\\POD”);

      2.//HeaderingExcel($filename); //file name xls

      3. $workbook = new Workbook($filename);

      terima kasih tutorial generate xls nya pak

  4. Anwar says:

    Mas Ari, terima kasih ilmu nya ya.

    mau tanya mas, kalo format text (add format) untuk “merge & Center” (menyatukan 2 cell atau lebih menjadi 1 cell) gmn ya ? ,

    Mungkinkah seperti ini:

    $format =& $workbook->add_format();
    $format->set_merge(‘center’);

    Maaf mas, saya kepengen banget bisa nih..hehe makasih ya mas. sukses selalu buat mas ari.

    • Yadi Mulyadi says:

      Pa, untuk pengaturan merge cells nya gimana ya…

      untuk merge nya berhasil, tapi letak tulisannya itu kayak belum di merge….

  5. anwar says:

    Adapun method set_row(x, y) digunakan untuk mengatur lebar baris pada kolom ke-x, dengan lebar y pixel.

    harusnya gini ga sih:
    Adapun method set_row(x, y) digunakan untuk mengatur lebar baris pada baris ke-x, dengan lebar y pixel.

  6. Vicka says:

    kalau script yang pake codeigniter ada ga pak??

  7. Tommy says:

    Ada Source Code untuk Export ke .txt ga?
    saya lagi mau Develop untuk export ke .txt
    thk’s

  8. santo says:

    Maaf pak klo di PHP 5.4 kok tidak bisa berjalan ya??mohon bantuannya

  9. maryanto says:

    saya mengalai masalah dengan script ini ketika di jalankan di php 5.3. mohon solusinya …

  10. Yadi Mulyadi says:

    Pa, Kalo setting Format Cells untuk number gimana ya… ?
    misal : 58209580,45
    di format cells number, dengan sparator
    hasil : 58.209.580

    trus untuk membuat border apa pilihannya cuma thin (1) dan thick (2) aja….

    • rosihanari says:

      @yadi mulyadi: sepertinya belum bisa mas, kalaupun bisa angka tersebut sbg string, bukan number sehingga tidak bisa diolah lagi untuk perhitungannya.

  11. Ridwan Arifin says:

    Pak, saya sudah menjalankan source diatas dan telah saya modifikasi sesuai kebutuhan. Di lokal sangat berhasil pak, akan tetapi setelah saya upload ke webserver dan saya jalankan itu tidak bisa pak. Setelah saya buka file excel hasil convertnya tidak tercipta data yang saya inginkan. Didalam excelnya hanya ada tulisan sbb :Can’t create temporary file.

    apakah file OLEwriternya perlu dimodifikasi lagi, atau memang ada syntax yang terblokir oleh pihak hosting pak?

    sebelumnya saya ucapkan banyak terimakasih, mohon tanggapannya :)

    • rosihanari says:

      @ridwan arifin: sepertinya anda tidak punya hak akses ke folder temporary file di server hostingnya.

  12. Fatoni says:

    pak, waktu saya upload kok di file excel muncul tulisan kayak gini :
    Warning: fpassthru() has been disabled for security reasons in /home/sisfouni/public_html/users/xls/OLEwriter.php on line 222



 

Komentar/pertanyaan Anda!

Mohon maaf, komentar/pertanyaan yang menggunakan email palsu atau komentar bernada negatif, cemooh, umpatan, cacian atau sejenisnya secara otomatis akan terhapus karena dianggap spam dan tidak akan tampil

Dimohon jangan pula memasukkan code program dalam bentuk apapun ke dalam komentar karena akan dianggap spam.

Mohon maaf jika seandainya ada beberapa pertanyaan Anda yang tidak direspon atau ditanggapi mengingat banyaknya komentar yang masuk dan keterbatasan kemampuan/ilmu saya dalam menjawab pertanyaan satu persatu. Saya harap Anda bisa memahami dan memakluminya.

Semua komentar atau pertanyaan yg masuk tidak akan langsung tampil di halaman ini, karena akan dimoderasi dahulu.


*