Data Labels on a chart

[Beth C]

Hello,

Is there a way to get the data labels to be displayed on a chart using openpyxl?

I would like to have the percentages displayed on the different slices of the pie chart, but I can't seem to find anything about including data labels on any of the charts except a declined pull request from 2014 on bitbucket and a labels module in charts (http://openpyxl.readthedocs.org/en/2.3.3/api/openpyxl.chart.label.html) that I'm not sure how it works.

Note: The picture below is what I am trying to accomplish (I used excel to make it as a demo), not my actual openpyxl chart

Thanks

[Charlie Clark]

Am .02.2016, 18:41 Uhr, schrieb Beth C <ecal...@nd.edu>:

> Hello,
>
> Is there a way to get the data labels to be displayed on a chart using
> openpyxl?

Yes, but you'll have to dig around a bit.

> I would like to have the percentages displayed on the different slices of
> the pie chart, but I can't seem to find anything about including data
> labels on any of the charts except a declined pull request from 2014 on
> bitbucket and a labels module in charts (
> http://openpyxl.readthedocs.org/en/2.3.3/api/openpyxl.chart.label.html)
> that I'm not sure how it works.

Neither am I! ;-) The spec doesn't tell you everything so often you'll
have to make a chart and look at the relevant XML. Then, it's pretty easy
to get openpyxl to play along.

from openpyxl.chart.label import DataLabelList

chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True

Should get you started.

The gauge chart example also contains some useful ideas, if not directly
relevant:
https://openpyxl.readthedocs.org/en/latest/charts/gauge.html

Good luck and looking forward to your contribution to 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

[Beth C]

I wanted to update this in case anyone else out there is looking for the same answer.

        from openpyxl.chart.label import DataLabelList 

        chart.dataLabels = DataLabelList() 

        chart.dataLabels.showPercent = True 

Using showPercent instead of showVal accomplished exactly what I was looking for. Below is an image of the openpyxl generated pie chart :-)

[Charlie Clark]

Am .02.2016, 16:25 Uhr, schrieb Beth C <ecal...@nd.edu>:

> I wanted to update this in case anyone else out there is looking for the
> same answer.

> from openpyxl.chart.label import DataLabelList
> chart.dataLabels = DataLabelList()
> chart.dataLabels.showPercent = True

> Using *showPercent* instead of *showVal* accomplished exactly what I was

> looking for. Below is an image of the openpyxl generated pie chart

Great the you got it working! How about adding this example to the docs?
C'mon, you know you want to!

[alex.ro...@gmail.com]

Hello! Can you go into further detail of how to do this? I tried to do what you did, but I think I need something in the parentheses for DataLabellist(). What was the full code you had to generate the pie chart with percentage?

[Beth C]

Hi Alex,

Below is the entire view that I wrote for generating the excel sheet with the data labels. My overall project is done in Django and it is just a test program to learn how openpyxl works, so it's probably not as well done as it probably could be. I hope this helps!

