Using PhpSpreadsheet for Excel Import/Export and Chart Generation in PHP

PhpSpreadsheet is the actively maintained successor to PHPExcel, written in pure PHP and following modern standards such as namespaces and PSR. The article provides a concise guide to its import/export capabilities, focusing on chart creation.

1. Installation

composer require phpoffice/phpspreadsheet

2. Required namespaces

use PhpOffice\\PhpSpreadsheet\\Chart\\Chart;
use PhpOffice\\PhpSpreadsheet\\Chart\\ChartColor;
use PhpOffice\\PhpSpreadsheet\\Chart\\DataSeries;
use PhpOffice\\PhpSpreadsheet\\Chart\\DataSeriesValues;
use PhpOffice\\PhpSpreadsheet\\Chart\\GridLines;
use PhpOffice\\PhpSpreadsheet\\Chart\\Layout;
use PhpOffice\\PhpSpreadsheet\\Chart\\Legend as ChartLegend;
use PhpOffice\\PhpSpreadsheet\\Chart\\PlotArea;
use PhpOffice\\PhpSpreadsheet\\Chart\\Properties;
use PhpOffice\\PhpSpreadsheet\\Chart\\Title;
use PhpOffice\\PhpSpreadsheet\\IOFactory;
use PhpOffice\\PhpSpreadsheet\\Spreadsheet;

3. Example code

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->fromArray([
    ['', 2010, 2011, 2012],
    ['Q1', 12, 15, 21],
    ['Q2', 56, 73, 86],
    ['Q3', 52, 61, 69],
    ['Q4', 30, 32, 0],
]);
$colors = ['cccccc', '00abb8', 'b8292f', 'eb8500'];
// Define data series, labels, and layout for a bar chart
$dataSeriesLabels1 = [new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1)];
$xAxisTickValues1 = [new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$5', null, 4)];
$dataSeriesValues1 = [new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', null, 4, [], null, $colors)];
$labelLayout = (new Layout())
    ->setShowVal(true)
    ->setLabelFontColor(new ChartColor('FFFF00'))
    ->setLabelFillColor(new ChartColor('accent2', null, 'schemeClr'));
$dataSeriesValues1[0]->setLabelLayout($labelLayout);
$series1 = new DataSeries(
    DataSeries::TYPE_BARCHART,
    null,
    range(0, count($dataSeriesValues1) - 1),
    $dataSeriesLabels1,
    $xAxisTickValues1,
    $dataSeriesValues1
);
$layout1 = (new Layout())->setShowVal(true)->setShowPercent(true);
$plotArea1 = new PlotArea($layout1, [$series1]);
$legend1 = new ChartLegend(ChartLegend::POSITION_RIGHT, null, false);
$title1 = new Title('Test Bar Chart');
$chart1 = new Chart('chart1', $title1, $legend1, $plotArea1, true, DataSeries::EMPTY_AS_GAP, null, null);
// Gridlines configuration
$majorGridlinesY = new GridLines();
$majorGridlinesY->setLineColorProperties('FF0000');
$minorGridlinesY = new GridLines();
$minorGridlinesY->setLineStyleProperty('dash', Properties::LINE_STYLE_DASH_ROUND_DOT);
$chart1->getChartAxisY()->setMajorGridlines($majorGridlinesY)->setMinorGridlines($minorGridlinesY);
$majorGridlinesX = new GridLines();
$majorGridlinesX->setLineColorProperties('FF00FF');
$minorGridlinesX = new GridLines();
$minorGridlinesX->activateObject();
$chart1->getChartAxisX()->setMajorGridlines($majorGridlinesX)->setMinorGridlines($minorGridlinesX);
$chart1->setTopLeftPosition('A7');
$chart1->setBottomRightPosition('H20');
$worksheet->addChart($chart1);
// Donut chart example
$dataSeriesLabels2 = [new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1)];
$xAxisTickValues2 = [new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$5', null, 4)];
$dataSeriesValues2Element = new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', null, 4);
$dataSeriesValues2Element->setFillColor($colors);
$dataSeriesValues2 = [$dataSeriesValues2Element];
$series2 = new DataSeries(
    DataSeries::TYPE_DONUTCHART,
    null,
    range(0, count($dataSeriesValues2) - 1),
    $dataSeriesLabels2,
    $xAxisTickValues2,
    $dataSeriesValues2
);
$layout2 = (new Layout())->setShowVal(true)->setShowCatName(true)->setLabelFillColor(new ChartColor('FFFF00'));
$plotArea2 = new PlotArea($layout2, [$series2]);
$title2 = new Title('Test Donut Chart');
$chart2 = new Chart('chart2', $title2, null, $plotArea2, true, DataSeries::EMPTY_AS_GAP, null, null);
$chart2->setTopLeftPosition('I7');
$chart2->setBottomRightPosition('P20');
$worksheet->addChart($chart2);
// Output the workbook as an XLSX file
$filename = "test";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8');
header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
header('Cache-Control: max-age=0');
ob_end_clean();
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setIncludeCharts(true);
$writer->save('php://output');
$spreadsheet->disconnectWorksheets();

4. Result

The generated workbook contains the data table, a bar chart, and a donut chart positioned as specified, which can be downloaded as an XLSX file.