CARA EXPORT FILE DARI EXCEL KE PHP

Do you need to export data to Excel using PHP? Do not worry, it is not a complicated process. In this tutorial, I will guide you through the steps to export data to Excel using PHP. We will cover exporting data from the database to an Excel file, and we will also cover exporting an HTML table to Excel.

Exporting Database Data to Excel

The first thing we need to do is connect to our database. For this tutorial, we will be using MySQL. After connecting to the database, we will fetch the data we want to export to Excel and store it in a variable. Here is the code:

// Database connection
$conn = mysqli_connect("localhost", "username", "password", "database_name");

// Fetching data from database
$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);
$data = array();

while ($row = mysqli_fetch_assoc($result)) 
    $data[] = $row;


mysqli_close($conn);

We have now fetched the data from the “users” table and stored it in the $data variable. Now, we need to create an Excel file and write the data to it. We can create an Excel file using the PHPExcel library. Here’s how to install it:

composer require phpoffice/phpexcel

After installing the PHPExcel library, we can create an Excel file and write the data to it. Here’s the code:

// Creating Excel file
require_once 'vendor/autoload.php';
$objPHPExcel = new PHPExcel();

// Setting the properties
$objPHPExcel->getProperties()->setCreator("Your Name")
    ->setLastModifiedBy("Your Name")
    ->setTitle("Export to Excel")
    ->setSubject("Export to Excel")
    ->setDescription("Export to Excel")
    ->setKeywords("Export to Excel")
    ->setCategory("Export to Excel");

// Creating the worksheet
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Users Data');

// Writing data to Excel
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'ID');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Name');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Email');

$rowCount = 2;
foreach ($data as $row) 
    $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowCount, $row['id']);
    $objPHPExcel->getActiveSheet()->setCellValue('B' . $rowCount, $row['name']);
    $objPHPExcel->getActiveSheet()->setCellValue('C' . $rowCount, $row['email']);
    $rowCount++;


// Downloading the file
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="users.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

exit();

We have now created an Excel file and written the data to it. When you run this script, it will download the Excel file named “users.xls”. You can customize the filename and file type as per your requirements.

Exporting HTML Table to Excel

Now let’s see how to export an HTML table to Excel. We can use the same PHPExcel library for this purpose. Here is the code:

// Creating Excel file
require_once 'vendor/autoload.php';
$objPHPExcel = new PHPExcel();

// Setting the properties
$objPHPExcel->getProperties()->setCreator("Your Name")
    ->setLastModifiedBy("Your Name")
    ->setTitle("Export to Excel")
    ->setSubject("Export to Excel")
    ->setDescription("Export to Excel")
    ->setKeywords("Export to Excel")
    ->setCategory("Export to Excel");

// Creating the worksheet
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Users Data');

// Writing HTML table to Excel
$htmlTable = '<table><tr><th>ID</th><th>Name</th><th>Email</th></tr><tr><td>1</td><td>John</td><td>[email protected]</td></tr><tr><td>2</td><td>Jane</td><td>[email protected]</td></tr></table>';

$objReader = PHPExcel_IOFactory::createReader('HTML');
$objPHPExcel = $objReader->loadFromString($htmlTable);

// Downloading the file
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="users.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

exit();

In this example, we have created an HTML table and stored it in the $htmlTable variable. We have then used the PHPExcel library to write the HTML table to the Excel file. When you run this script, it will download the Excel file named “users.xls”. You can customize the filename and file type as per your requirements.

FAQ

1. What is PHPExcel?

PHPExcel is a set of classes for PHP that allow developers to read and write spreadsheets in various formats. It supports various file formats such as Excel, CSV, PDF, HTML, and more.

2. Is PHPExcel still maintained?

No, PHPExcel is no longer maintained. It has been replaced by the PhpSpreadsheet library, which is a more powerful and actively maintained successor to the PHPExcel library.

Conclusion

In this tutorial, we have seen how to export data to Excel using PHP. We have covered exporting data from the database to an Excel file and exporting an HTML table to Excel. We have also seen a couple of FAQ related to the PHPExcel library. I hope this tutorial has been helpful to you.