John Davidson

Message:

I want to export large data from mysql database to Excel file in PHP that directly can download and generated on client side download progress. So the http response don't need to wait about completely query progress and generated excel on the server, because if it's take too long it will be timed out.

I want that all progress generated on the fly download progress client side until download process 100% completed. It's possible or not? How can i do this concept?

---

My Answer:
Yes, it is possible to export large data from a MySQL database to an Excel file in PHP and have the download progress displayed on the client side. One way to achieve this is by using the PHPExcel library to generate the Excel file and then send it to the client using output buffering.

Here is an example code snippet to demonstrate this concept:

php

// Include PHPExcel library
require_once 'PHPExcel/Classes/PHPExcel.php';

// Create a new PHPExcel object
$objPHPExcel = new PHPExcel();

// Add data to the Excel file
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'ID')
->setCellValue('B1', 'Name')
->setCellValue('C1', 'Email');

// Query data from MySQL database
// Replace this with your actual database connection and query
$pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
$stmt = $pdo->prepare('SELECT * FROM users');
$stmt->execute();
$row = 2;
while ($data = $stmt->fetch(PDO::FETCH_ASSOC)) {
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A' . $row, $data['id'])
->setCellValue('B' . $row, $data['name'])
->setCellValue('C' . $row, $data['email']);
$row++;
}

// Set headers for Excel file download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="export.xlsx"');
header('Cache-Control: max-age=0');

// Use output buffering to send Excel file to client
ob_end_clean();
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

exit;


In this code snippet, we first create a new PHPExcel object and add data to it from a MySQL database query. We then set the headers for the Excel file download and use output buffering to send the Excel file to the client.

This way, the client will see the download progress as the Excel file is being generated and downloaded. This approach should help prevent timeouts and provide a better user experience.