def get_line_level_editable_meta_key_list(sheet):
finalised_metakeys_list = [
'SizeRange', 'OriginalLineNumber', 'OriginalLineDescription',
'OriginalColour', 'OriginalRrpGBP', 'OriginalIntakeWeek',
'AvailableForDirect', 'ArcadiaSeason', 'BrandDept'
]
awkward_meta_key_list = [
'ArcadiaCategory', 'Dc', 'BlockOrder', 'RevisedDespatchMonth',
'OriginalDespatchMonth'
]
meta_key_set = set()
meta_key_row_number = xwu.find_row_by_metakey(sheet, "MetaKey")
db_table_row_number = xwu.find_row_by_metakey(sheet, "DatabaseTable")
size_number_row_number = xwu.find_row_by_metakey(sheet, "SizeNumber")
first_data_row = xwu.find_first_data_row(sheet)
column = 2
while sheet.range(size_number_row_number, column).value == None:
db_table_value = sheet.range(db_table_row_number, column).value
cell_colour = sheet.range(first_data_row, column).color
meta_key_value = sheet.range(meta_key_row_number, column).value
if db_table_value != "Formula" and cell_colour != (
222, 222, 222
) and meta_key_value not in finalised_metakeys_list and meta_key_value not in awkward_meta_key_list and cell_colour != (
128, 255, 255
) and cell_colour != (
229, 255, 225
) and "Iscm" not in meta_key_value and "Original" not in meta_key_value:
meta_key_set.add(sheet.range(meta_key_row_number, column).value)
column += 1
meta_key_list = list(meta_key_set)
return meta_key_list
def get_editable_meta_key_list(sheet):
finalised_metakeys_list = [
'SizeRange', 'OriginalLineNumber', 'OriginalLineDescription',
'OriginalColour', 'OriginalRrpGBP', 'OriginalIntakeWeek',
'AvailableForDirect'
]
awkward_meta_key_list = [
'ArcadiaCategory', 'Dc', 'BlockOrder', 'DeliveryRoute'
]
meta_key_set = set()
meta_key_row_number = xwu.find_row_by_metakey(sheet, "MetaKey")
db_table_row_number = xwu.find_row_by_metakey(sheet, "DatabaseTable")
first_data_row = xwu.find_first_data_row(sheet)
column = 2
while sheet.range(meta_key_row_number,
column).color == (128, 255, 255) or sheet.range(
meta_key_row_number, column).color == (255, 255, 0):
db_table_value = sheet.range(db_table_row_number, column).value
cell_colour = sheet.range(first_data_row, column).color
meta_key_value = sheet.range(meta_key_row_number, column).value
if meta_key_value in meta_key_set:
pass
elif (db_table_value != "Formula" and cell_colour != (222, 222, 222)
and meta_key_value not in finalised_metakeys_list
and meta_key_value not in awkward_meta_key_list
and cell_colour != (128, 255, 255) and cell_colour !=
(229, 255, 225) and cell_colour != (225, 246, 255)
and "Iscm" not in meta_key_value
and "Original" not in meta_key_value):
meta_key_set.add(meta_key_value)
else:
pass
column += 1
meta_key_list = list(meta_key_set)
return meta_key_list
def get_list_of_locked_metakey(sheet):
meta_key_row_number = xwu.find_row_by_metakey(sheet, "MetaKey")
database_table_row_number = xwu.find_row_by_metakey(sheet, "DatabaseTable")
db_status_col_number = xwu.find_col_by_metakey("DbStatus", sheet)
meta_key_col_number = xwu.find_col_by_metakey("MetaKey", sheet)
first_data_row = xwu.find_first_data_row(sheet)
locked_grey_column_colour = sheet.range(first_data_row,
db_status_col_number).color
formula_blue_column_colour = sheet.range(meta_key_row_number,
meta_key_col_number).color
locked_blue_column_colour = c.FORMULA_VALUE_CELL_COLOUR
column = 1
set_of_locked_metakeys = set()
while sheet.range(
database_table_row_number,
column).color == formula_blue_column_colour or sheet.range(
database_table_row_number, column).color == (255, 255, 0):
data_cell_colour = sheet.range(first_data_row, column).color
if data_cell_colour == locked_grey_column_colour or data_cell_colour == locked_blue_column_colour or sheet.range(
database_table_row_number, column).value == "Formula":
set_of_locked_metakeys.add(
sheet.range(meta_key_row_number, column).value)
column += 1
list_of_locked_metakeys = list(set_of_locked_metakeys)
return list_of_locked_metakeys
def populate_po_deadlines_or_date_confirmed(
sheet, metakey, partner_metakey, metakey_col_dict
): #inputted into new structure partnertrackermaanger | populate_date_confirmed_ppo_date_deadline
values_dictionary = {}
current_row = xwu.find_first_data_row(sheet)
last_row = xwu.find_last_data_row(sheet)
while current_row <= last_row:
final_line_number_value = sheet.range(
current_row,
metakey_col_dict[("FinalLineNumber", None, None)]).value
final_despatch_month = sheet.range(
current_row,
metakey_col_dict[("FinalDespatchMonth", None, None)]).value
try:
sheet.range(current_row, metakey_col_dict[(
metakey, partner_metakey,
None)]).value = values_dictionary[(final_line_number_value,
final_despatch_month)]
except KeyError:
values_dictionary[(
final_line_number_value,
final_despatch_month)] = fc.generate_from_meta_key(metakey)
sheet.range(current_row, metakey_col_dict[(
metakey, partner_metakey,
None)]).value = values_dictionary[(final_line_number_value,
final_despatch_month)]
current_row += 1
def insert_data_into_existing_rows(sheet_tab, metakey_list):
data_start_row = xwu.find_first_data_row(sheet_tab)
db_status_col_number = xwu.find_col_by_metakey('DbStatus')
while sheet_tab.range(data_start_row, db_status_col_number).value == 2:
fc.test_populator(sheet_tab, data_start_row, metakey_list,
partner_list)
data_start_row = data_start_row + 1
def clear_all_pon_from_tracker(
sheet): #Use clear_specific_column_values in xwu
meta_key_row = xwu.find_row_by_metakey(sheet, "MetaKey")
start_row = xwu.find_first_data_row(sheet)
pon_metakey = "PartnerPoNumber"
order_row_id_col_num = xwu.find_col_by_multi_metakey(["OrderRowId"], sheet)
current_column = 1
while sheet.range(meta_key_row, current_column).color is not None:
if sheet.range(meta_key_row, current_column).value == pon_metakey:
while sheet.range(start_row,
order_row_id_col_num).value is not None:
pon_value = sheet.range(start_row, current_column).value
if pon_value not in [None, '']:
sheet.range(start_row, current_column).value = None
start_row += 1
start_row = xwu.find_first_data_row(sheet)
current_column += 1
def set_db_values(self):
metakey_list = ['PartnerBusinessUnit', 'CategoryGroup', 'PartnerSeason']
first_data_row = xwu.find_first_data_row(self.tracker_data_tab)
for metakey_tuple in self.tracker_data_metakey_col_dict:
for metakey in metakey_list:
if metakey == metakey_tuple[0]:
metakey_valid_values_list = xwu.dropdown_decoder(self.tracker_data_tab.range(first_data_row, self.tracker_data_metakey_col_dict[metakey_tuple]))
for valid_value in metakey_valid_values_list:
self.tracker_data_tab.range(first_data_row, self.tracker_data_metakey_col_dict[metakey_tuple]).value = valid_value
def set_subcategory_db_values(self):
category_group_metakey_tuple = ('CategoryGroup', None, None)
sub_category_metakey_tuple = ('SubCategory', None, None)
first_data_row = xwu.find_first_data_row(self.tracker_data_tab)
category_group_valid_values_list = xwu.dropdown_decoder(self.tracker_data_tab.range(first_data_row, self.tracker_data_metakey_col_dict[category_group_metakey_tuple]))
for category_group in category_group_valid_values_list:
self.tracker_data_tab.range(first_data_row, self.tracker_data_metakey_col_dict[category_group_metakey_tuple]).value = category_group
sub_category_valid_values = fc.get_sub_category_list_from_category_group(self.book, category_group)
for sub_category in sub_category_valid_values:
xwu.ensure_focus(self.tracker_data_tab, self.tracker_data_tab.range(first_data_row, self.tracker_data_metakey_col_dict[sub_category_metakey_tuple]))
pwa.keyboard.send_keys(str(sub_category) + '~')
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 generate_dictionary_for_all_db_status_rows(metakey_col_number_list,
sheet_tab):
data_start_row = xwu.find_first_data_row(sheet_tab)
db_status_col_number = xwu.find_col_by_metakey('DbStatus')
dictionary = {}
values_dictionary = {}
while sheet_tab.range(data_start_row, db_status_col_number).value != None:
for column in metakey_col_number_list:
if sheet_tab.range(data_start_row,
column).value != None and sheet_tab.range(
data_start_row, column).value != '':
meta_key = xwu.find_metakey_value_by_col(column)
values_dictionary[meta_key] = sheet_tab.range(
data_start_row, column).value
dictionary[data_start_row] = values_dictionary
values_dictionary = {}
data_start_row = data_start_row + 1
return dictionary
def get_random_row_from_sht(sheet):
data_start_row_num = xwu.find_first_data_row(sheet)
print(data_start_row_num)
print(101)
orid_col_num = xwu.find_col_by_metakey("OrderRowId", sheet)
print(orid_col_num)
print(102)
data_end_row_num = data_start_row_num
while sheet.range(data_end_row_num, orid_col_num).value != None:
print(data_end_row_num)
data_end_row_num += 1
print(data_start_row_num)
print(data_end_row_num)
random_order_row_row_num = random.randrange(data_start_row_num,
data_end_row_num, 1)
print(random_order_row_row_num)
return random_order_row_row_num
lastDataCol = xwu.get_last_col(order_sheet_tab1)
lastDataRow = xwu.get_last_data_row(order_sheet_tab1)
#set values for the test
iterator = 1
test_pass = 1
columns_not_stored_in_database_count = 0
#commence test
while (iterator < 26):
#select a random cell
random_row = rnd.randint(
xwu.find_first_data_row(order_sheet.sheets[order_sheet_tab1]),
lastDataRow)
random_col = rnd.randint(
find_col_by_metakey("WholesaleReadyStatus") + 1, lastDataCol)
random_cell_value = order_sheet_tab1.range(random_row, random_col).value
#find the metakey for that cell
cell_metakey = xwu.find_metakey_value_by_col(
random_col, order_sheet.sheets[order_sheet_tab1])
#find the order row id for that cell
order_row_col = xwu.find_col_by_metakey("OrderRowId")
order_row_id = order_sheet_tab1.range(random_row, order_row_col).value
#use the metakey linked with the dictionary to get the method in order to retrieve the selected data from the db
try:
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 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 __init__(self, sheet): self.first_row = xwu.find_first_data_row(sheet) self.sheet = sheet
#Log order book creation
#action = "Create order book " + order_book_name
# if(order_book_details[order_book_name] is not None):
# log_result = "Creation Successful"
# else:
# log_result = "Creation Failed"
# logger.write_log(process, action, log_result)
##Open the order sheet (if not automated, pass in a test sheet id as parameter)
order_sheet = xw.Book(order_sheet_xl_name)
order_sheet_tab = order_sheet.sheets(xl_tab_name)
#Choose a line on which to operate
data_row = xwu.find_first_data_row(order_sheet_tab)
test_col = xwu.find_col_by_metakey("OrderBookName")
while (order_sheet_tab.range(data_row, test_col).value is not None):
data_row = data_row + 1
#input book and tab names onto the order sheet.
book_col = xwu.find_col_by_metakey("OrderBookName")
order_sheet_tab.range(data_row, book_col).value = order_book_name
tab_col = xwu.find_col_by_metakey("OrderBookTabName")
order_sheet_tab.range(data_row, tab_col).value = order_book_tab_name
##Turn events back on once the book has been added to the worksheet.
g.enable_events()
#create dictionary object for storing the input values