CODEWITHSUNDEEP
chartsphpexcel
IIIOXIII
IIIOXIII

Reputation: 1010

PHPExcel - Y-Axis overlaps data chartarea

I am having a problem with the y-axis label positioning in PHPExcel.

When I create a chart, the y-axis labels are not appearing to the left of the plot area, they are always overlapping the plot area, starting at category 4 instead of 0.

I am assuming the issue is being caused by my category-x-axis values and Excel not being able to determine where to place the y-axis relative to the data. My category (x-axis) values are in string form:

yyyy, i.e. 1980 1981 1982 1983 etc.

And here is my PHPExcel chart creation code, all working except for the y-axis positioning.

$labels = array(
    new PHPExcel_Chart_DataSeriesValues('String', "'Report Summary'!C1", null, 1),
    new PHPExcel_Chart_DataSeriesValues('String', "'Report Summary'!D1", null, 1)
);
$chrtCols = "'Report Summary'!B2:B$rowNum";
$chrtVals = "'Report Summary'!C2:C$rowNum";
$chrtVals2 = "'Report Summary'!D2:D$rowNum";
$periods = new PHPExcel_Chart_DataSeriesValues('Number', $chrtCols, null, $rowNum-1);
$values = new PHPExcel_Chart_DataSeriesValues('Number', $chrtVals, null, $rowNum-1);
$values2 = new PHPExcel_Chart_DataSeriesValues('Number', $chrtVals2, null, $rowNum-1);
$series = new PHPExcel_Chart_DataSeries(
    PHPExcel_Chart_DataSeries::TYPE_AREACHART,       // plotType
    PHPExcel_Chart_DataSeries::GROUPING_STANDARD,  // plotGrouping
    array(0,1),                                       // plotOrder
    $labels,                                        // plotLabel
    array($periods,$periods),                                // plotCategory
    array($values,$values2)                                  // plotValues
);
$series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
$layout = new PHPExcel_Chart_Layout();

$plotarea = new PHPExcel_Chart_PlotArea($layout, array($series));
$chart = new PHPExcel_Chart('sample', null, null, $plotarea);
$chart->setTopLeftPosition('A1', 24, 24);
$chart->setBottomRightPosition('B18', -24);
$actSheet->addChart($chart);

Is there a setting to tell the y-axis to cross the x-axis at category 0 rather than it automatically selecting the wrong intersection? Or is there a way I can alter the source data to make it automatically select the correct intersection?

enter image description here EDIT: A 'possible' workaround may be to simply set the y-axis Line Color to "no Line", and the y-axis label display to "low" rather than "nextTo", however, I do not think PHPExcel has the ability to alter axis Line Color settings at this point. Or does it?

Upvotes: 1

Views: 2653

Answers (2)

Rainer Krauss
Rainer Krauss

Reputation: 83

There is a bug at least in my version of PHPExcel in PHPExcel/Classes/Writer/Excel2007/Chart.php

Find:

if ($id1 > 0) {
    $objWriter->startElement('c:crossAx');
        $objWriter->writeAttribute('val', $id2);
    $objWriter->endElement();

and change the $id2 to $id1:

if ($id1 > 0) {
    $objWriter->startElement('c:crossAx');
        $objWriter->writeAttribute('val', $id1);
    $objWriter->endElement();

The axis should then be correct.

Upvotes: 5

IIIOXIII
IIIOXIII

Reputation: 1010

I am still quite uncertain as to why my y-axis is not appearing to the left of the plot area and would be happy to learn the correct fix to my problem rather than to simply work-around it, however, to enable the above workaround, I made a few code changes to: 

PHPExcel/Classes/Writer/Excel2007/Chart.php

After line 541:

$objWriter->startElement('c:valAx');

I added:

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

which removed the y-axis border. Then I changed lines 616ish-620ish from:

$objWriter->startElement('c:tickLblPos');
    $objWriter->writeAttribute('val',"nextTo");
$objWriter->endElement();

to:

$objWriter->startElement('c:tickLblPos');
    $objWriter->writeAttribute('val', "low");
$objWriter->endElement();

to reposition the y-axis to the left of the plot area.

Though this does not solve the initial issue of the axis not appearing where it should, I as you can see by the image attached below of the chart after implementing the changes to chart.php, the y axis now "appears" to be in the correct position.

enter image description here

PS. This workaround will affect all charts which use the same chart.php file, changing the y-axis label positioning to "low" and removing the y-axis line.

Upvotes: 3

Related Questions