Pada era digital seperti ini, sangat penting bagi perusahaan untuk memiliki data yang tercatat dalam database. Bahkan, terdapat jenis pekerjaan yang khusus untuk memanajemen database, yaitu seorang database administrator (DBA). Namun, ketika suatu data perlu diatur dan dikelola dengan efisien, tidak jarang perlu dilakukan proses ekspor-impornya. Salah satu format yang sering digunakan adalah Excel.
Bagi Anda yang memiliki kebutuhan serupa, artinya ada data yang perlu diekspor dari database ke file Excel, pada tutorial kali ini kami akan berbagi cara sederhana untuk melakukan ekspor data ke Excel dengan menggunakan PHP dan MySQLi.
## Persiapan
Sebelum melakukan ekspor data ke Excel, Anda perlu menyiapkan beberapa persiapan. Berikut ini adalah persiapan yang harus Anda lakukan:
1. Pastikan telah terinstall web server seperti XAMPP atau WAMP.
2. Pastikan telah terinstall MySQL pada computer.
3. Buat sebuah database pada MySQL.
4. Buat sebuah tabel pada database yang telah dibuat dan masukkan beberapa data.
5. Unduh library phpoffice/phpspreadsheet melalui Composer pada command prompt.
Setelah persiapan di atas telah dilakukan, langkah selanjutnya adalah melakukan koneksi ke MySQL dan mengambil data dari tabel.
## Koneksi ke MySQL
Sebelum mengambil data, Anda perlu membuat sebuah koneksi ke MySQL terlebih dahulu. Pada tutorial ini, Anda bisa menggunakan koneksi MySQLi. Berikut ini adalah contoh kode koneksi ke MySQL:
“`
“`
## Mengambil Data dari Tabel
Setelah melakukan koneksi ke MySQL, langkah selanjutnya adalah mengambil data dari tabel. Pada contoh ini, data akan diambil dari tabel karyawan. Berikut ini adalah contoh kode untuk mengambil data dari tabel:
“`
“`
Pada contoh di atas, fungsi mysqli_query digunakan untuk mengeksekusi query MySQL. Hasil dari query tersebut akan disimpan pada variabel $result.
## Menggunakan PHPExcel
Untuk mengolah data dan mengekspor ke Excel, pada tutorial ini kita akan menggunakan library PHPExcel. Namun, library PHPExcel sudah tidak lagi dikembangkan dan diganti dengan PhpSpreadsheet. Kita akan menggunakan PhpSpreadsheet karena merupakan versi terbaru dari library PHP Excel.
Untuk memulai penggunaan PhpSpreadsheet, pertama-tama Anda perlu menginstall PhpSpreadsheet melalui Composer. Berikut ini adalah cara instalasi PhpSpreadsheet melalui command prompt.
“`
composer require phpoffice/phpspreadsheet
“`
Setelah berhasil melakukan instalasi, library PhpSpreadsheet siap digunakan. Selanjutnya, buat sebuah file php baru untuk mengekspor data ke Excel.
## Membuat File Excel
Pertama-tama, buat sebuah variabel spreadsheet dan buat sebuah worksheet baru. Berikut ini adalah contoh kode untuk membuat file Excel:
“`
getActiveSheet();
?>
“`
Pada contoh di atas, terdapat beberapa hal yang perlu diperhatikan. Pertama, harus mengambil data dari tabel karyawan menggunakan kode yang telah dijelaskan sebelumnya. Selanjutnya, tinggal menggunakan nama variabel dari library PhpSpreadsheet, yaitu Spreadsheet dan membuat sebuah worksheet baru dengan memanggil method getActiveSheet().
## Membuat Header pada File Excel
Setelah membuat file baru, langkah selanjutnya adalah membuat header pada file Excel. Pada header, Anda perlu menyertakan nama kolom yang akan dikeluarkan pada file Excel. Berikut ini adalah contoh kode untuk membuat header pada file Excel:
“`
getActiveSheet();
// Mengatur nama header
$sheet->setCellValue(‘A1’, ‘ID Karyawan’);
$sheet->setCellValue(‘B1’, ‘Nama’);
$sheet->setCellValue(‘C1’, ‘Email’);
$sheet->setCellValue(‘D1’, ‘No HP’);
$sheet->setCellValue(‘E1’, ‘Alamat’);
?>
“`
Pada contoh di atas, nama kolom yang akan dikeluarkan pada file Excel dapat diatur dengan memanggil method setCellValue. Nama kolom di atas adalah ID Karyawan, Nama, Email, No HP, dan Alamat.
## Looping Data dari Tabel
Setelah berhasil membuat header, langkah selanjutnya adalah mengeluarkan data dari tabel melalui loop. Dalam proses loop ini, data dari tabel akan dikirimkan satu per satu ke file Excel. Berikut ini adalah contoh kode untuk loop data dari tabel:
“`
getActiveSheet();
// Mengatur nama header
$sheet->setCellValue(‘A1’, ‘ID Karyawan’);
$sheet->setCellValue(‘B1’, ‘Nama’);
$sheet->setCellValue(‘C1’, ‘Email’);
$sheet->setCellValue(‘D1’, ‘No HP’);
$sheet->setCellValue(‘E1’, ‘Alamat’);
// Meletakkan data pada row berikutnya
$numrow = 2;
while($row = mysqli_fetch_array($result))
$sheet->setCellValue(‘A’.$numrow, $row[‘id_karyawan’]);
$sheet->setCellValue(‘B’.$numrow, $row[‘nama’]);
$sheet->setCellValue(‘C’.$numrow, $row[’email’]);
$sheet->setCellValue(‘D’.$numrow, $row[‘no_hp’]);
$sheet->setCellValue(‘E’.$numrow, $row[‘alamat’]);
$numrow++;
?>
“`
Pada contoh di atas, terdapat sebuah while loop yang akan menjalankan kode untuk memasukkan data pada file Excel secara otomatis. Saat pertama kali dijalankan, variabel $numrow akan diatur ke nilai 2. Variabel tersebut bertujuan untuk mendapatkan row berikutnya pada Excel. Saat looping dijalankan, nilai dari variabel $numrow akan bertambah 1.
## Mengatur Format File Excel
Setelah semua data berhasil dimasukkan ke dalam file Excel, hal yang perlu dilakukan adalah mengatur format tampilan file Excel. Beberapa format yang bisa disetting adalah:
1. Judul pada file Excel
2. Menambahkan perbatasan pada kolom
3. Menambahkan garis bawah pada header pada file Excel
4. Mengatur format tampilan pada angka
Berikut ini adalah contoh kode untuk mengatur format tampilan file Excel:
“`
getActiveSheet();
// Mengatur nama header
$sheet->setCellValue(‘A1’, ‘ID Karyawan’);
$sheet->setCellValue(‘B1’, ‘Nama’);
$sheet->setCellValue(‘C1’, ‘Email’);
$sheet->setCellValue(‘D1’, ‘No HP’);
$sheet->setCellValue(‘E1’, ‘Alamat’);
// Meletakkan data pada row berikutnya
$numrow = 2;
while($row = mysqli_fetch_array($result))
$sheet->setCellValue(‘A’.$numrow, $row[‘id_karyawan’]);
$sheet->setCellValue(‘B’.$numrow, $row[‘nama’]);
$sheet->setCellValue(‘C’.$numrow, $row[’email’]);
$sheet->setCellValue(‘D’.$numrow, $row[‘no_hp’]);
$sheet->setCellValue(‘E’.$numrow, $row[‘alamat’]);
$numrow++;
// Mengatur judul pada file Excel
$sheet->setTitle(‘Data Karyawan’);
// Mengatur ukuran lebar kolom
$sheet->getColumnDimension(‘A’)->setWidth(10);
$sheet->getColumnDimension(‘B’)->setWidth(25);
$sheet->getColumnDimension(‘C’)->setWidth(25);
$sheet->getColumnDimension(‘D’)->setWidth(20);
$sheet->getColumnDimension(‘E’)->setWidth(50);
// Menambahkan border pada kolom
$sheet->getStyle(‘A1:E’.($numrow – 1))->applyFromArray(
[
‘borders’ => [
‘outline’ => [
‘borderStyle’ => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
‘color’ => [‘argb’ => ‘FFFF0000’],
],
],
]
);
// Menambahkan garis bawah pada header pada file Excel
$sheet->getStyle(‘A1:E1’)->applyFromArray(
[
‘font’ => [
‘bold’ => true
],
‘borders’ => [
‘bottom’ => [
‘borderStyle’ => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
‘color’ => [‘argb’ => ‘FFFF0000’],
],
],
]
);
// Mengatur format tampilan pada angka
$sheet->getStyle(‘D2:D’.($numrow – 1))->getNumberFormat()->setFormatCode(‘#0’);
// Set worksheet orientation to landscape
$sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
?>
“`
## Menyimpan File Excel
Setelah semua format tampilan masih sesuai dengan keinginan Anda, langkah terakhir adalah menyimpan file Excel yang sudah dibuat. Berikut ini adalah contoh kode untuk menyimpan file Excel:
“`
getActiveSheet();
// Mengatur nama header
$sheet->setCellValue(‘A1’, ‘ID Karyawan’);
$sheet->setCellValue(‘B1’, ‘Nama’);
$sheet->setCellValue(‘C1’, ‘Email’);
$sheet->setCellValue(‘D1’, ‘No HP’);
$sheet->setCellValue(‘E1’, ‘Alamat’);
// Meletakkan data pada row berikutnya
$numrow = 2;
while($row = mysqli_fetch_array($result))
$sheet->setCellValue(‘A’.$numrow, $row[‘id_karyawan’]);
$sheet->setCellValue(‘B’.$numrow, $row[‘nama’]);
$sheet->setCellValue(‘C’.$numrow, $row[’email’]);
$sheet->setCellValue(‘D’.$numrow, $row[‘no_hp’]);
$sheet->setCellValue(‘E’.$numrow, $row[‘alamat’]);
$numrow++;
// Mengatur judul pada file Excel
$sheet->setTitle(‘Data Karyawan’);
// Mengatur ukuran lebar kolom
$sheet->getColumnDimension(‘A’)->setWidth(10);
$sheet->getColumnDimension(‘B’)->setWidth(25);
$sheet->getColumnDimension(‘C’)->setWidth(25);
$sheet->getColumnDimension(‘D’)->setWidth(20);
$sheet->getColumnDimension(‘E’)->setWidth(50);
// Menambahkan border pada kolom
$sheet->getStyle(‘A1:E’.($numrow – 1))->applyFromArray(
[
‘borders’ => [
‘outline’ => [
‘borderStyle’ => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
‘color’ => [‘argb’ => ‘FFFF0000’],
],
],
]
);
// Menambahkan garis bawah pada header pada file Excel
$sheet->getStyle(‘A1:E1’)->applyFromArray(
[
‘font’ => [
‘bold’ => true
],
‘borders’ => [
‘bottom’ => [
‘borderStyle’ => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
‘color’ => [‘argb’ => ‘FFFF0000’],
],
],
]
);
// Mengatur format tampilan pada angka
$sheet->getStyle(‘D2:D’.($numrow – 1))->getNumberFormat()->setFormatCode(‘#0’);
// Set worksheet orientation to landscape
$sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
// Menyimpan file
$filename = ‘data-karyawan.xlsx’;
header(‘Content-Type: application/vnd.ms-excel’);
header(‘Content-Disposition: attachment;filename=”‘. $filename .'”‘);
header(‘Cache-Control: max-age=0’);
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, ‘Xlsx’);
$writer->save(‘php://output’);
?>
“`
## FAQ
1. Apa itu PHPExcel?
PHPExcel adalah sebuah library PHP yang memungkinkan pengguna untuk membuat dan membaca file Excel. Namun, library PHPExcel sudah tidak lagi dikembangkan dan diganti dengan PhpSpreadsheet.
2. Apa perbedaan antara PhpSpreadsheet dengan library PHPExcel?
PhpSpreadsheet adalah sebuah library PHP yang memungkinkan pengguna untuk membuat dan membaca file Excel. Namun, PhpSpreadsheet merupakan versi terbaru dari library PHPExcel dan banyak dikembangkan oleh developer.
## Video Tutorial
Berikut ini adalah video tutorial yang dapat Anda gunakan sebagai referensi dalam melakukan ekspor data ke Excel dengan menggunakan PHP dan MySQLi:
