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)
