Bar Chart Axis Formatting

[Adam Whitaker]

I'm trying to format x-axis and data label text within a column chart. Starting with x-axis text and using the example at http://openpyxl.readthedocs.io/en/default/charts/bar.html as a basis. Executing the below works fine.

from openpyxl import Workbook

from openpyxl.chart import BarChart, Series, Reference

wb = Workbook(write_only=True)

ws = wb.create_sheet()

rows = [

    ('Number', 'Batch 1', 'Batch 2'),

    (2, 10, 30),

    (3, 40, 60),

    (4, 50, 70),

    (5, 20, 10),

    (6, 10, 40),

    (7, 50, 30),

]

for row in rows:

    ws.append(row)

chart1 = BarChart()

chart1.type = "col"

chart1.style = 10

chart1.title = "Bar Chart"

chart1.y_axis.title = 'Test number'

chart1.x_axis.title = 'Sample length (mm)'

data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)

cats = Reference(ws, min_col=1, min_row=2, max_row=7)

chart1.add_data(data, titles_from_data=True)

chart1.set_categories(cats)

chart1.shape = 4

ws.add_chart(chart1, "A10")

wb.save("bar.xlsx")

Looking through the openpyxl documentation, sample charts, and viewing the chart XML leads me to believe that inserting the below (above the ws.add_chart function) should work.

from openpyxl.chart.text import RichText

from openpyxl.drawing.text import RichTextProperties, Paragraph, ParagraphProperties, CharacterProperties

chart1.x_axis.txPr = RichText(

    bodyPr=RichTextProperties(),

    p=[Paragraph(

            pPr=ParagraphProperties(

                defRPr=CharacterProperties(sz=1200, b=True)),

            endParaRPr=CharacterProperties()

        )]

)

This results in a file that Excel won't open because "Excel could not open bar.xlsx because some content is unreadable. Do you want to open and repair this workbook?" If you repair the file it removes the chart. I've been able to successfully manipulate the graphicalProperties for the x_axis objects (fill, outline, etc.). Not having luck anywhere when trying to format rich text. Any help would be appreciated. Thanks.

python 2.7

openpyxl 2.4.4

[Charlie Clark]

Am .03.2017, 21:25 Uhr, schrieb Adam Whitaker <apwhi...@gmail.com>:

> I'm trying to format x-axis and data label text within a column chart.
> Starting with x-axis text and using the example
> at http://openpyxl.readthedocs.io/en/default/charts/bar.html as a basis.
> Executing the below works fine.
>

> from openpyxl.chart.text import RichText
> from openpyxl.drawing.text import RichTextProperties, Paragraph,
> ParagraphProperties, CharacterProperties
>
> chart1.x_axis.txPr = RichText(
> bodyPr=RichTextProperties(),
> p=[Paragraph(
> pPr=ParagraphProperties(
> defRPr=CharacterProperties(sz=1200, b=True)),
> endParaRPr=CharacterProperties()
> )]
> )
>
> This results in a file that Excel won't open because "Excel could not
> open
> bar.xlsx because some content is unreadable. Do you want to open and
> repair
> this workbook?" If you repair the file it removes the chart. I've been
> able
> to successfully manipulate the graphicalProperties for the x_axis objects
> (fill, outline, etc.). Not having luck anywhere when trying to format
> rich
> text. Any help would be appreciated. Thanks.

First of all I'll take my hat off to you for delving so deep into this!
When I was working on Charts I decided to stop once all the basic
functionality was available. I think the problem you've run into is that
one of the objects should be a sequence but is currently configured to
take only one item. I think this would be apparent if you compared the
source of the Excel file with that created by openpyxl. Hopefully this
would be fairly easy and painless to fix and involve in changing the
relevant descriptor. PR including docs welcome!

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