def gen_csv_from_sheet(xwsheet: xw.Sheet):
first_row = xwu.find_first_data_row(xwsheet)
last_row = xwu.get_insert_data_row_number(xwsheet) + 1
first_col = xwu.find_col_by_metakey('IsFinalised', xwsheet)
last_col = xwu.get_last_col(xwsheet, first_col) + 1
with open(filename, 'w') as file:
for r in range(first_row, last_row):
for c in range(first_col, last_col):
extract_range = xwsheet.range(r, c)
ref_string = get_ref_from_cell(extract_range)
if (ref_string is not None):
file.write(ref_string + '\n')
def test_column_lock_data_copy_paste(ticket_id, test_description,
expected_result, book_test, sheet,
field_to_test, copy_sheet):
actual_result = ''
test_status = None
# target metakey column number
target_field_col_number = xwu.find_col_by_multi_metakey([field_to_test],
sheet)
first_data_row = xwu.find_first_data_row(sheet)
row_number_insert = xwu.get_insert_data_row_number(sheet)
for test_execution in range(3):
selected_row = rnd.randint(first_data_row, row_number_insert)
# record the current target data value
initial_target_cell_range = sheet.range(selected_row,
target_field_col_number)
initial_target_line_data = initial_target_cell_range.value
# copy/paste value into selected cell
book_test.clicker.setup("OK")
pwau.copy_paste_to_sheet_range(copy_sheet, copy_sheet.range(1, 1),
sheet, initial_target_cell_range)
time.sleep(3)
# check that an error message appeared and the data remains unchanged
if book_test.clicker.click_count == 0:
test_status = 0
actual_result = "The error message did not appear for column " + field_to_test + "for at least one instance. "
if sheet.range(
selected_row,
target_field_col_number).value != initial_target_line_data:
test_status = 0
actual_result = actual_result + "For at least one test instance the value in field " + field_to_test + " has changed"
if test_status == 0:
pass
else:
test_status = 1
actual_result = "Error messages appeared for the field " + field_to_test + " and the " + field_to_test + " field remains unchanged"
# Write the test to the database
logger.write_result(ticket_id, test_description, expected_result,
actual_result, test_status)
def test_field_lock(ticket_id, test_description, expected_result, book_test,
sheet, field_to_test):
actual_result = ""
test_status = None
# target metakey column number
target_field_col_number = xwu.find_col_by_multi_metakey([field_to_test],
sheet)
first_data_row = xwu.find_first_data_row(sheet)
row_number_insert = xwu.get_insert_data_row_number(sheet)
for test_execution in range(3):
selected_row = rnd.randint(first_data_row, row_number_insert)
# record the current target data value
initial_target_line_data = sheet.range(selected_row,
target_field_col_number).value
# send keys to line number cell
book_test.clicker.setup("OK")
updated_target_line_data = fc.gen_line_desc()
xwu.ensure_focus(sheet,
sheet.range(selected_row, target_field_col_number))
pwa.keyboard.send_keys(updated_target_line_data + "~")
time.sleep(4)
# check that an error message appeared and the data remains unchanged
if book_test.clicker.click_count == 0:
test_status = 0
actual_result = "The error message did not appear for column " + field_to_test + "for at least one instance. "
if sheet.range(
selected_row,
target_field_col_number).value != initial_target_line_data:
test_status = 0
actual_result = actual_result + "For at least one test instance the value in field " + field_to_test + " has changed"
if test_status == 0:
pass
else:
test_status = 1
actual_result = "Error messages appeared for the field " + field_to_test + " and the " + field_to_test + " field remains unchanged"
# Write the test to the database
logger.write_result(ticket_id, test_description, expected_result,
actual_result, test_status)
def test_line_level_field(ticket_id, test_description, expected_result,
field_to_test):
"""
SUMMARY:
Ensure data cannot be changed in especific columns received as paramedters.
As an expected result, an error message must appear in the screen and the
data remains unchanged
DETAILS:
Opens Order Sheet workbook and runs Reset macro
Reads the column for the especific field to test in the first data sheet
Gets the first row with data in a range
Gets the first row available for insert in a range
Runs 10 times a test getting a row at random and attempts to write a value
Check if the script changed the data or an screen error appeared and the data remains unchanged.
Write the test result to the database
NOTES:
This script will generate error messages in the screen and will attemp to
answer them. Thus, the focus should always be hold by the worksheet, so using
the keyboard during the test will make the script to fail.
"""
#set up the ordersheet with tinysheet2 data
book_test = setup_order_sheet(parameter_path)
global pid
pid = book_test.process_id
#tab object
ts2_tab1 = book_test.order_sheet.sheets[
book_test.excel_book_parameters['OrderSheet Tabs'][0]]
#target metakey column number
target_field_col_number = xwu.find_col_by_multi_metakey([field_to_test],
ts2_tab1)
first_data_row = xwu.find_first_data_row(ts2_tab1)
row_number_insert = xwu.get_insert_data_row_number(ts2_tab1)
#run test 10 times for the target field (different rows)
for test_execution in range(10):
#select row number
selected_row = rnd.randint(first_data_row, row_number_insert)
#record the current target data value
initial_target_line_data = ts2_tab1.range(
selected_row, target_field_col_number).value
#send keys to line number cell
book_test.clicker.setup("OK")
updated_target_line_data = fc.gen_line_desc()
xwu.ensure_focus(ts2_tab1,
ts2_tab1.range(selected_row, target_field_col_number))
pwa.keyboard.send_keys(updated_target_line_data + "~")
time.sleep(3)
#check that an error message appeared and the data remains unchanged
print(book_test.clicker.click_count)
if book_test.clicker.click_count == 0:
test_status = 0
actual_result = "For at least one test instance the error message did not appear, "
if ts2_tab1.range(
selected_row,
target_field_col_number).value != initial_target_line_data:
test_status = 0
actual_result = "For at least one test instance the value in field " + field_to_test + " has changed"
try:
if test_status == 0:
pass
except:
test_status = 1
actual_result = "Error messages appeared and the " + field_to_test + " field remains unchanged"
#Write the test to the database
logger.write_result(ticket_id, test_description, expected_result,
actual_result, test_status)
#Close the ordersheet/clicker thread
book_test.clicker.shut_down()
book_test.order_sheet.close()
def test1():
# setup parameters
ticket_id = ''
test_description = 'After inserting a wholesale line which will be transmitted to the OrderBook. For a partner without any units, populate a unit for them. Ensure that both Delivery Not Before/After dates have been populated correctly.'
expected_result = 'Delivery Not Before and Delivery Not After Dates have been Populated correctly.'
#Get WSR List
whole_sale_ready_list = fc.get_wholesale_ready_metakey_list(
os_column_tab, exclude_order_book=True)
whole_sale_ready_list.append("PartnerOriginalUnitsBySize")
whole_sale_ready_list.append("SizeRange")
whole_sale_ready_list.append("OriginalDespatchMonth")
#Get Random Partner
partner_list = cu.get_partners(order_sheet)
random_partner = rnd.choice(partner_list)
partner_list.remove(random_partner)
#Get data insert row
data_insert_row_no = xwu.get_insert_data_row_number(tiny_sheet_tab_1)
#Populate Wholesale ready fields and Units for given Partner
fc.test_populator(tiny_sheet_tab_1,
data_insert_row_no,
whole_sale_ready_list, [random_partner],
max_col_range=2000)
#Get ORID of inputted row
order_row_id_val = str(
tiny_sheet_tab_1.range(
data_insert_row_no,
metakey_col_dict_tiny_sheet[("OrderRowId", None, None)]).value)
#Gets a dictionry of possible metakeys to insert units into to proc the delivery not before/after
by_units_ob_metakey_list = []
for key_tuple in metakey_col_dict_tiny_book:
if key_tuple[0] in [
"PartnerOriginalUnitsBySize", "PartnerRevisedUnitsBySize"
] and key_tuple[1] != random_partner:
by_units_ob_metakey_list.append(key_tuple)
by_units_ob_metakey_set = set(by_units_ob_metakey_list)
metakey_units_by_size_insert_dict = xwu.get_metakey_dict_for_sheet(
by_units_ob_metakey_set, metakey_col_dict_tiny_book)
#Sync the orderbook
tool_object_ob.refresh_all_sheets()
#Get Row no in the ob to insert data into
ob_row_insert_no = xwu.find_row_no_by_orwid(tiny_book_tab_1,
order_row_id_val)
#populate a random units cell block
selected_metakey_tuple = rnd.choice(
list(metakey_units_by_size_insert_dict.keys()))
selected_metakey = selected_metakey_tuple[0]
selected_partner_ob = selected_metakey_tuple[1]
fc.test_populator(tiny_book_tab_1, ob_row_insert_no, [selected_metakey],
[selected_partner_ob])
#Get the original despatch month of the partner you inputted data into
original_despatch_month_value = tiny_book_tab_1.range(
ob_row_insert_no, metakey_col_dict_tiny_book[("OriginalDespatchMonth",
None, None)]).value
#Set the arcadia book to the sheet you need and get the deliverynotvefore/after required values
arcadia_tool.set_current_sheet(selected_partner_ob)
expected_delivery_not_before_value = arcadia_tool.get_lookup_value_given_despatch_month_for_partner(
"DeliveryNotBefore", original_despatch_month_value)
expected_delivery_not_after_value = arcadia_tool.get_lookup_value_given_despatch_month_for_partner(
"DeliveryNotAfter", original_despatch_month_value)
#Check that delivery not before/after have been populated correctly
if tiny_book_tab_1.range(
ob_row_insert_no, metakey_col_dict_tiny_book[(
"DeliveryNotBefore", selected_partner_ob,
None)]).value == expected_delivery_not_before_value:
test_status = 1
actual_result = "The Delivery Not Before Date has been correctly populated"
else:
test_status = 0
actual_result = "The Delivery Not Before Date has been incorrectly populated"
if tiny_book_tab_1.range(
ob_row_insert_no, metakey_col_dict_tiny_book[(
"DeliveryNotAfter", selected_partner_ob,
None)]).value == expected_delivery_not_after_value:
actual_result = actual_result + ", The Delivery Not After Date has been correctly populated"
else:
test_status = 0
actual_result = actual_result + ", The Delivery Not After Date has been incorrectly populated"
logger.write_result(ticket_id, test_description, expected_result,
actual_result, test_status)
def copy_paste_valid_dropdown_values_into_each_column_test():
"""
SUMMARY:
Tests if the allowed values in the Excel data validation can be pasted into cells in the column
DETAILS:
Opens Order Sheet workbook and runs Reset macro
Reads all the columns in the first data sheet and builds a list of the metakeys that use data validation
Removes certain values from that list
Selects one partner name, at random, from the list of partners in the Order Sheet
Populates a temporary worksheet with all the allowed values for each of the metakeys in the list
For each metakey in the list, attempts to copy & paste allowed values from the temporary sheet into the data sheet.
Checks for the existence of a message box after each paste – this indicates a failure.
"""
#ticket id
ticket_id = ""
#setup ordersheet
ordersheet = setup_order_sheet(parameter_path)
# take the JIRA ticket Id from the excelBook class
ticket_id = ordersheet.test_tag
#setup copy book
copy_book = xw.Book()
copy_sheet = copy_book.sheets[0]
#setup TS1 Tab 1
TS1_Tab1 = ordersheet.order_sheet.sheets[ordersheet.excel_book_parameters['OrderSheet Tabs'][0]]
#Get list of dropdown columns and list of partners
list_of_drop_down_columns_metakeys = fc.get_list_of_dropdown_metakeys(TS1_Tab1)
list_of_drop_down_columns_metakeys.remove("SizeRange")
list_of_drop_down_columns_metakeys.remove("ArcadiaCategory")
list_of_drop_down_columns_metakeys.remove("DeliveryRoute")
list_of_drop_down_columns_metakeys.remove("PartnerSizingCategory")
list_of_drop_down_columns_metakeys.remove("PartnerSeason")
list_of_partners = fc.get_all_partner_list(TS1_Tab1)
#Select random partner
selected_partner = rnd.choice(list_of_partners)
#Get first datarow
first_datarow = xwu.find_first_data_row(TS1_Tab1)
last_data_row = xwu.get_insert_data_row_number(TS1_Tab1)
partner_name_row_no = xwu.find_row_by_metakey(TS1_Tab1, "PartnerName")
#populate the copysheet with data for each dropdown to copy/paste into
input_generated_values_into_copy_sheet(list_of_drop_down_columns_metakeys, TS1_Tab1, copy_sheet,first_datarow, last_data_row)
#copy/paste valid values back into the correct columns
ticket_id_iterator = 1
for metakey in list_of_drop_down_columns_metakeys:
time.sleep(2)
#ticket_id_iterator = 1
ticket_id_metakey = ticket_id + str(ticket_id_iterator)
test_description = "Ensure it is possible to copy and paste valid values into the dropdown field for metakey: " + metakey
expected_result = "Valid values have successfully been copied and pasted into the sheet for column with metakey: " + metakey
meta_key_col = xwu.find_col_by_multi_metakey([metakey], copy_sheet)
meta_key_col_in_sheet = xwu.find_col_by_multi_metakey([metakey], TS1_Tab1)
partner_value_for_metakey = TS1_Tab1.range(partner_name_row_no, meta_key_col_in_sheet).value
if partner_value_for_metakey is not None:
meta_key_col_in_sheet = xwu.find_col_by_multi_metakey([metakey, selected_partner], TS1_Tab1)
data_row_number_end = 1
data_row_number_start = 2
row_data = copy_sheet.range(data_row_number_end, meta_key_col).value
while row_data is not None:
data_row_number_end += 1
row_data = copy_sheet.range(data_row_number_end, meta_key_col).value
ordersheet.clicker.setup(("OK"))
copy_data_range = copy_sheet.range((data_row_number_start, meta_key_col), (data_row_number_end, meta_key_col))
paste_data_range = TS1_Tab1.range((first_datarow, meta_key_col_in_sheet), (first_datarow + (data_row_number_end - data_row_number_start), meta_key_col_in_sheet))
pwau.copy_paste_to_sheet_range(copy_sheet, copy_data_range, TS1_Tab1, paste_data_range)
if ordersheet.os_app['Arcadia Group Order Sheet Template'].exists():
ordersheet.clicker.shut_down()
pwa.keyboard.send_keys('ok')
ordersheet.clicker.setup('OK')
time.sleep(1)
if ordersheet.clicker.click_count > 0:
test_status = 0
actual_result = "Error message appeared when copying/pasting correct values for metakey :" + metakey
else:
test_status = 1
actual_result = "No error message appeared when copying/pasting correct values for metakey :" + metakey
print(metakey)
print(copy_data_range.value)
print(paste_data_range.value)
if copy_data_range.value != paste_data_range.value:
test_status = 0
actual_result = actual_result + ", the data has not been copied to the sheet for metakey: " + metakey
else:
actual_result = actual_result + ", the data has been correctly copied to the sheet for metakey: " + metakey
logger.write_result(ticket_id_metakey, test_description, expected_result, actual_result, test_status)
ticket_id_iterator += 1
# Close the workbook defined in Initial_Input_Parameters.txt
ordersheet.clean_up(False)
# Close the copy_book
copy_book.close()
def write_data_to_order_sheet_tab(sheet_tab):
row_number_to_write = xwu.get_insert_data_row_number(sheet_tab)
fc.test_populator(sheet_tab, row_number_to_write, not_wholesale_ready_list,
partner_list)
return row_number_to_write
def test6():
# setup parameters
ticket_id = 'B2B3PL-1402'
test_description = 'After inserting non-wholesale ready data into a new row on a fresh new ordersheet. Save the book and reopen. Ensure that the non-wholesale ready row is still available and filled in with the same information'
expected_result = 'The data remains present'
# Setup ordersheet
book_a_test_3 = setup_order_sheet(fresh_sheet_parameter_path)
global pid
pid = book_a_test_3.process_id
not_wholesale_ready_number_list = get_metakey_col_numbers(
not_wholesale_ready_list)
# Writing data to TS2 Tab1
ts2_tab1_row_number_to_write = write_data_to_order_sheet_tab(
book_a_test_3.order_sheet.sheets[
book_a_test_3.excel_book_parameters['OrderSheet Tabs'][0]])
# Create Initial dictionary for TS2 Tab 1
ts2_tab1_initial_dictionary = create_generator_dictionary(
not_wholesale_ready_number_list, book_a_test_3.order_sheet.sheets[
book_a_test_3.excel_book_parameters['OrderSheet Tabs'][0]],
ts2_tab1_row_number_to_write)
# save the ordersheet
book_a_test_3.save_order_sheet()
book_a_test_3.clicker.shut_down()
# close the ordersheet
book_a_test_3.order_sheet.close()
time.sleep(5)
# open ordersheet without resetting
book_b_test_3 = open_third_order_sheet()
# get the row nuber that was just written into the first ordersheet
row_number_to_read = xwu.get_insert_data_row_number(
book_b_test_3.order_sheet.sheets[
book_b_test_3.excel_book_parameters['OrderSheet Tabs'][0]]) - 1
# Create saved dictionary for TS2 Tab 1
ts2_tab1_updated_dictionary = create_generator_dictionary(
not_wholesale_ready_number_list, book_b_test_3.order_sheet.sheets[
book_b_test_3.excel_book_parameters['OrderSheet Tabs'][0]],
row_number_to_read)
# Check dictionaries are equal
if (ts2_tab1_initial_dictionary == ts2_tab1_updated_dictionary):
test_status = 1
actual_result = 'The saved data that has not been uploaded to the database remains present'
else:
test_status = 0
actual_result = 'The saved data that has not been uploaded to the database is no longer present'
# delete any existing tabs
for tab in book_b_test_3.order_sheet_tabs:
book_b_test_3.delete_xl_tab(tab)
# save the ordersheet
book_b_test_3.save_order_sheet()
# Write the test to the database
logger.write_result(ticket_id, test_description, expected_result,
actual_result, test_status)
# Close the ordersheet
book_b_test_3.order_sheet.close()
book_b_test_3.clicker.shut_down()