Choosing spreadsheets with similar names and variating index

Question

I need an advice for choosing sheet while reading through loads of Excel-spreadsheets. The firm I'm working for already has a big database of 100+ Excel-spreadsheets, where I am searching for values in a specific sheet. The problem is that the name and index of the spreadsheet variates, especially when it comes to the old documents. I've tried both sheet_by_idex(6) and sheet_by_name('name'), but I cant seem to get it right.

When searching for sheet_by_index(6), the problem is that the index changes in some documents, from for example index 5 to index 6. Another problem is that they also have hidden sheets, which I'm not sure if will interrupt or just become the last indexes.

When searching for sheet_by_name('name'), the problem is that the sheet name in most cases is called "Ship Res. Coeff WL1", but it might as well be called "Ship Res. Coeff WL2B" in other spreadsheets. In addition to that there is a similar sheet in each spreadsheet, with the name "Ship Res. Coeff WL1"(or WL2B if it's one of those documents)

Here is my code with a try, except function, allowing me to notice that the choice of sheets was the problem.

def find_Froudnumbers():
        Fdict = {}
        for filename in file_list:
            try:
                wb = xlrd.open_workbook(os.path.join(start_dir, filename))
                sheet = wb.sheet_by_index('6')  
                 Teljar = 25
                Flist = []
                for Frouden in xrange(25, sheet.nrows):
                    Frouden = sheet.cell_value(int(Teljar), 2)
                    Flist.append(Frouden)
                    Teljar += 1
                 Fdict[filename] = [Flist]
            except xlrd.XLRDError:
                print 'Problem with finding Froudenumbers from:', filename
        return Fdict       # map(str.strip(' '), Frouden)
     Fdict = find_Froudnumbers()
    print Fdict

If further explanation is needed, don't hesitate to ask. Any suggestions are greatly appreciated.

Answer 1

from xlrd import open_workbook

def find_Froudnumbers():
        Fdict = {}
        for filename in file_list:
            try:
                wb = xlrd.open_workbook(os.path.join(start_dir, filename))

                # this will have a problem if there is no similar sheet

                srch_string = 'Ship Res. Coef'
                for sheet_index in range(wb.nsheets):
                    first_sheet = wb.sheet_by_index(sheet_index)
                    second_sheet = wb.sheet_by_index(sheet_index + 1)
                    if (srch_string in first_sheet.name and
                        srch_string in second_sheet.name):

                        # do something with second_sheet

                 Teljar = 25
                Flist = []
                for Frouden in xrange(25, sheet.nrows):
                    Frouden = sheet.cell_value(int(Teljar), 2)
                    Flist.append(Frouden)
                    Teljar += 1
                 Fdict[filename] = [Flist]
            except xlrd.XLRDError:
                print 'Problem with finding Froudenumbers from:', filename
        return Fdict       # map(str.strip(' '), Frouden)
     Fdict = find_Froudnumbers()
    print Fdict

this should be close, i dont have time to test it unfortunately but be cautious because this will only work if there is a similar book right before it...maybe you can tweak it to be a little more flexible