Achmatim.Net



« | »

Membuat Laporan Excel dengan PHP

Dalam pembuatan sistem atau aplikasi berbasis web, seringkali diperlukan laporan (report) untuk berbagai keperluan. Pada umumnya, laporan dapat disajikan dalam 3 (tiga) bentuk yaitu laporan HTML, laporan bentuk PDF dan laporan Excel atau Spreadsheet. Bentuk laporan HTML merupakan bentuk laporan yang ditampilkan secara langsung di halaman web, dapat dibuat dengan perintah HTML dan CSS. Sedangkan laporan dalam bentuk PDF, pada umumnya disajikan sebagai format siap cetak atau siap disajikan. Tentang bagaimana membuat laporan PDF dengan PHP, silahkah baca tutorial mengenai Step by Step membuat laporan PDF.

Dalam tutorial ini, akan dibahas bentuk laporan yang ketiga yaitu laporan Excel dengan PHP. Namun dalam bentuk yang sangat sederhana. Tutorial ini terbatas pada pembahasan untuk membuat dan menuliskan data ke dalam file Excel dengan format .xls yang kompatibel dengan Microsoft Excel, Open Office, Libre Office dan program pengolah spreadsheet lainnya. Masih dalam bentuk sederhana juga, tutorial ini akan diakhiri dengan contoh laporan (report) dimana data diambil dari database MySQL.

php-excel

Class untuk Membuat dan Menulis ke Excel

Untuk membuat maupun menulis file excel sebenarnya tidak terlalu sulit, karena sudah cukup banyak tersedia library atau class yang dibuat khusus untuk menangani membuat, menulis bahkan membaca file excel dan spreadsheet. Beberapa library tersebut akan diulas secara singkat di bagian akhir tutorial ini. Dalam tutorial ini, kita akan menggunakan satu class untuk membuat dan menulis file excel yang dibuat oleh Shararia Azam dan dipublikasikan di website http://www.shahariaazam.com. Class penanganan excel buatannya cukup singkat dan sederhana namun sudah sangat cukup untuk memberikan gambaran bagaimana penanganan file excel dengan menggunakan PHP.

  1. <?php
  2. /**
  3.  * By Shaharia Azam
  4.  * shaharia.azam@gmail.com
  5.  * http://www.shahariaazam.com
  6.  */
  7. class Excel{
  8.  
  9. /**
  10.   * set the header configuration
  11.   * @param $filename the xls file name
  12.   */
  13. function setHeader($filename)
  14. {
  15. header("Pragma: public");
  16. header("Expires: 0");
  17. header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  18. header("Content-Type: application/force-download");
  19. header("Content-Type: application/octet-stream");
  20. header("Content-Type: application/download");;
  21. header("Content-Disposition: attachment;filename=$filename");
  22. header("Content-Transfer-Encoding: binary ");
  23. }
  24.  
  25. /**
  26.   * write the xls begin of file
  27.   */
  28. function BOF() {
  29. echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
  30. return;
  31. }
  32.  
  33. /**
  34.   * write the xls end of file
  35.   */
  36. function EOF() {
  37. echo pack("ss", 0x0A, 0x00);
  38. return;
  39. }
  40.  
  41. /**
  42.   * write a number
  43.   * @param $Row row to write $Value (first row is 0)
  44.   * @param $Col column to write $Value (first column is 0)
  45.   * @param $Value number value
  46.   */
  47. function writeNumber($Row, $Col, $Value) {
  48. echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
  49. echo pack("d", $Value);
  50. return;
  51. }
  52.  
  53. /**
  54.   * write a string label
  55.   * @param $Row row to write $Value (first row is 0)
  56.   * @param $Col column to write $Value (first column is 0)
  57.   * @param $Value string value
  58.   */
  59. function writeLabel($Row, $Col, $Value) {
  60. $L = strlen($Value);
  61. echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
  62. echo $Value;
  63. return;
  64. }
  65. }

 

