def function1(pathname, sheetname): workbook = xlrd.open_workbook(pathname) worksheet = workbook.sheet_by_name(sheetname) l = [] for i in range(25): if worksheet.cell(i, 0).value == xlrd.empty_cell.value: continue x = worksheet.cell(i, 0).value l.append(x) return l
def _load_unique_items_to_code(self, workbook): """ Load in the terms TODO: Refactor this!! """ terminology = {} if isinstance(workbook, openpyxl.workbook.Workbook): for sheet_name in ('Fields to be Coded', 'Fields Coded'): sheet = workbook.get_sheet_by_name(sheet_name) for (idx, content) in enumerate(sheet.rows): if idx == 0: # ignore header continue if not si(content[0]): # blank rows continue if '|' in si(content[0]): # multiple terms, split 'em terms = [x.strip() for x in si(content[0]).split('|')] values = [x.strip() for x in si(content[1]).split('|')] if not len(terms) == len(values): print 'Error in mapping: %s => %s (Not a match in numbers)' % (si(content[0]), si(content[1])) else: terminology.update(dict(zip(terms, values))) pass else: # Map a 'None' to '' terminology[si(content[0])] = {None : '', 'None' : ''}.get(si(content[1]), si(content[1])) else: for sheet_name in ('Fields to be Coded', 'Fields Coded'): sheet = workbook.sheet_by_name(sheet_name) for row in range(1, sheet.nrows): content = sheet.row_values(row) if '|' in si(content[0]): terms = [x.strip() for x in si(content[0]).split('|')] values = [x.strip() for x in si(content[1]).split('|')] if not len(terms) == len(values): print 'Error in mapping: %s => %s (Not a match in numbers)' % (si(content[0]), si(content[1])) else: terminology.update(dict(zip(terms, values))) else: terminology[si(content[0])] = {None : '', 'None' : ''}.get(si(content[1]), si(content[1])) return terminology
def get_inventory(self): workbook = xlrd.open_workbook('Records.xlsx') inventory = workbook.sheet_by_name('Inventory') row_count = inventory.nrows col_count = inventory.ncols print("Getting inventory...\n\n") time.sleep(1) keys = [inventory.cell(0, col_index).value for col_index in range(col_count)] dict_list = [] for row_index in range(1, row_count): d = {keys[col_index]: inventory.cell(row_index, col_index).value for col_index in range(col_count)} dict_list.append(d) return dict_list
def showexcel(file='test1.xlsx', colnameindex=1): workbook = xlrd.open_workbook(file) sheets = workbook.sheet_names() print(sheets) table = workbook.sheet_by_name('testtab1') nrows = table.nrows colnames = table.row_values(colnameindex) list = [] for rownum in range(1, nrows): row = table.row_values(rownum) print(type(row)) if row: app = {} for i in range(len(colnames)): app[colnames[i]] = row[i] list.append(app) print(list) return list
""" terminology = {} print 'Loading %s from Content Template' % filename try: workbook = xlrd.open_workbook(filename) except xlrd.biffh.XLRDError, e: # use the xml reader terminology = self._load_template_xml(filename) return terminology except IOError, e: print 'Failed to open %s - %s' % (filename, e) sys.exit(1) if 'Fields to be Coded' in workbook.sheet_names(): terminology = self._load_unique_items_to_code(workbook) else: sheet = workbook.sheet_by_name('Terms') for row in range(1, sheet.nrows): content = sheet.row_values(row) terminology[si(content[0])] = si(content[1]) print 'Loaded %s terms' % len(terminology) return terminology def _load_template_xml(self, filename): """ use a different reader """ try: workbook = openpyxl.reader.excel.load_workbook(filename) except Exception, e: import traceback, sys print 'Failed to open %s : %s' % (filename, e)
def accounts(self): workbook = xlrd.open_workbook('Records.xlsx') accounts = workbook.sheet_by_name('Accounts') print(accounts.cell(0, 1))
def customers(self): workbook = xlrd.open_workbook('Records.xlsx') customers = workbook.sheet_by_name('Customers') print(customers.cell(0, 1))
def vendors(self): workbook = xlrd.open_workbook('Records.xlsx') vendors = workbook.sheet_by_name('Vendors') print(vendors.cell(0, 1))