PHPExcel graph design (border, graph color, graph

问题:

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 ?

回答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, within the public function writeChart(), add:

$cBorderColor = "000000";
$objWriter->startElement('c:spPr');
    $objWriter->startElement('a:ln');
        $objWriter->startElement('a:solidFill');
            $objWriter->startElement('a:srgbClr');
                $objWriter->writeAttribute('val',$cBorderColor);
            $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.

回答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

回答3:

In my case I wanted to change to original colors of the pie chart, I was able to accomplish this by editing the PHPExcel_Writer_Excel2007_Theme class without editing its original writer Excel2007 like so:

• copy Excel2007 folder and past it in the same folder with a different name e.g. 'Excel2007Custom'

• open all the classes in the Excel2007Custom folder and rename the classes e.g.

  PHPExcel_Writer_Excel2007_Chart will become PHPExcel_Writer_Excel2007Custom_Chart

  PHPExcel_Writer_Excel2007_Comments will become PHPExcel_Writer_Excel2007Custom_Comments

and so on.

• copy Excel2007.php file and past it in the same folder with a different name e.g. 'Excel2007Custom.php'
• open the class file Excel2007Custom.phpand:
  • rename the classes from PHPExcel_Writer_Excel2007 will become PHPExcel_Writer_Excel2007Custom
  • change the array $writerPartsArray value in the constructor.

from:

---

    $writerPartsArray = array(
        'stringtable'   => 'PHPExcel_Writer_Excel2007_StringTable',
        'contenttypes'  => 'PHPExcel_Writer_Excel2007_ContentTypes',
        'docprops'      => 'PHPExcel_Writer_Excel2007_DocProps',
        'rels'          => 'PHPExcel_Writer_Excel2007_Rels',
        'theme'         => 'PHPExcel_Writer_Excel2007_Theme',
        'style'         => 'PHPExcel_Writer_Excel2007_Style',
        'workbook'      => 'PHPExcel_Writer_Excel2007_Workbook',
        'worksheet'     => 'PHPExcel_Writer_Excel2007_Worksheet',
        'drawing'       => 'PHPExcel_Writer_Excel2007_Drawing',
        'comments'      => 'PHPExcel_Writer_Excel2007_Comments',
        'chart'         => 'PHPExcel_Writer_Excel2007_Chart',
        'relsvba'       => 'PHPExcel_Writer_Excel2007_RelsVBA',
        'relsribbonobjects' => 'PHPExcel_Writer_Excel2007_RelsRibbon'
     );

to:

---

    $writerPartsArray = array(
        'stringtable'   => 'PHPExcel_Writer_Excel2007Custom_StringTable',
        'contenttypes'  => 'PHPExcel_Writer_Excel2007Custom_ContentTypes',
        'docprops'      => 'PHPExcel_Writer_Excel2007Custom_DocProps',
        'rels'          => 'PHPExcel_Writer_Excel2007Custom_Rels',
        'theme'         => 'PHPExcel_Writer_Excel2007Custom_Theme',
        'style'         => 'PHPExcel_Writer_Excel2007Custom_Style',
        'workbook'      => 'PHPExcel_Writer_Excel2007Custom_Workbook',
        'worksheet'     => 'PHPExcel_Writer_Excel2007Custom_Worksheet',
        'drawing'       => 'PHPExcel_Writer_Excel2007Custom_Drawing',
        'comments'      => 'PHPExcel_Writer_Excel2007Custom_Comments',
        'chart'         => 'PHPExcel_Writer_Excel2007Custom_Chart',
        'relsvba'       => 'PHPExcel_Writer_Excel2007Custom_RelsVBA',
        'relsribbonobjects' => 'PHPExcel_Writer_Excel2007Custom_RelsRibbon'
     );

• And then editing PHPExcel_Writer_Excel2007Custom_Theme class $_colourScheme class property
• In the end I called the class writer like so:

---

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007Custom');

回答4:

Just took some hours to have a look on that -> change the color of the graphs.

Open the files Theme.php \PHPExcel\Classes\PHPExcel\Writer\Excel2007\Theme.php

At the bottom, you will find:

private function writeColourScheme($objWriter)
{
    foreach (self::$colourScheme as $colourName => $colourValue) {
        $objWriter->startElement('a:'.$colourName);

            $objWriter->startElement('a:srgbClr');
                $objWriter->writeAttribute('val', $colourValue);
            $objWriter->endElement();

        $objWriter->endElement();
    }
}

Instead, You will have to place this:

private function writeColourScheme($objWriter)
{
    $ci = 0;
    $colorNDX=array();
    $colorNDX[0] = "a09a9a";
    $colorNDX[1] = "1b1b1b";
    $colorNDX[2] = "350d0d";
    $colorNDX[3] = "ff0000";
    $colorNDX[4] = "b9a8a8";
    $colorNDX[5] = "a09a9a";
    $colorNDX[6] = "ff0000";
    $colorNDX[7] = "a09a9a";
    $colorNDX[8] = "1b1b1b";
    $colorNDX[9] = "ff0000";
    $colorNDX[10] = "1b1b1b";

    foreach (self::$colourScheme as $colourName => $colourValue) {

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

            $ci++;
        $objWriter->endElement();
    }
}

Hope this work for you :-)

回答5:

The current release: PHPExcel 1.7.9 doesn't allow to do anything you want.

回答6:

In the piecharts I found that PHPExcel write only 1 datapoint from serie, number 3 (why not 5,or 1, I don't know =) ), therefore if you want to customize colors of pies, you have to edit file Classes/PHPExcel/Writer/Excel2007/Charts.php in function _writePlotGroup

//Getting datapoints and  loop around $objWrite->startElement(c:dPt) 
 $plotSeriesValues = $plotGroup->getPlotValuesByIndex($plotSeriesRef);
if (($groupType == PHPExcel_Chart_DataSeries::TYPE_PIECHART) ||
                    ($groupType == PHPExcel_Chart_DataSeries::TYPE_PIECHART_3D) ||
                    ($groupType == PHPExcel_Chart_DataSeries::TYPE_DONUTCHART)) {


foreach($plotSeriesValues->getDataValues() as $plotSeriesKey => $plotSeriesValue) {
...
   /*instead of $objWriter->writeAttribute('val', 3); put after
 $objWriter->startElement('c:dPt');
                        $objWriter->startElement('c:idx');*/
$objWriter->writeAttribute('val', $plotSeriesKey);
//according to previous answer, find the color of pie by index of datapoint in colorNDX

$objWriter->writeAttribute('val',$colorNDX[$plotSeriesKey]);