Export Data To Excel Using PHP: Source Code Included

by Alex Braham 53 views

Hey guys! Ever needed to export data from your PHP application directly into an Excel file? It's a pretty common requirement, whether you're generating reports, backing up data, or just making information easier to share. I will show you how to export data into excel using PHP.

Why Export to Excel?

Before diving into the code, let's quickly chat about why exporting to Excel is such a big deal. Excel is a powerhouse tool that many people use daily. Here's why it's awesome:

  • Universally Accessible: Almost everyone has Excel or a compatible spreadsheet program.
  • Data Manipulation: Users can easily sort, filter, and analyze the data.
  • Reporting: Excel makes it super easy to create charts, graphs, and reports.
  • Offline Access: Once exported, the data is available offline.

So, being able to seamlessly export data to Excel from your PHP application opens up a world of possibilities for your users.

Prerequisites

Before we start coding, make sure you have the following:

  • PHP: You'll need PHP installed on your server. Most web hosting environments have this already.
  • Web Server: Apache or Nginx – whatever you usually use to serve PHP pages.
  • Basic PHP Knowledge: You should be comfortable with PHP syntax and working with arrays and database queries.
  • Excel (or Compatible Software): To open and view the exported .xls or .xlsx files.

The Basic Approach

There are a few ways to tackle this, but the most common involves these steps:

  1. Fetch Data: Grab the data you want to export from a database or other source.
  2. Format Data: Structure the data into a format suitable for Excel (usually a 2D array).
  3. Set Headers: Configure the PHP headers to tell the browser that it's receiving an Excel file.
  4. Output Data: Echo the data in a format Excel can understand (like CSV or a proper Excel format).

Method 1: Simple CSV Export

The simplest way to export data to Excel is by generating a CSV (Comma Separated Values) file. Excel can open CSV files and automatically parse the data into columns. Here's how to do it:

Step 1: Fetch Your Data

First, let's assume you have a database connection and a query to fetch the data. For example:

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname, email FROM users";
$result = $conn->query($sql);

$data = array();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
}

$conn->close();
?>

This code fetches data from a users table. Adapt the SQL query to match your needs.

Step 2: Prepare CSV Data

Now, let's format the data into a CSV string:

<?php
$csv_data = array();

// Add CSV header row
if (!empty($data)) {
    $csv_data[] = array_keys($data[0]);

    // Add CSV data rows
    foreach ($data as $row) {
        $csv_data[] = $row;
    }
}

function arrayToCsv(array &$array)
{
   if (count($array) == 0) {
     return null;
   }
   ob_start();
   $df = fopen("php://output", 'w');
   fputcsv($df, array_keys(reset($array)));
   foreach ($array as $row) {
      fputcsv($df, $row);
   }
   fclose($df);
   return ob_get_clean();
}

function download_send_headers(string $filename)
{
    // disable caching
    $now = gmdate("D, d M Y H:i:s");
    header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
    header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
    header("Last-Modified: {\$now} GMT");

    // force download  
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");

    // disposition / encoding on response body
    header("Content-Disposition: attachment;filename={$filename}");
    header("Content-Transfer-Encoding: binary");
}

download_send_headers("data_export_" . date("Y-m-d") . ".csv");

echo arrayToCsv($csv_data);

die();
?>

This code does the following:

  • Creates $csv_data array: Adds the column headers (field names from the database) as the first row.
  • Loops through data: Adds each row of data to the $csv_data array.

Step 3: Set Headers and Output

Finally, set the HTTP headers to tell the browser it's receiving a CSV file, and then output the CSV data:

<?php
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="data_export.csv"');

// Open a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// Loop through the array and output to the CSV
foreach ($csv_data as $row) {
    fputcsv($output, $row);
}

// Close the file pointer
fclose($output);
?>

Here's what this code does:

  • header('Content-Type: text/csv');: Tells the browser the content type is CSV.
  • header('Content-Disposition: attachment; filename="data_export.csv"');: Tells the browser to download the file with the name "data_export.csv".
  • fopen('php://output', 'w');: Opens a stream to write directly to the output.
  • fputcsv($output, $row);: Writes each row of data to the CSV file, properly formatting it.
  • fclose($output);: Closes the stream.

Putting it All Together

Here's the complete code:

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname, email FROM users";
$result = $conn->query($sql);

$data = array();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
}

