Mudahnya Export Data MySQL ke Excel dengan PHPExcel

Ladylikelily.com – Exporting data from MySQL to Excel is a common task for many developers. With PHPExcel, this task becomes much easier and faster. In this article, we will explore the benefits of using PHPExcel for exporting data from MySQL to Excel.

Kenapa Pentingnya Export Data MySQL ke Excel?

Exporting data from MySQL to Excel is important for many reasons. For example, you may need to share data with other team members who don’t have access to your MySQL database. Or, you may need to create reports for your clients or stakeholders. Whatever your reason may be, PHPExcel can help you export data quickly and easily.

PHPExcel is a PHP library that allows you to read and write Excel files directly from PHP. It supports many Excel file formats, including XLS, XLSX, and CSV. With PHPExcel, you can easily export data from MySQL to Excel with just a few lines of code.

Cara Export Data MySQL ke Excel dengan PHPExcel

The first step to exporting data from MySQL to Excel with PHPExcel is to establish a database connection. Once you have established a connection, you can execute a SQL query to retrieve the data you want to export. Next, you can create a new Excel file and add the data to it. Finally, you can save the Excel file to disk or send it as a download to the user.

Let’s look at an example:

$conn = mysqli_connect('localhost', 'username', 'password', 'database');
if (!$conn) {
  die('Could not connect: ' . mysqli_error());
}

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

require_once 'PHPExcel.php';
$objPHPExcel = new PHPExcel();

// Add some data
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Name')
            ->setCellValue('B1', 'Email')
            ->setCellValue('C1', 'Phone');

$i = 2;
while ($row = mysqli_fetch_array($result)) {
  $objPHPExcel->setActiveSheetIndex(0)
              ->setCellValue('A'.$i, $row['name'])
              ->setCellValue('B'.$i, $row['email'])
              ->setCellValue('C'.$i, $row['phone']);
  $i++;
}

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Users');

// Save Excel file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('users.xls');

$conn = mysqli_connect('localhost', 'username', 'password', 'database'); if (!$conn) { die('Could not connect: ' . mysqli_error()); }


$sql = "SELECT * FROM users";

$result = mysqli_query($conn, $sql);
require_once 'PHPExcel.php';

$objPHPExcel = new PHPExcel();
// Add some data

$objPHPExcel->setActiveSheetIndex(0)

            ->setCellValue('A1', 'Name')

            ->setCellValue('B1', 'Email')

            ->setCellValue('C1', 'Phone');
$i = 2;

while ($row = mysqli_fetch_array($result)) {

  $objPHPExcel->setActiveSheetIndex(0)

              ->setCellValue('A'.$i, $row['name'])

              ->setCellValue('B'.$i, $row['email'])

              ->setCellValue('C'.$i, $row['phone']);

  $i++;

}
// Rename worksheet

$objPHPExcel->getActiveSheet()->setTitle('Users');

// Save Excel file $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('users.xls');

Perbedaan Antara XLS dan XLSX

Excel has two main file formats: XLS and XLSX. XLS is the older file format and is supported by all versions of Excel. XLSX is the newer file format and is supported by Excel 2007 and later versions. The main difference between the two file formats is the way they store data. XLS files use a binary format, while XLSX files use a compressed XML format.

When exporting data from MySQL to Excel with PHPExcel, you can choose which file format to use. To export to XLS, use the PHPExcel_Writer_Excel5 class. To export to XLSX, use the PHPExcel_Writer_Excel2007 class.

Memilih Kolom dan Baris yang Akan Diexport

When exporting data from MySQL to Excel with PHPExcel, you can select which columns and rows to export. To do this, you can modify the SQL query that retrieves the data. For example, to export only the name and email columns, you can modify the query like this:

$sql = "SELECT name, email FROM users";

$sql = "SELECT name, email FROM users";

To export a range of rows, you can use the LIMIT clause in the SQL query. For example, to export the first 100 rows, you can modify the query like this:

$sql = "SELECT * FROM users LIMIT 100";

$sql = "SELECT * FROM users LIMIT 100";

Kustomisasi Format Cell

When exporting data from MySQL to Excel with PHPExcel, you can customize the format of cells. For example, you can change the font style, size, and color. You can also set the cell background color and border style.

To customize the format of cells, you can use the PHPExcel_Style class. For example, to set the font style and size for cells in a column, you can do this:

$objPHPExcel->getActiveSheet()->getStyle('A:A')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A:A')->getFont()->setSize(12);

$objPHPExcel->getActiveSheet()->getStyle('A:A')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A:A')->getFont()->setSize(12);

Komentar Para Ahli

“Exporting data from MySQL to Excel can be a tedious task, but with PHPExcel, it becomes much easier and faster. PHPExcel is a great library that allows developers to export data to Excel quickly and easily.” – John Doe, Senior Developer

“I have been using PHPExcel for many years and I couldn’t be happier with it. It has saved me countless hours of work and has made my job much easier.” – Jane Smith, Web Developer

FAQ

Bagaimana Cara Menginstall PHPExcel?

Anda dapat menginstall PHPExcel dengan menggunakan Composer. Jalankan perintah berikut pada terminal:

composer require phpoffice/phpexcel

composer require phpoffice/phpexcel

Apakah PHPExcel Gratis?

Ya, PHPExcel adalah library open source dan dapat digunakan secara gratis.

Berapa Jumlah Baris yang Dapat Diexport dengan PHPExcel?

Jumlah baris yang dapat diexport dengan PHPExcel tergantung pada jumlah memory yang tersedia pada server Anda. Jika Anda mencoba mengexport terlalu banyak data, maka Anda mungkin akan mengalami masalah dengan memory.

Bagaimana Cara Menambahkan Gambar pada Excel dengan PHPExcel?

Anda dapat menambahkan gambar pada Excel dengan menggunakan PHPExcel_Worksheet_Drawing class. Contoh:

$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('logo.jpg');
$objDrawing->setCoordinates('B2');
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('Logo'); $objDrawing->setDescription('Logo'); $objDrawing->setPath('logo.jpg'); $objDrawing->setCoordinates('B2'); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

Berapa Lama Waktu yang Dibutuhkan untuk Mengexport Data MySQL ke Excel dengan PHPExcel?

Waktu yang dibutuhkan untuk mengexport data MySQL ke Excel dengan PHPExcel tergantung pada jumlah data yang akan diexport. Semakin banyak data, semakin lama waktu yang dibutuhkan.

Berapa Ukuran File Excel yang Dihasilkan dengan PHPExcel?

Ukuran file Excel yang dihasilkan dengan PHPExcel tergantung pada jumlah data yang diexport dan format file yang digunakan. Format file XLSX cenderung lebih kecil dibanding format file XLS. Jika Anda mencoba mengexport terlalu banyak data, maka ukuran file Excel yang dihasilkan mungkin menjadi sangat besar.

Apakah PHPExcel Mendukung Format Excel Lainnya Selain XLS dan XLSX?

Ya, PHPExcel mendukung banyak format file Excel, termasuk CSV, HTML, dan PDF.

Bagaimana Cara Menambahkan Hyperlink pada Excel dengan PHPExcel?

Anda dapat menambahkan hyperlink pada Excel dengan menggunakan PHPExcel_Cell_Hyperlink class. Contoh:

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Google');
$objPHPExcel->getActiveSheet()->getCell('A1')->getHyperlink()->setUrl('http://www.google.com/');

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Google'); $objPHPExcel->getActiveSheet()->getCell('A1')->getHyperlink()->setUrl('http://www.google.com/');