Pada class sederhana di atas terdapat beberapa fungsi. Fungsi pertama adalah fungsi setHeader() di baris 13-23 yang berperan untuk mengatur header file excel, termasuk nama file excel. Parameter dari fungsi ini adalah nama file excel. Setelah fungsi setHeader(), terdapat fungsi BOF() dan EOF(), masing-masing pada baris 28-31 dan 36-39. Kedua fungsi tersebut akan sebagai penanda awal dan akhir file excel. Di dalamnya terdapat fungsi pack() yang merupakan fungsi PHP untuk menghasilkan nilai biner berdasarkan format tertentu. Format fungsi pack yang digunakan dalam class diatas antara lain “s” yang berarti signed-short dan “d” yang berarti double. Selanjutnya untuk mengisikan data ke dalam cell di excel, dibuatlah fungsi writeNumber() dan writeLabel(). Fungsi writeNumber() digunakan untuk menulis data bertipe numerik (angka) dan fungsi writeLabel() untuk menulis data bertipe string (text). Kedua fungsi tersebut memiliki tiga parameter yaitu $Row yang menunjukkan posisi baris cell, $Col yang menunjukkan posisi kolom cell dan $Value yang merupakan nilai nilai yang akan ditulis. Perlu diingat juga bahwa urutan baris dan kolom diawali dengan 0.

Membuat dan Menulis ke Excel

Program PHP berikut ini akan memberikan contoh sederhana dari penggunaan class Excel sederhana di atas. Program akan membuat file excel dengan nama “contoh-1.xls”. Pada cell yang pertama (Row=0, Col=0) diisi dengan text “NIM”, pada cell kedua (Row=0, Col=1) diisi dengan text “1111500200” dan seterusnya.

  1. <?php
  2. require_once "Excel.class.php";
  3.  
  4. $excel = new Excel();
  5. // Send Header
  6. $excel->setHeader('contoh-1.xls');
  7. $excel->BOF();
  8.  
  9. $excel->writeLabel(0, 0, "NIM :");
  10. $excel->writeLabel(0, 1, "1111500200");
  11. $excel->writeLabel(1, 0, "NAMA :");
  12. $excel->writeLabel(1, 1, "ACHMAD SOLICHIN");
  13. $excel->writeLabel(2, 0, "NILAI :");
  14. $excel->writeNumber(2, 1, 85);
  15.  
  16. $excel->EOF();
  17. exit();
  18. ?>

 

Jika program di atas dijalankan maka akan terbuat sebuah file excel dengan nama “contoh-1.xls” yang isinya kurang lebih sebagai berikut (dibuka dengan LibreOffice 3) :

File Excel Hasil Contoh Program 1

File Excel Hasil Contoh Program 1

Hasil dari program pertama di atas dapat dilihat secara langsung disini.

Membuat Laporan Excel dari Database MySQL

Untuk keperluan contoh kali ini, seperti biasanya, kita akan menggunakan tabel sederhana berisi data mahasiswa (nama tabel=mahasiswa) dengan struktur sebagai berikut:

FIELD TYPE LENGTH DESCRIPTION

nim

VARCHAR

10

NIM Mahasiswa

nama

VARCHAR

30

NAMA Mahasiswa

alamat

TEXT

-

Alamat Mahasiswa

 

Sebelum mencoba program di bawah ini, pastikan database dan tabel di atas sudah dibuat di MySQL. Jangan lupa isikan beberapa contoh data ke dalam tabel mahasiswa tersebut.

Sementara itu, untuk menangani koneksi dan pengambilan data dari database MySQL, kita akan menggunakan fungsi mysqli yang merupakan fungsi mysql yang telah ditingkatkan kemampuan dan performanya. Kita akan menggunakan gaya penulisan mysqli dengan gaya object oriented. Saat ini, fungsi ini yang sangat disarankan untuk digunakan, dibanding menggunakan fungsi mysql_*.

