Cara Mudah Export Data ke Excel dengan PHP
Membuat aplikasi web yang berhubungan dengan data, sering memerlukan fungsi untuk dapat mengekspor data yang ada di dalam database, dalam bentuk file Excel. Hal ini sangat sensitif untuk melindungi privasi data. Kemudian bagaimana cara mudah export data ke Excel dengan PHP?
Cara Menggunakan PHPExcel
1. Persiapan Excel
Langkah awal adalah membuat template Excel terlebih dahulu. Kita buat file excel.xls kemudian buat tabel di Worksheet 1 seperti gambar dibawah ini.
Tabel diatas adalah salah satu data dummy dengan beberapa kolom yang kita perlukan untuk di-export. Pada umumnya data dari database akan kita tampilkan dengan menggunakan tabel html dari data yang dihasilkan oleh query SELECT. Maka kita akan membuat script terlebih dahulu untuk menampilkan data yang dihasilkan oleh query SELECT yang kita buat.
2. Mengambil Data dari Database
Jalankan script PHP berikut pada halaman yang memiliki koneksi database.
<?php
require_once 'koneksi.php'; // Koneksi ke database
$sql = "SELECT * FROM mahasiswa";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0)
while($row = mysqli_fetch_assoc($result))
echo "<tr><td>".$row["nim"]."</td><td>".$row["nama"]."</td></tr>";
else
echo "0 results";
mysqli_close($conn);
?>
Sekarang kita akan merubah script tersebut menjadi script yang dapat menghasilkan export data ke dalam bentuk Excel. Kita menggunakan library PHPExcel untuk melakukan export.
3. Menggunakan PHPExcel Library
Sebelum menggunakan PHPExcel, pastikan anda telah memiliki file library tersebut. Jika belum, anda bisa mendownloadnya di website resminya atau menggunakan composer juga.
Setelah itu, kita tambahkan beberapa script berikut di awal file PHP:
<?php
require_once 'koneksi.php';
require_once 'Classes/PHPExcel.php'; // Include PHPExcel library
$objPHPExcel = new PHPExcel(); // Inisialisasi objek PHPExcel
$objPHPExcel->setActiveSheetIndex(0); // Set active sheet index menjadi 0
// Buat tabel berisi judul kolom
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'NIM');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Nama');
// Ambil data dari database dan isi tabel
$sql = "SELECT * FROM mahasiswa";
$result = mysqli_query($conn, $sql);
$i = 2;
while($row = mysqli_fetch_assoc($result))
$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $row["nim"]);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $row["nama"]);
$i++;
// Set judul file excel dan header
$file_name = 'Daftar Mahasiswa.xls';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$file_name.'"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); // Proses file excel dan download
exit;
?>
Dalam script tersebut, kita melakukan beberapa hal dibawah ini:
- Membuat objek PHPExcel
- Mengambil aktivitas sheet index yang ada di dalam file excel
- Membuat tabel berisi judul kolom (NIM, Nama)
- Mengambil data dari database dan mengisi tabel
- Mengatur header excel seperti judul file dan tipe file yang akan dienkapsulasi
- Proses file excel dan download
4. Mengecek Data
Selanjutnya kita cek hasil data yang telah di-export ke dalam Excel, berikut contohnya:
Kita juga dapat melakukan export data dengan library PhpSpreadsheet.
Cara Menggunakan PhpSpreadsheet
1. Install PhpSpreadsheet
Gunakan composer untuk install PhpSpreadsheet dengan menjalankan perintah di terminal:
composer require phpoffice/phpspreadsheet
2. Mengambil Data Dari Database
Setelah itu, kita buat script untuk mengambil data dari database.
<?php
require_once 'koneksi.php';
$sql = "SELECT * FROM mahasiswa";
$result = mysqli_query($conn, $sql);
$data = array();
while($row = mysqli_fetch_assoc($result))
$data[] = $row;
mysqli_close($conn);
return $data;
?>
3. Export Data ke Excel dengan PhpSpreadsheet
Setelah mempersiapan data dari database, selanjutnya kita akan membuat script eksport data ke file excel. Simak code berikut:
<?php
require_once 'vendor/autoload.php';
require_once 'koneksi.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet(); // Create new Spreadsheet instance
$data = get_data_mahasiswa(); // Ambil data dari database
$worksheet = $spreadsheet->getActiveSheet(); // Get active sheet
$worksheet->setTitle('Daftar Mahasiswa'); // Set worksheet title
// Make header
$worksheet->setCellValue('A1', 'NIM');
$worksheet->setCellValue('B1', 'Nama');
$baris = 2;
foreach($data as $d)
$worksheet->setCellValue('A'.$baris, $d['nim']);
$worksheet->setCellValue('B'.$baris, $d['nama']);
$baris++;
$writer = new Xlsx($spreadsheet); // Create new Xlsx instance
$writer->save('php://output'); // Proses file excel dan download
exit;
?>
Kita berhasil melakukan export data ke dalam bentuk file Excel. Selanjutnya, bagaimana cara mengimport data dari Excel?
Cara Import Data dari Excel ke MySQL dengan PhpSpreadsheet
Di dalam PhpSpreadsheet, terdapat class untuk membaca isi file excel yang merupakan PhpSpreadsheet\Reader\Xlsx. Dengan menggunakan class tersebut, kita dapat membaca data dari file Excel kemudian kita insert ke dalam database MySQL.
1. Menyiapkan Database MySQL dan File Excel
Untuk menjalankan script import data dari file excel ke database MySQL, kita harus menyiapkan database MySQL dengan tabel yang akan kita gunakan simpan data. Selain itu, kita juga harus menyiapkan file excel yang akan kita gunakan untuk diimport kedalam database MySQL.
2. Import Data dari Excel
Langkah pertama yang harus kita lakukan adalah mengambil data dari file excel menggunakan PhpSpreadsheet. Kita akan menggunakan beberapa script seperti dibawah ini:
<?php
require_once "vendor/autoload.php";
require_once "koneksi.php";
use PhpOffice\PhpSpreadsheet\IOFactory;
$inputFileType = 'Xlsx'; // Tipe input file
$inputFileName = './sample.xlsx'; // Lokasi file excel yang akan dibaca
// Tampilkan error jika gagal membaca file
if (!file_exists($inputFileName))
exit("file tidak ada");
// Load file excel
$spreadsheet = IOFactory::load($inputFileName);
// Get active sheet
$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // Get highest row
$highestColumn = $worksheet->getHighestColumn(); // Get highest column
$data = array();
// Looping through rows
for ($row = 2; $row <= $highestRow; $row++)
$rowData = array(
'nim' => $worksheet->getCellByColumnAndRow(1, $row)->getValue(),
'nama' => $worksheet->getCellByColumnAndRow(2, $row)->getValue(),
);
$data[] = $rowData;
mysqli_query($conn, "TRUNCATE mahasiswa"); // Clear data dari tabel
foreach($data as $d)
$insert = "INSERT INTO mahasiswa(nim, nama) VALUES('".$d['nim']."', '".$d['nama']."')";
mysqli_query($conn, $insert);
mysqli_close($conn);
?>
Setelah script diatas dieksekusi, data dari file excel akan dimasukkan ke dalam tabel mahasiswa dengan menggunakan script INSERT yang terdapat pada $insert.
3. Mengetahui Hasil Import Data
Untuk memastikan data telah diimport ke database, kita dapat menggunakan script yang sama seperti pada bagian sebelumnya untuk menampilkan data yang tersimpan di database mahasiswa.
FAQ (Frequently Asked Questions)
1. Apa itu PhpSpreadsheet?
PhpSpreadsheet merupakan sebuah library PHP yang digunakan untuk memproses file Excel dari PHP. Dengan PhpSpreadsheet, kita bisa membaca, mengubah, dan menuliskan file Excel dengan mudah tanpa menggunakan program Microsoft Excel.
2. Apa keuntungan menggunakan PhpSpreadsheet?
Dengan menggunakan PhpSpreadsheet kita tidak perlu memasukkan data ke dalam Excel secara manual, ini akan mempercepat pekerjaan kita, meminimalkan kesalahan manusia, meningkatkan keakuratan data, dan membuat hasil yang konsisten.
3. Apakah PhpSpreadsheet memiliki fitur untuk mengekspor ke format selain Excel?
Ya, PhpSpreadsheet juga memiliki kemampuan untuk mengekspor file ke dalam format seperti CSV, PDF, dan XML.
4. Apakah PhpSpreadsheet sulit digunakan?
Awal penggunaan library PhpSpreadsheet mungkin sedikit membingungkan, tetapi dengan sedikit latihan, penggunaan library ini akan sangat mudah dan tentunya sangat bermanfaat bagi pengembang aplikasi PHP.
5. Bisakah PhpSpreadsheet diintegrasikan dengan framework PHP?
Tentu saja bisa. Kita hanya perlu menyertakan file autoload dan memuat Panduan penggunaan librarynya sebelum bisa digunakan.
Video Tutorial
Berikut ini adalah video tutorial penjelasan penggunaan PHPExcel dan PhpSpreadsheet.
