Python Attempt to overwrite cell error

Question

I am trying to create a new excel sheet and add data to it using python. However, I keep running into the "Attempt to overwrite cell" error. What I don't understand is that according to the way I wrote my code I don't think it should be overwriting any cell. Here is the code:

    wb = xlwt.Workbook()
    ws = wb.add_sheet('Sheet1', cell_overwrite_ok=True)
for key in DBDICT:
    numList = []
    for value in DBDICT[key]:

        numList.append(float(value))
    for i in range(len(DBDICT)):
        ws.write(i, 0, key.lat)
        ws.write(i, 1, key.lon)
        for j in range(len(numList)):
            ws.write(i, (j+2), numList[j])

All I am trying to do is read from a dictionary called DBDICT and, for each pair in the dictionary, write the first number of the key (the key is a tuple of two numbers that I made with namedtuple()) in the first column of a certain row in the excel file, the second number of the key in the second column, and then each number of the corresponding value (which is a list of numbers) in the remaining boxes. In other words, if

    DBDICT = {(-90, -180):[1.0 , 2.0 , 3.0], (-180, -360):[2.0, 3.0, 4.0]}

then in the first row of the excel file, the code below should write: | -90 | -180 | 1.0 | 2.0 | 3.0 | and in the second row: | -180 | -360 | 2.0 | 3.0 | 4.0 |

Why does Python think I'm overwriting a cell?

Answer 1

It looks like your loops are off. Your indentation didn't come through quite right, so I'm not entirely sure what your original loop looks like, but I see that you're referencing key in the loop that writes to the worksheet - and triggering that loop for each key with the same row number.

It should be something like this:

rownum = 0
for key in DBDICT:
    ws.write(rownum, 0, key.lat)
    ws.write(rownum, 1, key.lon)
    colnum = 2
    for val in DBDICT[key]:
        ws.write(rownum, colnum, float(val))
        colnum += 1
    rownum += 1

That's a fairly minimal change to your code. I would actually write it like this, using the enumerate built-in and the iteritems method of the dictionary:

for (rownum, (key, value)) in enumerate(DBDICT.iteritems()):
    ws.write(rownum, 0, key.lat)
    ws.write(rownum, 1, key.lon)
    for (val_index, val) in enumerate(value):
        ws.write(rownum, val_index + 2, float(val))

Answer 2

The problem is that you loop twice over your dictionary, which does cause many overwrites! You could combine the two loops into one by using enumerate:

for i, key in enumerate(DBDICT):
    numList = []
    for value in DBDICT[key]:
        numList.append(float(value))
    ws.write(i, 0, key.lat)
    ws.write(i, 1, key.lon)
    for j in range(len(numList)):
        ws.write(i, (j+2), numList[j])

Note that you also loop twice over the list with values. This doesn't cause any problems this time, but it can be more efficient. Again using enumerate:

for i, key in enumerate(DBDICT):
    ws.write(i, 0, key.lat)
    ws.write(i, 1, key.lon)
    for j, val in enumerate(DBDICT[key]):
        ws.write(i, j+2, float(val))