PHPExcel graph design (border, graph color, graph inner position)

Question

I'm using PHPExcel to build excel sheet with multiple graphs and im trying to customize them. i have only 3 problems left unsolved: 1. i want the graph to have no border. 2. i want to change the color of the graphs lines. 3. i want to change the position of the graph inside the graphs area. as for now this is the way i build graphs:

$xAxisTickValues = $TruexAxisTickValues;
$series = new PHPExcel_Chart_DataSeries(
  PHPExcel_Chart_DataSeries::TYPE_LINECHART,        // plotType
  PHPExcel_Chart_DataSeries::GROUPING_STANDARD,     // plotGrouping
  range(0, 10),                                 // plotOrder
  null,                                         // plotLabel
  $xAxisTickValues,                                 // plotCategory
  $values                                           // plotValues
);
$series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
$plotarea = new PHPExcel_Chart_PlotArea(null, array($series));
$chart = new PHPExcel_Chart(
  'chart1',                                       // name
  null,                                           // title
  null,                                         // legend
  $plotarea,                                      // plotArea
  true,                                           // plotVisibleOnly
  0,                                              // displayBlanksAs
  null,                                           // xAxisLabel
  null                                            // yAxisLabel
);
$chart->setTopLeftPosition('C5' );
$chart->setBottomRightPosition('J11' );
$sheet->addChart($chart);   

is there a way to do this customize graphs ?

Answer 1

As Rzangue stated, PHPExcel does not currently provide an easy way of doing so, however, if you don't mind hard-coding the changes for all graphs created with PHPExcel, you can make the changes suggested below to your PHPExcel/Classes/Writer/Excel2007/Chart.php file.

To alter the chart's border color and thickness, within the public function writeChart(), add:

$cBorderColor = "000000";
$objWriter->startElement('c:spPr');
    $objWriter->startElement('a:ln');
    $objWriter->writeAttribute('w', '40000');//alters border thickness
       $objWriter->startElement('a:solidFill');
           $objWriter->startElement('a:srgbClr');
           $objWriter->writeAttribute('val',$cBorderColor);//changes the color
           $objWriter->endElement();
       $objWriter->endElement();
    $objWriter->endElement();
$objWriter->endElement();

after:

    $objWriter->startElement('c:showDLblsOverMax');
        $objWriter->writeAttribute('val', 0);
    $objWriter->endElement();
            
$objWriter->endElement();

but before:

$this->_writePrintSettings($objWriter); 

which should be around line 106 of the Chart.php file.

Obviously replacing "000000" with whatever web color you desire to be your chart border color to be. To remove the border color entirely, insert:

$objWriter->startElement('c:spPr');
    $objWriter->startElement('a:ln');
        $objWriter->startElement('a:noFill');
        $objWriter->endElement();
    $objWriter->endElement();
$objWriter->endElement();

instead.

Next, to alter the positioning of the plot area within the chart, scroll down within the Chart.php file to the private function _writeLayout().

Delete all code within the function besides the open/close brackets {}. Within the function, add:

$layoutTarget = "inner";
$xMode = "edge";
$yMode = "edge";
$xOffset = 0.1;  //The left margin in percentage of graph width.
$yOffset = 0.1;  //The top margin in percentage of graph width.
$paWidth = 0.9;  //The percentage width of the plot area relative to the graph width;
$paHeight = 0.9; //The percentage height of the plot area relative to the graph height;

$objWriter->startElement('c:layout');
    $objWriter->startElement('c:manualLayout');
        $objWriter->startElement('c:layoutTarget');
            $objWriter->writeAttribute('val',$layoutTarget);
        $objWriter->endElement();
        $objWriter->startElement('c:xMode');
            $objWriter->writeAttribute('val',$xMode);
        $objWriter->endElement();
        $objWriter->startElement('c:yMode');
            $objWriter->writeAttribute('val',$yMode);
        $objWriter->endElement();
        $objWriter->startElement('c:x');
            $objWriter->writeAttribute('val',$xOffset);
        $objWriter->endElement();
        $objWriter->startElement('c:y');
            $objWriter->writeAttribute('val',$yOffset);
        $objWriter->endElement();
        $objWriter->startElement('c:w');
            $objWriter->writeAttribute('val',$paWidth);
        $objWriter->endElement();
        $objWriter->startElement('c:h');
            $objWriter->writeAttribute('val',$paHeight);
        $objWriter->endElement();
    $objWriter->endElement(); 
$objWriter->endElement();

You can then adjust the x/y offset and w/h as you wish.

To control/change the colors of each data series, within:

private function _writePlotGroup()

before:

foreach($plotSeriesOrder as $plotSeriesIdx => $plotSeriesRef) {

add:

$ci=-1;
$colorNDX=array();
$colorNDX[0] = "111111";
$colorNDX[1] = "222222";
$colorNDX[2] = "333333";
$colorNDX[3] = "444444";
$colorNDX[4] = "555555";
$colorNDX[5] = "666666";
$colorNDX[6] = "777777";

and so on, being sure to add enough color indexes for all series of data and obviously changing the 111111,222222,333333 to web colors of your liking.

Also, after:

foreach($plotSeriesOrder as $plotSeriesIdx => $plotSeriesRef) {

Add:

$ci++;

And after:

//  Labels
$plotSeriesLabel = $plotGroup->getPlotLabelByIndex($plotSeriesRef);
if ($plotSeriesLabel && ($plotSeriesLabel->getPointCount() > 0)) {
    $objWriter->startElement('c:tx');
    $objWriter->startElement('c:strRef');
        $this->_writePlotSeriesLabel($plotSeriesLabel, $objWriter);
    $objWriter->endElement();
$objWriter->endElement();
}

Add:

$objWriter->startElement('c:spPr');
    $objWriter->startElement('a:solidFill');
        $objWriter->startElement('a:srgbClr');
            $objWriter->writeAttribute('val',$colorNDX[$ci]);
        $objWriter->endElement();
    $objWriter->endElement();
$objWriter->endElement();

Let me know if this helps. Again, these changes will be applied to all charts generated by PHPExcel, however, a couple well placed if statements should be more than enough to make the changes more dynamic on a per chart type basis.

Answer 2

Adding IIIOXIII's code, specifically the following block, when using LineCharts caused Excel 2007 to error for me

$objWriter->startElement('c:spPr');
 $objWriter->startElement('a:solidFill');
  $objWriter->startElement('a:srgbClr');
   $objWriter->writeAttribute('val',$colorNDX[$ci]);
  $objWriter->endElement();
 $objWriter->endElement();
$objWriter->endElement();

Firstly add the following condition statement around the above block

if ($groupType !== PHPExcel_Chart_DataSeries::TYPE_LINECHART && $groupType !== PHPExcel_Chart_DataSeries::TYPE_STOCKCHART) {
    // above code block
}

Then after the block around a dozen lines further down the code which reads

if ($groupType == PHPExcel_Chart_DataSeries::TYPE_STOCKCHART) {
  $objWriter->startElement('a:noFill');
  $objWriter->endElement();
}

add the following

$objWriter->startElement('a:solidFill');
  $objWriter->startElement('a:srgbClr');
   $objWriter->writeAttribute('val',$colorNDX[$ci])
  $objWriter->endElement();
$objWriter->endElement();

This will then prevent Excel from erroring and allow you to colour line charts