Pendahuluan

Ekspor data ke Excel adalah hal yang umum dilakukan dalam pengolahan data. Beberapa contoh penggunaan ekspor data ke Excel adalah untuk memperlihatkan laporan keuangan, data pelanggan, atau data penjualan pada perusahaan. Saat ini, banyak sistem dan aplikasi dapat menghasilkan output file Excel (XLS atau XLSX), namun terkadang kita perlu menghasilkan file Excel dari data yang disimpan di dalam database kita.

Dalam artikel ini, kita akan membahas langkah-langkah untuk menghasilkan file Excel dari data yang disimpan di dalam MySQL database menggunakan script PHP.

Langkah 1: Mempersiapkan Data

Sebelum kita dapat membuat script PHP untuk menghasilkan file Excel, pertama-tama harus ada data yang hendak diekspor. Dalam contoh ini, kita akan menggunakan tabel “customers” dalam database “shop”.

Tabel “customers”

Tabel “sales”

Kita juga akan menggunakan tabel “sales” yang ingin kita ekspor ke Excel.

Dalam contoh ini, kita akan menghasilkan file Excel yang berisi isi tabel “sales”.

Langkah 2: Membuat Script PHP

Setelah kita mempersiapkan data, selanjutnya kita perlu membuat script PHP untuk menghasilkan file Excel. Untuk melakukannya, kita akan menggunakan library PHPExcel.

Instalasi PHPExcel

Langkah pertama adalah menginstal library PHPExcel. Kita dapat menggunakan composer, atau mengunduh versi terbaru dari github PHPExcel dan mengekstraknya ke dalam folder proyek kita. Pada contoh ini, kita menyimpan folder PHPExcel di dalam folder “lib” dan menyertakan kodenya menggunakan fungsi require_once:

<?php
require_once 'lib/PHPExcel.php';
...
?>

Membuat Template Excel

Tahap selanjutnya adalah membuat template Excel yang akan kita populasi dengan data dari database. Kita dapat membuat template ini menggunakan aplikasi Excel, lalu menyimpannya sebagai “.xlsx” atau “.xls” file. Pada contoh ini, kita menyimpan template ke dalam file “sales_template.xlsx”.

Untuk memulai proyek kita, kita perlu menambahkan beberapa kode awal:

<?php
require_once 'lib/PHPExcel.php';
 
$excel = new PHPExcel();
 
// Pengaturan nama-nama header pada file excel
$excel->getProperties()->setCreator('Nama Anda')
    				 ->setLastModifiedBy('Nama Anda')
    				 ->setTitle("Data Sales")
    				 ->setSubject("Sales Data")
    				 ->setDescription("Laporan Data Sales")
    				 ->setKeywords("Penggunaan Excel PHP")
    				 ->setCategory("Data Sales");
 
$excel->setActiveSheetIndex(0);
 
// Pengisian Header pada Excel
$excel->getActiveSheet()->setCellValue('A1', "No");
$excel->getActiveSheet()->setCellValue('B1', "Tanggal");
$excel->getActiveSheet()->setCellValue('C1', "Nama Produk");
$excel->getActiveSheet()->setCellValue('D1', "Jumlah");
...
?>

Dalam kode tersebut, kita menggunakan PHPExcel untuk membuat file excel kosong, memberikan informasi tentang file, mengatur lembar aktif dan mengisi header pada file excel.

Mengambil Data dari MySQL Database

Setelah file excel kosong dibuat, selanjutnya kita perlu mengambil data dari MySQL database. Kita dapat melakukan ini menggunakan kode PHP dan fungsi-fungsi yang telah disediakan oleh PHPExcel.

<?php
require_once 'lib/PHPExcel.php';
 
$excel = new PHPExcel();
 
// Pengaturan nama-nama header pada file excel
$excel->getProperties()->setCreator('Nama Anda')
    				 ->setLastModifiedBy('Nama Anda')
    				 ->setTitle("Data Sales")
    				 ->setSubject("Sales Data")
    				 ->setDescription("Laporan Data Sales")
    				 ->setKeywords("Penggunaan Excel PHP")
    				 ->setCategory("Data Sales");
 
$excel->setActiveSheetIndex(0);
 
// Pengisian Header pada Excel
$excel->getActiveSheet()->setCellValue('A1', "No");
$excel->getActiveSheet()->setCellValue('B1', "Tanggal");
$excel->getActiveSheet()->setCellValue('C1', "Nama Produk");
$excel->getActiveSheet()->setCellValue('D1', "Jumlah");
 
