29

I wrote code which opens an excel file and iterates through each row and passes the value to another function.

import openpyxl
wb = load_workbook(filename='C:\Users\xxxxx')
for ws in wb.worksheets:
    for row in ws.rows:
        print row
        x1=ucr(row[0].value)
        row[1].value=x1  #  i am having error at this point

I am getting the following error when I tried to run the file.

TypeError: IndexError: tuple index out of range

Can I write the returned value x1 to the row[1] column. Is it possible to write to excel (i.e using row[1]) instead of accessing single cells like ws.['c1']=x1

ballade4op52
  • 2,142
  • 5
  • 27
  • 42
Priyaranjan
  • 407
  • 3
  • 9
  • 16

1 Answers1

68

Try this:

import openpyxl
wb = load_workbook(filename='xxxx.xlsx')
ws = wb.worksheets[0]
ws['A1'] = 1
ws.cell(row=2, column=2).value = 2

This will set Cells A1 and B2 to 1 and 2 respectively (two different ways of setting cell values in a worksheet).

The second method (specifying row and column) is most useful for your situation:

import openpyxl
wb = load_workbook(filename='xxxxx.xlsx')
for ws in wb.worksheets:
    for index, row in enumerate(ws.rows, start=1):
        print row
        x1 = ucr(row[0].value)
        ws.cell(row=index, column=2).value = x1
Jake Griffin
  • 2,014
  • 12
  • 15
  • there are 150 rows, it will be tough to access each cell and write the data. like ws['a1']=x1,ws['a2']='' . i wish to have general solution.Thanks for the help – Priyaranjan Jul 13 '15 at 23:44
  • 1
    So use the second form I gave: ws.cell(row=X, column=Y).value = V – Jake Griffin Jul 14 '15 at 00:23
  • 1
    I have updated my answer to include how you might use that method in your particular situation. – Jake Griffin Jul 15 '15 at 18:02
  • 27
    Note that the changes will not be reflected in the Excel file unless you save the file. `wb.save(fileName)` – MackM Jun 07 '18 at 20:58
  • In openpyxl 3.0.7 the method using `ws.cell(row, column)/ws.cell(coordinate)` doesn't work any more. `ws["A1"]` works as expected. – Cecilya Oct 15 '21 at 07:53
  • 1
    @Cecilya - I checked the documentation, and it no longer mentions the "coordinate" kwarg, so I think you're right about method 3 not working anymore. I just tested all of these again on openpyxl 3.0.9, though, and the second one does still work for me there. And according to the documentation and my testing, apparently `ws.cell(row=4, column=4, value=4)` also works now, however I think the second method in my answer is clearer, so I am going to leave it out. I am updating my answer to reflect the third method not working anymore. – Jake Griffin Oct 16 '21 at 14:55