Ekspor data dari database MySQL ke dalam format Excel merupakan tindakan penting yang dilakukan oleh banyak pengembang web, terutama untuk menyimpan data dalam format yang mudah dipelajari. Karena Excel adalah salah satu program spreadsheet paling populer, ukuran file yang dihasilkan juga relatif kecil, sehingga sangat bagus untuk digunakan dalam pengembangan web. Artikel ini akan membahas beberapa cara untuk mengimpor dan mengekspor data ke Excel dalam database MySQL menggunakan plugin PHPExcel dan PHPOffice pada PHP. Hal ini dirancang untuk membantu pengembang web dalam menjalankan tugas-tugas ini dengan mudah dan efisien.
Plugin PHPExcel
PHPExcel adalah plugin yang dibangun di atas PHP untuk membantu pengembang web dalam mengimpor dan mengekspor data ke Excel dengan mudah. Fitur-fiturnya meliputi berbagai macam primitif untuk membuat file Excel dan mengisi data mereka dengan format yang berbeda, seperti format angka, format tanggal, format waktu, dan banyak lagi. Dalam artikel ini, kami akan menggunakan plugin PHPOffice yang merupakan fork dari PHPExcel dan termasuk dalam proyek Office PHP pertama yang mencakup semua jenis dokumen Office, seperti Word, Excel, PowerPoint, dan OpenDocument, bernama PhpSpreadsheet.
Cara Mengimpor Data dari Excel ke Database MySQL
1. Mengimpor data menggunakan pustaka PHPExcel (Sudah tidak digunakan)
Plugin PHPexcel sekarang sudah tidak digunakan lagi, namun saya tetap memberikan informasinya sebagai referensi. Cara mengimpor data dari Excel ke dalam database MySQL menggunakan PHPExcel relatif mudah. Pertama-tama, kita perlu memuat pustaka PHPExcel sesuai dengan spesifikasi di file Excel yang akan diimpor. Penjelasan lebih lanjut tentang struktur dan format dokumen Excel dapat ditemukan di situs web Microsoft Office. Setelah memuat pustaka PHPExcel, kita harus memuat file Excel dan membacanya untuk membaca data yang disimpan di dalamnya. Untuk melakukan ini, kita dapat menggunakan fungsi-fungsi yang tersedia dalam pustaka.
“`php
load(“data.xlsx”);
//choose sheet with data
$worksheet = $objPHPExcel->getActiveSheet();
//last active row
$lastrow = $worksheet->getHighestRow();
$lastcol = $worksheet->getHighestColumn();
//get data
for($i=2; $i<=$lastrow; $i++)
//do something with data
?>
“`
2. Mengimpor data menggunakan pustaka PHPExcel (Sudah tidak digunakan)
Plugin PHPexcel sekarang sudah tidak digunakan lagi, namun saya tetap memberikan informasinya sebagai referensi. Cara mengekspor data dari MySQL ke dalam file Excel menggunakan PHPExcel relatif mudah. Untuk melakukan ini, kita harus menyiapkan sebuah workbook Excel dan worksheet yang akan berisi data yang dihasilkan oleh kueri SQL. Kemudian, kita dapat menggunakan pustaka ini untuk membaca data dari database dan menulisnya ke dalam file Excel.
“`php
setActiveSheetIndex(0);
$worksheet = $objPHPExcel->getActiveSheet();
//define data layout
$data = array(‘col1’, ‘col2’, ‘col3’);
$row = 1;
$colum = ‘A’;
//write row
foreach ($data as $value)
$worksheet->setCellValue($colum.$row,$value);
$colum++;
$row++;
//take data
while($cek_get=mysql_fetch_assoc($query))
$colum= ‘A’;
foreach($cek_get as $value)
$worksheet->setCellValue($colum.$row, $value);
$colum++;
$row ++;
//make excel file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007’);
$objWriter->save(‘data.xlsx’);
//close database connection
mysql_close($conn);
?>
“`
Cara Mengimpor Data dari Excel ke Database MySQL Menggunakan Plugin PHPOffice
Plugin PHPOffice adalah fork dari plugin PHPExcel dan melakukan migrasi dari plugin sebelumnya dengan memanfaatkan kode yang sama. PHPOffice menggabungkan plugin PHPExcel dengan pustaka lain untuk membentuk rangkaian pilihan penanganan dokumen Office paling lengkap untuk PHP yang disebut PhpSpreadsheet. PhpSpreadsheet berisi pustaka penanganan dan kelas utama untuk membaca dan menulis dokumen Excel dengan format yang berbeda. Dibandingkan dengan PHPExcel, PHPOffice memberi lebih banyak fitur, format, dan dukungan yang dapat digunakan untuk memperluas kemampuan pengembangan pengguna dalam mengimpor dan mengekspor data ke Excel.
1. Langkah Pertama
Untuk mengimpor data dari file Excel menggunakan plugin PHPOffice, kita perlu memuat pustaka PhpSpreadsheet ke dalam proyek PHP. PhpSpreadsheet adalah salah satu pilihan terbaik untuk membaca dan menulis file Excel dalam PHP. Seperti PHPExcel, Phpspreadsheet menyediakan fitur dan format pembuatan dokumen spreadsheet. Selain itu, Phpspreadsheet juga mendukung pembacaan dan penulisan dokumen lain, seperti PowerPoint, Word, dan OpenDocument.
2. Langkah kedua
Untuk mengimpor data dari Excel ke dalam database MySQL menggunakan PHPOffice, pertama-tama kita akan membuat formulir untuk mengunggah file Excel dan membaca dokumennya. Setelah berhasil mengunggah file Excel, kita dapat mengambil nilai-nilai yang disimpan di dalamnya dengan menggunakan fungsi dan metode PHPExcel/PhpSpreadsheet.
“`php
else
$filename=$_FILES[‘file’][‘tmp_name’];
$objPHPExcel = IOFactory::load($filename);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g ‘F’
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) – 64;
for ($row = 2; $row <= $highestRow; ++ $row)
$get_rows = $worksheet->getCellByColumnAndRow ( 0, $row );$data1 = mysqli_real_escape_string($conn,$get_rows->getValue());
$get_rows = $worksheet->getCellByColumnAndRow ( 1, $row );$data2 = mysqli_real_escape_string($conn,$get_rows->getValue());
$get_rows = $worksheet->getCellByColumnAndRow ( 2, $row );$data3 = mysqli_real_escape_string($conn,$get_rows->getValue());
//INSERT DATA INTO MYSQL TABLE respective to the fields
if (!empty($data1))
$query = “INSERT INTO `tb1` (`field1`, `field2`, `field3`) VALUES (‘” . $data1 . “‘, ‘” . $data2 . “‘, ‘” . $data3 . “‘)”;
$result = mysqli_query($conn, $query);
//REDIRECT TO SAME PHP PAGE
header(‘Location: excel2db.php?success=1’);
?>
“`
Cara Mengekspor Data dari Database MySQL ke dalam File Excel Menggunakan Plugin PHPOffice
Sama seperti mengimpor data dari file Excel ke dalam database MySQL menggunakan PHPOffice, untuk mengekspor data dari database MySQL ke dalam file Excel, kita perlu memuat pustaka PhpSpreadsheet ke dalam proyek PHP. Setelah itu kita buat query yang sesuai dengan struktur tabel yang akan diambil datanya dan membuat file Excel.
“`php
setActiveSheetIndex(0);
// Set column headers
$worksheet->setCellValue(‘A1’, ‘field1’);
$worksheet->setCellValue(‘B1’, ‘field2’);
$worksheet->setCellValue(‘C1’, ‘field3’);
// Fetch data from database table
$query = “SELECT * FROM `tb1` “;
$result = mysqli_query(mysqli_connect($hostname, $username, $password, $database), $query);
// Start at row 2
$i = 2;
while ($row = mysqli_fetch_array($result))
$worksheet->setCellValue(‘A’.$i, $row[‘field1’]);
$worksheet->setCellValue(‘B’.$i, $row[‘field2’]);
$worksheet->setCellValue(‘C’.$i, $row[‘field3’]);
++$i;
// Set content type and send header information
header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
header(‘Content-Disposition: attachment;filename=”demo.xlsx”‘);
header(‘Cache-Control: max-age=0’);
// Create Excel file
$writer = new Xlsx($spreadsheet);
$writer->save(‘php://output’);
?>
“`
FAQ
1. Apa bedanya antara PHPExcel dan PHPOffice?
PHPExcel dan PHPOffice adalah dua plugin PHP yang digunakan untuk membantu pengembang dalam mengimpor dan mengekspor data ke dalam format Excel. PHPOffice sebenarnya adalah fork dari PHPExcel dan termasuk dalam proyek Office PHP pertama yang mencakup semua jenis dokumen Office, seperti Word, Excel, PowerPoint, dan OpenDocument, bernama PhpSpreadsheet. PHPOffice menggabungkan plugin PHPExcel dengan pustaka lain untuk membentuk rangkaian pilihan penanganan dokumen Office paling lengkap untuk PHP yang disebut PhpSpreadsheet.
2. Jika saya mengubah struktur tabel MySQL, apakah saya perlu mengubah kode PHP yang digunakan untuk mengimpor atau mengekspor data?
Ya, jika struktur tabel MySQL berubah, maka kode PHP yang digunakan untuk mengimpor atau mengekspor data juga harus diubah agar sesuai. Penggunaan nama kolom, ukuran kolom, dan tipe data juga harus disesuaikan agar sesuai dengan perubahan struktur tabel.
Conclusion
Plugin PHPExcel dan PHPOffice sangat membantu dalam mengimpor dan mengekspor data ke dalam Excel dalam pengembangan web. Dalam artikel ini, kami telah membahas cara mengimpor dan mengekspor data di PHP menggunakan PHPExcel dan PHPOffice, berbeda dengan sebelumnya. Namun, sekarang PHPExcel tidak lagi digunakan dan digantikan dengan PHPOffice, dengan demikian PHPOffice adalah cara terbaik untuk mengimpor dan mengekspor data ke dalam format Excel. Demikian pula, pengguna harus membuat kode PHP yang sesuai dengan struktur tabel MySQL untuk mengimpor dan mengekspor data. Semoga artikel ini bermanfaat dan membantu Anda membaca dan menulis data dari atau ke dalam file Excel.
