How can I write to specific Excel columns using openpyxl?

I’m writing a Python script that needs to write collections of data down specific columns in an Excel document.

More specifically, I’m calling an API that returns a list of items. Each item in the list contains multiple fields of data (item name, item version, etc). I would like to iterate through each item in the list, then write selected fields of data down specific columns in Excel.

Currently, I’m iterating through the items list, then appending the fields of data I want as a list into an empty list (creating a list of lists). Once I’m done iterating through the list of items, I iterate through the list of lists and append to each row of the Excel document. This works, but makes writing to a specific column complicated.

Here is roughly the code that I currently have:

import requests
import json
from openpyxl import Workbook

def main():
    wb = Workbook()
    ws = wb.active

    r = requests.get(api_url)    # Can't provide URL
    items_list = r.json()['items']

    filler_list = []
    for item in items_list:
        item_name = item['itemName']
        item_version = item['itemVersion']
        # etc...

        filler_list.append([item_name, item_version])

    for i in filler_list:
        ws.append(i)

    wb.save('output.xlsx')

if __name__ == "__main__":
    main()

The above code will write to the Excel document across row 1, then row 2, etc. for however many lists were appended to the filler list. What I would prefer to do is specify that I want every item name or item version to be added to whatever column letter I want. Is this possible with openpyxl? The main function would look something like this:

def main():
    wb = Workbook()
    ws = wb.active

    r = requests.get(api_url)    # Can't provide URL
    items_list = r.json()['items']

    for item in items_list:
        item_name = item['itemName']
        item_version = item['itemVersion']

        # Add item name to next open cell in column B (any column)
        # Add item version to next open cell in column D (any column)

    wb.save('output.xlsx')

Answer

There are two general methods for accessing specific cells in openpyxl.

One is to use the cell() method of the worksheet, specifying the row and column numbers. For example:

ws.cell(row=1, column=1).value = 5

sets the first cell.

The second method is to index the worksheet using Excel-style cell notation. For example:

ws["A1"].value = 5

does the same thing as above.

If you’re going to be setting the same column positions in each row, probably the simplest thing to do is make items_list an iterator and loop through the columns you want to set. This is a simplified example from your code above:

columns = ["B", "D", "G", etc. etc.]
items_list = iter(r.json()['items'])
row = 1
for col in columns:
    ws[f"{col}{row}"].value = next(items_list)