PHPExcel Scatterplot: How to Plot Data With Irregular Intervals

Question

PHPExcel offers a very simple and useful example for how to create an Excel file containing a simple scatterplot based on a small array of data. However, the problem is that this example uses regularly-spaced "string" values (Q1 - Q4) for its x-axis.

What I am trying to do is create a scatter plot with irregularly-spaced numerical x-axis values, so I have modified the code like this:

$objWorksheet->fromArray(
    array(
        array('',   2010,   2011,   2012),
        array('0',   12,   15,      21),
        array('10',   56,   73,     86),
        array('30',   52,   61,     69),
        array('70',   30,   32,     0),
    )
);

$dataseriesLabels = array(
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$1', NULL, 1),   //  2010
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$1', NULL, 1),   //  2011
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$1', NULL, 1),   //  2012
);

$xAxisTickValues = array(
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$A$2:$A$5', NULL, 4),  //  0 to 70
);

$dataSeriesValues = array(
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$5', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$5', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$2:$D$5', NULL, 4),
);

When I run this code, the excel file that I get looks like this:

Notice how the first data series (2010) plots properly, but the other two do not. I also noticed that if you select the 2010 data series, it does, indeed have both y-values and x-values defined:

However, when you click on any of the other data sets, you can see that only the y-values are defined:

I feel like this is a relatively simple oversight on my part, but sadly, I have been unable to find the problem. I am worried that this type of thing may require a hack in the PHPExcel.php file but I wanted to run this by the community first before messing around in the source files.

Thanks a lot in advance for any advice...

ChiralCenter ChiralCenter · Accepted Answer · 2016-02-15T16:54:50

Figured it out!

Turns out it really was something simple, but PHPExcel really needs to update their example files.

I changed this:

$xAxisTickValues = array(
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$A$2:$A$5', NULL, 4),  //  0 to 70
);

To this:

$xAxisTickValues = array(
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$A$2:$A$5', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$A$2:$A$5', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$A$2:$A$5', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$A$2:$A$5', NULL, 4)
);

In other words, the array that contains x-axis series objects needs to be the same length as the array that contains the y-axis series objects. The confusing this is that the x-axis array is populated with multiple identical x-axis objects.

A simple problem, but a frustrating one.

Cheers!

PHPExcel Scatterplot: How to Plot Data With Irregular Intervals

1 Answers