def scrubber(dataog, app=None):
data = dataog.copy()
for slidecount, slide in enumerate(dataog):
if app != None:
msg_for_ui = 'Scrubbing Data — ' + str(trueround(((slidecount / len(dataog)) * 100), 0)) + '%'
v.log_entry(msg_for_ui, app_holder=app, fieldno=1)
page_config = dataog[slide]['page config']
page_config['number of tables'] = 0
page_config['number of charts'] = 0
for card in dataog[slide]:
if card != 'page config':
config = dataog[slide][card]['config']
if config['intended chart'] == 'TABLE':
page_config['number of tables'] += 1
elif config['has data']:
page_config['number of charts'] += 1
datavizcount = page_config['number of charts'] + page_config['number of tables']
if page_config['page title'] != None:
copycount = 1 + len(page_config['page copy'])
else:
copycount = len(page_config['page copy'])
if datavizcount + copycount == 0:
del data[slide]
return data
def collect_template_data(template, report_type):
template_config = {}
prs = Presentation(template)
# Create dictionary for each layout
for layout_idx, layout in enumerate(prs.slide_layouts):
template_config[layout_idx] = {}
template_config[layout_idx]['layout config'] = v.assign_layout_config(
name=layout.name)
config = template_config[layout_idx]['layout config']
# Grab placeholder details shape by shape
for shape in layout.placeholders:
shape_index = shape.placeholder_format.idx
shape_type = str(shape.placeholder_format.type).split()[0]
for ph_type in v.placeholder_types:
if ph_type in shape_type:
shape_name = shape_type + ' ' + str(shape_index)
template_config[layout_idx][shape_name] = {
'index': shape_index,
'width': round(shape.width.inches, 2),
'height': round(shape.height.inches, 2),
'left': round(shape.left.inches, 2)
}
try: # Increases the placeholder counts for each layout if appropriate
config[(shape_type.lower() + ' count')] += 1
if shape_type in ['CHART', 'TABLE', 'PICTURE']:
config['width test'].append(
round(shape.width.inches, 2))
except KeyError:
pass
template_config = assign_preferred_layouts(template_config, report_type)
v.log_entry('Template Analyzed')
return template_config
def data_import(app,
template,
wbdata,
templatedata,
trusave,
msg='Processing Data. '):
logging.info('Starting Data Import')
global prs
prs = Presentation(template)
# Select Layout and drop in charts
for slideidx, page in enumerate(wbdata):
page_config = wbdata[page]['page config']
footer_text = []
total_charts = str(page_config['number of charts'] +
page_config['number of tables'])
logging.info('Slide no. ' + str(slideidx + 1) + '--There is/are ' +
total_charts + ' Charts:')
layout, shapeslist = assign_layout(page_config, templatedata)
used_ph, used_data = [], [
] # Used for multiple charts. Checks if placholders/data used already
slide = prs.slides.add_slide(prs.slide_layouts[layout])
slidenotes, preflightlst = [], []
try: # If page doesn't have a title, drops in section tags
slide.shapes.title.text = page_config['page title']
except AttributeError:
for shape in shapeslist:
if 'BODY' in shape:
if 12 > shapeslist[shape]['width'] > 9:
if shapeslist[shape]['height'] < 1:
phidx = shapeslist[shape]['index']
placeholder = slide.placeholders[phidx]
text_frame = placeholder.text_frame
p = text_frame.paragraphs[0]
p.text = page_config['section tag']
for chart in wbdata[page]:
if chart != 'page config':
chart_config = wbdata[page][chart]['config']
intended_chart = chart_config['intended chart']
title_question = chart_config['title question']
error_list = chart_config['error list']
slidenotes.append(chart_config['notes'])
try: # If title question has a value, this will handle it appropriately
chart_config['data question'], chart_config[
'chart title'] = longestval(title_question)
footer_text.append('Q: ' + (chart_config['data question']))
except IndexError: # Some imports skip this step
pass
try: # Adds worksheet information to logging if available
msg += str(chart_config['notes'][0])[11:-1]
except IndexError:
pass
v.log_entry(msg, app_holder=app, fieldno=2)
if len(chart_config['bases']) > 0:
footer_text.append('Base: ' +
(str(chart_config['bases'])[1:-1]))
if len(chart_config['note']) > 0:
for item in chart_config['note']:
footer_text.append(item)
df = wbdata[page][chart]['frame']
chart_data = assign_chart_data(df, chart_config)
preflightlst.append(error_list)
if intended_chart == 'TABLE':
for shape in shapeslist:
if 'TABLE' in shape:
if shape not in used_ph and chart_data not in used_data:
phidx = shapeslist[shape]['index']
placeholder = slide.placeholders[phidx]
chc.create_table(df, placeholder, chart_config)
if chart_config['*HEAT MAP'] is True:
add_gradient_legend(slide)
used_ph.append(shape)
used_data.append(chart_data)
elif intended_chart == 'STAT':
for shape in shapeslist:
if 'BODY' in shape:
if shape not in used_ph and chart_data not in used_data:
if shapeslist[shape][
'height'] < 3 and shapeslist[shape][
'width'] < 3:
phidx = shapeslist[shape]['index']
placeholder = slide.placeholders[phidx]
text_frame = placeholder.text_frame
insert_text(chart_data, text_frame)
used_ph.append(shape)
used_data.append(chart_data)
elif intended_chart == 'PICTURE':
for shape in shapeslist:
if 'PICTURE' in shape:
if shape not in used_ph and chart_data not in used_data:
phidx = shapeslist[shape]['index']
placeholder = slide.placeholders[phidx]
picture = placeholder.insert_picture(
chart_data)
used_ph.append(shape)
used_data.append(chart_data)
else:
for shape in shapeslist:
if 'CHART' in shape:
if shape not in used_ph:
if chart_data not in used_data:
phidx = shapeslist[shape]['index']
placeholder = slide.placeholders[phidx]
chc.create_chart(df, placeholder,
chart_data, chart_config)
used_ph.append(shape)
used_data.append(chart_data)
else: # If it is page config
pagecontent = wbdata[page][chart]
if len(pagecontent) > 0:
textlst = wbdata[page][chart]['page copy']
for shape in shapeslist:
if 'BODY' in shape:
if shape not in used_ph and textlst not in used_data:
if (shapeslist[shape]['height'] > 3) or (
shapeslist[shape]['height'] > 2
and shapeslist[shape]['width'] > 9):
phidx = shapeslist[shape]['index']
placeholder = slide.placeholders[phidx]
text_frame = placeholder.text_frame
insert_text(textlst, text_frame)
used_ph.append(shape)
used_data.append(textlst)
if len(pagecontent['callouts']) > 0:
shapes = slide.shapes
for callout in pagecontent['callouts']:
c = pagecontent['callouts'][callout]
width, height, left, top = Inches(c[0]), Inches(
c[1]), Inches(c[2]), Inches(c[3])
shape = shapes.add_shape(MSO_SHAPE.RECTANGLE, left,
top, width, height)
shape.shadow.inherit = False
shape.line.fill.background()
fill = shape.fill
if c[4] is None:
fill.background()
elif c[4] == 'white':
fill.solid()
fill.fore_color.theme_color = MSO_THEME_COLOR.BACKGROUND_1
fill.fore_color.brightness = 1
if c[6] == 'c':
alignment = PP_ALIGN.CENTER
else:
alignment = PP_ALIGN.LEFT
callout_formatting(shape, c[5], alignment, c[7])
try:
create_footer(slide, footer_text)
except AttributeError:
logging.info('No Footer Placeholder found')
notesinsert(slide, slidenotes) # Add notes to every slide
preflight(
slide,
preflightlst) # Add boxes with error messages to applicable slides
prs.save(trusave)
v.log_entry('File Saved', app_holder=app, fieldno=2)
def data_collector(app, file, pptx_name, country=None):
wb = load_workbook(filename=file, data_only=True)
page_data = {}
most_recent_tab_color = None
page_counter = 0
sheet_data = {}
for sheet_idx, sheet in enumerate(wb.worksheets):
msg_for_ui = 'Reading ' + str(wb.sheetnames[sheet_idx])
v.log_entry(msg_for_ui, level='info', app_holder=app, fieldno=1)
# Create default config data for chart and add it to the sheet data dictionary
sheet_data[sheet_idx] = {}
config = v.assign_chart_config(
state=sheet.sheet_state, tab_color=sheet.sheet_properties.tabColor)
config['notes'].append(sheet)
# Create dictionary to collect data. Can only be one 'categories' per sheet
frame_data = {}
column_list = [] # Used for bases
# Iterate over excel by column to collect data
for col_idx, col in enumerate(sheet.iter_cols()):
series_lst = [] # Resets the list for new series
cell_strike_list = [
] # Tracks data detection to help filter trash values
# Check each cell for commands or color coding
for cell_idx, cell in enumerate(col):
cell_val, cell_color = sheet[
cell.coordinate].value, cell.fill.start_color.index
cell_val_cleaned_up = str_cleanup(
cell_val,
True) # Removes extra spaces, capitalizes strings
if cell_val_cleaned_up in config.keys(): # Checks for commands
config[cell_val_cleaned_up] = True
elif cell_val_cleaned_up in chart_types: # detects chart type
config['intended chart'], config[
'chart chosen'] = cell_val_cleaned_up, True
elif cell_color == 4: # detects data question for footer/chart title
config['title question'].append(
cell_val) # Update split to question and title
elif cell_color == 5: # detects bases for footer
column_list.append(
col_idx
) # Tracks column indexes of bases to align them with correct axis
try:
config['bases'].append(
('{:,}'.format(cell_val)
)) # Adds commas to base numbers when possible
except ValueError:
config['bases'].append(cell_val)
elif cell_color == 7: # detects all data
# First, does the cell have content?
if cell_val is not None:
if cell_val == 0:
series_lst.append(0.0)
elif cell_val == 1:
cell_strike_list.append(0)
elif cell_val_cleaned_up in v.exception_list:
if '*' in cell_val_cleaned_up:
config['directional check'] = True
# If not a percent, is it far enough away from others to be counted as a null?
if (cell_idx - cell_strike_list[-1]) > 3:
series_lst.append(0)
cell_strike_list.append(0)
else:
series_lst.append(cell_val)
cell_strike_list.append(cell_idx)
frame_data[
col_idx] = series_lst # Data is still pretty dirty at this point.
# Cleans up collected data
clean_data, config = clean_up_data(frame_data, config)
if len(clean_data) > 0:
try:
# Now that data is clean, create dataframe
df = pd.DataFrame.from_dict(clean_data)
df.set_index('categories', inplace=True)
# Selects only specific country data for country reports
if country is not None:
df, config['bases'] = filter_countries(
df.copy(), country, config['bases'].copy())
except ValueError:
# Data length mismatch causes errors
error_data = {
"ERROR": ['CHECK', 'DATA', 'SHEET'],
"COLLECTION": [0, 0, 0],
"CHECK DATA": [0, 0, 0]
}
df = pd.DataFrame.from_dict(error_data)
df.set_index('ERROR', inplace=True)
config['error list'].append('100')
config['notes'].append(str(clean_data))
else:
continue # Stops iteration before recording empty data frames
# Applies transposition
if config['*TRANSPOSE']:
df_t = df.transpose()
df = df_t # replaces data with transposed version
config['notes'].append("Chart Transposed")
# If chart is Top Box, create sum column
top_box_name = 'Top ' + str(len(
df.columns)) + ' Box' # Only used for Top Box charts
if config['*TOP BOX']:
if config['intended chart'] not in [
'STACKED BAR', 'STACKED COLUMN'
]:
config['intended chart'] = 'STACKED BAR'
df[top_box_name] = df.sum(axis=1)
# Sort data by first column in DF after transposing, or if Top Box, sum first, then first column
sort_parameters = [
config['*SORT'], config['*TOP 5'], config['*TOP 10'],
config['*TOP 10']
]
top_ranges = {'*TOP 5': 5, '*TOP 10': 10, '*TOP 20': 20}
if True in sort_parameters:
config['notes'].append("Chart sorted by " + str(df.columns[0]))
if config['*TOP BOX']:
df_s = df.sort_values(by=[top_box_name, df.columns[0]],
ascending=[False, False])
else:
df_s = df.sort_values(by=[df.columns[0]], ascending=[False])
df = df_s
# Truncates data to top selection.
# If multiple top selections are added, should use the largest.
for top_range in top_ranges:
if config[top_range]:
truncated_df = df.iloc[0:top_ranges[top_range], :]
df = truncated_df
# Used to highlight highest values in table row after transposing
config['max values'] = df.max(axis=1).values.tolist()
# Check Tab color against most recent. If different, create new page.
if config['tab color'] is None or config[
'tab color'] != most_recent_tab_color:
page_counter += 1
page_data[page_counter] = {}
most_recent_tab_color = config['tab color']
page_data[page_counter][sheet_idx] = {'frame': df, 'config': config}
# Count data visualizations and assign page config
formatted_page_data = configure_pages(page_data, tag=pptx_name)
return formatted_page_data
def press():
clear_status()
file, filename = create_filename()
# Find the report type
report_selection = app.getOptionBox('Report Type')
report_list = report_config[report_selection]['report list']
report_type = report_config[report_selection]['report type']
report_suffix = report_config[report_selection]['report suffix']
# Read Optional Value inputs
dtv_et_totals = []
for value in ['QSR', 'FC', 'MID', 'CD']:
if app.getEntry(value) != '':
dtv_et_totals.append(app.getEntry(value))
if len(dtv_et_totals) < 4:
if len(dtv_et_totals) > 0:
v.log_entry('Must have 0 or 4 DTV values',
level='warning',
app_holder=app,
fieldno=0)
return
else:
dtv_et_totals = None
# Find and read the template
template = app.getEntry('template')
v.log_entry('Loading Template', app_holder=app, fieldno=0)
template_data = tr.collect_template_data(template, report_type)
# Begin excel reading and report building process
v.log_entry('Opening Excel', app_holder=app, fieldno=1)
dsave = app.directoryBox(title='Where should report(s) be saved?',
dirName=None,
parent=None)
for report in report_list: # Allows for multiple reports from one excel
if report is None:
report_name = report_type + '_' + datetime.now().strftime(
"%B %d, %Y")
full_directory = filename + '.pptx'
else:
report_name = report + report_suffix
full_directory = dsave + '/' + report + '.pptx'
# Find and read the excel file
try:
if report_type in ['general', 'global']:
if app.getCheckBox(
'Use Single-Color Import'
): # statement will be removed after new selection method
wbdata = er2.data_collector(app,
file,
report_name,
country=report)
else:
wbdata = er.readbook(app,
file,
report_name,
country=report)
elif report_type in ['lto', 'value']:
wbdata = er.scorecard(file, report_config[report_selection])
elif report_type == 'dtv':
wbdata = er.dtv_reader(file, dtv_et_totals)
# Create pptx and drop in data
sc.data_import(app,
template,
wbdata,
template_data,
full_directory,
msg='Processing Data')
v.log_entry(full_directory + ' saved.')
v.log_entry('Import Complete', app_holder=app, fieldno=3)
except PermissionError:
v.log_entry('File open cannot save',
level='warning',
app_holder=app,
fieldno=3)
'''except ValueError:
def create_filename():
file = app.getEntry('xlsxfile')
filename = file.replace('.xlsx', '')
v.log_entry('***********************************************')
v.log_entry(('Start of import for ' + filename))
return file, filename
def readbook(app, file, pptxname, country = None):
wb = load_workbook(filename=file, data_only=True)
slide_data = OrderedDict()
combinecount = 1 # Indicates how many charts per slide
most_recent_tabcolor = None
slidecount = 0
for sheetcount, wksht in enumerate(wb.worksheets):
msg_for_ui = 'Reading ' + str(wb.sheetnames[sheetcount])
v.log_entry(msg_for_ui, app_holder=app, fieldno=1)
if wksht.sheet_state == "visible":
tabcolor = wksht.sheet_properties.tabColor
if sheetcount not in slide_data:
slide_data[sheetcount] = {} # sets up dictionary for future use
slide_data[sheetcount]['page config'] = v.assign_page_config(tag=pptxname)
# Creates new config dict for each tab
config = v.assign_chart_config(state=wksht.sheet_state, tab_color=wksht.sheet_properties.tabColor)
if wksht.sheet_state == "visible":
tabcolor = wksht.sheet_properties.tabColor # Used for combining charts
config['notes'].append(wksht)
framedata = OrderedDict() # Used to create dataframe
framedata['categories'] = []
seriesnamelist = []
colchecklst = []
for col in wksht.iter_cols(): # Colcheckno is used to see if bases are vertical
colcheckno = 0 # Resets to 0 each column. If higher than 0, indicates vertical positioning.
serieslist = [] # blanks out series for every new column
for cell in col:
cellval = wksht[cell.coordinate].value
cellcolor = cell.fill.start_color.index
# update config
cellval_c = strcleanup(cellval, True) # Used for commands and chart types, not copy.
if cellval_c in config.keys():
config[cellval_c] = True
elif cell.font.underline == 'single': # detects slide copy command
config['copy'].append(cellval)
elif cellval_c in chart_types: # detects chart type command
config['intended chart'], config['chart chosen'] = cellval_c, True
# color based selections
elif cellcolor == 4: # detects data question for footer/chart title
config['title question'].append(cellval) # Update split to question and title
elif cellcolor == 5: # detects bases for footer
colchecklst.append(colcheckno) # Used to determine base labels (rows v. cols)
try:
config['bases'].append(('{:,}'.format(cellval))) # Adds commas to base numbers
except ValueError:
config['bases'].append(cellval)
colcheckno += 1
elif cellcolor == 7: # detects categories
if cellval is None:
config['error list'].append('106')
logging.warning('106 ERROR: blank category cell collected')
else:
cat = strcleanup(cellval)
framedata['categories'].append(cat)
elif cellcolor == 8: # Collects series name (TO UPDATE: merge with series list with POP?)
if cellval is not None:
series_name = strcleanup(cellval)
else:
series_name = "ERROR_PLACEHOLDER"
seriesnamelist.append(series_name)
elif cellcolor == 9: # detects data
config['has data'] = True
if config['*FORCE FLOAT'] or config['*FORCE CURRENCY']:
config['percent check'] = False
if cellval is None: # Separate check for error reporting
serieslist.append(0)
config['error list'].append('106')
logging.warning('106 ERROR')
if config['*FORCE PERCENT'] is True:
if cellval in exception_list:
percentvalue = 0
else:
percentvalue = float(cellval) / 100
serieslist.append(percentvalue)
elif type(cellval) != float:
if type(cellval) is int and cellval > 1:
serieslist.append(float(cellval))
if cellval == 1 and config['percent check'] is True:
serieslist.append(1.0)
elif cellval == 0 or cellval_c in exception_list:
serieslist.append(0.0)
elif '=' in str(cellval):
config['error list'].append('107') # TO UPDATE: Read formulas
logging.warning('107 ERROR')
serieslist.append(0.0)
else:
serieslist.append(cellval)
if len(serieslist) > 0: # Checks if series were collected
if len(framedata['categories']) != len(serieslist):
config['error list'].append('100')
framedata[series_name] = serieslist # Adds series to data
# Cleanup series names for vertical series (happens after initial collection)
if len(seriesnamelist) > (len(framedata) - 1):
newdata = OrderedDict()
newdata['categories'] = framedata['categories'].copy()
# The following appends non-repeating series names.
# The set command doesn't maintain order, causing major issues.
newseriesnames = []
for name in seriesnamelist:
if name not in newseriesnames:
newseriesnames.append(name)
lststep = len(newseriesnames) # determines the step number for the data reorg
# Find series name in original data for parsing
if 1 < len(framedata) < 3:
for key in framedata:
if key != 'categories':
keyholder = key
else:
logging.error('Major data issue on ' + str(wksht) + '. Check series selections.')
for lstidx, series in enumerate(newseriesnames):
newdata[series] = []
for idx in range(lstidx, len(framedata[keyholder]), lststep):
newdata[series].append(framedata[keyholder][idx])
framedata = newdata.copy() # Replaces erroroneous data with reconfigured data
if '100' in config['error list']: # Removes previously applied error for vertical series charts
config['error list'].remove('100')
# Creates a dataframe from the collected data. The dictionary is dropped from here on in.
try:
if country is not None: # If this is a country report, it will filter out countries
newbases = []
df_og = pd.DataFrame.from_dict(framedata)
basedict = {'categories': 'bases'}
for idx, col in enumerate(df_og.columns, start=-1): # Create dictionary of bases
if col != 'categories':
basedict[col] = config['bases'][idx]
df_w_bases = df_og.append(basedict, ignore_index=True)
new_df = df_w_bases[['categories', 'Global Average', country]].copy()
baseholder = new_df.iloc[-1:].values.tolist()
for base in baseholder:
for b in base:
if b != 'bases':
newbases.append(b)
config['bases'] = newbases
new_df.drop(new_df.tail(1).index,inplace=True)
df = new_df
df.set_index('categories', inplace=True)
else:
df = pd.DataFrame.from_dict(framedata)
df.set_index('categories', inplace=True)
config['max values'] = df.max(axis=1).values.tolist()
except: # This drops in dummy data if the data doesn't fit into the dataframe.
df = pd.DataFrame.from_dict(data_error)
config['error list'].append('100')
logging.warning('100 ERROR')
df.set_index('categories', inplace=True)
# Base Collection—Allows multiple bases
data_base_list = []
basecount = len(config['bases'])
config['directional check'] = basecount > 0 and '35' in config['bases']
colcheck = 1 in colchecklst # True if vertical base selection
basestr = '' # Used for complex country report base formatting
for nameidx, base in enumerate(config['bases']):
if basecount == 1:
data_base_list.append('Base: ' + str(base))
else:
if country is None:
if colcheck: # Means bases refers to categories, not series
data_base_list.append(str(framedata['categories'][nameidx]) + ' Base: ' + str(base))
else:
data_base_list.append(str(df.columns[nameidx]) + ' Base: ' + str(base))
else: # TO UPDATE: This will break is GA comes after country in data.
if df.columns[nameidx] == 'Global Average':
basestr = ('Base: ' + str(base) + '(global average)')
elif df.columns[nameidx] == country:
basestr = basestr + (' and ' + str(base) + '(' + str(df.columns[nameidx]) + ')')
data_base_list.append(basestr)
config['bases'] = data_base_list
if len(df.index) > 10 and config['intended chart'] == 'PIE':
config['intended chart'] = 'BAR'
# Transposes Data
if config['*TRANSPOSE'] is True:
df_t = df.transpose()
df = df_t # replaces data with transposed version
config['notes'].append("Chart Transposed")
logging.info("Chart Transposed")
# If chart is Top Box, create sum column
if config['*TOP BOX'] == True:
if config['intended chart'] not in ['STACKED BAR', 'STACKED COLUMN']:
config['intended chart'] = 'STACKED BAR'
topboxname = 'Top ' + str(len(df.columns)) + ' Box'
df[topboxname] = df.sum(axis=1)
# Sort data with Pandas. Does not work if category/series mismatch error thrown earlier
sort_parameters = [config['*SORT'], config['*TOP 5'], config['*TOP 10'], config['*TOP 10']]
top_ranges = {'*TOP 5': 5, '*TOP 10': 10, '*TOP 20': 20}
if True in sort_parameters and '100' not in config['error list']:
if len(framedata) > 0:
colname = df.columns[0]
s_note = "Chart sorted by " + str(colname)
config['notes'].append(s_note)
if config['*TOP BOX'] == True:
df_s = df.sort_values(by=[topboxname, colname], ascending=[False, False])
else:
df_s = df.sort_values(by=[colname], ascending=[False])
df = df_s
# If top selections are true:
for top_range in top_ranges:
if config[top_range]:
truncated_df = df.iloc[0:top_ranges[top_range], : ]
df = truncated_df
# Combine Check looks at tab color to see if new tab combined with previous on slide
if combinecount > 6:
logging.warning('Too many charts for combining. Cannot combine more than 6.')
combinecount = 1
if tabcolor is not None:
if tabcolor == most_recent_tabcolor: # compares tab to most recent tab
combinecount += 1
slidecount -= 1 # Keeps slide count accurate for error reporting
else:
combinecount = 1
most_recent_tabcolor = tabcolor
else:
combinecount = 1
slide_data[slidecount][combinecount] = {'config': config, 'frame': df}
slidecount += 1
# Scrub Blanks
v.log_entry('Scrubbing Data', app_holder=app, fieldno=1)
slide_data_sd = scrubber(slide_data, app)
v.log_entry('Data Collection Complete', app_holder=app, fieldno=1)
return slide_data_sd