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()