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))
