To export data from MySQL to Excel using phpSpreadsheet, you can follow the steps below:
- Connect to the MySQL database using PHP.
$servername = "localhost";
$username = "yourusername";
$password = "yourpassword";
$dbname = "yourdbname";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
- Execute a SQL query to fetch data from the MySQL database.
$sql = "SELECT * FROM yourtablename";
$result = $conn->query($sql);
- Create a new instance of the Spreadsheet class and set the active sheet.
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
- Write the column headings to the first row of the Excel sheet.
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'Email');
- Loop through the result set and write each row to the Excel sheet.
$row = 2;
if ($result->num_rows > 0) {
while($data = $result->fetch_assoc()) {
$sheet->setCellValue('A' . $row, $data['id']);
$sheet->setCellValue('B' . $row, $data['name']);
$sheet->setCellValue('C' . $row, $data['email']);
$row++;
}
}
- Set the appropriate headers to prompt the user to download the file.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="data.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
This will export the data from the MySQL database to an Excel file named "data.xlsx" and prompt the user to download the file.