示例#1
0
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
示例#3
0
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
示例#4
0
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)
示例#7
0
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
示例#8
0
 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