def init():
# Get workbook
wb_path = get_directory([".xlsx"], "Type path of your excel file (.xlsx): ")
wb = openpyxl.load_workbook(wb_path)
sheets = wb.sheetnames # Edited depreciated function: "wb.get_sheet_names()"
# Initialize 2D dictionary representing each sheet and its column headers
sheet_header_lookup = {}
for sheet_name in sheets:
sheet_header_lookup.setdefault(sheet_name, {})
sheet = wb.get_sheet_by_name(sheet_name)
for cell in sheet[1]:
sheet_header_lookup[sheet_name].setdefault(cell.value, None)
# Get user selections for each sheet
for sheet_name in sheets:
# Check if user wants to process this sheet
analyze_sheet = input("Would you like to analyze sheet " + sheet_name + "? (y/n) ")
if analyze_sheet not in ("yes", "Yes", "Y", "y"):
continue
# Menu object used below
cleanup_menu = menus.Value_Menu("cleanup", ANALYSIS_OPTIONS_LIST, ANALYSIS_OPTIONS_LIST)
# Get user selections for each header in sheet
for header in sheet_header_lookup[sheet_name]:
print('-' * 40)
print("SHEET: " + str(sheet_name))
print("HEADER: " + str(header))
user_selection = ANALYSIS_OPTIONS_LIST.index(cleanup_menu.display_shift_menu())
if user_selection == BREAK_SHEET: # Check if user wants to break out of sheet
break
elif user_selection == NO_ANALYSIS: # Check if user wants to skip this column
continue
else:
sheet_header_lookup[sheet_name][header] = user_selection
# Analyze data
change_file_flag = 0
for sheet_name in sheets:
sheet = wb.get_sheet_by_name(sheet_name)
for col in range(1, sheet.max_column + 1):
analysis_number = sheet_header_lookup[sheet_name][sheet.cell(row=1, column=col).value]
if analysis_number is not None:
change_file_flag = 1
col_letter = get_column_letter(col)
perform_analysis(wb, sheet_name, sheet[col_letter], int(analysis_number))
# Save new file
if change_file_flag:
save_file(wb, wb_path, ".xlsx")
else:
print()
print("File not changed, no need to save new version.")
input("Press enter to continue...")
# Loop back to top menu
menu_header()
def get_sheet(wb):
# Creates menu
sheets = wb.sheetnames # Edited depreciated function: "wb.get_sheet_names()"
sheet_menu = menus.Value_Menu("duplicate_removal", sheets, sheets)
print()
print(" Choose a sheet to remove duplicates ".center(70, "="))
print('-' * 70)
# User selects sheet to use, returns that sheet
sheet = wb.get_sheet_by_name(sheet_menu.display_shift_menu())
return sheet
def remove_duplicate_cols(args):
wb = args[0]
wb_path = args[1]
sheet = get_sheet(wb)
# Select row to base removal on
col_index = range(0, sheet.max_row)
col_name = range(1, sheet.max_row + 1)
col_menu = menus.Value_Menu("duplicate_removal", col_name, col_index)
print()
print(" Choose a column to base duplicate removal on ".center(70, "="))
print('-' * 70)
# Which col to compare
removal_index = col_menu.display_shift_menu()
# Initialize col list, duplicate list, col index
cols_list = []
dup_count = 0
col_num = 1
duplicate_flag = False
# Iterate over each col
for col in sheet.iter_cols(min_col=col_num):
# Store current col repsective row value
current_col = col[removal_index].value
# If row is identical, add to duplicates list
if current_col in cols_list:
sheet.delete_cols(col_num, 1)
dup_count += 1
# Else, add to list of already read rows
else:
cols_list.append(current_col)
col_num += 1
# Print how many rows were deleted in what sheets
if dup_count > 0:
duplicate_flag = True
print("Removed " + str(dup_count) + " duplicate rows from list.")
# Return for change_file_flag in init()
if not duplicate_flag:
print("Tool could not find any duplicate rows to remove!")
input("Press enter to continue...")
else:
save_file(wb, wb_path, ".xlsx")
def init():
# Get workbook
wb_path = get_directory([".xlsx"], "Type path of your excel file (.xlsx): ")
wb = openpyxl.load_workbook(wb_path)
sheets = wb.sheetnames # Edited depreciated function: "wb.get_sheet_names()"
# Select sheet
sheet_menu = menus.Value_Menu("cleanup", sheets, sheets)
user_selection = sheet_menu.display_shift_menu()
sheet = wb.get_sheet_by_name(user_selection)
wb.active = sheet
# Create header menu to use for selecting x and y axes
headers = []
for cell in sheet[1]:
headers.append(str(cell.value))
header_menu = menus.Value_Menu("cleanup", headers, headers)
# Select x-axis
print("Select x-axis:")
x_axis = header_menu.display_shift_menu()
x = np.empty(sheet.max_row - 1)
rows = list(sheet.iter_rows(min_row=2,
max_row=sheet.max_row,
min_col=headers.index(x_axis) + 1,
max_col=headers.index(x_axis) + 1))
for i in range(sheet.max_row - 1):
try:
x[i] = rows[i][0].value
except Exception:
print("ERROR: Incompatible data type for x on " + str(rows[i][0].value)
+ ". All data in x must be either a float or an integer.")
return
# Select y-axis
print("Select y-axis:")
y_axis = header_menu.display_shift_menu()
y = np.empty(sheet.max_row - 1)
rows = list(sheet.iter_rows(min_row=2,
max_row=sheet.max_row,
min_col=headers.index(y_axis) + 1,
max_col=headers.index(y_axis) + 1))
for i in range(sheet.max_row - 1):
try:
y[i] = rows[i][0].value
except Exception:
print("ERROR: Incompatible data type for y on " + str(rows[i][0].value)
+ ". All data in x must be either a float or an integer.")
return
# Plot graph
plt.plot(x, y, "ob")
plt.xlabel(sheet.cell(column=headers.index(x_axis) + 1, row=1).value)
plt.ylabel(sheet.cell(column=headers.index(y_axis) + 1, row=1).value)
print("Plotting graph...")
plt.show()
input("Press enter to continue...")
# Display Menu Header loop
menu_header()