How to Write and Export Data to Excel in PHP

Introduction

Exporting data to Excel in PHP is a common requirement for applications dealing with reports, data analysis, and database exports. Instead of manually copying data, PhpSpreadsheet enables dynamic Excel file generation, allowing users to download structured reports in .xls or .xlsx formats.

With PhpSpreadsheet, you can:

✅ Generate Excel files dynamically in PHP
✅ Write structured data (tables, lists, reports) into Excel
✅ Format Excel sheets with styles, colors, and borders
✅ Export MySQL database data into an Excel file

In this guide, we will:

1️⃣ Install PhpSpreadsheet
2️⃣ Write data into an Excel file dynamically
3️⃣ Apply styling and formatting to Excel sheets
4️⃣ Export MySQL database data to Excel
5️⃣ Automate Excel downloads for reports

Let’s get started! 🚀

1. Installing PhpSpreadsheet in PHP

To work with Excel files in PHP, install PhpSpreadsheet via Composer.

Install via Composer (Recommended)

composer require phpoffice/phpspreadsheet

Include PhpSpreadsheet in Your PHP Script

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

✅ Now, your PHP application is ready to generate Excel files.

2. Creating a Simple Excel File and Writing Data

Let's generate an Excel file and write some basic data into it.

Example: Write Data to an Excel File in PHP

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Write data into cells
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'Email');

$sheet->setCellValue('A2', 1);
$sheet->setCellValue('B2', 'John Doe');
$sheet->setCellValue('C2', 'john@example.com');

// Save Excel file
$writer = new Xlsx($spreadsheet);
$writer->save('data.xlsx');

echo "Excel file created successfully!";

Explanation:

✅ Spreadsheet() – Creates a new Excel file.
✅ setCellValue('A1', 'ID') – Writes data into Excel cells.
✅ Writer\Xlsx($spreadsheet)->save('data.xlsx') – Saves the file in .xlsx format.

🔹 Now, data.xlsx is created with structured data.

3. Formatting and Styling Excel Cells

To make Excel files look professional, add bold text, colors, borders, and alignment.

Example: Apply Styles to Excel Cells

use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

$sheet->getStyle('A1:C1')->getFont()->setBold(true);
$sheet->getStyle('A1:C1')->getFont()->getColor()->setARGB(Color::COLOR_WHITE);
$sheet->getStyle('A1:C1')->getFill()->setFillType('solid')->getStartColor()->setARGB('0000FF');

$sheet->getStyle('A1:C10')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
$sheet->getStyle('A1:C10')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

$writer->save('styled_data.xlsx');

Explanation:

✅ setBold(true) – Makes header text bold.
✅ setARGB('0000FF') – Sets background color (Blue).
✅ getBorders()->getAllBorders()->setBorderStyle(BORDER_THIN) – Adds borders.
✅ setHorizontal(Alignment::HORIZONTAL_CENTER) – Centers text.

🔹 Now, styled_data.xlsx has a well-formatted table.

4. Exporting MySQL Data to Excel

A common use case is exporting database records to an Excel file.

Example: Fetch MySQL Data and Write to Excel

$conn = new mysqli("localhost", "root", "", "users_db");

$result = $conn->query("SELECT id, name, email FROM users");
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Set headers
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'Email');

$row = 2;
while ($data = $result->fetch_assoc()) {
    $sheet->setCellValue("A$row", $data['id']);
    $sheet->setCellValue("B$row", $data['name']);
    $sheet->setCellValue("C$row", $data['email']);
    $row++;
}

// Save file
$writer = new Xlsx($spreadsheet);
$writer->save('users.xlsx');

echo "Database exported to Excel!";

✅ Converts MySQL data into an Excel report automatically.

5. Downloading an Excel File in PHP

To allow users to download the generated Excel file, use Content-Disposition.

Example: Force Excel Download in PHP

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="export.xlsx"');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');

✅ Triggers a file download instead of saving it to the server.

🔹 Useful for generating downloadable reports on the fly.

6. Writing Multi-Sheet Excel Files

To create an Excel file with multiple sheets, use createSheet().

Example: Add Multiple Sheets to an Excel File

$spreadsheet->createSheet();
$spreadsheet->setActiveSheetIndex(1)->setTitle('Second Sheet');

$spreadsheet->getSheet(1)->setCellValue('A1', 'Hello from Sheet 2');

$writer->save('multi_sheet.xlsx');

✅ Creates an Excel file with multiple sheets (tabs).

7. Automating Excel File Generation with Cron Jobs

To generate Excel files on a schedule, set up a cron job.

Example: Automate Report Generation (Daily)

0 0 * * * php /var/www/html/generate_excel.php

✅ Automatically generates and updates Excel reports every day.

Best Practices for Writing Excel Files in PHP

✔ Use PhpSpreadsheet for both .xls and .xlsx formats.
✔ Optimize Excel reports with proper formatting and styles.
✔ Convert MySQL data to Excel for structured reporting.
✔ Allow file downloads dynamically using headers.
✔ Automate recurring Excel exports with cron jobs.

Conclusion

With PhpSpreadsheet, PHP can generate Excel files dynamically for reports, data exports, and database processing.

✅ Write and format Excel files in PHP
✅ Export MySQL data to Excel for reports
✅ Download and automate Excel file generation
✅ Create professional, structured Excel documents

By implementing these techniques, you can enhance reporting and automate data exports in PHP applications! 🚀