Chart where y-axis doesn't start at zero & Position chart [openpyxl 2.3]

[haraldn...@gmail.com]

I'm using Openpyxl-2.3.

I want to create a chart where the y-axis doesn't start at zero.

Either to have it be automatically fitted to the data, or manually specify y-axis values in the range 25 to 45. I tried this: (as described by Charlie Clark here, http://stackoverflow.com/questions/26224513/how-to-set-limts-on-x-and-y-axis-openpyxl-charts)

        chart.auto_axis = False

        chart.y_axis.min = 25

        chart.y_axis.max = 45

to no avail. Setting auto_axis = False doesn't seem to do anything at all.

I also want to specify the positioning of the chart in the worksheet. In 2.2 I used chart.drawing.left, chart.drawing.width and chart.drawing.height to specify position and sizes, but these don't work in 2.3.

[Charlie Clark]

Am .10.2015, 22:14 Uhr, schrieb <haraldn...@gmail.com>:

> I'm using Openpyxl-2.3.
>
> I want to create a chart where the y-axis doesn't start at zero.
>
> Either to have it be automatically fitted to the data, or manually
> specify y-axis values in the range 25 to 45. I tried this: (as
> described by Charlie Clark here,
> http://stackoverflow.com/questions/26224513/how-to-set-limts-on-x-and-y-axis-openpyxl-charts)
>
> chart.auto_axis = False
> chart.y_axis.min = 25
> chart.y_axis.max = 45
>
> to no avail. Setting auto_axis = False doesn't seem to do anything at
> all.

As noted in the release notes for openpyxl 2.3.0 the charting code was
*completely* rewritten. It is now both more flexible and complete by being
closer to the specification. Unfortunately, this also meant that the
existing API could not be preserved because it did not follow the
specification. Auto-scaling in openpyxl, which as my post noted, never
worked too well anyway, was one of the "features" that were dropped as a
result.

We haven't been able to document all the features now available. However,
I think that what you want can be achieved quite easily:

chart.y_axis.crosses = 'min'

If not you're best looking at the source of a chart you'd like and
comparing the values of the relevant "valAx". It will look something like
this:

<valAx>
<axId val="100"></axId>
<scaling>
<orientation val="minMax"></orientation>
</scaling>
<axPos val="l" />
<majorGridlines />
<crossAx val="10" />
</valAx>

Do let us know once you've figured it out so that we can update the
documentation. Better still: submit a PR of the documentation.

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

[Thomas Nygårds]

What Charlie wrote:
 

chart.y_axis.crosses = 'min'

 
together with

chart.y_axis.scaling.min = pc.vaxis.min
chart.y_axis.scaling.max = pc.vaxis.max

will enable you to control the axis limits.

Manual positioning the chart in the plot area can be done with

chart.plot_area.layout = openpyxl.chart.layout.Layout(manualLayout=openpyxl.chart.layout.ManualLayout(yMode='edge', xMode='edge', x=my_x, y=my_y, h=my_height, w=my_width))

Cheers
Thomas

[Thomas Nygårds]

chart.y_axis.scaling.min = pc.vaxis.min
chart.y_axis.scaling.max = pc.vaxis.max

Removing my cryptic objects...

chart.y_axis.scaling.min = my_min
chart.y_axis.scaling.max = my_max

[Harald Nordgren]

Thanks Thomas!

chart.y_axis.scaling.min = my_min
chart.y_axis.scaling.max = my_max

worked like a charm. chart.y_axis.crosses = 'min' wasn't necessary, and so far as I can tell does nothing.

About the chart positioning, I was asking about how to position the chart box (includes axes, title and legend) within the sheet. Using ManualLayout, seems to aim at positioning the grid and graphs within the chart box. Although the code posted doesn't do anything, in 2.3 at least. I tried some reasonable values and some absurdly large. Nothing happens.

While I have your attention. Is there any way to turn off the vertical gridlines for a ScatterChart? And also, is there a way to display the data as smoothed lines?

--
You received this message because you are subscribed to a topic in the Google Groups "openpyxl-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openpyxl-users/QR8I42obeqk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to openpyxl-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Charlie Clark]

> About the chart positioning, I was asking about how to position the chart
> box (includes axes, title and legend) within the sheet. Using
> ManualLayout,
> seems to aim at positioning the grid and graphs within the chart box.
> Although the code posted doesn't do anything, in 2.3 at least. I tried
> some
> reasonable values and some absurdly large. Nothing happens.

Positioning can be tricky – see the earlier discussion between Thomas and
myself – but the default is you set the top left-corner of the chart to
the cell when you add it to the worksheet. You can then also set the
height and width approximately in cm. You'll have to experiment with this
to see what works best for you. From the source code:

anchor = "E15" # default anchor position
width = 15 # in cm, approx 5 rows
height = 7.5 # in cm, approx 14 rows

> While I have your attention. Is there any way to turn off the vertical
> gridlines for a ScatterChart? And also, is there a way to display the
> data
> as smoothed lines?

See the majorGridlines and minorGridLines elements of the relevant axes.

[Harald Nordgren]

Thanks!

Anchoring works great.

setting chart.x_axis.majorGridlines = None removes the vertical gridlines.

I smoothed the graph using series.smooth = True.

[Harald Nordgren]

Another question.

I can set the title of a chart axis with chart.y_axis.title, is there any way to change the text size of the title?

[Charlie Clark]

Am .10.2015, 16:14 Uhr, schrieb Harald Nordgren <haraldn...@gmail.com>:

> I can set the title of a chart axis with chart.y_axis.title, is there any
> way to change the text size of the title?

This is currently only theoretically possible at the moment. The way that
title's are implemented allows "subatomic" formatting for which it is
difficult to create a reasonable interface for client code.

If you look at the source XML of any chart with a formatted title you'll
see how it breaks down into something like:

title.tx.rich.paragraphs[0].r.t # which is OOXML's verbosity at it's limit

You can probably create something similar to the title_maker factory
function in the chart/title.py module to help you but you'll have to work
your way through the specification for drawings (§21.1.2) as well as
charts. The code for the text primitives should work but there's no
guarantee for anything than the most simple stuff works.

[Thomas Nygårds]

Hi Charlie.

Any plans on making this work in the standard package?

Even if it would require a command like:

ch.title.tx.rich.p[0].r.rPr.sz=5

(which can already be improved by aliases to:)

ch.title.text.rich.paragraphs[0].r.properties.sz=5

(which can be improved further with more aliases...)

I think It would be appreciated by many!

Regards
Thomas

[Charlie Clark]

Am .02.2016, 17:25 Uhr, schrieb Thomas Nygårds <thomas...@gmail.com>:

> Hi Charlie.
>
> Any plans on making this work in the standard package?
>
> Even if it would require a command like:
> ch.title.tx.rich.p[0].r.rPr.sz=5
> (which can already be improved by aliases to:)
> ch.title.text.rich.paragraphs[0].r.properties.sz=5
> (which can be improved further with more aliases...)

I think that's already the case it's just fucking impossible™ to work
with! ;-) But I did test reading with this yesterday (primitives for this
will be in 2.4 for the brave of heart but not the whole thing).

openpyxl.chart.title.title_maker will already convert a stream of text
into paragraphs. So I'm not sure what more you need. Supporting
"sub-atomic" nightmare in the API just isn't easy.

[Thomas Nygårds]

I think that's already the case it's just fucking impossible™ to work  
with! ;-) But I did test reading with this yesterday (primitives for this  
will be in 2.4 for the brave of heart but not the whole thing).