Berikut ini contoh program kedua untuk membuat laporan excel dari tabel mahasiswa yang sudah dibuat sebelumnya.

  1. <?php
  2. require_once "Excel.class.php";
  3.  
  4. #koneksi ke mysql
  5. $mysqli = new mysqli("localhost","root","qwerty","demo");
  6. if ($mysqli->connect_error) {
  7. die('Connect Error (' . $mysqli->connect_error . ') ');
  8. }
  9. #akhir koneksi
  10.  
  11. #ambil data
  12. $query = "SELECT nim, nama, alamat FROM mahasiswa";
  13. $sql = $mysqli->query($query);
  14. $arrmhs = array();
  15. while ($row = $sql->fetch_assoc()) {
  16. array_push($arrmhs, $row);
  17. }
  18. #akhir data
  19.  
  20. $excel = new Excel();
  21. #Send Header
  22. $excel->setHeader('contoh-2.xls');
  23. $excel->BOF();
  24.  
  25. #header tabel
  26. $excel->writeLabel(0, 0, "NIM");
  27. $excel->writeLabel(0, 1, "NAMA");
  28. $excel->writeLabel(0, 2, "ALAMAT");
  29.  
  30. #isi data
  31. $i = 1;
  32. foreach ($arrmhs as $baris) {
  33. $j = 0;
  34. foreach ($baris as $value) {
  35. $excel->writeLabel($i, $j, $value);
  36. $j++;
  37. }
  38. $i++;
  39. }
  40.  
  41. $excel->EOF();
  42.  
  43. exit();
  44. ?>

 

Berikut penjelasan singkat dari program PHP di atas:

Hasil dari program kedua di atas dapat dilihat secara langsung disini dimana kurang lebih hasil file excelnya sebagai berikut:

Tampilan File Excel Hasil Program 2

Tampilan File Excel Hasil Program 2

Berbagai Library Penanganan Excel Spreadsheet

