def set_tracker_tab(self,
order_book_list=c.ORDERBOOK_LIST,
partner_list=c.PARTNERS_LIST,
size_range_list=c.SIZE_RANGE_LIST):
xw.apps.active.api.EnableEvents = False
xwu.unhide_tab(self.order_sheet, c.PT_TRACKER_TAB_NAME)
tracker_sheet = self.order_sheet.sheets[c.PT_TRACKER_TAB_NAME]
tracker_sheet.activate()
first_data_row_tracker_tab = xwu.find_first_data_row_tracker_partner_tracker(
tracker_sheet)
#clear data from config tab
xwu.clear_config_cells(tracker_sheet)
for metakey in c.PT_TRACKER_COL_NO_DICT:
current_row = first_data_row_tracker_tab
metakey_col_number = c.PT_TRACKER_COL_NO_DICT[metakey]
if metakey == c.PT_TRACKER_ORDER_BOOK_NAMES:
for book in order_book_list:
tracker_sheet.range(current_row,
metakey_col_number).value = book
current_row += 1
if metakey == c.PT_TRACKER_PARTNERS:
for partner in partner_list:
tracker_sheet.range(current_row,
metakey_col_number).value = partner
current_row += 1
if metakey == c.PT_TRACKER_SIZE_RANGE:
for size_range in size_range_list:
tracker_sheet.range(current_row,
metakey_col_number).value = size_range
current_row += 1
xw.apps.active.api.EnableEvents = True
def set_xl_tabs(self): # look to improve with metakey
xwu.unhide_tab(self.order_sheet, 'OrderSheets')
row_number = xwu.find_first_data_row_config_ordersheets(
self.order_sheet.sheets['OrderSheets'])
insert_row_number = row_number
while self.order_sheet.sheets['OrderSheets'].range(
'A' + str(row_number)).value is not None:
self.order_sheet.sheets['OrderSheets'].range(
'A' + str(row_number)).value = ''
self.order_sheet.sheets['OrderSheets'].range(
'B' + str(row_number)).value = ''
self.order_sheet.sheets['OrderSheets'].range(
'C' + str(row_number)).value = ''
self.order_sheet.sheets['OrderSheets'].range(
'D' + str(row_number)).value = ''
self.order_sheet.sheets['OrderSheets'].range(
'E' + str(row_number)).value = ''
self.order_sheet.sheets['OrderSheets'].range(
'F' + str(row_number)).value = ''
row_number = row_number + 1
for tab_name in self.order_sheet_tabs:
self.order_sheet.sheets['OrderSheets'].range(
'A' + str(insert_row_number)).value = tab_name
self.order_sheet.sheets['OrderSheets'].range(
'F' + str(insert_row_number)).value = '165;110;4;55;214'
insert_row_number += 1
def set_settings_tab(self, brand_code=c.DEFAULT_BRAND_CODE):
#setup
xw.apps.active.api.EnableEvents = False
xwu.unhide_tab(self.order_sheet, c.SETTINGS_CFG_TAB)
settings_sheet = self.order_sheet.sheets[c.SETTINGS_CFG_TAB]
#change brand code value
settings_sheet.range(c.BRAND_CODE_RANGE).value = brand_code
def change_order_sheet_name(book, sheet_name):
tab_name = c.SETTINGS_CFG_TAB
xwu.unhide_tab(book, tab_name)
sheet = book.sheets[tab_name]
sheet.range(c.OS_NAME_RANGE).value = sheet_name
xwu.hide_tab(book, tab_name)
def get_partners(book):
tab_name = c.PARTNER_CONFIG_TAB_NAME
xwu.unhide_tab(book, tab_name)
sheet = book.sheets[tab_name]
partners_list = []
start_range = sheet.range("A3")
row_offset = 0
while (start_range.offset(row_offset).value is not None):
partners_list.append(start_range.offset(row_offset).value)
row_offset = row_offset + 1
return partners_list
def get_ob_partners(book, tab):
tab_name = c.OB_CFG_TAB_NAME
xwu.unhide_tab(book, tab_name)
sheet = book.sheets[tab_name]
row = 1
lookup_col = c.OB_TAB_NAME_COL
while (sheet.range(row, lookup_col).value != tab):
row += 1
partners_string = sheet.range(row, c.OB_PTNR_LIST_COL).value
partners_list = partners_string.split(';')
return partners_list
def get_existing_os_tabs(book):
tab_name = c.OS_CFG_TAB_NAME
xwu.unhide_tab(book, tab_name)
sheet = book.sheets[tab_name]
data_row = 3
tab_name_col = c.OS_TAB_NAME_COL
name_list = []
while (sheet.range(data_row, tab_name_col).value is not None):
name_list.append(sheet.range(data_row, tab_name_col).value)
data_row += 1
xwu.hide_tab(book, tab_name)
return name_list
def find_all_partners_in_config_tab(workbook, partner_tab_sheet):
partner_list = []
# To find all partner in tab we need to unhide tab inorder to get partners
xwu.unhide_tab(workbook, "Partners") # unhide tab in config
active_row = c.PARTNER_CONFIG_PARTNERS_NAME_ROW_BEGIN
while partner_tab_sheet.range(
active_row, c.PARTNER_CONFIG_PARTNERS_NAME_COL).value != None:
partner_list.append(
partner_tab_sheet.range(active_row,
c.PARTNER_CONFIG_PARTNERS_NAME_COL).value)
active_row += 1
return partner_list
def set_partner_default_routes(book, partners, order_book=False):
# find column depending on book type
default_col = c.PARTNER_DEFAULT_ROUTE_COL
if (order_book):
default_col = c.OB_DEFAULT_ROUTE_COL
tab_name = c.PARTNER_CONFIG_TAB_NAME
xwu.unhide_tab(book, tab_name)
sheet = book.sheets[tab_name]
data_row = 3
partner_col = 1
while (sheet.range(data_row, partner_col).value is not None):
if (sheet.range(data_row, partner_col).value in partners):
gen_range = sheet.range(data_row, default_col)
xwu.write_random_valid_value(gen_range)
data_row += 1
def set_book_tabs(self,
xl_tool_type,
partner_string: str = default_partners,
size_string: str = default_sizes):
order_book_partners = partner_string
xwu.unhide_tab(self.order_sheet, xl_tool_type)
tab_config_sheet = self.order_sheet.sheets[xl_tool_type]
row_number = xwu.find_first_data_row_config_ordersheets(
tab_config_sheet)
insert_row_number = row_number
col_number = 1
meta_key_col_number_dictionary = {}
while tab_config_sheet.range(1, col_number).value is not None:
meta_key_col_number_dictionary[tab_config_sheet.range(
1, col_number).value] = col_number
#xwu.find_col_by_metakey(tab_config_sheet.range(1, col_number).value, tab_config_sheet)
col_number += 1
col_number = 1
while tab_config_sheet.range(row_number, col_number).value is not None:
while tab_config_sheet.range(row_number,
col_number).value is not None:
tab_config_sheet.range(row_number, col_number).value = ''
col_number += 1
col_number = 1
row_number += 1
for tab_name in self.order_sheet_tabs:
if "Order Book Tab Name" in meta_key_col_number_dictionary:
tab_config_sheet.range(
insert_row_number,
meta_key_col_number_dictionary["Order Book Tab Name"]
).value = tab_name
if "Partners" in meta_key_col_number_dictionary:
tab_config_sheet.range(
insert_row_number,
meta_key_col_number_dictionary["Partners"]
).value = order_book_partners
if "Size Ranges" in meta_key_col_number_dictionary:
tab_config_sheet.range(
insert_row_number,
meta_key_col_number_dictionary["Size Ranges"]
).value = size_string
insert_row_number += 1
def get_partner_default_route(book, partner, order_book=False):
default_col = c.PARTNER_DEFAULT_ROUTE_COL
if (order_book):
default_col = c.OB_DEFAULT_ROUTE_COL
tab_name = c.PARTNER_CONFIG_TAB_NAME
xwu.unhide_tab(book, tab_name)
sheet = book.sheets[tab_name]
data_row = 3
partner_col = 1
while (sheet.range(data_row, partner_col).value != partner
and sheet.range(data_row, partner_col).value is not None):
data_row += 1
if (sheet.range(data_row, partner_col).value is None):
return ''
else:
return sheet.range(data_row, default_col).value
def set_partner_tab(self,
partner_list=c.PARTNERS_LIST,
currency_list=c.CURRENCY_LIST,
cp_factor_list=c.CP_FACTOR_LIST,
partner_seasons_list=c.PARTNER_SEASONS_LIST):
xw.apps.active.api.EnableEvents = False
xwu.unhide_tab(self.order_sheet, c.PT_TRACKER_PARTNERS)
partner_sheet = self.order_sheet.sheets[c.PT_TRACKER_PARTNERS]
first_data_row_partner_tab = xwu.find_first_data_row_tracker_partner_tracker(
partner_sheet)
# clear data from config partner tab
xwu.clear_config_cells(partner_sheet)
#populate partners tab
for metakey in c.PT_PARTNERS_COL_NO_DICT:
current_row = first_data_row_partner_tab
metakey_col_no = c.PT_PARTNERS_COL_NO_DICT[metakey]
if metakey == c.PT_PARTNERS_PARTNER_NAME or metakey == c.PT_PARTNERS_CALENDAR_TAB:
for partner in partner_list:
partner_sheet.range(current_row,
metakey_col_no).value = partner
current_row += 1
if metakey == c.PT_PARTNERS_CURRENCY:
for currency in currency_list:
partner_sheet.range(current_row,
metakey_col_no).value = currency
current_row += 1
if metakey == c.PT_PARTNERS_CP_FACTOR:
for cp_factor in cp_factor_list:
partner_sheet.range(current_row,
metakey_col_no).value = cp_factor
current_row += 1
if metakey == c.PT_PARTNERS_PARTNER_SEASONS:
for partner_season in partner_seasons_list:
partner_sheet.range(current_row,
metakey_col_no).value = partner_season
current_row += 1
xw.apps.active.api.EnableEvents = True
def test_column_config_tab_setup_locked_fields():
book_test = setup_partner_tracker(parameter_path)
partner_tracker = book_test.order_sheet
global pid
pid = book_test.process_id
tiny_nordstrom_tab = partner_tracker.sheets[
book_test.excel_book_parameters['OrderSheet Tabs'][0]]
xwu.unhide_tab(partner_tracker, "Columns")
column_tab = partner_tracker.sheets["Columns"]
locked_field_list = fc.get_list_of_locked_metakey(tiny_nordstrom_tab)
iterator = 1
for field in locked_field_list:
ticket_id = "B2B3PL-2087 - " + str(iterator)
test_description = "Ensure that the column " + field + " is configured correctly inside the columns tab"
expected_result = "Columns config is configured properly for field: " + field
test_column_configuration_formula_or_grey_field(
ticket_id, test_description, expected_result, column_tab, field)
iterator += 1
# Close the ordersheet/clicker thread
book_test.clicker.shut_down()
book_test.order_sheet.close()
excel_pid.join()
open_sheet.join()
#maximise the window - include if statement later
excel_window = win32gui.GetForegroundWindow()
win32gui.ShowWindow(excel_window, win32con.SW_MAXIMIZE)
#set the ordersheet to xlwings object/get process id
process_id = xw.apps.active.pid
order_sheet = xw.apps[process_id].books[order_sheet_xl_location[:-5]]
#List of tab names for the ordersheet
list_of_tabs = [xl_tab1_name, xl_tab2_name, xl_tab3_name, xl_tab4_name]
#change tab names inside the ordersheet config
xwu.unhide_tab(order_sheet, 'OrderSheets')
row_number = xwu.find_first_data_row_config_ordersheets(
order_sheet.sheets['OrderSheets'])
for tab_name in list_of_tabs:
order_sheet.sheets['OrderSheets'].range('A' +
str(row_number)).value = tab_name
row_number += 1
#setup variables to reset the ordersheet
reset_order_sheet_macro = et.macro(3, "OrderSheetReset", "click reset macro")
send_keys_to_order_sheet_name = et.key(4, xl_order_sheet_name,
"type in the order sheet name")
#click on the reset ribbon
reset_order_sheet_macro.start()
reset_box_active = 0