openpyxl.chart.title.title_maker will already convert a stream of text  
into paragraphs. So I'm not sure what more you need. Supporting  
"sub-atomic" nightmare in the API just isn't easy.

Ok. Sound like I need an upgrade when 2.4 is out then...
because running the code

ch = chart.ScatterChart()
ch.title = self.chart_title
ch.title.tx.rich.p[0].r.rPr.sz=5

in openpyxl 2.3.1 results in

<type 'exceptions.AttributeError'>: 'NoneType' object has no attribute 'sz'

Regards
Thomas

[Charlie Clark]

Am .02.2016, 11:09 Uhr, schrieb Thomas Nygårds <thomas...@gmail.com>:

>
>
>> I think that's already the case it's just fucking impossible™ to work
>> with! ;-) But I did test reading with this yesterday (primitives for
>> this
>>
>> will be in 2.4 for the brave of heart but not the whole thing).
>>
>> openpyxl.chart.title.title_maker will already convert a stream of text
>> into paragraphs. So I'm not sure what more you need. Supporting
>> "sub-atomic" nightmare in the API just isn't easy.
>>
>
> Ok. Sound like I need an upgrade when 2.4 is out then...
> because running the code
> ch = chart.ScatterChart()
> ch.title = self.chart_title
> ch.title.tx.rich.p[0].r.rPr.sz=5
>
> in openpyxl 2.3.1 results in

That's probably because no title has been set and there is nothing to have
any properties set: Excel will automatically add a title even on empty
charts. :-/

[Joseph Fox-Rabinovitz]

On Tuesday, February 23, 2016 at 5:31:58 AM UTC-5, Charlie Clark wrote:

Am .02.2016, 11:09 Uhr, schrieb Thomas Nygårds <thomas...@gmail.com>:

>
>
>> I think that's already the case it's just fucking impossible™ to work
>> with! ;-) But I did test reading with this yesterday (primitives for  
>> this
>>
>> will be in 2.4 for the brave of heart but not the whole thing).
>>
>> openpyxl.chart.title.title_maker will already convert a stream of text
>> into paragraphs. So I'm not sure what more you need. Supporting
>> "sub-atomic" nightmare in the API just isn't easy.
>>
>
> Ok. Sound like I need an upgrade when 2.4 is out then...
> because running the code
> ch = chart.ScatterChart()
> ch.title = self.chart_title
> ch.title.tx.rich.p[0].r.rPr.sz=5

As Charlie says, setting properties of the non-existent object is impossible. Try this instead:

from openpyxl.drawing.text import CharacterProperties
...
ch.title.tx.rich.p[0].r.rPr = CharacterProperties(sz=5, b=True)

 

[Thomas Nygårds]

Thank you Joseph!
But doing

...
ch.title="test1"

ch.title.tx.rich.p[0].r.rPr = CharacterProperties(sz=5, b=True)

ws.add_chart(ch, "A10")
wb.save("chart_title.xlsx")

in 2.3.4 will result in an corrupted xlsx file.

Any ideas here?

Regards
Thomas

[Charlie Clark]

Am .08.2016, 15:06 Uhr, schrieb Thomas Nygårds <thomas...@gmail.com>:

> Any ideas here?

Try with a 2.4 checkout. But you might want to create your own
"title_maker" function for this kind of thing.

[Thomas Nygårds]

Try with a 2.4 checkout.

Will do. Is there a final release date scheduled for 2.4? It has been in beta a while now, right?

Regards
Thomas

[Thomas Nygårds]

Try with a 2.4 checkout. But you might want to create your own  
"title_maker" function for this kind of thing.

Tested with a checkout of the tip of the 2.4 branch (c31ac9586459)
The following code results in a corrupted xlsx file that Excel repairs by removing the chart.

from openpyxl import Workbook, load_workbook
from openpyxl import chart
from openpyxl.drawing.text import CharacterProperties

wb = Workbook()
ws = wb.active

rows = [
    ['Size', 'Batch 1'],
    [2, 40],
    [3, 40],
    [4, 50],
    [5, 30],
    [6, 25],
    [7, 20],
]

for row in rows:
    ws.append(row)

ch = chart.ScatterChart()
ch.scatterStyle = "marker"
ch.title = "Scatter Chart"

ch.title.tx.rich.p[0].r.rPr = CharacterProperties(sz=5, b=True)

ch.style = 13
ch.x_axis.title = 'Size'
ch.y_axis.title = 'Percentage'
ch.x_axis.scaling.logBase = 10

xvalues = chart.Reference(ws, min_col=1, min_row=2, max_row=7)
values = chart.Reference(ws, min_col=2, min_row=1, max_row=7)
series = chart.Series(values, xvalues, title_from_data=True)
ch.series.append(series)

ws.add_chart(ch, "A10")

wb.save("chart_title.xlsx")

Regards
Thomas

[Charlie Clark]

Am .08.2016, 16:50 Uhr, schrieb Thomas Nygårds <thomas...@gmail.com>:

> ch.title.tx.rich.p[0].r.rPr = CharacterProperties(sz=5, b=True)

This may be because I'm not keen on making using a sequence here: I mean
that really is a horrible API (thanks, Microsoft). What does the validator
say?

[Thomas Nygårds]

