How to Export Data to Excel in PHP 8.2 Using PhpSpreadsheet

Introduction

Exporting data to Excel is a common requirement in web applications, whether it’s for generating reports, downloading records, or sharing data with users. PHP makes it easy to export data to Excel using the powerful PhpSpreadsheet library.

In this tutorial, we’ll walk through how to export data to Excel in PHP 8.2 using PhpSpreadsheet. By the end, you’ll be able to generate and download Excel files dynamically from your PHP application.

Prerequisites

Before we begin, ensure you have:

• PHP 8.2 installed
• Composer for dependency management
• A basic understanding of PHP

Step 1: Install PhpSpreadsheet via Composer

First, we need to install the PhpSpreadsheet library. Run the following command in your project directory:

composer require phpoffice/phpspreadsheet

This will download and install the latest version of PhpSpreadsheet along with its dependencies.

Step 2: Create a PHP Script to Export Data

Let’s create a simple script that exports an array of data to an Excel file.

Example Code:

<?php
require 'vendor/autoload.php';

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

// Sample data to export
$data = [
    ['Name', 'Email', 'Phone'],
    ['John Doe', 'john@example.com', '123-456-7890'],
    ['Jane Smith', 'jane@example.com', '987-654-3210'],
    ['Tanveer', 'tanveer@example.com', '555-123-4567'],
];

// Create a new Spreadsheet object
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Fill the sheet with data
$sheet->fromArray($data, null, 'A1');

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

// Create Excel file and send to browser
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
?>

Explanation:

1. Include Autoloader: We load the Composer autoloader to use PhpSpreadsheet.
2. Sample Data: We define an array of data (can be fetched from a database).
3. Create Spreadsheet: Initialize a new Spreadsheet object.
4. Fill Data: Use fromArray() to populate the Excel sheet.
5. Set Headers: Tell the browser to download the file instead of displaying it.
6. Save & Export: Use Xlsx writer to generate and output the Excel file.

Step 3: Run the Script

Save the file as export_excel.php and access it via a browser. It will automatically download an Excel file named exported_data.xlsx with your data.

Advanced Customizations

Apply Styling to Cells

You can format cells, apply colors, and set column widths:

// Set column width
$sheet->getColumnDimension('A')->setWidth(20);

// Apply styling (bold headers)
$headerStyle = [
    'font' => [
        'bold' => true,
        'color' => ['rgb' => 'FFFFFF']
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'startColor' => ['rgb' => '4285F4']
    ]
];

$sheet->getStyle('A1:C1')->applyFromArray($headerStyle);

Export Data from Database

Instead of a static array, fetch data from MySQL:

// Example: Fetch data from MySQL
$pdo = new PDO("mysql:host=localhost;dbname=test", "username", "password");
$stmt = $pdo->query("SELECT name, email, phone FROM users");
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Add headers
array_unshift($data, ['Name', 'Email', 'Phone']);

Conclusion

Exporting data to Excel in PHP 8.2 using PhpSpreadsheet is straightforward and highly customizable. You can generate dynamic Excel reports, apply styling, and even pull data directly from a database.

Try integrating this into your project and enhance it further by adding charts, formulas, or multiple sheets!