def fill_the_form_using_the_data_from_the_excel_file(): excel = Files() excel.open_workbook("SalesData.xlsx") sales_reps = excel.read_worksheet_as_table(header=True) excel.close_workbook() for sales_rep in sales_reps: fill_and_submit_the_form_for_one_person(sales_rep)
def read_excel_worksheet(path, worksheet): lib = Files() lib.open_workbook(path) try: return lib.read_worksheet(worksheet) finally: lib.close_workbook()
def test_wrong_extension_fallback_xls(): # openpyxl will refuse to read wrong extension and xlrd does not support xlsx library = Files() with pytest.raises(ValueError, match=".*wrong_extension.xls.*path.*extension.*"): library.open_workbook("tests/resources/wrong_extension.xls") assert library.workbook is None
def test_wrong_extension_fallback_xls(): # openpyxl will refuse to read wrong extension and xlrd does not support xlsx library = Files() path = str(RESOURCES_DIR / "wrong_extension.xls") with pytest.raises(ValueError, match=".*wrong_extension.xls.*path.*extension.*"): library.open_workbook(path) assert library.workbook is None
def main(engine=None): rh = RecordHandler() excel = Files() excel.open_workbook("challenge.xlsx") sheet = excel.read_worksheet_as_table(header=True) engines_to_run = [engine] if engine in engines.keys() else engines.keys() for engine in engines_to_run: rh.set_engine(engine) eval(f"{engines[engine]}(rh, sheet)")
def test_read_worksheet_with_formulas(excel_file, data_only): library = Files() excel_path = RESOURCES_DIR / excel_file library.open_workbook(excel_path, data_only=data_only) assert library.get_worksheet_value(2, "A") == 1 assert library.get_worksheet_value(2, "B") == 3 if library.workbook.path.suffix == ".xlsx": assert library.get_worksheet_value(2, "C") == 4 if data_only else "=A2+B2" else: assert library.get_worksheet_value(2, "C") == 4 library.close_workbook()
class ExcelUtility: def __init__(self, path_to_excel_file: str): self.path_to_file = path_to_excel_file self.lib = Files() def open_file(self): if self.lib is None: self.lib = Files() self.lib.open_workbook(self.path_to_file) def set_active_sheet(self, sheet_name: str): self.lib.set_active_worksheet(sheet_name) def write_agencies_to_file(self, agencies: []): row_number: int = 2 for item in agencies: agency: AgencyModel = item self.lib.set_worksheet_value(row_number, 1, agency.name) self.lib.set_worksheet_value(row_number, 2, agency.amount) self.lib.set_worksheet_value(row_number, 3, agency.link) row_number += 1 def write_table_page_data_to_file(self, data: [], row_number: int = 2): for item in data: indv_invst_item: IndividualInvestmentsModel = item self.lib.set_worksheet_value(row_number, 1, indv_invst_item.uii) self.lib.set_worksheet_value(row_number, 2, indv_invst_item.uii_link) self.lib.set_worksheet_value(row_number, 3, indv_invst_item.bureau) self.lib.set_worksheet_value(row_number, 4, indv_invst_item.investment_title) self.lib.set_worksheet_value(row_number, 5, indv_invst_item.total_fy_spending) self.lib.set_worksheet_value(row_number, 6, indv_invst_item.type_) self.lib.set_worksheet_value(row_number, 7, indv_invst_item.cio_rating) self.lib.set_worksheet_value(row_number, 8, indv_invst_item.project_num) row_number += 1 return row_number def save_and_close_file(self) -> object: if self.lib is not None: self.lib.save_workbook(self.path_to_file) self.lib.close_workbook()
def get_orders(self, excel): files = Files() workbook = files.open_workbook(excel) rows = workbook.read_worksheet(header=True) tables = Tables() table = tables.create_table(rows) tables.filter_empty_rows(table) orders = [] for row in table: order = { "item": row.Item, "zip": int(row.Zip), "first_name": row[0].split()[0], "last_name": row[0].split()[1] } orders.append(order) return orders
def library(request): lib = Files() lib.open_workbook(request.param) yield lib lib.close_workbook()
def test_wrong_extension_fallback_xlsx(): # openpyxl does not support xls (actual format) but xlrd will succeed library = Files() path = str(RESOURCES_DIR / "wrong_extension.xlsx") library.open_workbook(path) assert library.workbook is not None
def test_open_missing(filename): with pytest.raises(FileNotFoundError): lib = Files() lib.open_workbook(filename)
def _library(excel_file): lib = Files() excel_path = RESOURCES_DIR / excel_file lib.open_workbook(excel_path) yield lib lib.close_workbook()
def test_wrong_extension_fallback_xlsx(): # openpyxl does not support xls (actual format) but xlrd will succeed library = Files() library.open_workbook("tests/resources/wrong_extension.xlsx") assert library.workbook is not None
def test_wrong_extension_fallback(filename): library = Files() library.open_workbook(filename) assert library.workbook is not None library.close_workbook()
myeval( browser, '//input[@ng-reflect-name="labelCompanyName"]', row["Company Name"], ) myeval(browser, '//input[@ng-reflect-name="labelRole"]', row["Role in Company"]) myeval(browser, '//input[@ng-reflect-name="labelAddress"]', row["Address"]) myeval(browser, '//input[@ng-reflect-name="labelEmail"]', row["Email"]) myeval( browser, '//input[@ng-reflect-name="labelPhone"]', str(row["Phone Number"]) ) browser.execute_javascript( """document.querySelector('input[type="submit"]').click();""" ) result = browser.execute_javascript( """return document.querySelector('.message2').textContent;""" ) rh.check_for_new_record(browser, result) browser.close_browser() rh = RecordHandler() excel = Files() excel.open_workbook("challenge.xlsx") sheet = excel.read_worksheet_as_table(header=True) rh.set_engine("playwright") run_with_playwright(rh, sheet) rh.set_engine("rpabrowser") run_with_rpabrowser(rh, sheet)
def main(input_excel): library = Files() library.open_workbook(input_excel) sheets = library.list_worksheets() for sheet in sheets: sheet = sheet.strip() testcases = [] variables = [] tests_text = "" keywords = dict() variable_index = 1 settings_text = f"Documentation Created from sheet {sheet} of {input_excel}\n" table = library.read_worksheet_as_table(name=sheet, header=True) for row in table.iter_dicts(): if row["test case"]: testcasename = row["test case"].title() tests_text += testcasename testcases.append(testcasename) else: keyword_name = row["steps"].title().replace(" ", "_") tests_text += f"{' '*4}{keyword_name.replace('_', ' ')}" if keyword_name not in keywords.keys(): keywords[keyword_name] = 0 argument_count = 0 for idx in range(1, 10): ar = f"arg{idx}" if ar in row.keys() and row[ar]: argument_count += 1 try: converted_list = (str(row[ar]).replace( "[", "").replace("]", "").split(",")) if len(converted_list) > 1: variable_name = f"@{{LIST_{variable_index}}}" variables.append( f"{variable_name}{' '*4}{' '.join(converted_list)}" ) tests_text += ( f"{' '*4}{variable_name.replace('@', '$')}" ) else: tests_text += f"{' '*4}{row[ar]}" except TypeError: pass if argument_count > keywords[keyword_name]: keywords[keyword_name] = argument_count tests_text += "\n" robotfilename = f"{sheet}.robot" print("Creating Robot file:", robotfilename) with open(robotfilename, "w") as robotfile: for tc in testcases: print("\tCreating Test Case:", tc) robotfile.write("*** Settings ***\n" + settings_text) if len(variables) > 0: robotfile.write("\n*** Variables ***\n") for var in variables: robotfile.write(f"{var}\n") if tests_text: robotfile.write("\n*** Test Cases ***\n" + tests_text) if len(keywords) > 0: robotfile.write("\n*** Keywords ***\n") for kw, val in keywords.items(): robotfile.write(kw.replace("_", " ")) if val > 0: robotfile.write("\n\t[Arguments]") for idx in range(1, val + 1): robotfile.write(f"{' '*4}${{arg{idx}}}") robotfile.write("\n") for idx in range(1, val + 1): robotfile.write(f"\tLog{' '*4}${{arg{idx}}}\n") else: robotfile.write("\n\tNo Operation\n")