CODEWITHSUNDEEP
excelchartsphpspreadsheet
Marty Westra
Marty Westra

Reputation: 11

PhpSpreadsheet and Excel Charts, Y Axis in wrong spot

I'm trying to create a single series bar or line chart in Excel using PhpSpreadsheet. I can't get the Y Axis in the right spot. Whether it's a line or bar chart, the Y Axis shows up at random spots in the chart. Here's my code, taken primarily from the sample code.

    //  Set the Labels for each data series we want to plot
    $label=$sheetname."!\$H\$1";
    $dataSeriesLabels = [
        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, $label, null, 1),
    ];

    //  Set the X-Axis Labels
    $xrange=$sheetname."!\$C\$2:\$C\$".$lastrow;
    $xAxisTickValues = [
        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, $xrange, null, $lastrow),
    ];

    //  Set the Data values for each data series we want to plot
    $data=$sheetname."!\$H\$2:\$H\$".$lastrow;
    $dataSeriesValues = [
        new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, $data, null, $lastrow),
    ];

    //  Build the dataseries
    $series = new DataSeries(
        DataSeries::TYPE_BARCHART,   // plotType
        DataSeries::GROUPING_CLUSTERED,  // plotGrouping
        range(0, count($dataSeriesValues) - 1),     // plotOrder
        $dataSeriesLabels,                // plotLabel
        $xAxisTickValues,               // plotCategory
        $dataSeriesValues               // plotValues
    );

    //  Set the series in the plot area
    $plotArea = new PlotArea(null, array($series));

    //  Set the chart legend
    $legend = new Legend(Legend::POSITION_RIGHT, null, false);
    $title = new Title('Labor');
    $xAxisLabel = new Title('Part Number');
    $yAxisLabel = new Title('Minutes');

    //  Create the chart
    $chart = new Chart(
        'chart1',   // name
        $title,     // title
        $legend,    // legend
        $plotArea,    // plotArea
        true,     // plotVisibleOnly
        0,        // displayBlanksAs
        $xAxisLabel,     // xAxisLabel
        $yAxisLabel   // yAxisLabel
    );
    //  Set the position where the chart should appear in the worksheet
    $chart->setTopLeftPosition('A17');
    $chart->setBottomRightPosition('N34');

    //  Add the chart to the worksheet
    $sheet->addChart($chart);

The above code generates the following chart: Example

I have the same issue with a line graph as well. How can I get the Y Axis values to move to their proper spot on the left side?

Upvotes: 0

Views: 2379

Answers (1)

Marty Westra
Marty Westra

Reputation: 11

I found the answer in some PHPExel documentation. I just added an X and Y Axis style.

    $yaxis = new Axis();
    $xaxis = new Axis();
    $yaxis->setAxisOptionsProperties('low', null, null, null, null, null, -20, 20, null, null);
    $yaxis->setLineParameters('FFFFFF',100,Axis::EXCEL_COLOR_TYPE_ARGB);
    $xaxis->setAxisOptionsProperties('low', null, null, null, null, null, 0, 0, null, null);

Then added the values to my chart

    //  Create the chart
    $chart = new Chart(
        'chart1',   // name
        $title,     // title
        $legend,    // legend
        $plotArea,    // plotArea
        true,     // plotVisibleOnly
        0,        // displayBlanksAs
        $xAxisLabel,     // xAxisLabel
        $yAxisLabel,   // yAxisLabel
        $yaxis,
        $xaxis
    );

PHPExcel Documentation

Upvotes: 1

Related Questions