PHPSpreadsheet - Custom Color in Data Series of Chart

Question

Using PHPSpreadsheet to create a custom line graph. I have data loaded into the spreadsheet and it is properly building the graph. However, I am not able to adjust the color of the lines in the graph. When I try to apply a color to my series, the spreadsheet opens but there is some sort of corruption and the graph does not load. Excel tells me that it has removed the graph so that it can open the spreadsheet: 'Removed Part: /xl/drawings/drawing1.xml part. (Drawing shape)'

I was unable to find any information in the documentation regarding custom chart colors but it was committed and merged into the project earlier this year.

I have checked the source code for my version of PHPSpreadsheet and it does have the updated files/classes that allow for custom chart coloration.

<?php

require_once '/opt/sites/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\Legend;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;

$phpsheet = new Spreadsheet();

$phpsheet->setActiveSheetIndex(0);
$phpsheet->getActiveSheet()->setTitle('Worksheet');

$file = "report.xlsx";

header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment; filename=\"".$file."\"");
header('Cache-Control: max-age=0');


$phpsheet->getActiveSheet()->getColumnDimension('A')->setWidth(15);
$phpsheet->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$phpsheet->getActiveSheet()->getColumnDimension('C')->setWidth(15);

$r = 1;

$phpsheet->getActiveSheet()->setCellValue('A' . $r, "Date");
$phpsheet->getActiveSheet()->setCellValue('B' . $r, "Val  One");
$phpsheet->getActiveSheet()->setCellValue('C' . $r, "Val Two");

$r++;

while($r<=20)
{
    $randNumOne =  rand(1,100);
    $randNumTwo =  rand(1,100);

    $phpsheet->getActiveSheet()->setCellValue('A' . $r, "2018-04-".$r);
    $phpsheet->getActiveSheet()->setCellValue('B' . $r, $randNumOne);
    $phpsheet->getActiveSheet()->setCellValue('C' . $r, $randNumTwo);

    $r++;
}

/////////////////////////////////////////////////////////
//////////////////////////Error Here/////////////////////
///If I change the two hex codes to NULL, there is no issue but of course then there is no change in color either.
/////////////////////////////////////////////////////////
//////////////////////////Error Here/////////////////////

$dsl=array(
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$B$1', NULL, 1, [], NULL, "d914e0"), //Cause of the error
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', NULL, 1, [], NULL, "2ce81b"), //Cause of the error
);

$x = $r - 1;

$xal=array(
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$'.$r, NULL, $x),
);

$dsv = array(
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$2:$B$'.$r, NULL, $x),
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$'.$r, NULL, $x),
);

$ds = new DataSeries(
    DataSeries::TYPE_LINECHART,
    DataSeries::GROUPING_STANDARD,
    range(0, count($dsv)-1),
    $dsl,
    $xal,
    $dsv
);

$pa = new PlotArea(NULL, array($ds));


$legend = new Legend(Legend::POSITION_RIGHT, NULL, false);

$title = new Title('Sample Chart');


$chart = new Chart(
    'chart1',
    $title,
    $legend,
    $pa,
    0,
    0,
    NULL,
    NULL
);


$chart->setTopLeftPosition('E1');
$chart->setBottomRightPosition('V20');

$phpsheet->getActiveSheet()->addChart($chart);


$Excel_writer = new Xlsx($phpsheet);

ob_end_clean();
$Excel_writer->setIncludeCharts(true);

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

I have no errors/warnings/issues on the server side of things. Everything executes as expected and I get the excel file without issue other than the 'drawing corruption'.

I've just been doing something similar. It's a problem with the XLSX writer for charts, it doesn't append the fill colour in the correct place. It causes 2 <c:spPr /> tags to be appended, when it should add one and apply the fill colour to the <a:ln /> tag. There is no workaround apart from either contributing to PHPSpreadsheet or extending the XLSX writer and making a new chart writer. — R. Chappell

Eskiso Eskiso · Accepted Answer · 2020-02-14T12:17:22

Fixed my issue by replacing 0 with 'gap', seems a recent push changed expected int '0' to string 'gap':

$chart = new Chart(
   'chart1',
    $title,
    $legend,
    $pa,
    0,
    'gap', //change here from 0 to gap
    NULL,
    NULL
);

The push: https://github.com/PHPOffice/PhpSpreadsheet/commit/144a0cabbc572780d0f49db50243f15b104ce26e#diff-0fa257b790aee274c5c82d3156288e94

PHPSpreadsheet - Custom Color in Data Series of Chart

1 Answers