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_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 library(request):
lib = Files()
lib.open_workbook(request.param)
yield lib
lib.close_workbook()
def test_create_after_close(fmt):
library = Files()
library.create_workbook(fmt=fmt)
library.close_workbook()
library.create_workbook(fmt=fmt)
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(filename):
library = Files()
library.open_workbook(filename)
assert library.workbook is not None
library.close_workbook()
