Customizing the x-axis labels

[schacht...@gmail.com]

This is extended from a previous SO discussion I had with Charlie. 

http://stackoverflow.com/questions/31305309/possible-to-set-the-x-values-used-in-openpyxl

Basically I want my chart to output the delay times as the x axis. I am having issues figuring out how to make this work.

chart_object= charts.LineChart()
x_axis= charts.Reference(overall_stats_sheet, (11,2), (11, 5))
chart_object.set_categories(x_axis)

[Charlie Clark]

Am .07.2015, 18:38 Uhr, schrieb <schacht...@gmail.com>:

> chart_object= charts.LineChart()
> x_axis= charts.Reference(overall_stats_sheet, (11,2), (11, 5))
> chart_object.set_categories(x_axis)

First of all,

hg up 2.3 to use the new charting facilities. The API is similar but not
entirely backwards compatible: everything related to charts was rewritten.

Assuming Delay = A11:D11

from openpyxl.chart import LineChart, Reference, Series

chart = LineChart()
timings = Reference(overall_stats_sheet, min_row=11, min_col=2, max_col=4)
# avoid calling it x_axis because the chart_object has an x-axis

erasure = Reference(overall_stats_sheet, min_row=15, min_col=1, max_col=4)
chart.series.append(Series(erasure, title_from_data=True)
# rinse and repeat for the other rows

chart.set_categories(timings) # this must be done after the series are
added.

Save the file, crack open a beer… Send me a pull request with the
documentation of how to do a line chart.

Charlie

PS. I was looking at whether you can have non-contiguous values in a
series. I suspect that it might be possible if you are prepared to work
with values as opposed to cell references but it's essentially
undocumented. Each series has one, and one only, values references. We
only work with the reference to cells in a worksheet and evaluate the
values but there are other possibilities. The primitives to do this are in
the source.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[schacht...@gmail.com]

Hey Charlie, thanks a lot for the great support, I will implement your suggestions ASAP. 

The reason I downgraded back to the older version is because I was getting a million lines of (get_highest_row is deprecated, use max_row).

I try converted all my statements to next_row = currentSheet.max_row() but figured I was using it incorrectly because it failed to work correctly. 

[Charlie Clark]

Am .07.2015, 19:12 Uhr, schrieb <schacht...@gmail.com>:

> Hey Charlie, thanks a lot for the great support, I will implement your
> suggestions ASAP.

> The reason I downgraded back to the older version is because I was
> getting
> a million lines of (get_highest_row is deprecated, use max_row).

Don't run before you can walk… unless you're called Moist von Lipwig, that
is! ;-)

> I try converted all my statements to next_row = currentSheet.max_row()

It's a property ("getters" and "setters" are ugly)

next_row = currentSheet.max_row

FWIW in 2.2 you can just do Series(values=values, xvalues=xvalues)

This syntax was dropped because it's not flexible enough, 2.3's
title_from_data knows whether it's working with rows or columns and you
only have to set the values for the axis once as opposed to doing it for
every series.

[schacht...@gmail.com]

Ah, thanks. Max row and max col working great on 2.3b1.

    chart.set_categories(timings) raises a syntax error, though.

    chart = LineChart()


    timings = Reference(overall_stats_sheet2, min_row=11, min_col=2, max_col=4)


    erasure = Reference(overall_stats_sheet2, min_row=15, min_col=1, max_col=4)


    chart.series.append(Series(erasure, title_from_data=True)


    chart.set_categories(timings)


    overall_chart_sheet.add_chart(chart, "E34")

[schacht...@gmail.com]

Ah, syntax error, think it's good now! Thanks Charlie! Will see if I can finalize this bad boy.

[oneil...@gmail.com]

Is there any way to extend the x-axis longer than the number of points? For instance I have the attached graph, the x--axis is set using the following

weeks = Reference(ws, min_col = 2, max_col = 2, min_row = 2, max_row = 20 )
c1.set_categories(weeks)

However, currently I only have 5 data points, up to week 38.  I am trying to get the x-axis to show from 34-52 weeks. 

[jimc...@googlemail.com]

Use the axis scaling factors. e.g.:

    chart1.x_axis.scaling.min = 34

    chart1.x_axis.scaling.max = 52

Version 2.4.9

Found this late from a random google search on something else...