Selain dengan class sederhana yang sudah dibahas di atas, terutama untuk keperluan yang lebih kompleks, kita dapat menggunakan berbagai library PHP. Library tersebut dapat ditemukan dengan mudah di internet. Berikut ini beberapa library beserta review singkatnya:

  1. PHP Excel (http://phpexcel.codeplex.com)

    PHPExcel ini merupakan salah satu project open source yang didanai oleh Microsoft. PHPExcel ini cukup tangguh dalam melakukan baca dan tulis ke file excel baik dalam format .xls maupun .xlsx. Pada situs resminya, disebutkan bahwa PHPExcel mendukung tipe file Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF dan HTML.

  2. PEAR Excel Spreadsheet Writer (http://pear.php.net/)

    Excel Spreadsheet Writer merupakan salah satu package dari PEAR (PHP Extension and Application Repository), sebuah kumpulan ekstensi dan library PHP. Package Excel Spreadsheet Writer sendiri sebenarnya memiliki kemampuan cukup lengkap dalam hal penulisan file Excel, namun sayangnya saat ini sudah tidak dikembangkan lagi. Library ini tidak bisa untuk membaca file excel.

  3. php-excel (https://code.google.com/p/php-excel/)

    Library ini merupakan library sederhana yang dapat digunakan untuk meng-ekspor data dari database ke file excel secara cepat. Tidak banyak fitur lain yang ditawarkan oleh library ini, namun tidak ada salahnya Anda coba.

  4. php-excel-reader (https://code.google.com/p/php-excel-reader/)

    Dengan library PHP ini kita dapat membaca isi file excel (.xls) beserta format-formatnya, kemudian menampilkannya menjadi format HTML. Format dan isinya dapat dibaca dengan baik oleh library ini, sehingga sangat cocok jika kita ingin membuat tampilan (preview) dari file excel yang sudah ada.

Kesimpulan

Secara sederhana, tutorial ini memberikan gambaran bagaimana membuat laporan dalam bentuk excel. Class yang digunakan masih dalam bentuk yang sederhana, namun sangat terbuka untuk dikembangkan lebih lanjut. Kita juga dapat menggunakan class atau library yang lebih kompleks seperti PHPExcel untuk keperluan penanganan file excel menggunakan PHP.

Download Tutorial dan Source Code

Tutorial Membuat Laporan Excel dengan PHP (341.3 KiB, 15,216 hits)
Contoh Program Membuat Laporan PHP ke Excel (3.6 KiB, 6,999 hits)

Referensi Terkait

 

Semoga tutorial ini bermanfaat untuk kita semua dan berbagilah hal baik ke sebanyak-banyaknya orang maka kebahagiaan akan menghampiri Anda. Satu Karya untuk Indonesia!

Achmad Solichin
Twitter: @achmatim

Posted by on February 7, 2013.

Tags: , , , , ,

Categories: MySQL, PHP

30 Responses

  1. Terima Kasih artikelnya..
    Sangat membantu sekali bagi saya….

    Salam,

    by Roch Aksiadi on Feb 7, 2013 at 13:05

  2. Terima telah berbagi ilmu semoga sukses selalu

    by Junaidi on Feb 11, 2013 at 04:32

  3. trimakasih
    bermanfaat pak

    by iskandar romadan on Feb 12, 2013 at 23:58

  4. terima kasih mas…sangat membantu sekali artikel
    sering sering aja mas update artikelnya tentang php lagi belajar ni

    by famtasi on Mar 19, 2013 at 14:04

  5. pak,,saya mau nanya. gimana caranya eksport gambar ke excel dengan php. saya pakai tapi kok gak bisa ya??? saya gunakan
    header(“Content-type: application/vnd-ms-excel”);
    header(“Content-Disposition: attachment; filename=”.$namaFile.””);
    untuk eksport ke excel…terimakasih

    by wisnu on Mar 24, 2013 at 18:33

  6. Terimakasih tutuorialnya, tetapi klo Ingin membuat multishet gmana??Kl Memakai PhpExcel Terlalu besar

    by Santo on Mar 30, 2013 at 12:13

  7. Terima kasih mas, semoga bisa berkembang lagi

    by dedy P on Apr 8, 2013 at 12:12

  8. mantapppp, ijin download tutorialnya ya . . . . . . . .
    mas, mau tanya caranya eksport data MySQL ke TXT dengan php, gimana ya?

    by alung on May 21, 2013 at 03:05

  9. Silahkan. TXT-nya seperti apa dulu? formatnya bagaimana?

    by Achmad Solichin on May 21, 2013 at 06:05

  10. tutorialnya bermanfaat banget Pak Achmad. Makasih…………
    saya mau tanya … kalau menampilkan data dari database mysql berdasarkan bulan sekarang gimana ?………………….. terimakasih

    by Lutvian M on Jul 14, 2013 at 15:02

  11. Pak saya mau tanya kalo export php tapi databasenya oracle gimana pa,,,
    saya coba tk jadi jadi,,,, error terus

    by Ayyas on Jul 18, 2013 at 13:43

  12. Mau Tanya, kalau tipe datanya tanggal bagaimana ? soalnya kalau pake writelabel jadi text dan ga bisa diedit otomatis jadi tnggl di excelnya.
    Terimakasih

    by Anjas on Jul 19, 2013 at 09:28

  13. kalo di tabelnya ada field untuk menyimpan bulan, ya tinggal pake GROUP BY field tersebut.

    by Achmad Solichin on Jul 20, 2013 at 01:20

  14. apapun databasenya, secara umum cuman beda koneksi sama fungsi untuk handle query.

    by Achmad Solichin on Jul 20, 2013 at 01:23

  15. Iya, contoh di tutorial tersebut menggunakan library yg sangat sederhana, jadi ga menyediakan fungsi khusus untuk date. Kalo mau lebih lengkap, gunakan library phpexcel atau lainnya (baca di bagian akhir tutorial).

    by Achmad Solichin on Jul 20, 2013 at 01:28

  16. Mau nanya mas, gimana kalau kita melakukan inputan, data siswa yang mau d print ke excel berdasarkan jurusan, sedangkan di tabel database lebih dari satu jurusan

    by ferdyansyah on Jul 24, 2013 at 13:28

  17. ya tinggal tambahkan filter jurusan di form, lalu query-nya difilter berdasarkan pilihan jurusan tersebut.

    by Achmad Solichin on Jul 28, 2013 at 05:50

  18. Terima kasih pa, tutorialnya sangat membantu saya.. Alhamdulillah..

    by fauzan on Aug 4, 2013 at 14:30

  19. Salam,
    Artikel yang menarik pak.
    Saya masih bingung, jika field nim, nama, dan alamat ditambah 1 field lagi yaitu foto.
    Bagaimana cara menampilkan di excel ya pak? Agar gambar (foto) tersebut bisa tampil di dalam cell. Misalnya pada cell D1, D2, D3 dst.
    Mohon pencerahannya.
    Terima kasih.

    by Afif on Sep 28, 2013 at 22:36

  20. Mungkin bisa gunakan library phpexcel, coba cek postingan ini https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/08-Recipes.md#add-a-drawing-to-a-worksheet

    by Achmad Solichin on Oct 2, 2013 at 23:43

  21. Dari contoh yang bapak berikan saya dapat memahami dengan jelas, pembahasannya juga tidak terlalu berbelit-belit. Jika boleh saya bertanya, bagaimana caranya atau contoh source untuk menulis sebuah label/isian berupa text ataupun angka kedalam file excel yang sudah ada.

    Terima kasih pak

    by budi on Jan 8, 2014 at 04:26

  22. assalamualaikum.. wr wb
    salam kenal pak achmad nama saya iman membaca articel yang menarik mohon kiranya dapat membantu saya baru pertama mau belajar php
    tapi bingung harus mulai dari mana
    mohon bimbingannya makasih banyak

    by iman on Mar 19, 2014 at 10:08

  23. om, tolong diperiksa kesalahannya dimana ya, pada saat menampilkan ( productitem_ItemName LIKE ‘2317%’ ) gambar tampil, tapi saat mau menampilkan ( productitem_ItemName LIKE ‘2316%’ ). kenapa tidak mau keluar cuma yang 2317?

    http://pastebin.com/sUq7870z

    Kalau bisa YM saya om di andreas_bistok@yahoo.co.id

    Tolong dijawab gan urgent..Makasih sebelumnya…

    by andreas on Mar 25, 2014 at 10:13

  24. besar kemungkinan itu terkait data di tabelnya, coba dieksekusi perintah sql-nya langsung ke DB.

    by Achmad Solichin on Mar 26, 2014 at 23:54

  25. Mlm pak,
    saya download : http://achmatim.net/download/48/ ; saya coba run namun menemukan kendala, array coloums Yang muncul di document EXCELnya hanya 12 coloums. Sedangkan saya sudah memasukan 14 coloums. apakah ada solusinya? Trims

    by Bonifacius Wiedyogo Nivem on Apr 8, 2014 at 01:01

  26. mungkin perintah sql-nya yang tidak di-SELECT semua kolomnya kali. coba cek kembali.

    by Achmad Solichin on Apr 8, 2014 at 04:43

  27. Tutorial bapak sungguh sangat berguna untuk saya, terimakasih sebelumnya. Tetapi saya mengalami kesulitan untuk membuat total pada akhir baris setiap laporan. Apakah bapak bisa membantu step-by stepnya? saya sudah mencoba dengan membuat query yang khusus menjumlahkan total, kemudian dipanggil hasil dari query tersebut, tapi tetap hasil file excel nya :’The file corupt…’ jika bapak berkenan,mohon bantuannya bapak. Terimakasih

    by Wahyu Handoko on May 20, 2014 at 23:58

  28. terimakasih tentang artikel ini sangat bermanfaat bagi saya

    by Nur Fajri on Oct 5, 2014 at 12:26

  29. terimakasih atas artukelnya sangat membantu saya

    by Nur Fajri on Oct 5, 2014 at 12:28

  30. Ada yang lebih mudah untuk melakukan proses cetak data atau laporan, yaitu mencetak data langsung dari browser. Silahkan Anda berkunjung ke alamat http://www.purbadian.com

    by Yenda Purbadian on Oct 22, 2014 at 16:37

Leave a Reply

 

« | »




Recent Posts


Pages