python xlrd, read from last sheet in a workbook

Question

I am writing a code which should compare values from 2 xls files. One of the files has more than 1 sheet and I always have to read the data only from the last sheet. I really don't know how manage with this. Below is my code:

#! /usr/bin/python
import xlrd #Import the package to read from excel
#start with station report
station_rep = xlrd.open_workbook("/home/fun/data/Station.xls",encoding_override='utf8') #Open the station report.xls
station_sheet = station_rep.sheet_by_index(0) #should get the last sheet
station_vn = station_sheet.col_values(5, start_rowx=1, end_rowx=None) #List of vouchers in station report
#start with billing export
billing_rep = xlrd.open_workbook("/home/fun/data/Export.xls",encoding_override='utf8') #Open billing report xls
billing_sheet = billing_rep.sheet_by_index(0) #get the current sheet 
billing_vn = billing_sheet.col_values(1, start_rowx=0, end_rowx=None)#list of vouchers in billing reports
for vn in station_vn: #For every voucher in station report
    if vn: #if there is data 
        vnb=vn[1:] #change data
        vnb=float(vnb) #change data type to float
        if vnb in billing_vn: # check if voucher exist in billing report
            row=station_vn.index(vn)+1 #take the row of current voucher
            station_vn_data = station_sheet.row_values(row, start_colx=0, end_colx=15) #take the data for current row from station report
            billing_vn_data = billing_sheet.row_values(billing_vn.index(vnb),start_colx=0, end_colx=15) #take the data for current voucher from billing report
            if float(station_vn_data[5])==billing_vn_data[1]: #check if vouchers are equal
                print "nomer na vouchera",  station_vn_data[5], billing_vn_data[1]  
                if round(station_vn_data[10],3)<>round(billing_vn_data[5],3): #check for differences in ammount
                    print "Razlika v edinichna cena", round(station_vn_data[10],3),"-" , round(billing_vn_data[5],3),"=", round(station_vn_data[10]-billing_vn_data[5],3)
                if station_vn_data[11]<>billing_vn_data[4]: #check for difference in price
                    print "kolichestvo", round(station_vn_data[11],4),"-", round(billing_vn_data[4],4),"=",round(station_vn_data[11]-billing_vn_data[4],4) #Ako ima razliki kolichestvata se printirat
                if station_vn_data[12]<>billing_vn_data[6]:# check for 1 more difference
                    print "obshta suma", round(station_vn_data[12],3),"-", round(billing_vn_data[6],3),"=",round(station_vn_data[12]-billing_vn_data[6],3) 
                else:
                    print "voucher is OK"
            print " " #print empty row for more clear view
        else: #if voucher do not exist in billing
            if vnb: 
                print vnb, "does not exist in billing report" #print the voucher number wich don`t exist

"here's my code, now fix it" is not exactly the way to get help here... please take [the tour](http://stackoverflow.com/tour) to understand how StackOverflow works — Tobias Kienzler, Dec 11 '14 at 12:46
`station_rep.sheet_by_index(0) #should get the last sheet` - on what basis? `0` is the *first* index, `-1` is the last. — jonrsharpe, Dec 11 '14 at 13:25

score 3 · Answer 1 · answered Dec 11 '14 at 15:18

station_sheet = station_rep.sheet_by_index(0) #should get the last sheet

There is no reason this should get the last sheet; Python indices are zero-based, so 0 is the first element in a sequence:

>>> [1, 2, 3][0]
1

If you want the last worksheet, note that Python allows negative indexing from the end of a sequence:

>>> [1, 2, 3][-1]
3

On that basis, I think you want:

station_sheet = station_rep.sheet_by_index(-1) # get the last sheet
                                         # ^ note index

Great examples for negative indexing! – Steven Lee Apr 15 '21 at 07:40 — Steven Lee, Apr 15 '21 at 07:40

score 0 · Answer 2 · answered Dec 11 '14 at 14:25

0

I managed to fix it with that code:

for id in  station_rep.sheet_names():
sheet_id=station_rep.sheet_names().index(id)
station_sheet = station_rep.sheet_by_index(sheet_id) #get the last sheet

answered Dec 11 '14 at 14:25

Jivodar Topanov

1
2

Why would you do that, rather than `for name in station_rep.sheet_names(): station_sheet = station_rep.sheet_by_name(name)`? Or `for station_sheet in station_rep.sheets():`? And if all you want is the last one, **use index `-1`**. – jonrsharpe Dec 11 '14 at 14:34
Method with: station_sheet=station_rep.sheet_by_index(-1) is working fine and it is the most elegant way. Thank you for the help. – Jivodar Topanov Dec 11 '14 at 15:15

python xlrd, read from last sheet in a workbook

2 Answers2