$conn->close();

$csv_data = array();

// Add CSV header row
if (!empty($data)) {
    $csv_data[] = array_keys($data[0]);

    // Add CSV data rows
    foreach ($data as $row) {
        $csv_data[] = $row;
    }
}

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="data_export.csv"');

// Open a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// Loop through the array and output to the CSV
foreach ($csv_data as $row) {
    fputcsv($output, $row);
}

// Close the file pointer
fclose($output);

exit();
?>

Important: Remember to replace the database credentials with your actual credentials!

Method 2: Using a Library (PHPExcel/PhpSpreadsheet)

For more complex Excel files (with multiple sheets, formatting, formulas, etc.), using a library like PHPExcel (now PhpSpreadsheet) is the way to go. It's more powerful but also more complex to set up.

Step 1: Install PhpSpreadsheet

The recommended library now is PhpSpreadsheet. You can install it using Composer:

composer require phpoffice/phpspreadsheet

Make sure you have Composer installed. If not, you can download it from https://getcomposer.org/.

Step 2: Write the Code

Here's an example of how to export data using PhpSpreadsheet:

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

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

$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname, email FROM users";
$result = $conn->query($sql);

$data = array();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
}

$conn->close();

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

// Add header row
$header = array_keys($data[0]);
for ($i = 0; $i < count($header); $i++) {
    $sheet->setCellValueByColumnAndRow($i + 1, 1, $header[$i]);
}

// Add data rows
$row_index = 2;
foreach ($data as $row) {
    $col_index = 1;
    foreach ($row as $cell) {
        $sheet->setCellValueByColumnAndRow($col_index, $row_index, $cell);
        $col_index++;
    }
    $row_index++;
}

$writer = new Xlsx($spreadsheet);

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

$writer->save("php://output");
exit();
?>

Let's break down the code:

  • require 'vendor/autoload.php';: Includes the autoloader generated by Composer.
  • use PhpOffice\PhpSpreadsheet\...;: Imports the necessary classes.
  • Creates a Spreadsheet object: $spreadsheet = new Spreadsheet(); creates a new spreadsheet.
  • Gets the active sheet: $sheet = $spreadsheet->getActiveSheet(); gets the first sheet in the spreadsheet.
  • Adds the header row: The code loops through the column names and adds them to the first row.
  • Adds the data rows: The code loops through the data and adds each cell to the appropriate row and column.
  • Creates a Writer object: $writer = new Xlsx($spreadsheet); creates a writer object to save the spreadsheet in XLSX format.
  • Sets Headers: Sets the correct headers for an XLSX file.
  • Saves to output: $writer->save("php://output"); saves the spreadsheet directly to the output stream.

Benefits of Using PhpSpreadsheet:

  • More Control: Full control over formatting, styles, and formulas.
  • Multiple Sheets: Easily create spreadsheets with multiple worksheets.
  • Complex Data: Handles complex data structures with ease.
  • Modern Format: Generates .xlsx files, which are the standard for modern Excel versions.

Method 3: Using a prebuilt package

If you are using a PHP framework like Laravel, you can leverage pre-built packages to handle excel exporting functionality. These packages are extremely easy to install and use.

Laravel Excel Package

One of the popular packages for laravel is called maatwebsite/excel. It provides a simple and elegant way to export and import excel files.

composer require maatwebsite/excel

Step 2: Write the Code

To export data using this package you can use the following sample code

<?php

namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    public function collection()
    {
        return User::all();
    }
}

<?php

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Controllers\Controller;

class UsersController extends Controller
{
    public function export()
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

Best Practices for Exporting Data to Excel

  • Sanitize Data: Always sanitize your data before outputting it to prevent Excel injection vulnerabilities.
  • Handle Large Datasets: For very large datasets, consider using chunking or streaming to avoid memory issues.
  • Error Handling: Implement proper error handling to gracefully handle database connection errors or other issues.
  • User Feedback: Provide feedback to the user during the export process (e.g., a loading message).
  • Choose the Right Method: Select the appropriate method (CSV or library) based on the complexity of your requirements.

Conclusion

Exporting data to Excel using PHP is a valuable skill. Whether you choose the simple CSV approach or a more powerful library like PhpSpreadsheet, you can empower your users to easily access and analyze their data. Happy coding, and may your spreadsheets always be filled with insightful information!