Export to Excel
You can export MySQL table data to an Excel file (XLSX format) in PHP using the PhpSpreadsheet library. Here's a step-by-step guide on how to do it:
Step 1: Install PhpSpreadsheet
First, you'll need to install PhpSpreadsheet using Composer. If you haven't installed Composer, download and install it from getcomposer.org.
Run the following command in your project directory to install PhpSpreadsheet:
composer require phpoffice/phpspreadsheet
Step 2: Export MySQL Table to Excel
Here's an example code snippet to export MySQL table data to an Excel file:
<?php
require 'vendor/autoload.php'; // Include PhpSpreadsheet library
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// Database connection
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Select data from your table
$sql = "SELECT * FROM your_table";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Create a new Spreadsheet object
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// Add headers
$column = 'A';
while ($fieldinfo = $result->fetch_field()) {
$sheet->setCellValue($column . '1', $fieldinfo->name);
$column++;
}
// Add data
$row = 2;
while ($rowdata = $result->fetch_assoc()) {
$column = 'A';
foreach ($rowdata as $value) {
$sheet->setCellValue($column++ . $row, $value);
}
$row++;
}
// Save the Excel file
$writer = new Xlsx($spreadsheet);
$filename = 'exported_data.xlsx';
$writer->save($filename);
// Download the file
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
readfile($filename);
unlink($filename); // Delete file after download
exit;
} else {
echo "No data found!";
}
$conn->close();
?>
Replace 'your_username', 'your_password', 'your_database', and 'your_table' with your MySQL credentials and table details.
This code fetches data from a MySQL table, creates an Excel spreadsheet using PhpSpreadsheet, adds the table data to the spreadsheet, saves it as 'exported_data.xlsx', and then initiates a download for the user. Finally, it deletes the file from the server after download.
Make sure the PHP script has write permissions in the directory where it is saved to generate the Excel file.