Categories in reverse order

Issue #556 resolved
Johan Abbors created an issue

Hi,

You mention that x and y axis are reversed for horizontal bar charts.

Would it be possible to to add an option to the bart charts to change the order upon rendering? In excel it's just a checkbox and the categories are reversed. See attached screen shots.

Comments (9)

  1. CharlieC

    Certainly don't want an option for this. We could do with a section on working with axes. "x" and "y" are spatial conventions that I find preferable to the typing that Excel uses: CategoryAxis, ValueAxis, DateAxis, SeriesAxis because they are conditional (a ScatterChart has an x and y but no CategoryAxis, unlike a LineChart which might use the same data…) but essentially we need a primer on how to control their use to suit your needs: each axis must have a unique axId. The different types have different defaults (10, 100, 500, 1000); axes are combined using the crossAx attribute (logic for this is undefined) and crosses = 'min', 'max', 'auto'.

    Contributions to the docs welcome!

  2. Johan Abbors reporter

    I was able to solve this easily using BarChart.

    Original code

    from openpyxl import Workbook
    from openpyxl.chart import BarChart, 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)
    
    horizontalBarChart = BarChart()
    horizontalBarChart.type = "bar"
    horizontalBarChart.style = 5
    values = Reference(ws, min_col=2, min_row=2, max_row=8, max_col=3)
    categories = Reference(ws, min_col=1, min_row=2, max_row=8)
    horizontalBarChart.add_data(values)
    horizontalBarChart.set_categories(categories)
    horizontalBarChart.shape = 4
    horizontalBarChart.legend = None
    ws.add_chart(horizontalBarChart, "E2")
    
    wb.save("ReverseCat.xlsx")
    

    Results in ...

    Screen Shot 2015-11-20 at 09.21.04.png

    And with the following patches ...

    diff -r 5b4034751f4c openpyxl/chart/__init__.py
    --- a/openpyxl/chart/__init__.py    Tue Nov 17 12:26:15 2015 +0100
    +++ b/openpyxl/chart/__init__.py    Fri Nov 20 09:17:29 2015 +0200
    @@ -1,6 +1,7 @@
     from __future__ import absolute_import
     # Copyright (c) 2010-2015 openpyxl
    
    +from .axis import Scaling
     from .area_chart import AreaChart, AreaChart3D
     from .bar_chart import BarChart, BarChart3D
     from .bubble_chart import BubbleChart
    
    --- ReverseCat.py   2015-11-20 09:25:40.000000000 +0200
    +++ ReverseCat.py   2015-11-20 09:19:36.000000000 +0200
    @@ -1,5 +1,5 @@
     from openpyxl import Workbook
    -from openpyxl.chart import BarChart, Reference
    +from openpyxl.chart import BarChart, Reference, Scaling
    
     wb = Workbook(write_only=True)
     ws = wb.create_sheet()
    @@ -17,6 +17,8 @@
         ws.append(row)
    
     horizontalBarChart = BarChart()
    +horizontalBarChart.x_axis.scaling = Scaling(orientation="maxMin")
    +horizontalBarChart.y_axis.axPos = 't'
     horizontalBarChart.type = "bar"
     horizontalBarChart.style = 5
     values = Reference(ws, min_col=2, min_row=2, max_row=8, max_col=3)
    

    I get

    Screen Shot 2015-11-20 at 09.29.08.png

  3. CharlieC

    I see no need to fill the chart namespace with all possibilities from openpyxl.chart.axis import Scaling is perfectly reasonable.

  4. CharlieC

    You can always submit improvements to the documentation which explain this kind of thing: ie. how scaling and axes work together.

  5. Log in to comment