Generating Dynamic Excel Reports in PHP

zeroexp.dev ZeroonLeave a Comment on Generating Dynamic Excel Reports in PHP
Generating Dynamic Excel Reports in PHP

Learn how to generate dynamic Excel reports in PHP using PhpSpreadsheet. Create structured, formatted, and automated reports from MySQL data.

Introduction

Generating dynamic Excel reports in PHP allows businesses and developers to create automated reports from databases without manual intervention. Whether for financial reports, sales data, user analytics, or inventory tracking, PhpSpreadsheet provides the ability to export structured, well-formatted Excel reports dynamically.

This guide covers:

  • Installing PhpSpreadsheet
  • Generating structured and formatted Excel reports
  • Exporting MySQL data into Excel
  • Applying charts, formulas, and styling
  • Automating Excel report generation

1. Installing PhpSpreadsheet in PHP

To generate Excel reports, install PhpSpreadsheet via Composer:

composer require phpoffice/phpspreadsheet

Include PhpSpreadsheet in your PHP script:

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

Once installed, your PHP application is ready to create Excel reports.

2. Creating a Basic Excel Report with Data

Let's create an Excel report with structured data.

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

$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'Department');
$sheet->setCellValue('D1', 'Salary');

$data = [
    [1, 'John Doe', 'HR', 50000],
    [2, 'Jane Smith', 'IT', 65000],
    [3, 'Michael Johnson', 'Marketing', 48000],
];

$row = 2;
foreach ($data as $entry) {
    $sheet->setCellValue("A$row", $entry[0]);
    $sheet->setCellValue("B$row", $entry[1]);
    $sheet->setCellValue("C$row", $entry[2]);
    $sheet->setCellValue("D$row", $entry[3]);
    $row++;
}

$writer = new Xlsx($spreadsheet);
$writer->save('report.xlsx');

echo "Excel report generated successfully.";

3. Fetching MySQL Data for Dynamic Reports

To create reports from a MySQL database, fetch data dynamically and insert it into Excel.

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

$result = $conn->query("SELECT id, name, department, salary FROM employees");

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

$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'Department');
$sheet->setCellValue('D1', 'Salary');

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

$writer = new Xlsx($spreadsheet);
$writer->save('employee_report.xlsx');

echo "Employee report generated.";

This report dynamically pulls employee data from MySQL into an Excel file.

4. Formatting and Styling Excel Reports

Reports should be readable and professional with proper styling.

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

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

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

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

5. Applying Formulas in Excel Reports

Excel reports often include calculations and summaries.

$sheet->setCellValue('D5', '=SUM(D2:D4)'); // Adds salary values
$sheet->setCellValue('B6', '=AVERAGE(D2:D4)'); // Calculates the average salary

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

6. Adding Charts to Reports

To visualize data, insert charts into reports.

use PhpOffice\PhpSpreadsheet\Chart\{Chart, DataSeries, DataSeriesValues, PlotArea, Title};

// Define the data range
$categories = [new DataSeriesValues('String', 'Sheet1!$B$2:$B$4', null, 3)];
$values = [new DataSeriesValues('Number', 'Sheet1!$D$2:$D$4', null, 3)];

// Create a data series
$series = new DataSeries(
    DataSeries::TYPE_BARCHART,
    null,
    range(0, count($values) - 1),
    [],
    $categories,
    $values
);

// Add the chart
$plotArea = new PlotArea(null, [$series]);
$chart = new Chart('Salary Chart', new Title('Salary Overview'), null, $plotArea);
$chart->setTopLeftPosition('F2');
$chart->setBottomRightPosition('M12');
$sheet->addChart($chart);

$writer = new Xlsx($spreadsheet);
$writer->setIncludeCharts(true);
$writer->save('report_with_chart.xlsx');

This generates a bar chart representing salaries.

7. Automating Report Generation

To generate reports automatically, schedule a cron job.

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

This command executes the report generation script daily at midnight.

8. Allowing Users to Download Excel Reports

To enable users to download reports dynamically, modify headers.

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

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

Best Practices for Excel Report Generation

  • Apply consistent formatting for professional reports.
  • Use database queries efficiently to fetch required data.
  • Add charts and formulas for better data representation.
  • Automate report generation with cron jobs.
  • Enable secure downloads for users accessing reports.

Conclusion

With PhpSpreadsheet, PHP can generate dynamic, structured, and well-formatted Excel reports.

This guide covered:

  • Fetching and writing data into Excel
  • Applying styling and formatting
  • Inserting formulas and charts for better insights
  • Automating report generation

By following these steps, PHP applications can automate business reporting efficiently.

Leave a Reply