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 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_list_of_formula_metakey(sheet):
meta_key_row_number = xwu.find_row_by_metakey(sheet, "MetaKey")
database_table_row_number = xwu.find_row_by_metakey(sheet, "DatabaseTable")
column = 1
set_of_formula_metakeys = set()
while sheet.range(database_table_row_number,
column).color == (128, 255, 255) or sheet.range(
database_table_row_number,
column).color == (255, 255, 0):
print(column)
if sheet.range(database_table_row_number, column).value == "Formula":
set_of_formula_metakeys.add(
sheet.range(meta_key_row_number, column).value)
column += 1
list_of_formula_metakeys = list(set_of_formula_metakeys)
return list_of_formula_metakeys
def find_lookup_col_numbers(
sheet): #depricated, just use metakey col dict in new class
col_num_dictionary = {}
meta_key_row_num = xwu.find_row_by_metakey(sheet, "MetaKey")
partner_name_row_num = xwu.find_row_by_metakey(sheet, "PartnerName")
current_col = 1
while sheet.range(meta_key_row_num, current_col).color != None:
meta_key = sheet.range(meta_key_row_num, current_col).value
if meta_key == c.PT_FINAL_DESPATCH_MONTH: #meta_key == c.PT_FINAL_LINE_NUMBER or
col_num_dictionary[meta_key] = current_col
if meta_key == c.PT_FINAL_PO_NUMBER or meta_key == c.PT_PARTNER_CANCELLATION_CODE: #meta_key == c.PT_FINAL_UNITS or
partner = sheet.range(partner_name_row_num, current_col).value
dictionary_metakey = partner + " | " + str(meta_key)
col_num_dictionary[dictionary_metakey] = current_col
current_col += 1
return col_num_dictionary
def get_sales_order_params_by_range_legacy_tool(self, range):
sheet = xwu.get_sheet_from_range(range)
row = range.row
column = range.column
sales_order_params = {}
partner_name_row_num = xwu.find_row_by_metakey(sheet, "PartnerName")
sales_order_params[
"OrderRowId"] = self.get_order_row_id_by_row_no_legacy(sheet, row)
sales_order_params["Partner"] = sheet.range(partner_name_row_num,
column).value
return sales_order_params
def get_list_of_dropdown_metakeys(sheet):
"""
Summary: Creates a list of all dropdown metakeys (columns that
can be filled by clicking in the dropdown list and chossing
a value from there).
Return: A list with the names of all dropdown metakeys
Defined: fieldcreator.py
"""
meta_key_row_number = xwu.find_row_by_metakey(sheet, "MetaKey")
validation_row_number = xwu.find_row_by_metakey(sheet, "Validation")
meta_key_col_number = xwu.find_col_by_metakey("MetaKey", sheet)
column = 2
set_of_dropdown_metakeys = set()
while sheet.range(meta_key_row_number, column).color != None:
if sheet.range(validation_row_number, column).value is not None:
set_of_dropdown_metakeys.add(
sheet.range(meta_key_row_number, column).value)
column += 1
list_of_dropdown_metakeys = list(set_of_dropdown_metakeys)
return list_of_dropdown_metakeys
def get_metakey_col_num_dictionary(sheet, metakey_list):
meta_key_col_number_dictionary = {}
current_column = 1
meta_key_row_number = xwu.find_row_by_metakey(sheet, "MetaKey")
partner_row_number = xwu.find_row_by_metakey(sheet, "PartnerName")
size_row_number = xwu.find_row_by_metakey(sheet, "SizeNumber")
while sheet.range(meta_key_row_number,
current_column).color == (128, 255, 255) or sheet.range(
meta_key_row_number,
current_column).color == (255, 255, 0):
metakey = sheet.range(meta_key_row_number, current_column).value
if metakey in metakey_list:
partner = sheet.range(partner_row_number, current_column).value
size_number = sheet.range(size_row_number, current_column).value
if partner is None:
partner = ""
if size_number is None:
size_number = ""
else:
size_number = int(size_number)
meta_key_col_number_dictionary[metakey + partner +
str(size_number)] = current_column
current_column += 1
return meta_key_col_number_dictionary
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 lock_column(col_sheet, metakey, metakey_col_dict):
metakey_row = xwu.find_row_by_metakey(col_sheet, metakey)
col_sheet.range(metakey_row, metakey_col_dict[("Wholesale Edit", None, None)]).value = 0
col_sheet.range(metakey_row, metakey_col_dict[("Core Edit", None, None)]).value = 0
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()