def CreateExcel(request):

    # Create workbook
    wb = Workbook()

    ws = wb.active

    my_fruits = ['apple', 'apple', 'orange', 'grape', 'apple', 'apple', 'grape', 'apple', 'apple', 'apple', 'orange', 'orange', 'grape', 'orange', 'apple',
                 'apple', 'apple', 'apple', 'orange', 'apple', 'peach', 'mango', 'kiwi', 'kiwi', 'mango', 'kiwi', 'banana', 'pear', 'tangerine',
                 'pomegranate', 'raspberry', 'blueberry', 'blueberry', 'banana', 'blueberry', 'blackberry', 'watermelon']

    fruit_set = set(my_fruits)
    fruit_categories = []

    for fruit in fruit_set:
        fruit_categories.append(str(fruit))
    ws['A1'] = "fruit"
    for i in range(2, len(my_fruits)+1):
        ws[(chr(0 + ord('A'))+str(i))] = my_fruits[i-1]

    ws['B1'] = "Fruit"
    ws['C1'] = "Quantity"

    fruit_count_dict = {}
    for fruit in fruit_categories:
        fruit_count_dict[str(fruit)] = 0

    for r in my_fruits:
            fruit_count_dict[r] += 1

    sorted_fruit_count = sorted(fruit_count_dict.items(), key=operator.itemgetter(1))
    print (sorted_fruit_count)

    row = 2

    for j in sorted_fruit_count:
        ws[(chr(0 + ord('B'))+str(row))] = str(j[0])
        ws[(chr(0 + ord('C'))+str(row))] = j[1]
        row += 1

    # Bar chart of fruits
    chart1 = BarChart()
    chart1.type = "bar"
    chart1.style = 10
    chart1.title = "A List of Fruits and their Quantities"
    chart1.legend = None
    chart1.shape = 4

    data = Reference(ws, min_col=3, min_row=1, max_row=len(fruit_categories)+1)
    cats = Reference(ws, min_col=2, min_row=2, max_row=len(fruit_categories)+1)
    chart1.add_data(data, titles_from_data=True)
    chart1.set_categories(cats)
    chart1.height = 10
    chart1.width = 20

    # set a pattern for the whole data set
    series = chart1.series[0]
    fill =  PatternFillProperties(prst="pct5")
    fill.foreground = ColorChoice(prstClr="navy")
    fill.background = ColorChoice(prstClr="navy")
    series.graphicalProperties.pattFill = fill

    # set a pattern for a 6th data point (0-indexed)
    pt = DataPoint(idx=5)
    pt.graphicalProperties.pattFill = PatternFillProperties(fgClr=ColorChoice(prstClr='gold'), bgClr=ColorChoice(prstClr='gold'))
    series.dPt.append(pt)

    ws.add_chart(chart1, "E30")

    # Pie chart
    # Use same data as bar chart

    pie = PieChart()
    pie.add_data(data, titles_from_data=True)
    pie.set_categories(cats)
    pie.title = "Fruits by category - % labels"
    pie.height = 15
    pie.width = 15

    # Find index of desired slice to explode
    apple_slice_index = 0
    orange_slice_index = 0
    for f in range(0, len(sorted_fruit_count)):
        if sorted_fruit_count[f][0] == "apple":
            apple_slice_index = f
        elif sorted_fruit_count[f][0] == "orange":
            orange_slice_index = f

    # Cut the desired slice out of the pie
    slice = DataPoint(idx=apple_slice_index, explosion=10)
    pie.series[0].data_points = [slice]

    # set a pattern for the whole data set
    pieseries = pie.series[0]
    fill =  PatternFillProperties(prst="pct5")
    fill.foreground = ColorChoice(prstClr="navy")
    fill.background = ColorChoice(prstClr="navy")
    pieseries.graphicalProperties.pattFill = fill

    # set a pattern for a 6th data point (0-indexed)
    pt = DataPoint(idx=apple_slice_index)
    pt.graphicalProperties.pattFill = PatternFillProperties(fgClr=ColorChoice(prstClr='blue'), bgClr=ColorChoice(prstClr='blue'))
    pieseries.dPt.append(pt)

    # set a pattern for a 6th data point (0-indexed)
    pt = DataPoint(idx=orange_slice_index)
    pt.graphicalProperties.pattFill = PatternFillProperties(fgClr=ColorChoice(prstClr='forestGreen'), bgClr=ColorChoice(prstClr='forestGreen'))
    pieseries.dPt.append(pt)

    ws.add_chart(pie, "E1")

    pie.dataLabels = DataLabelList()
    pie.dataLabels.showPercent = True

    name_part = 'fruit'

    # prep the Excel file
    response = HttpResponse(content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename="'+ name_part +'.xls"'

    # Save the workbook
    wb.save(response)

    return response

[Charlie Clark]

Am .05.2016, 18:45 Uhr, schrieb Beth C <ecal...@nd.edu>:

> Hi Alex,
>
> Below is the entire view that I wrote for generating the excel sheet with
> the data labels. My overall project is done in Django and it is just a
> test
> program to learn how openpyxl works, so it's probably not as well done as
> it probably could be. I hope this helps!

Thanks very much for sharing your code. I have a couple of notes.

> def CreateExcel(request):
>
> # Create workbook
> wb = Workbook()
>
> ws = wb.active
>
> my_fruits = ['apple', 'apple', 'orange', 'grape', 'apple', 'apple',
> 'grape', 'apple', 'apple', 'apple', 'orange', 'orange', 'grape',
> 'orange', 'apple',
> 'apple', 'apple', 'apple', 'orange', 'apple', 'peach',
> 'mango', 'kiwi', 'kiwi', 'mango', 'kiwi', 'banana', 'pear', 'tangerine',
> 'pomegranate', 'raspberry', 'blueberry', 'blueberry',
> 'banana', 'blueberry', 'blackberry', 'watermelon']
>
> fruit_set = set(my_fruits)
> fruit_categories = []
>
> for fruit in fruit_set:
> fruit_categories.append(str(fruit))

fruit_categories = list(fruit_set)

> ws['A1'] = "fruit"
> for i in range(2, len(my_fruits)+1):
> ws[(chr(0 + ord('A'))+str(i))] = my_fruits[i-1]

Please, please don't do this: write your own code for column letters: it's
unnecessary and will break if ord(s) > 26. I've seen this a few places now
which suggests it's in a recipe somewhere. You'll virtually never need it
but get_column_letter() will do it if you need it.

Here you want to do something like this:
for idx, fruit in enumerate(my_fruits, 2):
ws.cell(row=idx, col=1, value=fruit)

> ws['B1'] = "Fruit"
> ws['C1'] = "Quantity"
>
> fruit_count_dict = {}
> for fruit in fruit_categories:
> fruit_count_dict[str(fruit)] = 0
>
> for r in my_fruits:
> fruit_count_dict[r] += 1
>
> sorted_fruit_count = sorted(fruit_count_dict.items(),
> key=operator.itemgetter(1))
> print (sorted_fruit_count)

The collections module has a Counter for this:
fruit_count = Counter(my_fruits)

[Carlos Rodriguez]

here you go for the barchart

from openpyxl.chart.label import DataLabelList #this is not specified in documentation as dataLabels module does not exist

chartcol_geologica.dataLabels= DataLabelList()

chartcol_geologica.dataLabels.showSerName=True

chartcol_geologica.dataLabels.showVal=True

[Dario de Judicibus]

What is I want to use custom alphanumeric labels? For example "BBC", "RAI", "TVF"...

[Charlie Clark]

On 9 Nov 2021, at 18:12, Dario de Judicibus wrote:

> What is I want to use custom alphanumeric labels? For example "BBC", "RAI",
>
> "TVF"...

I think you should provide a little more information about what you're trying to do.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office

Waldlehne 23
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Dario de Judicibus]

