openpyxl BarChart 2D Grouping 'clustered' does not work

Issue #517 resolved
Jimmy Xie created an issue

Hi,

openpyxl-2.3.0b2-py2.7 provides 2D barchart library 'BarChart'. It includes 4 types of grouping: 'percentStached', 'clustered', 'standard', and 'stacked'. Grouping type 'clustered' does not work.

Also, the PieChart library could only draw the column data. Could you add an argument to choose from row or from column?

Thanks.

Comments (14)

  1. CharlieC

    Clustered is the default grouping for bar or column charts.

    If something is not working then please provide a code sample that helps us work out where the issue may be.

    The PieChart thing sounds like it a separate issue. Series are not handled differently by the various chart types. The frow_rows parameter should work here. If not, again: please submit some sample code to a new issue.

  2. Jimmy Xie reporter

    Thanks so much for your reply. Following is an example code. It generates four charts belonging to four grouping types. You will find that 'clustered grouping' and 'standard grouping' generate the same chart. Such chart is different from the general 'clustered grouping' chart generated by excel.

    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)

    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 = BarChart() chart1.type = 'col' chart1.style = 11 chart1.grouping = 'clustered' chart1.title = 'clustered chart' chart1.y_axis.title = 'Test number' chart1.x_axis.title = 'Sample length (mm)' chart1.add_data(data, titles_from_data=True) chart1.set_categories(cats) ws.add_chart(chart1, "E2")

    chart2 = BarChart() chart2.type = 'col' chart2.style = 11 chart2.grouping = 'standard' chart2.title = 'standard chart' chart2.y_axis.title = 'Test number' chart2.x_axis.title = 'Sample length (mm)' chart2.add_data(data, titles_from_data=True) chart2.set_categories(cats) ws.add_chart(chart2, "E18")

    chart3 = BarChart() chart3.type = 'col' chart3.style = 11 chart3.grouping = "stacked" chart3.title = 'stacked Chart' chart3.y_axis.title = 'Test number' chart3.x_axis.title = 'Sample length (mm)' chart3.add_data(data, titles_from_data=True) chart3.set_categories(cats) ws.add_chart(chart3, "E34")

    chart4 = BarChart() chart4.type = 'col' chart4.style = 11 chart4.grouping = "percentStacked" chart4.title = 'Percent Stacked Chart' chart4.y_axis.title = 'Test number' chart4.x_axis.title = 'Sample length (mm)' chart4.add_data(data, titles_from_data=True) chart4.set_categories(cats) ws.add_chart(chart4, "E50")

    wb.save("bar.xlsx")

  3. CharlieC

    This is the specification and so this is what we implement. So I don't see a bug.

    <xsd:simpleType name="ST_BarGrouping">
        <xsd:restriction base="xsd:string">
           <xsd:enumeration value="percentStacked"/>
           <xsd:enumeration value="clustered"/>
           <xsd:enumeration value="standard"/>
           <xsd:enumeration value="stacked"/>
        </xsd:restriction>
    </xsd:simpleType>
    
  4. Jimmy Xie reporter

    Hey Charlie,

    Thanks so much for your previous reply. I looked at the source code and tried different settings. I still can not generate the chart with grouping type 'clustered grouping'. Current generated 'clustered grouping' chart is actually the 'standard grouping' chart. Could you send me a simple example that can generate the chart with 'clustered grouping'?

  5. CharlieC

    What do you mean you can't "generate" the chart? Is the grouping not set correctly in the source of the chart file? Please provide a sample file illustrating the differences. Some of the chart settings have no effect on their own. :-/

  6. Jimmy Xie reporter

    Thanks again for your reply. Following is the sample I tried to generate the 'clustered grouping' chart. If you run the code, you can find that the 'clustered grouping' chart and 'standard grouping' chart are the same. I wonder if other settings needed for the 'clustered grouping' chart?

    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) ]

    for row in rows: ws.append(row)

    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 = BarChart()

    chart1.type = 'col'

    chart1.style = 11

    chart1.grouping = 'clustered'

    chart1.title = 'clustered chart'

    chart1.y_axis.title = 'Test number'

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

    chart1.add_data(data, titles_from_data=True)

    chart1.set_categories(cats)

    ws.add_chart(chart1, "E2")

    chart2 = BarChart()

    chart2.type = 'col'

    chart2.style = 11

    chart2.grouping = 'standard'

    chart2.title = 'standard chart'

    chart2.y_axis.title = 'Test number'

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

    chart2.add_data(data, titles_from_data=True)

    chart2.set_categories(cats)

    ws.add_chart(chart2, "E18")

    wb.save("bar.xlsx")

  7. CharlieC

    Okay, I think I've worked out what's going on. The overlap property affects all bar and column charts and controls whether the bar for one series overlaps another. This is currently set to 100% for all charts but this only makes sense for stacked charts. If you set chart1.overlap = 0 then you'll see the bars next to each other.

    PS. please format your code samples as code.

  8. Jimmy Xie reporter

    Thank you so much for your help. The problem is solved. I reformat the sample code as following.

    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)]
    for row in rows:
        ws.append(row)
    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 = BarChart()
    chart1.type = 'col'
    chart1.style = 11
    chart1.grouping = 'clustered'
    chart1.title = 'clustered chart'
    chart1.y_axis.title = 'Test number'
    chart1.x_axis.title = 'Sample length (mm)'
    chart1.add_data(data, titles_from_data=True)
    chart1.set_categories(cats)
    chart1.overlap = 0
    ws.add_chart(chart1, "E2")
    
    chart2 = BarChart()
    chart2.type = 'col'
    chart2.style = 11
    chart2.grouping = 'standard'
    chart2.title = 'standard chart'
    chart2.y_axis.title = 'Test number'
    chart2.x_axis.title = 'Sample length (mm)'
    chart2.add_data(data, titles_from_data=True)
    chart2.set_categories(cats)
    chart2.overlap = 100
    ws.add_chart(chart2, "E18")
    wb.save("bar.xlsx")
    
  9. Log in to comment