mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-08-21 16:14:21 +00:00
oleibman
dbea64cb03
Fix #413, which went stale in 2018 and is now reopened. A DataTable can be used on a chart in place of a Legend (or in addition to one, but it doesn't really make sense to have both). The fix was substantially developed by @topaDev before the issue went stale. This PR provides basic support for reading and writing DataTables on charts. The new sample on this PR shows them in action. They have obscure features which are not implemented (e.g. `glow`); those will have to wait for another day.
109 lines
3.6 KiB
PHP
109 lines
3.6 KiB
PHP
<?php
|
|
|
|
use PhpOffice\PhpSpreadsheet\Chart\Chart;
|
|
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
|
|
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
|
|
use PhpOffice\PhpSpreadsheet\Chart\DataTable;
|
|
//use PhpOffice\PhpSpreadsheet\Chart\Legend as ChartLegend;
|
|
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
|
|
use PhpOffice\PhpSpreadsheet\Chart\Title;
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PhpOffice\PhpSpreadsheet\Theme as SpreadsheetTheme;
|
|
|
|
require __DIR__ . '/../Header.php';
|
|
/** @var PhpOffice\PhpSpreadsheet\Helper\Sample $helper */
|
|
$spreadsheet = new Spreadsheet();
|
|
// same as 33_Chart_create_area, but with 2013+ schemes
|
|
$spreadsheet->getTheme()->setThemeColorName(SpreadsheetTheme::COLOR_SCHEME_2013_2022_NAME);
|
|
$worksheet = $spreadsheet->getActiveSheet();
|
|
$sheet2 = $spreadsheet->createSheet();
|
|
$sheet2->setTitle('Sheet2');
|
|
$worksheet->fromArray(
|
|
[
|
|
['', 2010, 2011, 2012],
|
|
['Q1', 12, 15, 21],
|
|
['Q2', 56, 73, 86],
|
|
['Q3', 52, 61, 69],
|
|
['Q4', 30, 32, 0],
|
|
]
|
|
);
|
|
|
|
// Set the Labels for each data series we want to plot
|
|
// Datatype
|
|
// Cell reference for data
|
|
// Format Code
|
|
// Number of datapoints in series
|
|
// Data values
|
|
// Data Marker
|
|
$dataSeriesLabels = [
|
|
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$B$1', null, 1), // 2010
|
|
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1), // 2011
|
|
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$D$1', null, 1), // 2012
|
|
];
|
|
// Set the X-Axis Labels
|
|
// Datatype
|
|
// Cell reference for data
|
|
// Format Code
|
|
// Number of datapoints in series
|
|
// Data values
|
|
// Data Marker
|
|
$xAxisTickValues = [
|
|
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$5', null, 4), // Q1 to Q4
|
|
];
|
|
// Set the Data values for each data series we want to plot
|
|
// Datatype
|
|
// Cell reference for data
|
|
// Format Code
|
|
// Number of datapoints in series
|
|
// Data values
|
|
// Data Marker
|
|
$dataSeriesValues = [
|
|
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$2:$B$5', null, 4),
|
|
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', null, 4),
|
|
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$D$2:$D$5', null, 4),
|
|
];
|
|
|
|
// Build the dataseries
|
|
$series = new DataSeries(
|
|
DataSeries::TYPE_AREACHART, // plotType
|
|
DataSeries::GROUPING_PERCENT_STACKED, // plotGrouping
|
|
range(0, count($dataSeriesValues) - 1), // plotOrder
|
|
$dataSeriesLabels, // plotLabel
|
|
$xAxisTickValues, // plotCategory
|
|
$dataSeriesValues // plotValues
|
|
);
|
|
|
|
// Set the series in the plot area
|
|
$plotArea = new PlotArea(null, [$series]);
|
|
$plotArea->setDataTable(new DataTable());
|
|
// No need for Legend if using DataTable
|
|
$legend = null; //new ChartLegend(ChartLegend::POSITION_TOPRIGHT, null, false);
|
|
|
|
$title = new Title('Test %age-Stacked Area Chart');
|
|
$yAxisLabel = new Title('Value ($k)');
|
|
|
|
// Create the chart
|
|
$chart = new Chart(
|
|
'chart1', // name
|
|
$title, // title
|
|
$legend, // legend
|
|
$plotArea, // plotArea
|
|
true, // plotVisibleOnly
|
|
DataSeries::EMPTY_AS_GAP, // displayBlanksAs
|
|
null, // xAxisLabel
|
|
$yAxisLabel // yAxisLabel
|
|
);
|
|
|
|
// Set the position where the chart should appear in the worksheet
|
|
$chart->setTopLeftPosition('A1');
|
|
$chart->setBottomRightPosition('H18');
|
|
|
|
// Add the chart to the worksheet
|
|
$sheet2->addChart($chart);
|
|
$spreadsheet->setActiveSheetIndex(1);
|
|
|
|
$helper->renderChart($chart, __FILE__);
|
|
|
|
// Save Excel 2007 file
|
|
$helper->write($spreadsheet, __FILE__, ['Xlsx'], true, null, false);
|