Adding labels to a Series to an Excel Chart using xlwings

Question

I am trying to automate graphs report analysis using windows.

I have been trying different ways to add the labels using the VBA recorder but haven't got to anything that works. How do I add labels to a SeriesCollection or FullSeriesCollection? Assume I have a functional sheet created using Xlwings

#Create Chart 
chart = sheet.charts.add() 
chart.chart_type = 'xy_scatter_lines' 
chart.api[1].SeriesCollection().NewSeries() 
# Assume I have some data 
x = data_p_sheet.range(f'{col_x}5').expand("down")
y = data_p_sheet.range(f'{col_y}5').expand("down")
chart.api[1].SeriesCollection(1).XValues = x.api
chart.api[1].SeriesCollection(1).Values = y.api 
chart.api[1].FullSeriesCollection(1).Name = "MyChart"

Assuming I have my labels in a range of the same number of rows as x and y

labels =data_p_sheet.range(f'{col_labels}5').expand("down") 
chart.api[1].FullSeriesCollection(1).DataLabels = labels

It is not working nor returning any error(s) so I am not sure if this is the correct way to go about it.

chart.api[1].FullSeriesCollection(i).ApplyDataLabels() 

The above snippet adds data labels but I still don't get the layout that I want.

Schema of how my sheet and data looks like

And I'm trying to get the id column to be a label to each point.

I got a nonchalant solution assuming you get your SeriesCollection index elsewhere:

for i, label in enumerate(labels):
    i_label=chart.api[1].SeriesCollection(j).DataLabels(i+1)
    i_label.Text = str(label)

Answer 1

I could not get the code to set the labels either from "value from cells" or by changing the value of the label. However not all charting functionality is possible with Xlwings.

Perhaps xlsxwriter would be Ok for your use. The option to do this was added in the last year or so.

import xlsxwriter

workbook = xlsxwriter.Workbook('scatter_labels.xlsx')
worksheet = workbook.add_worksheet()
sheetname = 'Sheet1'

### Header formatting
header_form = workbook.add_format(
    {
        "border":   6,
        "bold":     True,
        "align":    "center"
    }
)


# Add the worksheet data to be plotted.
data_list = [
    ['id', 'DISTANCE', 'TN'],
    ['S60 PUYR_X006255', 0, 200.259995],
    ['S60_PUYR_X006263', 30.9629993, 200.039993],
    ['S60_PUYR_X006265', 50.132, 199.830002],
    ['S60_PUYR_X006396', 235.852001, 204.660004],
    ['S60_PUYR_X017237', 430.316006, 206.832001],
    ['S56_PUYR_X017238', 430.316006, 206.832001],
    ['S56_PUYR_X006430', 430.963006, 206.832001],
    ['S56_PUYR_X006431', 475.673005, 208.039993]
]

### Add Headers and data to sheet and create labeling list
marker_labels = []
for row, data in enumerate(data_list, 4):
    if row > 4:
        worksheet.write_row(f'A{row}', data)
        marker_labels.append({'value':f'={sheetname}!$A${row}'})
    else:
        worksheet.write_row(f'A{row}', data, header_form)

# Create a new scatter chart object.
chart = workbook.add_chart({'type': 'scatter', "subtype": "straight_with_markers"})

chart.add_series({
    'name':         '=Sheet1!$C$4',
    'categories':   '=Sheet1!$B$5:$B$12',
    'values':       '=Sheet1!$C$5:$C$12',
    'data_labels': {'value': True, 'custom': marker_labels},
})
chart.set_chartarea({'border': {'none': True}})

### Set axis points
chart.set_x_axis({'min': 0, 'max': 500})
chart.set_y_axis({'min': 199, 'max': 209})

### Set Chart size
chart.set_size({'width': 800, 'height': 400})

# Insert the chart into the worksheet at cell F1.
worksheet.insert_chart('F1', chart)

### Auto fit columns
worksheet.autofit()

### Save and close
workbook.close()

This example will produce a scatter chart like this

xlsxwriter