// Pengambilan data dtabase
// Sesuaikan nama kolom pada tabel database dengan kebutuhan Anda. Panggil sejumlaha data yang dibutuhkan.
$no = 1;
$startrow = 2;
$con = new mysqli("localhost", "root", "", "shop");
$sql = "select * from sales";
$query = $con->query($sql);
while ($data = $query->fetch_array()) 
  $excel->getActiveSheet()->setCellValue('A'.$startrow, $no);
  $excel->getActiveSheet()->setCellValueExplicit('B'.$startrow, $data['tanggal'], PHPExcel_Cell_DataType::TYPE_STRING);
  $excel->getActiveSheet()->setCellValueExplicit('C'.$startrow, $data['nama_produk'], PHPExcel_Cell_DataType::TYPE_STRING);
  $excel->getActiveSheet()->setCellValue('D'.$startrow, $data['jumlah']);
  $no++;
  $startrow++;

...
?>

Dalam kode tersebut, kita menggunakan koneksi MySQL untuk mengambil data dari tabel “sales” dan memasukkannya ke lembar kerja file Excel. Kita juga menggunakan fungsi PHPExcel_Cell_DataType untuk memastikan bahwa tipe data yang dihasilkan adalah string.

Men-download File Excel

Setelah file Excel dihasilkan dan data telah dimasukkan, terakhir kita perlu menawarkan file Excel untuk di-download oleh pengguna. Kita dapat melakukan ini dengan menambahkan kode berikut ke script PHP kita:

<?php
require_once 'lib/PHPExcel.php';
 
$excel = new PHPExcel();
 
// Pengaturan nama-nama header pada file excel
$excel->getProperties()->setCreator('Nama Anda')
    				 ->setLastModifiedBy('Nama Anda')
    				 ->setTitle("Data Sales")
    				 ->setSubject("Sales Data")
    				 ->setDescription("Laporan Data Sales")
    				 ->setKeywords("Penggunaan Excel PHP")
    				 ->setCategory("Data Sales");
 
$excel->setActiveSheetIndex(0);
 
// Pengisian Header pada Excel
$excel->getActiveSheet()->setCellValue('A1', "No");
$excel->getActiveSheet()->setCellValue('B1', "Tanggal");
$excel->getActiveSheet()->setCellValue('C1', "Nama Produk");
$excel->getActiveSheet()->setCellValue('D1', "Jumlah");
 
// Pengambilan data dtabase
// Sesuaikan nama kolom pada tabel database dengan kebutuhan Anda. Panggil sejumlaha data yang dibutuhkan.
$no = 1;
$startrow = 2;
$con = new mysqli("localhost", "root", "", "shop");
$sql = "select * from sales";
$query = $con->query($sql);
while ($data = $query->fetch_array()) 
  $excel->getActiveSheet()->setCellValue('A'.$startrow, $no);
  $excel->getActiveSheet()->setCellValueExplicit('B'.$startrow, $data['tanggal'], PHPExcel_Cell_DataType::TYPE_STRING);
  $excel->getActiveSheet()->setCellValueExplicit('C'.$startrow, $data['nama_produk'], PHPExcel_Cell_DataType::TYPE_STRING);
  $excel->getActiveSheet()->setCellValue('D'.$startrow, $data['jumlah']);
  $no++;
  $startrow++;

 
$excel->getActiveSheet()->setTitle("Data Sales");
 
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment;filename=\"data_sales.xlsx\"");
header("Cache-Control: max-age=0");
 
$objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$objWriter->save('php://output');
exit;
?>

Dalam kode tersebut, setelah file Excel dihasilkan, kita menawarkan file Excel untuk di-download oleh pengguna. Kita mengatur header HTTP untuk memberitahu browser bahwa hasil yang dihasilkan adalah file Excel “data_sales.xlsx”.

FAQ

1. Bagaimana cara memodifikasi script PHP ini jika data berasal dari sumber yang berbeda?

Jika data berasal dari sumber yang berbeda, yang perlu Anda lakukan adalah mengganti koneksi database dan query untuk mengambil data. Anda juga dapat mengubah nama file, header, dan direktori output seperti yang diinginkan.

2. Apakah PHPExcel masih didukung dan dikembangkan?

PHPExcel telah dihentikan pengembangannya dan digantikan oleh PHPSpreadsheet. PHPSpreadsheet adalah pengganti PHPExcel yang kompatibel dan lebih canggih.