Python code to fetch data from Excel spreadsheets

get_data_from_xls.py

def get_data_from_xls(filename, column_letters=(), column_titles=()):
    """
    Fetch data in given columns from a xls/xlsx file
    Specify columns either by letters (e.g. ('A', 'B', 'AE')) or by titles in the first row
    Return a list of dict, with the given letters or titles as keys
    """
    wb = load_workbook(filename='data.xlsx')
    sheetname = wb.sheetnames[0] # assume first sheet in the workbook
    sheet = wb[sheetname]
    print('Reading %s in %s' % (sheetname, filename))

    cols = {}
    if len(column_letters) > 0:
        # find column numbers of given letters
        for col in range(1, sheet.max_column + 1):
            letter = sheet.cell(1, col).column_letter
            if letter in column_letters:
                cols[col] = letter
                print('    %s: %s' % (letter, sheet.cell(1, col).value))
    elif len(column_titles) > 0:
        # find column numbers of given titles
        for col in range(1, sheet.max_column + 1):
            title = sheet.cell(1, col).value
            if title in column_titles:
                cols[col] = title
                print('    %s: %s' % (sheet.cell(1, col).column_letter, title))

    if len(cols) == 0:
        exit('Cannot find given columns')

    data = []
    # read data
    for row in range(2, sheet.max_row + 1):
        row_data = {}
        for col, name in cols.items():
            row_data[name] = sheet.cell(row, col).value
        data.append(row_data)
        #print(row_data)
    return data