class ExcelFileRead():
#initial the split character for saving user operatoins.
def __init__(self):
self.SPLITCHAR = "|"
self.bookIns = Book()
#open one excel test case file to load.
def open_excel(self, fileName=r'../TestData/dataDriven_sample.xlsx'):
try:
#read workbook file into various excelFile.
self.bookIns = xlrd.open_workbook(fileName)
return True
except Exception:
traceback.print_exc()
print("Exception when open excel file " + fileName)
return False
#read first work sheet of test case and save actions into list.
def read_FirstWorksheet(self):
self.firstWorksheet = self.bookIns.sheet_by_index(0)
self.listOperation = [
] #use this list as repository of use's behaviors described in test case.
#user operation description should begin from row 2 in excel. please refer to test case
for self.iIterRow in range(1, self.firstWorksheet.nrows):
#one string maps one action, append into list.
self.listOperation.append(self.firstWorksheet.cell(self.iIterRow,1).value + self.SPLITCHAR + \
self.firstWorksheet.cell(self.iIterRow,2).value + self.SPLITCHAR + \
str(self.firstWorksheet.cell(self.iIterRow,3).value) )
return self.listOperation
def TestIterationList(self):
#print(len(self.listOperation))
#print(self.listOperation)
for oneAction in self.listOperation:
print(oneAction)
def Get_Excel_Row_Values(filepath,sheetName,uniqueValue):
Book = xlrd.open_workbook(filepath)
Sheet = Book.sheet_by_name(sheetName)
row_count = Sheet.nrows
col_count = Sheet.ncols
for i in range(0,row_count):
for j in range(0,col_count):
value = Sheet.cell_value(i, j)
if value == uniqueValue:
row_values = Sheet.row_values(i, 0)
return row_values
def get_datasheet_names(wb: Book) -> List[str]:
"""Gets data sheet names from a workbook
Args:
wb (Book): Pre-loaded XLRD Workbook obj
Returns:
list(str): List of datasheet names
"""
data_sheets: List[xlrd.sheet] = \
[x for x in wb.sheets() if x.name.startswith('data')]
datasheet_names: List[str] = [x.name for x in data_sheets]
return datasheet_names
def read_xlsform_data(workbook: Book) -> OrderedDict:
"""Return XLSForm definition data read from an XLRD Workbook."""
sheets = {x.name for x in workbook.sheets()}
required = {"survey", "choices", "settings"}
if not required.issubset(sheets):
raise ValueError(
"The required sheets for an XLSForm definition ({0}) were not "
"found in the workbook sheets ({1}).".format(required, sheets))
survey = xlrd_sheet_to_list_of_dict(
workbook.sheet_by_name(sheet_name='survey'))
choices = xlrd_sheet_to_list_of_dict(
workbook.sheet_by_name(sheet_name='choices'))
settings = xlrd_sheet_to_list_of_dict(
workbook.sheet_by_name(sheet_name='settings'))
form_def = OrderedDict()
form_def['@settings'] = settings[0]
for item in survey:
if item['type'].startswith('select'):
select_type, choice_name = item['type'].split(' ')
choice_list = [x for x in choices
if x['list_name'] == choice_name]
item['choices'] = choice_list
form_def[item['name']] = item
return form_def
def count_records(filename, outfile=sys.stdout):
from xlrd.biffh import biff_count_records
bk = Book()
bk.biff2_8_load(filename=filename, logfile=outfile, )
biff_count_records(bk.mem, bk.base, bk.stream_len, outfile)
def dump(filename, outfile=sys.stdout, unnumbered=False):
from xlrd.biffh import biff_dump
bk = Book()
bk.biff2_8_load(filename=filename, logfile=outfile, )
biff_dump(bk.mem, bk.base, bk.stream_len, 0, outfile, unnumbered)
def open_workbook_2007_xml(
zf,
component_names,
logfile=sys.stdout,
verbosity=0,
use_mmap=0,
formatting_info=0,
on_demand=0,
ragged_rows=0,
):
ensure_elementtree_imported(verbosity, logfile)
bk = Book()
bk.logfile = logfile
bk.verbosity = verbosity
bk.formatting_info = formatting_info
if formatting_info:
raise NotImplementedError("formatting_info=True not yet implemented")
bk.use_mmap = False #### Not supported initially
bk.on_demand = on_demand
if on_demand:
if verbosity:
print("WARNING *** on_demand=True not yet implemented; falling back to False", file=bk.logfile)
bk.on_demand = False
bk.ragged_rows = ragged_rows
x12book = X12Book(bk, logfile, verbosity)
zflo = zf.open('xl/_rels/workbook.xml.rels')
x12book.process_rels(zflo)
del zflo
zflo = zf.open('xl/workbook.xml')
x12book.process_stream(zflo, 'Workbook')
del zflo
props_name = 'docProps/core.xml'
if props_name in component_names:
zflo = zf.open(props_name)
x12book.process_coreprops(zflo)
x12sty = X12Styles(bk, logfile, verbosity)
if 'xl/styles.xml' in component_names:
zflo = zf.open('xl/styles.xml')
x12sty.process_stream(zflo, 'styles')
del zflo
else:
# seen in MS sample file MergedCells.xlsx
pass
sst_fname = 'xl/sharedStrings.xml'
x12sst = X12SST(bk, logfile, verbosity)
if sst_fname in component_names:
zflo = zf.open(sst_fname)
x12sst.process_stream(zflo, 'SST')
del zflo
for sheetx in range(bk.nsheets):
fname = x12book.sheet_targets[sheetx]
zflo = zf.open(fname)
sheet = bk._sheet_list[sheetx]
x12sheet = X12Sheet(sheet, logfile, verbosity)
heading = "Sheet %r (sheetx=%d) from %r" % (sheet.name, sheetx, fname)
x12sheet.process_stream(zflo, heading)
del zflo
comments_fname = 'xl/comments%d.xml' % (sheetx + 1)
if comments_fname in component_names:
comments_stream = zf.open(comments_fname)
x12sheet.process_comments_stream(comments_stream)
del comments_stream
sheet.tidy_dimensions()
return bk
def __init__(self):
self.SPLITCHAR = "|"
self.bookIns = Book()
def open_workbook_2007_xml(
zf, component_names, logfile=sys.stdout, verbosity=0, use_mmap=0, formatting_info=0, on_demand=0, ragged_rows=0
):
ensure_elementtree_imported(verbosity, logfile)
bk = Book()
bk.logfile = logfile
bk.verbosity = verbosity
bk.formatting_info = formatting_info
if formatting_info:
raise NotImplementedError("formatting_info=True not yet implemented")
bk.use_mmap = False #### Not supported initially
bk.on_demand = on_demand
if on_demand:
if verbosity:
print("WARNING *** on_demand=True not yet implemented; falling back to False", file=bk.logfile)
bk.on_demand = False
bk.ragged_rows = ragged_rows
x12book = X12Book(bk, logfile, verbosity)
zflo = zf.open("xl/_rels/workbook.xml.rels")
x12book.process_rels(zflo)
del zflo
zflo = zf.open("xl/workbook.xml")
x12book.process_stream(zflo, "Workbook")
del zflo
props_name = "docProps/core.xml"
if props_name in component_names:
zflo = zf.open(props_name)
x12book.process_coreprops(zflo)
x12sty = X12Styles(bk, logfile, verbosity)
if "xl/styles.xml" in component_names:
zflo = zf.open("xl/styles.xml")
x12sty.process_stream(zflo, "styles")
del zflo
else:
# seen in MS sample file MergedCells.xlsx
pass
sst_fname = "xl/sharedStrings.xml"
x12sst = X12SST(bk, logfile, verbosity)
if sst_fname in component_names:
zflo = zf.open(sst_fname)
x12sst.process_stream(zflo, "SST")
del zflo
for sheetx in range(bk.nsheets):
fname = x12book.sheet_targets[sheetx]
zflo = zf.open(fname)
sheet = bk._sheet_list[sheetx]
x12sheet = X12Sheet(sheet, logfile, verbosity)
heading = "Sheet %r (sheetx=%d) from %r" % (sheet.name, sheetx, fname)
x12sheet.process_stream(zflo, heading)
del zflo
comments_fname = "xl/comments%d.xml" % (sheetx + 1)
if comments_fname in component_names:
comments_stream = zf.open(comments_fname)
x12sheet.process_comments_stream(comments_stream)
del comments_stream
sheet.tidy_dimensions()
return bk