Generating Excel Dashboards with Charts in PHP

Learn how to generate Excel dashboards with charts in PHP using PhpSpreadsheet. Create interactive reports, visualize data, and automate Excel-based analytics.

Introduction

Excel dashboards provide a visual representation of key data, making it easier to analyze and interpret complex information. With PhpSpreadsheet, PHP can generate Excel dashboards with charts, tables, and dynamic analytics, automating the reporting process for businesses.

With PhpSpreadsheet, PHP can:

• Create Excel charts dynamically
• Visualize data with bar, line, and pie charts
• Generate structured reports with interactive elements
• Automate Excel dashboard creation for analytics

This guide covers:

• Installing PhpSpreadsheet
• Creating Excel dashboards dynamically
• Adding charts and data visualizations
• Automating Excel report generation

1. Installing PhpSpreadsheet for Excel Dashboard Generation

To create Excel dashboards, 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;
use PhpOffice\PhpSpreadsheet\Chart\{Chart, DataSeries, DataSeriesValues, PlotArea, Title, Legend};

Once installed, your application can generate interactive Excel dashboards.

2. Creating an Excel Dashboard with Data

Before adding charts, generate a structured dataset in Excel.

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

$sheet->setCellValue('A1', 'Month');
$sheet->setCellValue('B1', 'Sales');

$sheet->setCellValue('A2', 'January');
$sheet->setCellValue('B2', 1000);

$sheet->setCellValue('A3', 'February');
$sheet->setCellValue('B3', 1200);

$sheet->setCellValue('A4', 'March');
$sheet->setCellValue('B4', 1500);

$sheet->setCellValue('A5', 'April');
$sheet->setCellValue('B5', 1800);

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

echo "Excel dashboard generated.";

What This Does:

• Creates a sales report with monthly data
• Serves as the foundation for charts and analytics

3. Adding a Line Chart to the Excel Dashboard

To visualize sales trends over time, add a line chart.

$spreadsheet = IOFactory::load('dashboard.xlsx');
$worksheet = $spreadsheet->getActiveSheet();

$categories = [new DataSeriesValues('String', 'Sheet1!$A$2:$A$5', null, 4)];
$values = [new DataSeriesValues('Number', 'Sheet1!$B$2:$B$5', null, 4)];

$series = new DataSeries(
    DataSeries::TYPE_LINECHART,
    DataSeries::GROUPING_STANDARD,
    range(0, count($values) - 1),
    [],
    $categories,
    $values
);

$plotArea = new PlotArea(null, [$series]);
$chart = new Chart('Sales Chart', new Title('Monthly Sales Data'), new Legend(), $plotArea);
$chart->setTopLeftPosition('D2');
$chart->setBottomRightPosition('L12');

$worksheet->addChart($chart);

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

echo "Excel dashboard with chart generated.";

What This Does:

• Extracts monthly sales data
• Creates a line chart representing sales trends
• Positions the chart in Excel for interactive visualization

4. Adding a Bar Chart to the Dashboard

For comparative analysis, insert a bar chart alongside the line chart.

$barSeries = new DataSeries(
    DataSeries::TYPE_BARCHART,
    DataSeries::GROUPING_STANDARD,
    range(0, count($values) - 1),
    [],
    $categories,
    $values
);

$barPlotArea = new PlotArea(null, [$barSeries]);
$barChart = new Chart('Sales Bar Chart', new Title('Sales Performance'), new Legend(), $barPlotArea);
$barChart->setTopLeftPosition('D15');
$barChart->setBottomRightPosition('L25');

$worksheet->addChart($barChart);

$writer->setIncludeCharts(true);
$writer->save('dashboard_with_barchart.xlsx');

echo "Excel dashboard with bar chart generated.";

Why Use This Chart?

• Highlights sales distribution more clearly
• Makes comparisons easier between months

5. Adding a Pie Chart to Show Sales Distribution

A pie chart provides percentage-based visualization of total sales.

$pieSeries = new DataSeries(
    DataSeries::TYPE_PIECHART,
    DataSeries::GROUPING_STANDARD,
    range(0, count($values) - 1),
    [],
    $categories,
    $values
);

$piePlotArea = new PlotArea(null, [$pieSeries]);
$pieChart = new Chart('Sales Pie Chart', new Title('Sales Breakdown'), new Legend(), $piePlotArea);
$pieChart->setTopLeftPosition('D28');
$pieChart->setBottomRightPosition('L38');

$worksheet->addChart($pieChart);

$writer->setIncludeCharts(true);
$writer->save('dashboard_with_piechart.xlsx');

echo "Excel dashboard with pie chart generated.";

Why Use This Chart?

• Shows contribution of each month in total sales
• Useful for quick decision-making

6. Applying Conditional Formatting to Enhance Readability

Highlighting data helps users quickly identify important trends.

use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting;
use PhpOffice\PhpSpreadsheet\Style\Color;

$conditionalStyles = $worksheet->getStyle('B2:B5')->getConditionalStyles();

$condition = new ConditionalFormatting();
$condition->setConditionType(ConditionalFormatting::CONDITION_CELLIS);
$condition->setOperatorType(ConditionalFormatting::OPERATOR_GREATERTHAN);
$condition->addCondition(1200);
$condition->getStyle()->getFont()->setBold(true)->getColor()->setARGB(Color::COLOR_RED);

$conditionalStyles[] = $condition;
$worksheet->getStyle('B2:B5')->setConditionalStyles($conditionalStyles);

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

What This Does:

• Highlights sales above 1200 in red and bold text
• Draws attention to high-performing months

7. Automating Excel Dashboard Generation with Cron Jobs

To automate Excel report generation, use a cron job.

0 9 * * * php /var/www/html/generate_dashboard.php

This updates and regenerates the dashboard every morning at 9 AM.

8. Allowing Users to Download the Generated Excel Dashboard

To allow users to download the dashboard dynamically, modify headers.

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

$writer->save('php://output');

Best Practices for Excel Dashboards in PHP

• Use charts and graphs to make data easier to interpret
• Automate dashboard updates using PHP and cron jobs
• Apply conditional formatting to highlight key trends
• Optimize Excel files for faster loading and processing
• Enable downloads so users can access reports dynamically

Conclusion

With PhpSpreadsheet, PHP can generate interactive Excel dashboards that include charts, conditional formatting, and structured reports.

This guide covered:

• Creating Excel dashboards dynamically
• Adding line, bar, and pie charts for visualization
• Applying conditional formatting for better insights
• Automating dashboard generation using PHP

By implementing these techniques, PHP applications can streamline data visualization and reporting through Excel dashboards.