Why is my dictionary data not displaying correctly on a bar chart using openpyxl?

Dictionary Data not displaying correctly on Barchart (openpyxl).

I have a dictionary with key being product name and value being quantity purchased of each product. For some reason this won't display properly on a bar chart using openpyxl. I have printed the dictionary before creating barchart and all the values are correct but they do not match the barchart. Side note if possible how do I change the units on the y access to be whole numbers.

This is what the dictionary displays:

{'123': 2, '456': 1, '789': 1, '111': 0, '222': 1, '333': 1, '444': 1, '555': 1, '666': 0, '777': 1, '854': 0}

Here is the code:

for key in product_quantity:
    active_sheet.append([barcode_list[key],product_quantity[key]])

my_chart = BarChart()

data = Reference(worksheet=active_sheet,
                 min_row=1,
                 max_row=len(barcode_list),
                 min_col=2,
                 max_col=3)

my_chart.legend = None

my_chart.add_data(data, titles_from_data=True)
active_sheet.add_chart(my_chart, "A1")

categories = Reference(active_sheet, min_col=1, min_row=1, max_row=len(barcode_list))
my_chart.set_categories(categories)  # Adds the product names along the X-Axis

# Add Chart titles
my_chart.title = "Products Sold by Quantity"
my_chart.x_axis.title = 'Products'
my_chart.y_axis.title = 'Quantity Sold'

You set titles_from_data to True, so the add_data method of BarChart expects the heading of the data in the first row; in your example, it is treating the value in the first row as a heading. (You can debug this by opening up the generated Excel document and seeing which cells are referenced when you click on the bars.)

It looks like your data is something like:

barcode_list = {
    "123": "Milk (2L)",
    "456": "Bread (500g)",
    "789": "Juice (1L)",
    "111": "Bananas (2kg)",
    "222": "Apples (1kg)",
    "333": "Donuts (500g)",
    "444": "Kombucha (350ml)",
    "555": "Lemonade (1L)",
    "666": "Beef Mince (500g)",
    "777": "Chicken Breast (1kg)",
}

product_quantity = {
    "123": 2,
    "456": 1,
    "789": 1,
    "111": 0,
    "222": 1,
    "333": 1,
    "444": 1,
    "555": 1,
    "666": 0,
    "777": 1,
}

I prefer to be a little more explicit about where I would like to write the data, so, if ws is my active worksheet, I might do something like:

# Headings
ws["A1"] = "Product"
ws["B1"] = "Quantity Sold"

# Data
for i, (k, v) in enumerate(product_quantity.items()):
    row = i + 2
    ws[f"A{row}"] = barcode_list[k]
    ws[f"B{row}"] = v

In that case, I know exactly where my data is, and can see that your reference is looking at the right place:

data = Reference(
    worksheet=ws,
    min_row=1,
    max_row=len(barcode_list) + 1,
    min_col=2,
    max_col=2,
)

We can direct the BarChart object to this data using:

my_chart.add_data(data, titles_from_data=True)

Also update your categories reference:

categories = Reference(
    ws,
    min_col=1,
    min_row=2,
    max_row=len(barcode_list) + 1,
)

Regarding setting the units on the y axis, use:

my_chart.y_axis.minorUnit = 1

The rest should be okay.