Excel, PHPExcel, Scatter plot (XY) and Highcharts. The structure of scatter plots in Excel

Question

This question is part excel and charting question and part programming question.

Im using PHPExcel to convert chart data from a excel sheet into json. Im then using this json to convert it's data to a highcharts compatible datasource.

So far PHPExcel has charts stored with three distinct data types, labels, categories and values. As far as i can understand values are the dataseries in the charts, and categories are the x-axis points.

However, when exporting a scatter plot (xy) from excel, categories and values seems to be flipped. Values is an array with where each child element contains the same data as it's siblings. (producing incorrect graphs) while categories now differ in value.

so where other graphs would produce data like the following:

{
    "data": [{
            "label": "V3",
            "category": "T4:T43",
            "value": "V4:V43"
        }, {
            "label": "W3",
            "category": "T4:T43",
            "value": "W4:W43",
        }
    ],
    "type": "column"
}

a scatter plot would produce.

{
    "title": "",
    "data": [{
            "label": "AC1",
            "category": "A$2:AC98",
            "value": "AB2:AB98"
        }, {
            "label": "AD1"
            "category": "AD2:AD98"
            "value": "AB2:AB98"
        }, {
            "label": "AE1",
            "category": "AE2:AE98",
            "value": "AB2:AB98"
        }
    ],
    "type": "scatter"
}

Notice the difference AND equality in category and value keys.

In the column chart data the "value" is different between the dataseries. but category is equal.

In the scatter chart data the category is different between the dataseries, but value is equal, the oposite of the column chart.

So what is happening here?

Is a scatter plot defined in this way and i need to take necessary measures in order to convert this data? or Is this a bug in PHPExcel?

What is the solution? simply exchange "value" with "category" when "type" is "scatter"?

Any insight on this is greatly appreciated!

Sebastian Bochan Sebastian Bochan · Accepted Answer · 2013-04-29T13:32:03

I advice to familiar with chart configuration, how it should looks like. Categories should be placed in xAxis http://api.highcharts.com/highcharts#xAxis.categories.

Morever you can use CSV file to get values for data (http://docs.highcharts.com/#preprocessing)

What is important, values should be number type, not string.

Excel, PHPExcel, Scatter plot (XY) and Highcharts. The structure of scatter plots in Excel

2 Answers