The work around is actually to dynamically create static ranges programmatically. No other way unfortunately. In my case my charts are fully dynamic, i.e. the base table's length is dynamic, too.
I tried to get that resolved with PHP Spreadsheet by manipulating the chart dynamically like this. It basically worked but part of my changes were overwritten afterwards so it wasn't really successful.Now I decided to give up: I will create all of my charts programmatically now because changing a chart from a template has too many limitations if you are using dynamic charts.
foreach ($worksheet->getChartCollection() as $chart) {
if ($chart instanceof Chart) {
$plotArea = $chart->getPlotArea(); //get the plot area of the chart (one thing)
$dataSeries = $plotArea->getPlotGroup(); //array of all the data series
$dataManipulated = false;
foreach ($dataSeries as &$dataSer) { //by reference to change the values deep down!!
$val = $dataSer->getPlotValues();
foreach ( $val as &$dataSeriesValues) {
$dataSource = $dataSeriesValues->getDataSource();
$dataValues = $dataSeriesValues->getDataValues();
$dataValuesLength = count($dataValues);
for ($y=$pointCount; $y < $dataValuesLength; $y++) {
unset($dataValues[$y]);
}
if ( strpos($dataSource, 'DRange_Dates_' . $tf ) !== false ) {
$dataSeriesValues->setDataSource( $sheetName . '!D2:' . $xC . '2' );
} elseif ( strpos($dataSource, 'DRange_AvgVarInt_modified_' . $tf ) !== false ) {
$dataSeriesValues->setDataSource( 'midlayer!B10:' . $xCML . '10' );
} elseif ( strpos($dataSource, 'DRange_AvgVarRate_' . $tf ) !== false ) {
$dataSeriesValues->setDataSource( 'midlayer!B7:' . $xCML . '7' );
}
$dataSeriesValues->setDataValues( $dataValues );
}
$cat = $dataSer->getPlotCategories();
foreach ( $cat as &$categoryValues) {
$dataSource = $categoryValues->getDataSource();
$dataValues = $categoryValues->getDataValues();
$dataValuesLength = count($dataValues);
for ($y=$pointCount; $y < $dataValuesLength; $y++) {
unset($dataValues[$y]);
}
if ( strpos($dataSource, 'DRange_Dates_' . $tf ) !== false ) {
$categoryValues->setDataSource( $sheetName . '!D2:' . $xC . '2' );
} elseif ( strpos($dataSource, 'DRange_AvgVarInt_modified_' . $tf ) !== false ) {
$categoryValues->setDataSource( 'midlayer!B10:' . $xCML . '10' );
} elseif ( strpos($dataSource, 'DRange_AvgVarRate_' . $tf ) !== false ) {
$categoryValues->setDataSource( 'midlayer!B7:' . $xCML . '7' );
}
$categoryValues->setDataValues( $dataValues );
}
}
$plotArea->setPlotSeries($dataSeries);
unset($dataSeriesValues); // break the reference with the last element
unset($categoryValues); // break the reference with the last element
unset($dataSer); // break the reference with the last element
}
}
If you want you charts to look a little nicer you run into the next obstacle. You cannot set the color scheme of the charts youself.
class Theme extends WriterPart
contains this
private static $colourScheme = [
'dk2' => '1F497D',
'lt2' => 'EEECE1',
'accent1' => '4F81BD',
'accent2' => 'C0504D',
'accent3' => '9BBB59',
'accent4' => '8064A2',
'accent5' => '4BACC6',
'accent6' => 'F79646',
'hlink' => '0000FF',
'folHlink' => '800080',
];
This is the standard Office color scheme in hex. Using this your charts will look like everbody else's charts. If you don't want that you can't change it except for a hard coded change which of course isn't the best choice. But I didn't find another way. If anyone has a better way please tell me. Thanks. It would also be great to use several color schemes instead of just one. Didn't find a way to do this either unfortunately.
