This tutorial demonstrates how to create a PHP application that exports data from a MySQL database into an Excel file. We'll use the PhpSpreadsheet library for generating Excel files, ensuring compatibility with modern .xlsx formats.
Step 1: Prerequisites
PHP Version: Ensure PHP 7.4 or higher is installed.
Database: MySQL database setup with sample data.
Composer: Required for installing PhpSpreadsheet.
Web Server: Apache or Nginx.
Step 2: Set Up the MySQL Database
Create a MySQL database and table. Use the following SQL script for a sample table:
CREATE DATABASE tutorial_db;
USE tutorial_db;
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
course VARCHAR(100) NOT NULL
);
INSERT INTO students (name, email, course) VALUES
('John Doe', 'john@example.com', 'Computer Science'),
('Jane Smith', 'jane@example.com', 'Information Technology'),
('Alice Johnson', 'alice@example.com', 'Mathematics');
Step 3: Install PhpSpreadsheet
Install the PhpSpreadsheet library using Composer:
composer require phpoffice/phpspreadsheet
Verify the vendor folder is created in your project directory.
Step 4: Create the Export Script
Create a file named export.php with the following code:
<?php
require 'vendor/autoload.php'; // Load PhpSpreadsheet via Composer
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// Database connection
$host = 'localhost';
$dbname = 'tutorial_db';
$username = 'root';
$password = '';
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
// Fetch data from the MySQL table
$stmt = $pdo->query("SELECT id, name, email, course FROM students");
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Create a new Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// Set headers
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'Email');
$sheet->setCellValue('D1', 'Course');
// Populate data
$row = 2;
foreach ($data as $item) {
$sheet->setCellValue('A' . $row, $item['id']);
$sheet->setCellValue('B' . $row, $item['name']);
$sheet->setCellValue('C' . $row, $item['email']);
$sheet->setCellValue('D' . $row, $item['course']);
$row++;
}
// Set headers for file download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="students.xlsx"');
header('Cache-Control: max-age=0');
// Write and output the Excel file
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
?>
Step 5: Create the Frontend
Create a simple frontend to trigger the export script.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Export MySQL Data to Excel</title>
</head>
<body>
<h1>Export MySQL Data to Excel</h1>
<form action="export.php" method="post">
<button type="submit">Download Excel</button>
</form>
</body>
</html>
Step 6: Test the Application
Open the frontend in your browser (index.html).
Click the "Download Excel" button.
Verify that an Excel file named students.xlsx is downloaded, containing the MySQL data.
Enhancements
Styling the Excel Sheet: You can add styles (e.g., bold headers) using PhpSpreadsheet's styling options.
$sheet->getStyle('A1:D1')->getFont()->setBold(true);
Dynamic Query Parameters: Modify the SQL query to accept parameters (e.g., filters or date ranges) based on user input.
Error Handling: Add robust error handling for database connectivity and file generation.
Post a Comment for "Export MySQL Data to Excel in PHP: Step-by-Step Guide with PhpSpreadsheet"