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 init():
# Get workbook
wb_path = get_directory([".xlsx"],
"Type path of your excel file (.xlsx): ")
wb = openpyxl.load_workbook(wb_path)
sheets = 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 sheet_name in sheets:
# Check if user wants to process this sheet
process_sheet = input("Would you like to clean sheet " + sheet_name +
"? (y/n) ")
if process_sheet not in ("yes", "Yes", "Y", "y"):
continue
# Get user selections for each header in sheet
for header in sheet_header_lookup[sheet_name]:
print_menu(sheet_name, header, CLEANUP_OPTIONS_LIST)
user_selection = get_user_selection(CLEANUP_OPTIONS_LIST)
if user_selection == BREAK_SHEET: # Check if user wants to break out of sheet
break
elif user_selection == NO_CLEANING: # Check if user wants to skip this column
continue
else:
sheet_header_lookup[sheet_name][header] = user_selection
# Process data
for sheet_name in sheets:
sheet = wb.get_sheet_by_name(sheet_name)
for col in range(1, sheet.max_column + 1):
process_number = sheet_header_lookup[sheet_name][sheet.cell(
row=1, column=col).value]
if process_number is not None:
col_letter = get_column_letter(col)
process_column(wb, sheet[col_letter], int(process_number))
# Save to a new copy of the workbook
new_file = wb_path[:len(wb_path) - 5] + "_EDITED.xlsx"
wb.save(new_file)
print("Done! New file saved to " + new_file)
input("Press enter to continue...")
# Display Menu Header loop
menu_header()
def init():
# Get input for .csv file
wb_path = get_directory([".csv"], "Type path of your .csv file: ")
wb_csv = pandas.read_csv(wb_path)
# Get input for where to save new excel file
export_path = os.path.dirname(os.path.abspath(wb_path))
export_path += "/" + input("Input new file's name (saves to same directory): ") + ".xlsx"
wb_csv.to_excel(export_path, index = False)
# Sucess message
print("Done! New file saved to " + export_path)
input("Press enter to continue...")
# Display Menu Header again
menu_header()
def init():
# Get input for excel file
wb_path = get_directory([".xlsx"],
"Type path of your excel file (.xlsx): ")
wb_xls = pandas.read_excel(wb_path, 'Sheet1', index_col=None)
# Get input where to save export file (.csv file)
export_path = os.path.dirname(os.path.abspath(wb_path))
export_path += "/" + input(
"Input new file's name (saves to same directory): ") + ".csv"
wb_xls.to_csv(export_path, encoding='utf-8', index=False)
# Success message
print("Success!\n" + wb_path + " was saved to " + export_path)
input("Press enter to continue...")
# Display Menu Header again.
menu_header()
def init():
# Get path and determine zipfile name
path = os.path.abspath(
get_directory([],
"Type path of the file/folder you would like to zip: "))
zipfile_name = path
if zipfile_name.find(os.sep) != -1:
zipfile_name = zipfile_name[(zipfile_name.rfind(os.sep) + 1):]
if zipfile_name.find('.') != -1:
zipfile_name = zipfile_name[:zipfile_name.find('.')]
zipped_file = zipfile.ZipFile(zipfile_name + ".zip", 'w')
# If path is a directory, change directories to path and write all files in that directory to the zipfile
if os.path.isdir(path):
current_dir = os.curdir
os.chdir(path)
file_paths = os.listdir('.')
for file in file_paths:
zipped_file.write(file)
# If path is a file, change directories to housing directory and write single file to the zipfile
if os.path.isfile(path):
current_dir = os.curdir
os.chdir(os.path.dirname(path))
zipped_file.write(os.path.basename(path))
# Close the zipfile and move back to previous directory
zipped_file.close()
os.chdir(current_dir)
print("Done! New file saved from " + path)
input("Press enter to continue...")
# Clears the screen (checks os and uses command for that system)
os.system('cls' if os.name == 'nt' else 'clear')
# Loop back to compress menu
menu_header()
def init():
# Get user input for excel file
wb_path = get_directory([".xlsx"],
"Type path of your excel file (.xlsx): ")
wb = openpyxl.load_workbook(wb_path)
# Gets input from user if they want to remove duplicates from all sheets
if (len(wb.sheetnames) >
1): # Edited depreciated function: "wb.get_sheet_names()"
user_choice = input(
"Would you like to remove duplicates from all sheets (Y) or one sheet (N)?: "
)
if user_choice in ("yes", "Yes", "Y", "y"):
remove_duplicate_all_sheet(wb, wb_path)
else:
single_sheet(wb, wb_path)
else:
# If only 1 sheet, chooses single sheet option
single_sheet(wb, wb_path)
# Loop back to top menu
menu_header()
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()
def init():
# Get user input\
wb_path = get_directory([".xlsx"], "Type path of your excel file (.xlsx): ")
email_list = get_email_list()
sender_email = input("Enter your email address (supports gmail, outlook, hotmail, and yahoo): ")
password = getpass("Enter your email password: "******"Attempting to send emails...")
smtp = get_smtp(sender_email)
# Connect to Server
session = smtplib.SMTP(smtp, 587)
session.ehlo()
session.starttls()
try:
session.login(sender_email, password)
except Exception:
print()
print("ERROR: Unable to login to your email account")
input("Press enter to continue...")
# Display Menu Header loop
menu_header()
invalid_emails = []
message_content = "Hello,\n\n" + sender_email + " has shared this file with you.\n\n"
for email in email_list:
# Check the sending email is valid
if not email_regex.search(email):
invalid_emails.append(email)
email_list.remove(email)
continue
# Setup the MIME
message = MIMEMultipart()
message['From'] = sender_email
message['To'] = email
message['Subject'] = "File Share From " + sender_email
# The body and the attachments for the mail
message.attach(MIMEText(message_content, 'plain'))
attach_file = open(wb_path, 'rb') # Open the file as binary mode
payload = MIMEBase('application', 'vnd.ms-excel')
payload.set_payload((attach_file).read())
attach_file.close()
encoders.encode_base64(payload) # Encode the attachment
# Add payload header with filename
payload.add_header("Content-Disposition", "attachment", filename=os.path.basename(wb_path))
message.attach(payload)
# Create SMTP session for sending the email
text = message.as_string()
session.sendmail(sender_email, email, text)
session.quit()
# Print success message and list of invalid email addresses if there were any
print('-' * 40)
print()
print("Success!")
if len(invalid_emails) != 0:
print("The following emails were deemed invalid: ")
for email in invalid_emails:
print(email)
input("Press enter to continue...")
# Display Menu Header loop
menu_header()
