Re: [openpyxl-users] Display all categories on category-axis for bar chart

[Charlie Clark]

Hi Beth,

> This is the first time I've used openpyxl and I've been through the
> documentation, but I can't find a clear answer.

Well, that's partly because there isn't one.

> I have data with 14 categories (the types of fruit) and I wish to display
> this data in a bar graph. As you can see in the image below, the data is
> fine, but I can't get all the labels on the category-axis to display and
> it is much less useful to have a chart if I can't tell what bar
> represents
> what.

> <https://lh3.googleusercontent.com/-ySxqGu10Bjg/Vqk4X3MJIGI/AAAAAAAAAUI/x3CpiVIIpvc/s1600/openpyxl-question-axis-labels.PNG>

Unless otherwise specified, Excel will decide which labels to display and
how in the space available.

> I have tried setting a Manual Layout for the chart, to increase the
> height of the chart and the box that it is in, but neither of those
> appear to do
> anything.

I'd be surprised if they have no effect. The height makes a big difference
when I run the script. What program are you using to look at the file?

You can also force this with the following:

chart1.x_axis.tickLblSkip = 1

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

[Beth C]

Hi Charlie,

I was playing with my code some more last evening and I found that if I just set the height and width of the actual chart that would work.

So instead of doing 

    barchart.layout = Layout(

        ManualLayout(

            # x=0.25, y=0.25,

            h=10.0,  w=20.0,

            xMode="edge",

            yMode="edge",

        )

    )

I found that doing

    barchart.height = 10

    barchart.width = 20

would make the chart big enough that all the labels would be shown.

I have been using LibreOffice Calc to view the files.

I very much appreciate your response.

Thank you!

Beth

[Charlie Clark]

Am .01.2016, 16:32 Uhr, schrieb Beth C <ecal...@nd.edu>:

> Hi Charlie,
>
> I was playing with my code some more last evening and I found that if I
> just set the height and width of the actual chart that would work.

Good to know.

> So instead of doing
>
> barchart.layout = Layout(
> ManualLayout(
> # x=0.25, y=0.25,
> h=10.0, w=20.0,
> xMode="edge",
> yMode="edge",
> )
> )

Yeah, that stuff isn't very intuitive. Thanks to Thomas we have at least
documented it but it could probably do with some more abstract coverage of
what refers to what. Well, actually the OOXML spec should have that. :-/

> I found that doing
> barchart.height = 10
> barchart.width = 20

For reference: those values approximate to cm on a Windows screen. If you
want reliably big charts then just use a chartsheet: warning this will
currently only work with one worksheet and one chartsheet.

ws = wb.create_sheet("data")
ws.sheet_state = "hidden"
cs = wb.create_chartsheet("chart")
cs.add_chart(…)
wb._active_sheet_index = 1

> would make the chart big enough that all the labels would be shown.
>
> I have been using LibreOffice Calc to view the files.

You'll have to learn to live with the slight differences between the apps.

[Beth C]

This is a different question:

https://openpyxl.readthedocs.org/en/2.4/charts/bar.html#vertical-horizontal-and-stacked-bar-charts (more specifically: https://openpyxl.readthedocs.org/en/2.4/_images/bar.png)

Here in the image of the sample charts, they all have a solid white background meaning the spreadsheet cells aren't visible behind the labels and title. On mine, there is no such box, which makes reading labels and titles difficult. Is there a way to add this or do I have to calculate how many cells the entire chart (including labels and title) covers and then merge all those cells to get the same effect?

On Thursday, January 28, 2016 at 5:07:28 AM UTC-5, Charlie Clark wrote:

 

[Charlie Clark]

Am .01.2016, 17:25 Uhr, schrieb Beth C <ecal...@nd.edu>:

> Here in the image of the sample charts, they all have a solid white
> background meaning the spreadsheet cells aren't visible behind the labels
> and title. On mine, there is no such box, which makes reading labels and
> titles difficult. Is there a way to add this or do I have to calculate
> how many cells the entire chart (including labels and title) covers and
> then
> merge all those cells to get the same effect?

I suspect you'll have to look at the setting of your version of
LibreOffice. It looks like something is setting the canvas to 100 %
transparency. This doesn't happen on my local copies of Excel, LibreOffice
or OpenOffice unless I set the chart to be the background and I've no idea
how to do this programatically.