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