def xls2html(xls_sbtab,file_name,sbtype,def_file=None,def_file_name=None):
'''
generates html view out of xls file
'''
if def_file:
FileValidClass = validatorSBtab.ValidateFile(def_file,def_file_name)
def_delimiter = FileValidClass.checkseparator(def_file)
else:
def_file_open = open('./definitions/definitions.tsv','r')
def_file = def_file_open.read()
def_delimiter = '\t'
col2description = findDescriptions(def_file,def_delimiter,sbtype)
nice_sbtab = '<p><h2><b>'+file_name+'</b></h2></p>'
ident_url = False
dbook = tablib.Databook()
xl = xlrd.open_workbook(file_contents=xls_sbtab)
for sheetname in xl.sheet_names():
dset = tablib.Dataset()
dset.title = sheetname
sheet = xl.sheet_by_name(sheetname)
for row in range(sheet.nrows):
if row == 0:
new_row = ''
for thing in sheet.row_values(row):
if not thing == '': new_row += thing
nice_sbtab += '<a style="background-color:#00BFFF">'+new_row+'</a><br>'
nice_sbtab += '<table>'
elif row == 1:
new_row = ''
for i,thing in enumerate(sheet.row_values(row)):
try: title = col2description[thing[1:]]
except: title = ''
if not thing == '': new_row += '<td title="'+title+'">'+str(thing)+'</\td>'
if "Identifiers:" in thing:
urn_str = re.search("\w*\.\w*",thing)
urn = urn_str.group(0)
ident_url = 'http://identifiers.org/'+urn+'/'
ident_col = i
nice_sbtab += '<tr bgcolor="#87CEFA">'+new_row+'</tr>'
else:
new_row = ''
for i,thing in enumerate(sheet.row_values(row)):
if not ident_url:
new_row += '<td>'+str(thing)+'</td>'
else:
if i == ident_col:
ref_string = ident_url+thing
new_row += '<td><a href="'+ref_string+'" target="_blank">'+str(thing)+'</a></\td>'
else:
new_row += '<td>'+str(thing)+'</td>'
nice_sbtab += '<tr>'+new_row+'</tr>'
return nice_sbtab
def import_set(dset, in_stream, headers=True):
"""Returns dataset from XLS stream. Default sheet 1"""
dset.wipe()
book = xlrd.open_workbook(in_stream)
sheet = book.sheet_by_index(0)
ncols = sheet.ncols
for row in range(sheet.nrows):
row_list = [sheet.cell(row,col).value for col in range(ncols)]
if (row == 0) and (headers):
dset.headers = row_list
else:
dset.append(row_list)
def loadXLS(fpath, headers, set_only):
dbook = tablib.Databook()
f = open(fpath, 'rb')
xl = xlrd.open_workbook(file_contents=f.read())
for sheetname in xl.sheet_names():
dset = tablib.Dataset()
dset.title = sheetname
sheet = xl.sheet_by_name(sheetname)
for row in range(sheet.nrows):
if (row == 0) and (headers):
dset.headers = sheet.row_values(row)
else:
dset.append(sheet.row_values(row))
dbook.add_sheet(dset)
if set_only:
return dbook.sheets()[0]
else:
return dbook
def detect(stream):
"""Returns True if given stream is a readable excel file."""
try:
xlrd.open_workbook(file_contents=stream)
return True
except (TypeError, XLRDError):
pass
try:
xlrd.open_workbook(file_contents=stream.read())
return True
except (AttributeError, XLRDError):
pass
try:
xlrd.open_workbook(filename=stream)
return True
except:
return False
