How to loop through multiple excel worksheets and sort data using python?

Question

After downloading a spreadsheet from web and need to sort 4 of the 5 worksheets using python 2.7. I have been able to piece together code to download and save the file, and then sort it. However, I have been able to figure out how to loop through multiple sheets.

Code

import os
import os.path
import urllib
import xlwt
from xlrd import open_workbook

destination = 'C:\Users\Python'
if os.path.exists(destination) is False:
    os.mkdir(destination)

urllib.urlretrieve("http://www.eia.gov/dnav/pet/xls/PET_PRI_FUT_S1_D.xls", os.path.join(destination, "test.xls"))

target_column = 0     

book = open_workbook('test.xls')
sheet = book.sheets()[1]
data = [sheet.row_values(i) for i in xrange(sheet.nrows)]
labels = data[0]    # Don't sort our headers
data = data[1:]     # Data begins on the second row
data.sort(key=lambda x: x[target_column], reverse=True)

bk = xlwt.Workbook()
sheet = bk.add_sheet(sheet.name)

for idx, label in enumerate(labels):
     sheet.write(0, idx, label)

for idx_r, row in enumerate(data):
    for idx_c, value in enumerate(row):
        sheet.write(idx_r+1, idx_c, value)

bk.save('result.xls')

This question has already been asked and answered. http://stackoverflow.com/questions/22169325/read-excel-file-in-python — Merline Xavier, Feb 09 '17 at 19:05

score 3 · Accepted Answer · answered Feb 09 '17 at 19:10

3

You can loop through the sheets instead of grabbing a single sheet.

for sheet in book.sheets():

instead of

sheet = book.sheets()[1]

answered Feb 09 '17 at 19:10

Merline Xavier

56
1
2

Thanks. I appreciate it. I took that line of code and tried doing the following: `for sheet in book.sheets(): for sheet in range(1,5):` in order to be able to loop through specific worksheets but it didnt work. Is there a way to specify a range of sheets? – Rsaha Feb 09 '17 at 19:17
@Rsaha Do `for sheet in book.sheets()[1:5]:` – Will Feb 09 '17 at 19:24
@AnEpicPerson Thank you very much. – Rsaha Feb 09 '17 at 19:29
@Rsaha Please note that sequences start at zero, and the second parameter to the slice operator refers to the index 1 less than it, for example, `[1,2,3,4,5][1:5]` returns [2,3,4]. – Will Feb 09 '17 at 19:34
@AnEpicPerson Understood. Thank you very much. – Rsaha Feb 09 '17 at 20:01
One more question, is there a way to amend the code so the changes are made to the exciting file as opposed to being output to a new workbook? – Rsaha Feb 09 '17 at 21:48

How to loop through multiple excel worksheets and sort data using python?

1 Answers1