I want to create a Scatter chart from Excel data where each point has a label. The x-values for points are, for example, in B2:B10, the y-values in C2:C10 and the labels are alphanumeric and are in A2:A10.

I was able to create the scatter chart, and I was able to enable data labels, but I can show only y-values, x-values (i.e. categories) or series, but not custom labels. I use openpyxl, the latest version, and Python 3.10.

[Dario de Judicibus]

SEe this: https://stackoverflow.com/questions/69901850/howe-can-i-add-alphanumeric-data-labels-to-a-series-in-a-scatter-chart-by-openpy

[Charlie Clark]

On 10 Nov 2021, at 10:30, Dario de Judicibus wrote:

> SEe
>
> this: [https://stackoverflow.com/questions/69901850/howe-can-i-add-alphanumeric-data-labels-to-a-series-in-a-scatter-chart-by-openpy](https://stackoverflow.com/questions/69901850/howe-can-i-add-alphanumeric-data-labels-to-a-series-in-a-scatter-chart-by-openpy)

It will be easier if you provide the sample code here.

For an idea as to how to implement the labels you must be prepared to look at the XML source of a chart.

[Dario de Judicibus]

How can I see the XML source of a chart embedded in a .XSLX file?

[Dario de Judicibus]

OK, here is a sample code

import openpyxl as xl

# Load workbook and data sheet

data_wb = xl.load_workbook('data.xlsx', data_only=True)

data_ws = data_wb.active

# Select data for chart

chart_title = "Just another chart"

chart_ref_x  = xl.chart.Reference(data_ws, min_col=6)  # X values (eg. 2.87)

chart_ref_y  = xl.chart.Reference(data_ws, min_col=5)  # Y values (eg. 4.67)

chart_ref_l  = xl.chart.Reference(data_ws, min_col=4)  # Data labels (eg. "FGT")

# Make scatter chart

scatter_chart = xl.chart.ScatterChart()

scatter_chart.title = chart_title

scatter_chart.x_axis.title = "X values"

scatter_chart.y_axis.title = "Y values"

scatter_chart.legend = None

# Create series

series_data = xl.chart.Series(values= chart_ref_y, xvalues= chart_ref_x)

series_data.graphicalProperties.line.noFill = True

# Add labels to series from chart_ref_l

... HOW? ...

# Add series to chart

scatter_chart.series.append(series_data)

# Create chartsheet and add chart

chart_sheet = data_wb.create_chartsheet(title="Scatter chart")

chart_sheet.add_chart(scatter_chart)

# Save with a different name

data_wb.save('result.xlsx')

# Close workbook

data_wb.close()

Il giorno mercoledì 10 novembre 2021 alle 10:53:20 UTC+1 charli...@clark-consulting.eu ha scritto:

[Charlie Clark]

On 11 Nov 2021, at 9:26, Dario de Judicibus wrote:

> OK, here is a sample code
>
> import openpyxl as xl
>
> \# Load workbook and data sheet
>

> data\_wb = xl.load\_workbook('data.xlsx', data\_only=True)

Thanks, but this will be more useful with a file.

[Dario de Judicibus]

OK, here is a full working code...

import openpyxl as xl
# Create workbook and data sheet
wb = xl.Workbook()
ws = wb.active
ws.title = "Data"
# Prepare data
headers = ["Labels", "X values", "Y values"]
data = [["A", "B", "C", "D", "E"], [12, 23, 34, 45, 56], [65, 45, 38, 92, 17]]
# Write data in sheet
for col in range(1, 4):
    ws.cell(row=1, column=col).value = headers[col - 1]
    for row in range(2, 7):
        ws.cell(row=row, column=col).value = data[col - 1][row - 2]

# Select data for chart

mr = ws.max_row
chart_title = "Scatter chart"
chart_ref_x = xl.chart.Reference(ws, min_col=2, min_row=2, max_row=mr) # X values
chart_ref_y = xl.chart.Reference(ws, min_col=3, min_row=2, max_row=mr) # Y values
chart_ref_l = xl.chart.Reference(ws, min_col=1, min_row=2, max_row=mr) # Data labels

# Make scatter chart
scatter_chart = xl.chart.ScatterChart()
scatter_chart.title = chart_title
scatter_chart.x_axis.title = "X values"
scatter_chart.y_axis.title = "Y values"
scatter_chart.legend = None
# Create series
series_data = xl.chart.Series(values= chart_ref_y, xvalues= chart_ref_x)
series_data.graphicalProperties.line.noFill = True

series_data.marker = xl.chart.marker.Marker('diamond')
series_data.marker.size = 8

# Add labels to series from chart_ref_l

# *** WHAT I WANT IS TO ADD "A", "B", "C", etc... as labels to chart points... but HOW? ***

# Add series to chart
scatter_chart.series.append(series_data)
# Create chartsheet and add chart

chart_sheet = wb.create_chartsheet(title="Scatter chart")
chart_sheet.add_chart(scatter_chart)
# Save workbook
wb.save("labels.xlsx")
# Close it
wb.close()

What I need is to undestand how to create data labels for the points in scatter chart whose values are in column 1 (chart_ref_l).

[Dario de Judicibus]

Any answer?

[Megan Kamachi]

Hi @Dario, I am working on the same thing. Did you find a solution?

[Majid Rezaei]

hi , very thanks

--
You received this message because you are subscribed to the Google Groups "openpyxl-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openpyxl-user...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/8c715eed-c1f2-44cc-8a98-7e52fe26125cn%40googlegroups.com.