I am sorry.
I cannot seem to install the validator linked on openpyxl.readthedocs.io (http://www.microsoft.com/en-us/download/details.aspx?id=30425).
It requires .NET 4 and already have .NET 4.5 installed (that is needed for other stuff)... Stupid!
The code I posted above should be complete enough for anyone having the validator installed to check it though...

Regards
Thomas

[jenseri...@gmail.com]

Use xlsxwriter instead. There you can set the size of axis title and font type

[Charlie Clark]

Am .09.2016, 16:48 Uhr, schrieb <jenseri...@gmail.com>:

> Use xlsxwriter instead. There you can set the size of axis title and font
> type

But you can't work with existing files…

[jenseri...@gmail.com]

That I do not need

[Charlie Clark]

Am .09.2016, 19:25 Uhr, schrieb <jenseri...@gmail.com>:

> That I do not need

So, why are you answering a question about it then?

[jenseri...@gmail.com]

I wanted to change size of axis title in a new chart and I cannot do it in openpyxl, so I changed to xlxswriter and I could do it. Adding a chart there the chart is born with no text or legend, That you have to specify. Afterwards I use openpyxl

[jenseri...@gmail.com]

To add, I can place legend inside chart. That i can do i xlsxwriter, but not in openpyxl

[jenseri...@gmail.com]

Also the PQ question 2015 was about creating a new chart. So therefore my comment. I'm using Python 3.5.2 and xlsxwiter works as a charm. Something to add to openpyxl

[Charlie Clark]

Am .10.2016, 15:55 Uhr, schrieb <jenseri...@gmail.com>:

> Also the PQ question 2015 was about creating a new chart. So therefore my
> comment. I'm using Python 3.5.2 and xlsxwiter works as a charm. Something
> to add to openpyxl

We look forward to your pull request.

[dvince...@gmail.com]

Here is what the validator says:

The attribute 'sz' has invalid value '5'.  The MinInclusinve constraint failed.  The value must be greater than or equal to 100

If you change the sz = 500, I get the following chart:

[Charlie Clark]

Am .11.2016, 15:11 Uhr, schrieb <dvince...@gmail.com>:

> Here is what the validator says:
> The attribute 'sz' has invalid value '5'. The MinInclusinve constraint
> failed. The value must be greater than or equal to 100

ch.title.tx.rich.p[0].r.rPr = CharacterProperties(sz=5, b=True)

A bit bizarre without more context but it does indeed seem that the
DrawingML does impose limits here:

<xsd:simpleType name="ST_TextFontSize">
<xsd:restriction base="xsd:int">
<xsd:minInclusive value="100"/>
<xsd:maxInclusive value="400000"/>
</xsd:restriction>
</xsd:simpleType>

Yet another area of the specification which uses different measurements to
the rest. The narrative does provide a little bit more information:

"""
Specifies the size of text within a text run. Whole points are specified
in increments of 100 starting with 100 being a point size of 1. For
instance a font point size of 12 would be 1200 and a font point size of
12.5 would be 1250. If this attribute is omitted, than the value in defRPr
should be used.
"""

Elsewhere pts are used (such as in text cells) or EMUs. At least the
conversion factor here is easy.

It should be noted, however, that openpyxl makes no pretence to provide a
full implementation of the DrawingML specification. We've concentrated on
providing reasonable for chart basics. If you want any more then you're on
your own, though it most things should be possible.

[Vince West]

Having played with the module for the last couple of days I agree that the api is terrible. However it seems like I can achieve almost anything and have had pretty good success with title formats, series line and marker formats, error bars, axis scaling, tick labels and gridlines. Openpyxl seems to create very nice files with abundant features.

One thing is escaping me though. I can't for the life of me figure out how to make a line have some transparency. Any direction on something like that?

--
You received this message because you are subscribed to a topic in the Google Groups "openpyxl-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openpyxl-users/QR8I42obeqk/unsubscribe.

To unsubscribe from this group and all its topics, send an email to openpyxl-users+unsubscribe@googlegroups.com.

[Charlie Clark]

Am .11.2016, 22:28 Uhr, schrieb Vince West <dvince...@gmail.com>:

> Having played with the module for the last couple of days I agree that
> the api is terrible.

You could almost get the idea that the OOXML was drawn up by different
teams that didn't talk to each other.

> However it seems like I can achieve almost anything and
> have had pretty good success with title formats, series line and marker
> formats, error bars, axis scaling, tick labels and gridlines. Openpyxl
> seems to create very nice files with abundant features.

Well, that's nice to hear.

> One thing is escaping me though. I can't for the life of me figure out
> how to make a line have some transparency. Any direction on something
> like
> that?

Best thing is to reverse engineer an existing file and see what Excel
generates. At some point I gave up trying to work out all the
possibilities: sometimes transparency is element in its own right,
sometimes it's an alpha channel of the colour.

For a better understanding of how the openpyxl code works you might want
to look at the talk I gave at PyCon France last year:
http://videos-2015.pycon.fr/023_-_Charlie_Clark_-_When_generated_code_makes_sense.html

[mud...@gmail.com]

Hi all, after a lot of research I was able to find a way to change the font of axis title and chart title, like this:

cp = CharacterProperties(sz=1200)

xtStr = u"日期（天）"

ytStr = u"使用率（%）"

lc.x_axis.title = ""

lc.y_axis.title = ""

xPara = [Paragraph(pPr=ParagraphProperties(defRPr=cp), r=RegularTextRun(t=s)) for s in xtStr.split("\n")]

yPara = [Paragraph(pPr=ParagraphProperties(defRPr=cp), r=RegularTextRun(t=s)) for s in ytStr.split("\n")]

lc.x_axis.title.tx.rich.paragraphs = xPara

lc.y_axis.title.tx.rich.paragraphs = yPara

[alexa...@gmail.com]

This is genius.  Thank you!