Using Openpyxl (Python) to add information to existing cell

Question

I'm writing a script that will pull data from an API and store it in an Excel spreadsheet. I'm using Openpyxl for this process. The only issue I'm running into is that when I try to add to the end of an existing cell, that cell is just deleted and replaced by what I'm adding in. Can someone please tell me a great way that I can comma separate these values in the same cell instead of having them replace one another, I'd really appreciate it.

#Make sure that there is a game listed for them
    try:
        while games['games'][x]['name']:
            alist.append(games['games'][x]['name'])
            x = x + 1
    #After there are no more games, iterate through the list of games
    # and add them to the spreadsheet
    except IndexError:
        y = 0
        try:
            while alist[y]:
                ws['B'+str(namecount+1)] = alist[y]
                y = y + 1
        except IndexError:
            alist.clear()

It looks like whenever it's going through the 2nd while loop, instead of adding to the existing cell it's just deleting it. I've looked through the Openpyxl documentation and cannot find anything that will help me iterate through my list and then add each entry in the list, separated by commas, into the same cell. Any help would be greatly appreciated!

This is horrible code. Please try and write something that is closer to the style of the openpyxl docs. — Charlie Clark
– Charlie Clark, Commented Jan 7, 2016 at 7:42
God forbid anyone try to ya know....learn something here. Please try to be constructive instead of just a jerk. Thanks! — Heisenberg
– Heisenberg, Commented Apr 11, 2016 at 20:56

John Y · Accepted Answer · 2016-01-07 00:42:40Z

Excel isn't really built to store collections of values in a single cell. Each cell holds at most one value, and that value is fundamentally either numeric or string. (Dates, times, and truth values are variations on numeric.)

In your case, if you really want a comma-separated list of values in a single cell, then your only choice is to make the cell store a string representation of that list. (So to Excel, it's just one value, which is a string.) Then, each time you want to add an entry to that cell, you have to append the comma and the string representation of that new entry onto the end of the existing string value.

There are a few ways to do this. Most experienced Python programmers would probably use

ws[cell_address] = ', '.join(ws[cell_address], str(new_value))

You might also see

ws[cell_address] = ws[cell_address] + ', ' + str(new_value)

or

ws[cell_address] += ', ' + str(new_value)

(I've purposely used names other than what's in the text of your question, to hopefully better illustrate the general concept.)

Now, taking a closer look at your actual code, it seems to me that you really don't need to repeatedly assign that cell at all, because the list of values is already fully formed by the time you are trying to assign it the first time. So you can just take the string representation of the entire list and assign it to that cell in one shot, without using a loop.

I would probably do it this way:

ws[cell_address] = str(my_list)[1:-1]

where the [1:-1] is skipping the first and last character, which would be the leading and trailing brackets in Python's standard string representation of a list. Another possibility would be

ws[cell_address] = ', '.join(str(elem) for elem in my_list)

Now, if you know all your elements will already be strings, then that latter one can be simplified to

ws[cell_address] = ', '.join(my_list)

Collectives™ on Stack Overflow

Using Openpyxl (Python) to add information to existing cell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related