CODEWITHSUNDEEP
excelpython-2.7
Geoffrey Aloysius
Geoffrey Aloysius

Reputation: 13

Using xlrd to iterate through worksheets and workbooks

I am a total noob. I need to grab the same cell value from every other sheet (starting at the third) in a workbook and place them into another. I continue to get an IndexError: list index out of range. There are 20 sheets in the workbook. I have imported xlrd and xlwt.

Code:

sheet_id = 3
output = 0
cellval = enso.sheet_by_index(sheet_id).cell(20,2).value


sheet_cp = book_cp.get_sheet(output)
sheet_cp.write(1, 1, cellval)

    book_cp.save(path)
for sheet_id in range(0,20):
    sheet_enso = enso_cp.get_sheet(sheet)
    sheet_cp = book_cp.get_sheet(output)
    sheet_cp.write(1, 1, cellval)
    sheet_id = sheet_id + 2
    output = output + 1

Upvotes: 0

Views: 1689

Answers (1)

user7609283
user7609283

Reputation:

Your problem most probably exists in here:

sheet_id = 3
cellval = enso.sheet_by_index(sheet_id).cell(20,2).value # row:20, column:0

Check the following:
1- Make sure that sheet_id=3 is what you want (where the index of sheets starts from 0), so the 3rd sheet has index=2 unless you want the 4th sheet.
2- Check cell(20,0) exists in the selected sheet (where cell(0,0) is the first cell).

Plus, you don't need to define sheet_id
instead change the range to (2: 3rd sheet, 21: for 20 sheets) > in range(2,21) where:

range([start], stop[, step])

start: Starting number of the sequence.
stop: Generate numbers up to, but not including this number.
step: Difference between each number in the sequence.

Reference: Python's range() Parameters

and to get cellval from every sheet, put cellval inside the loop.

The final code could be:

output = 0
for sheet_id in range(2,21): # (starting at the 3rd sheet (index=2), stopping at 20 "21 not included")
    cellval = enso.sheet_by_index(sheet_id).cell(20,0).value # row 20, column 0
    #sheet_enso = enso_cp.get_sheet(sheet) # i don't know if you need that for something else
    sheet_cp = book_cp.get_sheet(output)
    sheet_cp.write(1, 1, cellval)
    output = output + 1
book_cp.save(path)

again check cell(20,0) exists in all source sheets to avoid errors.

Upvotes: 1

Related Questions