def populate(config, shell_xls, output_xls=None): """Populates an Excel spreadsheet from a shell. config: string of CSV file address containing config file shell_xls: string of XLSX file address containing Excel shell output_xls: string of XLSX file address for output file if output_xls is not specified, overwrite shell_xls """ # We temporarily change paths to config # file path (that's where all the output ought to be origPath = os.getcwd() configPath = os.path.dirname(config) configPathList = config.split("\\") configPathList.reverse() # os.chdir(configPath) # Read in all our inputs workbook = openpyxl.load_workbook(shell_xls) parsed_config = rc.readConfig(config) vd.validate_tabs(parsed_config, workbook) for enum, table in parsed_config.iterrows(): if table['ignore'] is not True: sheet = workbook.get_sheet_by_name(table['tabname']) csv_startcell = table['csv_startcell'] csv_start_row = int(csv_startcell.translate(None, string.letters)) csv_start_col = csv_startcell.translate(None, string.digits) csvpath = os.path.join(configPath, table['csv']) if not os.path.isfile(csvpath): error = "Could not find the file %s. Make sure your config is" "in the same location as your output " % table['csv'] raise ValueError(error) table_data = pd.read_csv(csvpath, skiprows=csv_start_row - 1) csv_start_col = helpers.col_to_number(csv_start_col) cols_to_drop = [x for x in range(0, csv_start_col)] table_data.drop(table_data.columns[cols_to_drop], axis=1, inplace=True) wt.write_tab(sheet, table_data, table['tab_startcell'], table['skiprows'], table['skipcols']) workbook.save(output_xls) # change back to old path os.chdir(origPath)
def write_tab(sheet, table_data, xls_startcell, skiprows, skipcols): """Writes the data for a particular table to the corresponding Excel spreadsheet. sheet: openpyxl worksheet to which you're writing table_data: pandas data frame containing data to write xls_startcell: cell in the sheet at which you will begin writing skiprows: list of rows in Excel spreadsheet to skip skipcols: list of columns in Excel spreadsheet to skip """ num_rows = table_data.shape[0] num_cols = table_data.shape[1] # We subtract one to remain 0-indexed start_row = int(xls_startcell.translate(None, string.ascii_letters)) - 1 start_col = helpers.col_to_number(xls_startcell.translate(None, string.digits)) num_skipcols = [helpers.col_to_number(col) for col in skipcols] total_rows = start_row + num_rows + len(skiprows) table_rows_to_write = [row for row in range(start_row, total_rows) if row not in skiprows] total_cols = start_col + num_cols + len(skipcols) table_cols_to_write = [col for col in range(start_col, total_cols) if col not in num_skipcols] for row_idx, row in enumerate(table_rows_to_write): for col_idx, col in enumerate(table_cols_to_write): current_cell = helpers.cell_name(row, col) value = table_data.iloc[row_idx, col_idx] try: value = float(value) except ValueError: pass sheet[current_cell].value = value
def populate(config, shell_xls, output_xls=None): """Populates an Excel spreadsheet from a shell. config: string of CSV file address containing config file shell_xls: string of XLSX file address containing Excel shell output_xls: string of XLSX file address for output file if output_xls is not specified, overwrite shell_xls """ # We temporarily change paths to config # file path (that's where all the output ought to be origPath = os.getcwd() configPath = os.path.dirname(config) configPathList = config.split("\\") configPathList.reverse() # os.chdir(configPath) # Read in all our inputs workbook = openpyxl.load_workbook(shell_xls) parsed_config = rc.readConfig(config) vd.validate_tabs(parsed_config, workbook) for enum, table in parsed_config.iterrows(): if table['ignore'] is not True: sheet = workbook.get_sheet_by_name(table['tabname']) csv_startcell = table['csv_startcell'] csv_start_row = int(csv_startcell.translate(None, string.letters)) csv_start_col = csv_startcell.translate(None, string.digits) csvpath = os.path.join(configPath, table['csv']) if not os.path.isfile(csvpath): error = "Could not find the file %s. Make sure your config is" "in the same location as your output " % table['csv'] raise ValueError(error) table_data = pd.read_csv(csvpath, skiprows=csv_start_row-1) csv_start_col = helpers.col_to_number(csv_start_col) cols_to_drop = [x for x in range(0, csv_start_col)] table_data.drop(table_data.columns[cols_to_drop], axis=1, inplace=True) wt.write_tab(sheet, table_data, table['tab_startcell'], table['skiprows'], table['skipcols']) workbook.save(output_xls) # change back to old path os.chdir(origPath)