def load_workbook(self, file_location):
"""
Loads workbook object from excel file
"""
try:
return openpyxl.load_workbook(file_location)
except Exception as traceback_error:
statement = "Problem laoding workbook from {}".format(file_location)
error_logger.logger(statement, traceback_error)
def add_values_to_cell_range(self, cell_range, values, column_index, row):
for i in range(len(cell_range)):
try:
self.sheet.cell(row=row, column=column_index).value = values[i]
except Exception as traceback_error:
# statement = "Problem setting cell values"
error_logger.logger(traceback_error=traceback_error)
row += 1
def group_by_exists(self):
'''
Checks if cell 'A1' has the value of 'Group By'
Method is meant to check if formatting should occur
'''
try:
return self.sheet['A1'].value == 'Group By'
except Exception as traceback_error:
statement = "Problem reading value from cell 'A1'"
error_logger.logger(statement, traceback_error)
def get_sheet(self, workbook_name, sheet_name):
"""
Retrieves sheet object from workbook object
"""
try:
return workbook_name[sheet_name]
except Exception as traceback_error:
statement = "Can't find sheet {} for {}".format(
sheet_name, self.file_name)
error_logger.logger(statement, traceback_error)
def save_doc(self):
# Change to enable saving to any location *args
"""
Saves ColumnManager changes to excel workbook
"""
try:
self.workbook.save(self.file_location)
except Exception as traceback_error:
statement = "Unable to save file {}".format(self.file_name)
error_logger.logger(statement, traceback_error)
def get_jobtype(self):
try:
job_type = self.sheet.cell(row=1, column=1).value
if job_type is None:
raise TypeError
return job_type
except Exception as traceback_error:
statement = "\nMake sure you select a job type for file {}".format(
self.file_name.upper())
error_logger.logger(statement, traceback_error)
def get_all_reports(column_managers, metric_titles=METRIC_TITLES):
try:
reports = [
report.Report(manager, metric_titles).get_final_report()
for manager in column_managers
]
return reports
except Exception as traceback_error:
statement = "Trouble getting reports from all files"
error_logger.logger(statement, traceback_error)
def get_column_cells(self, title, row=1):
"""
Returns list of cell objects from given column title
"""
index = self.get_column_index(title, row)
try:
return [cell for cell in list(self.sheet.columns)[index - 1]]
except Exception as traceback_error:
statement = "Problem finding cell for column w/ title {}".format(title)
error_logger.logger(statement, traceback_error)
def get_column_index(self, title, row=1):
"""
Return index number for given column title
"""
try:
columns = self.get_column_titles_with_index(row)
return columns[title]
except Exception as traceback_error:
statement = "Problem finding column titled {}".format(title)
error_logger.logger(statement, traceback_error)
def get_column_values(self, title, row=1):
"""
Returns column values given title
"""
try:
cells = self.get_column_cells(title, row)
values = [cell.value for cell in cells]
return values
except Exception as traceback_error:
statement = "Problem retrieving cells for column {}".format(title)
error_logger.logger(statement, traceback_error)
def get_column_cell_names(self, title, row=1):
"""
Return a list of cell names (ex."A32") for given column
"""
cells = self.get_column_cells(title, row)
try:
cell_names = ["{}{}".format(cell.column, cell.row)
for cell in cells]
return cell_names
except Execption as traceback_error:
statement = "Problem building cell name from {} column".format(title)
error_logger.logger(statement, traceback_error)
def get_column_titles_with_index(self, row=1):
"""
Returns dict with column title and column index
"""
max_column = self.sheet.max_column
try:
column_titles = {self.sheet.cell(row=row, column=column).value: column
for column in range(1, max_column+1)}
return column_titles
except Exception as traceback_error:
statement = "Problem retrieving column titles"
error_logger.logger(statement, traceback_error)
def get_column_titles(self, row=1):
"""
Returns list with column title
"""
max_column = self.sheet.max_column
try:
column_titles = [self.sheet.cell(row=row, column=column).value
for column in range(1, max_column+1)]
return column_titles
except Exception as traceback_error:
statement = "Problem retrieving column titles"
error_logger.logger(statement, traceback_error)
def build_data_set_template(metric_titles=METRIC_TITLES):
try:
final_data = {}
for job_type in JOB_TYPES:
final_data[job_type] = {}
for metric in METRIC_TITLES:
final_data[job_type][metric] = {}
for system in SYSTEMS:
final_data[job_type][metric][system] = []
return final_data
except Exception as traceback_error:
statement = "Trouble building data set template"
error_logger.logger(statement, traceback_error)
def build_metrics_columns(manager, new_columns=new_columns, row=1):
"""
Uses excel manager object to build new metrics columns
"""
try:
for new_column in new_columns:
manager.gen_new_metric_column(new_column['title'],
new_column['numerator'],
new_column['denominator'],
with_formulas=with_formulas)
manager.color_column(new_column['title'], column_color)
except Exception as traceback_error:
print(traceback_error)
statement = "Trouble building metrics column"
error_logger.logger(statement, traceback_error)
def add_validation(self, validation_string):
'''
Inserts a new row then adds a data validation drop down list to cell 'A1'.
'validation_string' parameter contains list of values added to drop down list.
'''
try:
self.sheet.insert_rows(1)
dv = DataValidation(type="list", formula1=validation_string, allow_blank=True)
self.sheet.add_data_validation(dv)
cell = self.sheet["A1"]
dv.add(cell)
error_logger.logger('Drop down menu added')
except Exception as traceback_error:
statement = "Trouble with adding validation drop-down menu"
error_logger.logger(statement, traceback_error)
def format_excel_files(file_paths, JOB_TYPES):
'''
Format all excel files to final result given list of file paths
'''
# try:
for file_path in file_paths:
status_output = get_formatted_filepath_status(file_path)
error_logger.logger(status_output)
try:
format_excel_file(JOB_TYPES, file_path)
except Exception as traceback_error:
statement_1 = "Problem manipulating file from filepath"
statement_2 = "Make sure Excel file is formatted properly- maybe try to re-export from Enterprise\n"
error_logger.logger("{}\n{}".format(statement_1, statement_2),
traceback_error)
def gen_new_metric_column(self, new_title, numer_column, denom_column, with_formulas=False, row=1):
'''
Divides existing numerator column values with demoninator column values
Creates new column with divided values
'''
try:
self.make_new_column(new_title)
if with_formulas:
new_values = self.divide_columns_formula(numer_column, denom_column, row=row)
else:
new_values = self.divide_columns_values(numer_column, denom_column, row=row)
self.set_column_values(new_title, new_values, row=row+1)
# error_logger.logger("Column {} added with values".format(new_title))
except Exception as traceback_error:
print(traceback_error)
statement = "Trouble with generating {} column".format(new_title)
error_logger.logger(statement, traceback_error)
def make_new_column(self, title, column_start=1):
"""
Create column w/ given title
"""
column_titles = self.get_column_titles()
if title in column_titles:
statement = "Column title {} NOT added- already exists for file {}".format(
title, self.file_name)
error_logger.logger(statement)
else:
try:
max_column = self.sheet.max_column
self.sheet.cell(row=1, column=max_column+column_start).value = title
error_logger.logger("{} column title added".format(title))
except Exception as traceback_error:
statement = "Something went wrong with building column {}".format(title)
self.logger(statement, traceback_error)
def divide_columns_formula(self, first_title, second_title, row=1):
"""
Return list of divided cell values from given columns
"""
try:
numerator_names = self.get_column_cell_names(first_title, row=row)
denominator_names = self.get_column_cell_names(second_title, row=row)
divided_formulas = ["={}/{}".format(numerator_names[i],
denominator_names[i])
for i
in range(2, len(numerator_names))]
return divided_formulas
except Exception as traceback_error:
statement = "Problem dividing {} values with {} values".format(
first_title, second_title)
error_logger.logger(statement, traceback_error)
def build_final_data_set(column_managers):
try:
template = build_data_set_template()
reports = get_all_reports(column_managers)
for report in reports:
for job_type in report:
if job_type == None:
continue
for metric in report[job_type]:
for system in report[job_type][metric]:
if report[job_type][metric][system] == 'None':
continue
template[job_type][metric][system].append(
report[job_type][metric][system])
return template
except Exception as traceback_error:
statement = "Trouble building final data set"
error_logger.logger(statement, traceback_error)
def build_avg_data(column_managers):
try:
data_set = build_final_data_set(column_managers)
for job_type in data_set:
if job_type == None:
continue
for metric in data_set[job_type]:
for system in data_set[job_type][metric]:
if data_set[job_type][metric][system] == None or \
not data_set[job_type][metric][system]:
continue
# print("{}:{}:{}: {}".format(job_type, metric,
# system, data_set[job_type][metric][system]))
data_set[job_type][metric][system] = statistics.mean(
data_set[job_type][metric][system])
# print(data_set[job_type][metric][system])
return data_set
except Exception as traceback_error:
statement = "Trouble building average data set"
error_logger.logger(statement, traceback_error)
def set_column_values(self, title, values, row=2):
"""
Sets cell values for given column
"""
try:
index = self.get_column_index(title)
cells_to_set = self.get_column_cells(title)[1:]
except Exception as traceback_error:
statement = "Problem with inputs to set_column_values"
error_logger.logger(statement, traceback_error)
if self.add_values_to_cell_range(cells_to_set, values, index, row):
error_logger.logger("Values added to column {}".format(title))
try:
for i in range(len(cells_to_set)):
self.sheet.cell(row=row, column=index).value = values[i]
row += 1
except Exception as traceback_error:
statement = "Problem setting cell values for column {}".format(title)
error_logger.logger(statement, traceback_error)
# maybe this should be a seperate file?
# maybe use try block here
with open("config.json", "r") as config_file:
configs = json.load(config_file)
try:
sheet_title = configs['SHEET_TITLE']
job_types = ", ".join(configs['JOB_TYPES'])
column_color = configs['COLUMN_COLOR']
with_formulas = configs['WITH_FORMULAS']
with_raise = configs['WITH_RAISE']
new_columns = configs['NEW_COLUMNS']
except Exception as traceback_error:
statement = "Trouble loading from config file"
error_logger.logger(statement, traceback_error)
# read from config.json ^^^^^^^^^^^^^^^^^^^
def get_file_paths(file_extention):
'''
Returns file paths for Excel files only- in current working directory
'''
cwd = os.getcwd()
files = os.listdir(cwd)
excel_files = [
file for file in files if file[len(file) - 5:] == file_extention
]
excel_file_paths = [
cwd + "\{}".format(file_name) for file_name in excel_files
def format_excel_file(JOB_TYPES, file_path):
'''
Format excel file to final result given file path
'''
try:
column_manager = columnmanager.ColumnManager(file_path, sheet_title)
except Exception as traceback_error:
statement_1 = "Problem manipulating file from file path"
statement_2 = "Make sure Excel file is formatted properly- maybe try to re-export Extension from Enterprise\n"
error_logger.logger("{}\n{}".format(statement_1, statement_2),
traceback_error)
if column_manager.group_by_exists():
try:
build_metrics_columns(column_manager)
column_manager.add_validation(JOB_TYPES)
column_manager.save_doc()
except Exception as traceback_error:
# location = manager.get_error_location()
statement = "Problem building metrics columns for {}".format(
location)
error_logger.logger(statement, traceback_error)
elif all_new_columns_exist(
column_manager) and column_manager.sheet['A2'].value == 'Group By':
formatted_check_symbol = " /\n\/"
error_logger.logger(formatted_check_symbol)
# error_logger.logger('Good')
else:
statement_1 = "Excel file was NOT formatted"
statement_2 = (
"Make sure 'Group By' text is located in cell A1- and rest of Excel file is formatted properly"
"\n..OR re-export Extension from Enterprise and try again")
formatted_fail_symbol = "\ /\n \/\n /\\\n/ \\"
error_logger.logger(statement_1)
error_logger.logger(statement_2)
error_logger.logger(formatted_fail_symbol)
def run_program(sheet_title):
'''
Gets files
Builds file manager objects
Adds new metric columns and data validation to all column manager objects
'''
excel_file_paths = format_file.get_file_paths(".xlsx")
try:
format_file.format_excel_files(excel_file_paths, JOB_TYPES)
except Exception as traceback_error:
error_logger.logger(traceback_error=traceback_error)
avg.show_avg_marquee()
column_managers = [
columnmanager.ColumnManager(path, SHEET_TITLE)
for path in excel_file_paths
]
result = avg.build_avg_data(column_managers)
avg_filename = 'avg_data'
cwd = os.getcwd()
try:
os.mkdir("{}\\{}".format(cwd, avg_filename))
except OSError:
None
except Exception as traceback_error:
statement = "Trouble building 'avg_data' directory"
logger(statement, traceback_error)
try:
wb = openpyxl.Workbook()
dest_filename = '{}\\{}\\{}.xlsx'.format(cwd, avg_filename,
avg_filename)
ws1 = wb.active
ws1.title = "Averages"
wb.save(filename=dest_filename)
print('{} has been generated'.format(avg_filename))
except Exception as traceback_error:
error_logger.logger("Trouble building {} file".format(dest_filename),
traceback_error)
avg_manager = columnmanager.ColumnManager(dest_filename, "Averages")
avg_manager.make_new_column('Job Type', column_start=0)
avg_manager.make_new_column('Metric')
avg_manager.make_new_column('System')
avg_manager.make_new_column('Value')
try:
row = 2
for job_type in result:
for metric in result[job_type]:
for system in result[job_type][metric]:
avg_manager.sheet['A{}'.format(row)].value = str(job_type)
avg_manager.sheet['B{}'.format(row)].value = str(metric)
avg_manager.sheet['C{}'.format(row)].value = str(system)
if result[job_type][metric][system]:
avg_manager.sheet['D{}'.format(
row)].value = result[job_type][metric][system]
row += 1
except Exception as traceback_error:
error_logger.logger(
'Trouble formatting {} with values'.format(dest_filename),
traceback_error)
avg_manager.save_doc()
