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 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 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 control_room_start_fix_process(self, data, original):
tables = Tables()
excel = Files()
google = Google()
conflicts = []
updated_table = tables.export_table(original,
with_index=True,
as_list=False)
for k in data.keys():
copyof_orig = copy.deepcopy(original)
if "nameerror" in k:
mid, _ = data[k].split(" - ")
tables.filter_table_by_column(copyof_orig, "Account Id", "==",
mid)
row = tables.get_table_row(copyof_orig, 0)
conflicts.append({"id": mid, "name": row["Name in SAP"]})
name_position = updated_table["Account Id"].index(mid)
updated_table["Name OK in SFDC"][name_position] = "TRUE"
if conflicts:
self.control_room_run_process(CONTROL_ROOM_PROCESS_FIX_SFDC,
conflicts)
google.set_robocorp_vault(vault_name="googlecloud",
vault_secret_key="credentials")
google.init_drive(use_robocorp_vault=True)
del updated_table["index"]
excel.create_workbook(COMPARISON_EXCEL)
excel.append_rows_to_worksheet(updated_table, header=True)
excel.save_workbook()
google.drive_upload_file(COMPARISON_EXCEL,
GOOGLE_DRIVE_SYNC_FOLDER,
overwrite=True)
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()
def test_create_workbook_default_sheet(fmt):
library = Files()
library.create_workbook(fmt=fmt)
assert library.list_worksheets() == ["Sheet"]
library.create_worksheet("Test")
assert library.list_worksheets() == ["Sheet", "Test"]
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 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_append_to_worksheet_empty_with_headers(fmt):
table = Table(
[
{"Index": 98, "Date": "today", "Id": "some_value"},
{"Index": 99, "Date": "tomorrow", "Id": "another_value"},
]
)
library = Files()
library.create_workbook(fmt=fmt)
library.append_rows_to_worksheet(table, header=True)
result = library.read_worksheet_as_table()
assert len(result) == 3
assert result[0] == ["Index", "Date", "Id"]
def _library(excel_file):
lib = Files()
excel_path = RESOURCES_DIR / excel_file
lib.open_workbook(excel_path)
yield lib
lib.close_workbook()
def open_file(self):
if self.lib is None:
self.lib = Files()
self.lib.open_workbook(self.path_to_file)
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 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
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 test_create_without_close(fmt):
library = Files()
library.create_workbook(fmt=fmt)
library.create_workbook(fmt=fmt)
def test_open_missing(filename):
with pytest.raises(FileNotFoundError):
lib = Files()
lib.open_workbook(filename)
def __init__(self, path_to_excel_file: str):
self.path_to_file = path_to_excel_file
self.lib = Files()
def test_create_after_close(fmt):
library = Files()
library.create_workbook(fmt=fmt)
library.close_workbook()
library.create_workbook(fmt=fmt)
def test_wrong_extension_fallback(filename):
library = Files()
library.open_workbook(filename)
assert library.workbook is not None
library.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 library(request):
lib = Files()
lib.open_workbook(request.param)
yield lib
lib.close_workbook()
def generate_data(user_count=100, feedback_count=5):
fake = Faker()
files = Files()
files.create_workbook("userdata.xlsx")
files.create_worksheet("profile", exist_ok=True)
files.create_worksheet("feedback", exist_ok=True)
users = []
names = []
for i in range(user_count):
userprofile = fake.profile()
if userprofile["name"] in names:
print("name %s already exists", userprofile["name"])
continue
userprofile.pop("website")
userprofile.pop("current_location")
files.append_rows_to_worksheet(userprofile, "profile", header=True)
users.append(userprofile)
for i in range(1, 16):
selected_user = users[i]
feedback = {
"name": selected_user["name"],
"feedback": choice(good_feedback)
}
print(feedback)
files.append_rows_to_worksheet(feedback, "feedback", header=True)
for i in range(17, 22):
selected_user = users[i]
feedback = {
"name": selected_user["name"],
"feedback": choice(neutral_feedback)
}
print(feedback)
files.append_rows_to_worksheet(feedback, "feedback", header=True)
for i in range(23, 28):
selected_user = users[i]
feedback = {
"name": selected_user["name"],
"feedback": choice(bad_feedback)
}
print(feedback)
files.append_rows_to_worksheet(feedback, "feedback", header=True)
files.remove_worksheet("Sheet")
files.save_workbook()
import os
import emailer
from RPA.Excel.Files import Files
from RPA.Tables import Tables
EMPLOYEES_EXCEL_PATH = os.environ["EMPLOYEES_EXCEL_PATH"]
TRAININGS_EXCEL_PATH = os.environ["TRAININGS_EXCEL_PATH"]
excel = Files()
tables = Tables()
def send_training_reminders():
employees = get_active_employees(EMPLOYEES_EXCEL_PATH)
trainings = read_excel_as_table(TRAININGS_EXCEL_PATH)
send_reminders(employees, trainings)
def get_active_employees(excel_path):
employees = read_excel_as_table(excel_path)
tables.filter_table_by_column(employees, "Status", "==", "Active")
tables.filter_table_by_column(employees, "Category", "==", "Employee")
return employees
def read_excel_as_table(excel_path):
try:
excel.open_workbook(excel_path)
return excel.read_worksheet_as_table(header=True)
finally:
excel.close_workbook()
def test_create_workbook(fmt, instance):
library = Files()
library.create_workbook(fmt=fmt)
assert isinstance(library.workbook, instance)
assert library.workbook._book is not None
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")
