def calc_profit():
# loading excel file to read and write to
wb = openpyxl.load_workbook(filename="product_list.xlsx")
sheet = wb.active
# Creating and styling Profit Column Heading
sheet.cell(row=1, column=8).value = "Profit"
sheet.cell(row=1, column=8).font = Font(bold=True, name="Helvetica", size=15)
sheet.cell(row=1, column=8).fill = PatternFill(patternType="solid", fgColor="00e5ee")
sheet.cell(row=1, column=8).alignment = Alignment(horizontal="center")
# Calculating Profit
row_index=2
for cell in sheet["H"][1:]:
cell.value = f"=G{row_index}-F{row_index}"
row_index = row_index + 1
cell.alignment = Alignment(horizontal="center")
# Adding Conditional Formatting on entire row
yellow_background = PatternFill(bgColor="e9ee9e")
diff_style = DifferentialStyle(fill=yellow_background)
rule = Rule(type="expression", dxf=diff_style)
rule.formula = ["$H1<0"]
sheet.conditional_formatting.add(f"A1:H{sheet.max_column}", rule)
# Same formula as but for a cell in a column
# sheet.conditional_formatting.add(f'H2:H{sheet.max_column}', CellIsRule(operator='lessThan', formula=['0'], fill=yellow_background))
wb.save("product_list.xlsx")
def _insert_compare_string_results(string_results, ws, row_height):
"""
Insert string result into a worksheet
Parameters
----------
string_results: dict
result to insert
ws: Excel worksheet instance
row_height: float
Height of the row
"""
# construct thick border
thin = Side(border_style="thin", color="000000")
border = Border(top=thin, left=thin, right=thin, bottom=thin)
row_heights = {}
# loop and output result
for result in string_results:
column = result['column']
if not 'result_df' in result.keys():
ws.append([column, result['error_msg']])
for col in ['A', 'B']:
ws['%s%d' %(col, ws.max_row)].style = 'Bad'
ws.append([''])
continue
result_df = result['result_df'][0][['feature', 'value', 'graph']]
value_counts_df = result['result_df'][1]
head_row = _insert_df(result_df, ws)
# if there is value counts result
if len(value_counts_df) > 0:
value_counts_df = value_counts_df.rename(columns={'value': 'top 10 values', 'count_x': 'count_1', 'count_y': 'count_2'})
databar_head = _insert_df(value_counts_df, ws, header=True, head_style='60 % - Accent5')
for row_idx in range(databar_head, databar_head+value_counts_df.shape[0]+1):
row_heights[row_idx] = 25
# add conditional formatting: data bar
first = FormatObject(type='num', val=0)
second = FormatObject(type='num', val=np.max([value_counts_df['count_1'].max(), value_counts_df['count_2'].max()]))
data_bar1 = DataBar(cfvo=[first, second], color=TABLE1_DARK.replace('#', ''), showValue=True, minLength=None, maxLength=None)
data_bar2 = DataBar(cfvo=[first, second], color=TABLE2_DARK.replace('#', ''), showValue=True, minLength=None, maxLength=None)
# assign the data bar to a rule
rule1 = Rule(type='dataBar', dataBar=data_bar1)
ws.conditional_formatting.add('B%d:B%d' %(databar_head+1, databar_head+len(value_counts_df)), rule1)
rule2 = Rule(type='dataBar', dataBar=data_bar2)
ws.conditional_formatting.add('C%d:C%d' %(databar_head+1, databar_head+len(value_counts_df)), rule2)
# draw the thick outline border
_style_range(ws, 'A%d:C%d'%(head_row, databar_head+len(value_counts_df)), border=border)
else:
_style_range(ws, 'A%d:C%d'%(head_row, head_row+result_df.shape[0]-1), border=border)
# add gap
ws.append([''])
_adjust_ws(ws, row_height=row_height, row_heights=row_heights, adjust_type='str')
def highlight_excel():
workbook = load_workbook(filename=excel_file)
sheet = workbook.active
row_count = sheet.max_row
dimensions = sheet.dimensions
sheet.auto_filter.ref = dimensions
red_background = PatternFill(bgColor='ff4d4d')
yellow_background = PatternFill(bgColor='ffff80')
green_background = PatternFill(bgColor='ccffcc')
diff_style1 = DifferentialStyle(fill=red_background)
diff_style2 = DifferentialStyle(fill=yellow_background)
diff_style3 = DifferentialStyle(fill=green_background)
rule3 = Rule(type="expression", dxf=diff_style3)
rule3.formula = ["$D1>=7"]
sheet.conditional_formatting.add('A1:C' + str(row_count), rule3)
sheet.conditional_formatting.add('E1:J' + str(row_count), rule3)
rule1 = Rule(type="expression", dxf=diff_style1)
rule1.formula = ["$D1<5"]
sheet.conditional_formatting.add('A1:C' + str(row_count), rule1)
sheet.conditional_formatting.add('E1:J' + str(row_count), rule1)
rule2 = Rule(type="expression", dxf=diff_style2)
rule2.formula = (["$D1<7"])
sheet.conditional_formatting.add('A1:C' + str(row_count), rule2)
sheet.conditional_formatting.add('E1:J' + str(row_count), rule2)
data = pd.read_csv(csv_collection)
start_value = min(data['SCORE'])
end_value = max(data['SCORE'])
data_bar_rule = DataBarRule(start_type="num",
start_value=start_value,
end_type="num",
end_value=end_value,
color='53ff4d')
sheet.conditional_formatting.add('D2:D' + str(row_count), data_bar_rule)
red_text = Font(color='a70000')
green_text = Font(color='00bb00')
for element in range(2, row_count + 1):
sheet['F' + str(element)].number_format = numbers.FORMAT_CURRENCY_USD
sheet['G' + str(element)].number_format = numbers.FORMAT_CURRENCY_USD
sheet['H' + str(element)].number_format = numbers.FORMAT_CURRENCY_USD
cell_value = sheet.cell(element, 8).value
if int(cell_value) < 0:
sheet.cell(element, 8).font = red_text
else:
sheet.cell(element, 8).font = green_text
workbook.save(excel_file)
def create_xls(args, final_acl):
print('Creating the spreadsheet...')
filename = os.path.join(args[4], args[5] + ".xlsx")
# Create workbook with the one defaut sheet and rename it
wb = Workbook()
ws1 = wb.active
ws1.title = "ACL Report"
# Add the headers, set font, colour and column width (from header dictionary)
for col, head in zip(range(1,len(header) + 1), header.items()):
ws1['{}1'.format(get_column_letter(col))] = head[0] # get_column_letter converts number to letter
ws1['{}1'.format(get_column_letter(col))].fill = PatternFill(bgColor=colors.Color("00DCDCDC"))
ws1['{}1'.format(get_column_letter(col))].font = Font(bold=True, size=14)
ws1.column_dimensions[get_column_letter(col)].width = head[1]
# Add the ACE entries. The columns holding numbers are changed to integrars
for ace in final_acl:
ace[1] = int(ace[1])
ace[8] = int(ace[8])
ws1.append(ace)
# Add a key at start with info on the colourised rows for ACEs with frequent hitcnts
ws1.insert_rows(1)
ws1.insert_rows(2)
keys = {'A1': 'Key:', 'B1':'Hit in last 1 day', 'E1':'Hit in last 7 days', 'G1':'Hit in last 30 days', 'I1':'Inactive'}
colour = {'B1':'E6B0AA', 'E1':'A9CCE3', 'G1':'F5CBA7', 'I1':'D4EFDF'}
for cell, val in keys.items():
ws1[cell] = val
ws1['A1'].font = Font(bold=True)
for cell, col in colour.items():
ws1[cell].fill = PatternFill(start_color=col, end_color=col, fill_type='solid')
ws1.freeze_panes = ws1['A4'] # Freezes the top row (A1) so remains when scrolling
ws1.auto_filter.ref = 'A3:L4' # Adds dropdown to headers to the headers
# Colours used for columns dependant on the last hit data (J column). Formula is a standard XL formula
style_grn = DifferentialStyle(fill=PatternFill(bgColor=colors.Color("00D4EFDF")))
rule_inactive = Rule(type="expression",formula=['=$L1="inactive"'], dxf=style_grn)
style_red = DifferentialStyle(fill=PatternFill(bgColor=colors.Color("00E6B0AA")))
rule_1day = Rule(type="expression",formula=["=AND(TODAY()-$J1>=0,TODAY()-$J1<=1)"], dxf=style_red)
style_blu = DifferentialStyle(fill=PatternFill(bgColor=colors.Color("00A9CCE3")))
rule_7day = Rule(type="expression", formula=["=AND(TODAY()-$J1>=0,TODAY()-$J1<=7)"], dxf=style_blu)
style_org = DifferentialStyle(fill=PatternFill(bgColor=colors.Color("00F5CBA7")))
rule_30day = Rule(type="expression", formula=["=AND(TODAY()-$J1>=0,TODAY()-$J1<=30)"], dxf=style_org)
# Apply the rules to workbook and save it
for rule in [rule_inactive, rule_1day, rule_7day, rule_30day]:
ws1.conditional_formatting.add(ws1.dimensions, rule)
wb.save(filename)
print('File {} has been created'.format(filename))
def addrulesBW(ws):
# rule pour les dates inférieures
diff = DifferentialStyle(font=Font(bold=True))
rule5 = Rule(type="expression",
dxf=diff,
formula=['AND(NOT(ISBLANK($D2)),($D2<TODAY()))'])
ws.conditional_formatting.add("D2:D466", rule5)
def example_pyxl():
first = FormatObject(type='percent', val=0)
second = FormatObject(type='percent', val=33)
third = FormatObject(type='percent', val=67)
iconset = IconSet(iconSet='3TrafficLights1', cfvo=[first, second, third], showValue=None, percent=None, reverse=None)
# assign the icon set to a rule
rule = Rule(type='iconSet', iconSet=iconset)
wb = Workbook()
dest_filename = 'empty_book.xlsx'
ws1 = wb.active
ws1.title = "range names"
for row in range(1, 40):
ws1.append(range(600))
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
_ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
test = f'{get_column_letter(col)}{col}'
print(f'Получаем первую ячейку заголовка: {test}')
print(ws3['AA10'].value)
ws1.conditional_formatting.add(f'{test}', rule)
wb.save(filename = dest_filename)
def test_conditional_formatting_customRule(worksheet_with_cf,
write_conditional_formatting):
ws = worksheet_with_cf
from openpyxl.formatting.rule import Rule
ws.conditional_formatting.add(
'C1:C10',
Rule(**{
'type': 'expression',
'formula': ['ISBLANK(C1)'],
'stopIfTrue': '1'
}))
cfs = write_conditional_formatting(ws)
xml = b""
for cf in cfs:
xml += tostring(cf)
diff = compare_xml(
xml, """
<conditionalFormatting sqref="C1:C10">
<cfRule type="expression" stopIfTrue="1" priority="1">
<formula>ISBLANK(C1)</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def test_conditional_formatting_customRule(self):
worksheet = self.ws
worksheet.conditional_formatting.add(
'C1:C10',
Rule(
**{
'type': 'expression',
'formula': ['ISBLANK(C1)'],
'stopIfTrue': '1',
}))
cfs = write_conditional_formatting(worksheet)
xml = b""
for cf in cfs:
xml += tostring(cf)
diff = compare_xml(
xml, """
<conditionalFormatting sqref="C1:C10">
<cfRule type="expression" stopIfTrue="1" priority="1">
<formula>ISBLANK(C1)</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def format_ws(ws):
header = DifferentialStyle(font=Font(bold=True))
r0 = Rule(type="expression", dxf=header)
last_row = ws.max_row
last_column = get_column_letter(ws.max_column)
ws.conditional_formatting.add(f"A1:{last_column}1", r0)
create_table(ws, ws.title, f"A1:{last_column}{last_row}")
alignment_wrapper(ws)
def format_data(writer, current_date):
workbook = writer.book
sheet = workbook[current_date]
#sheet = workbook.active
#Fill ("NEW") rule
blue_bg = PatternFill(bgColor=Color(indexed=27))
diff_style = DifferentialStyle(fill=blue_bg)
rule = Rule(type="expression", dxf=diff_style)
rule.formula = ['$D2 = "NEW"']
sheet.conditional_formatting.add("D2:E201", rule)
#Arrow rule
rule_arrow = IconSetRule('3Arrows', 'num', [-200, 0, 1])
sheet.conditional_formatting.add("D2:E201", rule_arrow)
write_further_info(sheet)
workbook.save(FILENAME)
def _insert_string_results(string_results, ws, row_height):
"""
Insert result of a string type column into a worksheet
Parameters
----------
string_results: dict
The result dictionary
ws: Excel worksheet instance
row_height: float
Height of the rows
"""
# construct thin border
thin = Side(border_style="thin", color="000000")
border = Border(top=thin, left=thin, right=thin, bottom=thin)
# loop and output result
for result in string_results:
column = result['column']
if 'result_df' not in result.keys():
ws.append([column, result['error_msg']])
for col in ['A', 'B']:
ws['%s%d' %(col, ws.max_row)].style = 'Bad'
ws.append([''])
continue
result_df = result['result_df'][0]
value_counts_df = result['result_df'][1]
head_row = _insert_df(result_df, ws)
# if there is value counts result
if len(value_counts_df) > 0:
value_counts_df = value_counts_df.rename(columns={column: 'top 10 values'})
databar_head = _insert_df(value_counts_df, ws, header=True, head_style='60 % - Accent5')
# add conditional formatting: data bar
first = FormatObject(type='num', val=0)
second = FormatObject(type='num', val=value_counts_df['count'].max())
data_bar = DataBar(cfvo=[first, second], color=DIS_LINE.replace('#', ''),
showValue=True, minLength=None, maxLength=None)
# assign the data bar to a rule
rule = Rule(type='dataBar', dataBar=data_bar)
ws.conditional_formatting.add('B%d:B%d' %(databar_head+1, databar_head+len(value_counts_df)), rule)
# draw the thick outline border
_style_range(ws, 'A%d:B%d'%(head_row, databar_head+len(value_counts_df)), border=border)
else:
_style_range(ws, 'A%d:B%d'%(head_row, head_row+result_df.shape[0]-1), border=border)
# add gap
ws.append([''])
# adjust the worksheet
_adjust_ws(ws=ws, row_height=row_height)
def cf_blanks(ws, first_cell, last_cell):
# Conditional formatting to highlight blanks as light gray
blankFill = PatternFill(start_color='EEEEEE',
end_color='EEEEEE',
fill_type='solid')
dxf = DifferentialStyle(fill=blankFill)
blank = Rule(type="expression",
formula=["ISBLANK({0})".format(first_cell)],
dxf=dxf,
stopIfTrue=True)
ws.conditional_formatting.add('{0}:{1}'.format(first_cell, last_cell),
blank)
def cf_mismatches(ws, first_cell, last_cell):
# Conditional formatting to highlight mismatches
redFill = PatternFill(start_color='EE6666',
end_color='EE6666',
fill_type='solid')
dxf = DifferentialStyle(fill=redFill)
rule = Rule(type="expression",
formula=["ISNUMBER(SEARCH(\"|\", {0}))".format(first_cell)],
dxf=dxf,
stopIfTrue=True)
ws.conditional_formatting.add('{0}:{1}'.format(first_cell, last_cell),
rule)
def formatting_xls():
wb = Workbook()
ws = wb.active
# Create fill
redFill = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
# Add a two-color scale
# Takes colors in excel 'RRGGBB' style.
ws.conditional_formatting.add('A1:A10', ColorScaleRule(start_type='min', start_color='AA0000', end_type='max', end_color='00AA00'))
# Add a three-color scale
ws.conditional_formatting.add('B1:B10', ColorScaleRule(start_type='percentile', start_value=10, start_color='AA0000', mid_type='percentile', mid_value=50, mid_color='0000AA', end_type='percentile', end_value=90, end_color='00AA00'))
# Add a conditional formatting based on a cell comparison
# addCellIs(range_string, operator, formula, stopIfTrue, wb, font, border, fill)
# Format if cell is less than 'formula'
ws.conditional_formatting.add('C2:C10', CellIsRule(operator='lessThan', formula=['C$1'], stopIfTrue=True, fill=redFill))
# Format if cell is between 'formula'
ws.conditional_formatting.add('D2:D10', CellIsRule(operator='between', formula=['1','5'], stopIfTrue=True, fill=redFill))
# Format using a formula
ws.conditional_formatting.add('E1:E10', FormulaRule(formula=['ISBLANK(E1)'], stopIfTrue=True, fill=redFill))
# Aside from the 2-color and 3-color scales, format rules take fonts, borders and fills for styling:
myFont = Font()
myBorder = Border()
ws.conditional_formatting.add('E1:E10', FormulaRule(formula=['E1=0'], font=myFont, border=myBorder, fill=redFill))
# Highlight cells that contain particular text by using a special formula
red_text = Font(color="9C0006")
red_fill = PatternFill(bgColor="FFC7CE")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText", operator="containsText", text="highlight", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("highlight",A1)))']
ws.conditional_formatting.add('A1:F40', rule)
wb.save("test.xlsx")
def cf_highlight_good_row(ws, first_cell, last_cell, start_row, end_row):
# Conditional formatting to highlight rows with no mismatches
greenFill = PatternFill(start_color='66EE66',
end_color='66EE66',
fill_type='solid')
dxf = DifferentialStyle(fill=greenFill)
rule = Rule(type="expression",
formula=[
"COUNT(SEARCH(\"|\",${0}:${1}))<1".format(
start_row, end_row)
],
dxf=dxf,
stopIfTrue=True)
ws.conditional_formatting.add('{0}:{1}'.format(first_cell, last_cell),
rule)
def add_color_conditioning(self, sheet, address_range):
""" This will create and apply a rule for color conditioning the heatmap sheet
"""
mylog.debug('Begin add color conditioning to heatmap sheet for range %s' % address_range)
mylog.debug('Setting minimum and maximum ranges, and mid to percentile with value of 50')
first = FormatObject(type='min')
mid = FormatObject(type='percentile', val=50)
last = FormatObject(type='max')
colors = [Color('7FA9D3'), Color('FCBF04'), Color('BF0200')]
mylog.debug('Colors in range are %s' % colors)
color_scale = ColorScale(cfvo=[first, mid, last], color=colors)
rule = Rule(type='colorScale', colorScale=color_scale)
mylog.debug('Applying conditional formatting to sheet at range %s' %
address_range)
sheet.conditional_formatting.add(address_range, rule)
return True
def openpyxl_rules(self, contrast_text=True):
if self.ignore_blanks:
rule_ignore_blanks = Rule(type="containsBlanks", stopIfTrue=True)
yield rule_ignore_blanks
for l_i in self.__legends:
interval_color = _COLOR_MAP.get(l_i.color.upper(), l_i.color)
color_fill = PatternFill(start_color=interval_color,
end_color=interval_color,
fill_type='solid')
# use a contrasting text colour, like white, against dark coloured fills
if contrast_text and l_i.color.upper() in _CONTRAST_MAP:
interval_font = Font(color=_CONTRAST_MAP[l_i.color.upper()],
bold=True)
else:
interval_font = Font(bold=True)
if l_i.start is None and l_i.end is None:
# make everything the same colour
rule = ColorScaleRule(start_type='percentile',
start_value=0,
start_color=interval_color,
end_type='percentile',
end_value=100,
end_color=interval_color,
font=interval_font)
elif l_i.start is None:
rule = CellIsRule(operator='lessThan',
formula=[str(l_i.end)],
stopIfTrue=True,
fill=color_fill,
font=interval_font)
elif l_i.end is None:
rule = CellIsRule(operator='greaterThanOrEqual',
formula=[str(l_i.start)],
stopIfTrue=True,
fill=color_fill,
font=interval_font)
else:
rule = CellIsRule(operator='between',
formula=[str(l_i.start),
str(l_i.end)],
stopIfTrue=True,
fill=color_fill,
font=interval_font)
yield rule
def addrulesCouleur(ws, longueur):
#rule pour la liste terminé
gbackground = PatternFill(start_color='00CC00',
end_color='00CC00',
fill_type='solid')
diff = DifferentialStyle(fill=gbackground)
rule4 = Rule(type="expression", dxf=diff)
rule4.formula = ['$A2="Terminé !"']
ws.conditional_formatting.add("A2:F" + str(longueur + 1), rule4)
#rule pour les dates inférieures
redbackground = PatternFill(start_color='FF0000',
end_color='FF0000',
fill_type='solid')
diff = DifferentialStyle(fill=redbackground)
#['AND(NOT(ISEMPTY($D1));($D1<TODAY()))']
rule5 = Rule(type="expression",
dxf=diff,
formula=['AND(NOT(ISBLANK($D2)),($D2<TODAY()))'])
ws.conditional_formatting.add("D2:D466", rule5)
#rule pour la liste en cours
ybackground = PatternFill(start_color='FFFF00',
end_color='FFFF00',
fill_type='solid')
diff = DifferentialStyle(fill=ybackground)
rule1 = Rule(type="expression", dxf=diff, formula=['$A2="En cours"'])
ws.conditional_formatting.add("A2:F" + str(longueur + 1), rule1)
#rule pour la liste bolqué
obackground = PatternFill(start_color='FF9933',
end_color='FF9933',
fill_type='solid')
diff = DifferentialStyle(fill=obackground)
rule2 = Rule(type="expression", dxf=diff, formula=['$A2="Bloqué"'])
ws.conditional_formatting.add("A2:F" + str(longueur + 1), rule2)
#rule pour la liste presque fini
lgbackground = PatternFill(start_color='99FF33',
end_color='99FF33',
fill_type='solid')
diff = DifferentialStyle(fill=lgbackground)
rule3 = Rule(type="expression", dxf=diff, formula=['$A2="Presque fini"'])
ws.conditional_formatting.add("A2:F" + str(longueur + 1), rule3)
def create_matrix_tab(
worksheet: Worksheet,
matrix_rows: list,
account_overall_scores: dict,
accounts: dict
) -> Worksheet:
"""
Function to generate the workbook based data already gatered and parsed.
Parameters:
matrix_rows (list): Direct input for the itemized worksheet.
account_overall_scores (dict): Mapping from account id to account overall score
accounts (list): List of accounts.
Returns:
Workbook: The workbook object ready to be saved.
"""
formatting = MatrixTabFormatting()
### Add data ###
# header rows
account_header = []
for account in accounts:
if 'account_name' in account:
account_header.append(account['account_name'])
else:
account_header.append(account['accountId'])
# add header row
worksheet.append(formatting.HEADERS + account_header)
# add account score rows
worksheet.append([formatting.ACCOUNT_SCORE, '', ''] + list(account_overall_scores.values()))
# add requirement rows
rows = sorted(matrix_rows, key=lambda row: row['description']) # sort by description field
for row in rows:
worksheet.append([
row['description'], row['requirementId'], row['severity']
] + row['numFailing'])
if all(score == scores_table.NOT_APPLICABLE for score in row['numFailing']):
worksheet.row_dimensions[worksheet.max_row].hidden = True
# add footer
worksheet.append(['']) # empty row
worksheet.append([f'Scored Against CSS Version: {formatting.version}'])
worksheet.append([f'Report Generated at {datetime.now()} GMT'])
### Apply formatting ###
worksheet.title = formatting.TITLE
# bold headers
for header_cell in worksheet[1][:len(formatting.HEADERS)]:
header_cell.font = Font(bold=True, size=11)
# vertically align account names for readability
for account_name in worksheet[1][len(formatting.HEADERS):]:
account_name.alignment = Alignment(text_rotation=45)
# word wrap long descriptions
for description in worksheet['A']:
description.alignment = Alignment(wrap_text=True)
# freeze first column and first row
worksheet.freeze_panes = formatting.FREEZE
# bold overall scores
overall_score_row = 2
for grade_cell in worksheet[overall_score_row][:worksheet.max_column]:
grade_cell.font = Font(bold=True, size=11)
# right align ACCOUNT_SCORE cell
worksheet[overall_score_row][1].alignment = Alignment(horizontal='right')
# set appropriate font size
for row in worksheet.iter_rows(min_row=overall_score_row + 1):
for cell in row:
cell.font = Font(size=9)
# set Description column width
worksheet.column_dimensions['A'].width = 80
# set other column widths
for col_index in range(len(formatting.HEADERS) + 1, worksheet.max_column + 1):
worksheet.column_dimensions[get_column_letter(col_index)].width = 8
# hide requirement id column
worksheet.column_dimensions['B'].hidden = True
# cell coloring/conditional formatting
# format account scores
colors_ordered_by_weight = list(reversed(sorted(formatting.severity_formatting.values(), key=lambda severity: severity['weight'])))
for account_score in worksheet[overall_score_row][len(formatting.HEADERS):]:
try:
score = int(account_score.value)
except: # pylint: disable=bare-except
score = 0
account_score.number_format = '0'
# colors in reverse order by weight so first one encountered is correct
for color in colors_ordered_by_weight:
if score >= color['weight']:
account_score.fill = PatternFill(start_color=color['fill'], end_color=color['fill'], fill_type='solid')
account_score.font = Font(color=color['font_color'], bold=True)
break
# add conditional formatting for error scores
score_cell_range = '{}3:{}{:d}'.format(
get_column_letter(len(formatting.HEADERS) + 1),
get_column_letter(worksheet.max_column),
len(matrix_rows) + 2
)
score_cell_top_left = '{}3'.format(get_column_letter(len(formatting.HEADERS) + 1))
for error_format in formatting.error_formatting:
# convert python string to excel string
if isinstance(error_format['value'], str):
check_value = formatting.excel_string(error_format['value'])
else:
check_value = error_format['value']
worksheet.conditional_formatting.add(
score_cell_range,
Rule(
type='expression',
formula=[f'{check_value}={score_cell_top_left}'],
priority=worksheet.conditional_formatting.max_priority + 1,
stopIfTrue=True,
dxf=DifferentialStyle(
font=Font(
color=error_format['font_color']
),
fill=PatternFill(
start_color=error_format['fill'],
end_color=error_format['fill'],
fill_type='solid',
)
)
)
)
severity_column_reference = '${}3'.format(get_column_letter(formatting.SEVERITY_COLUMN))
for severity, severity_format in formatting.severity_formatting.items():
# convert python string to excel string
check_value = formatting.excel_string(severity)
worksheet.conditional_formatting.add(
score_cell_range,
Rule(
type='expression',
formula=[f'{check_value}={severity_column_reference}'],
priority=worksheet.conditional_formatting.max_priority + 1,
stopIfTrue=True,
dxf=DifferentialStyle(
font=Font(
color=severity_format['font_color']
),
fill=PatternFill(
start_color=severity_format['fill'],
end_color=severity_format['fill'],
fill_type='solid',
)
)
)
)
return worksheet
# Let's create a style template for the header row
header = NamedStyle(name="header")
header.font = Font(bold=True)
header.border = Border(bottom=Side(border_style="thin"))
header.alignment = Alignment(horizontal="center", vertical="center")
# Now let's apply this to all first row (header) cells
header_row = sheet[1]
for cell in header_row:
cell.style = header
workbook.save(filename="sample_styles.xlsx")
red_background = PatternFill(bgColor=colors.RED)
diff_style = DifferentialStyle(fill=red_background)
rule = Rule(type="expression", dxf=diff_style)
rule.formula = ["$H1<3"]
sheet.conditional_formatting.add("A1:O100", rule)
workbook.save("sample_conditional_formatting.xlsx")
color_scale_rule = ColorScaleRule(start_type="min",start_color=colors.RED,end_type="max",end_color=colors.GREEN)
# Again, let's add this gradient to the star ratings, column "H"
sheet.conditional_formatting.add("H2:H100", color_scale_rule)
workbook.save(filename="sample_conditional_formatting_color_scale.xlsx")
color_scale_rule = ColorScaleRule(start_type="num",start_value=1,start_color=colors.RED,
mid_type="num",mid_value=3,mid_color=colors.YELLOW,end_type="num",
end_value=5,end_color=colors.GREEN)
# Again, let's add this gradient to the star ratings, column "H"
kiirando_fajl = "balance.xlsx"
logging.info('A kiírandó file: ' + kiirando_fajl)
arfolyam_file = "arfolyamok.csv"
logging.info('A használt árfolyamok file: ' + arfolyam_file)
arfolyam = {}
egyenleg2 = ""
# xml fájl betöltése
mydoc = minidom.parse(olvasando_fajl)
# Munkalap stílusának beálítása forintra
still = NamedStyle(name="Pénzecske") # Ilyen nevű stílus hozzáadása
still.number_format = '#,##0 "HUF";-#,##0 "HUF"' # A számformátum beállítása forintra
diff_style = DifferentialStyle(fill=PatternFill(
bgColor='C6EFCE', fgColor='006100')) # A feltételes formázás beállítása
rule = Rule(type="expression", dxf=diff_style) # Feltételes kifejezés megadása
rule.formula = ["$B2>0"] # Formula a feltételes formázáshoz
# Árfolyam file meglétének ellenőrzése és frissítése ha két óránál régebbi
fileido = datetime.fromtimestamp(os.stat(
'./arfolyamok.csv').st_ctime) # a meglévő file idejének lekérdezése
now = datetime.now()
max_delay = timedelta(hours=2)
try:
if now - fileido > max_delay:
shutil.copyfile('arfolyamok.csv', 'arfolyamok.old')
logging.debug(
"Régi a fájl ezért lekérdezem az árfolyamokat: {} ".format(
fileido))
c = CurrencyRates() # Aktuális árfolyam lekérdezése
arfolyam = c.get_rates('HUF') # átszámítás forint vs. valutákra
import os
import re
from fuzzywuzzy import fuzz
from operator import itemgetter, add
from openpyxl import worksheet
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, colors
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule
import collections
full_path = os.path.abspath(os.getcwd())
# file_name = r"C:\Users\willlee\Desktop\Manufacturing Issue\2020\Test Station Part Number.xlsx"
red_background = PatternFill(bgColor=colors.BLUE)
diff_style = DifferentialStyle(fill=red_background)
rule = Rule(type="expression", dxf=diff_style)
rule.formula = ["istext($AA2)"] # <---need to change to dynamic
def text_splitter(text, regex_expression, take_which_group=1):
matches = re.finditer(regex_expression, text, re.MULTILINE)
for matchNum, match in enumerate(matches, start=1):
print("Match {matchNum} was found at {start}-{end}: {match}".format(
matchNum=matchNum,
start=match.start(),
end=match.end(),
match=match.group()))
for groupNum in range(0, len(match.groups())):
def create_spreadsheet(base_channel_report):
# Setup Excel file
filename = "server_os_report.xlsx"
workbook = Workbook()
ws1 = workbook.create_sheet("Sheet_A")
ws1.title = "Overview Linux OS"
ws2 = workbook.create_sheet("Sheet_B")
ws2.title = "Data"
ws3 = workbook.create_sheet("Sheet_C")
ws3.title = "Approver Breakdown"
sheet = workbook["Data"]
for row in base_channel_report:
sheet.append(row)
darkyellow_background = PatternFill(bgColor=colors.DARKYELLOW)
yellow_background = PatternFill(bgColor=colors.YELLOW)
blue_background = PatternFill(bgColor=colors.BLUE)
green_background = PatternFill(bgColor=colors.GREEN)
diff_style7 = DifferentialStyle(fill=darkyellow_background)
rule7 = Rule(type="expression", dxf=diff_style7)
rule7.formula = ["$C1=7"]
sheet.conditional_formatting.add("A1:E600", rule7)
diff_style8 = DifferentialStyle(fill=blue_background)
rule8 = Rule(type="expression", dxf=diff_style8)
rule8.formula = ["$C1=7"]
sheet.conditional_formatting.add("A1:E600", rule8)
diff_style6 = DifferentialStyle(fill=yellow_background)
rule6 = Rule(type="expression", dxf=diff_style6)
rule6.formula = ["$C1=6"]
sheet.conditional_formatting.add("A1:E600", rule6)
diff_style5 = DifferentialStyle(fill=green_background)
rule5 = Rule(type="expression", dxf=diff_style5)
rule5.formula = ["$C1=5"]
sheet.conditional_formatting.add("A1:E600", rule5)
sheet = workbook["Overview Linux OS"]
data = [
['Centos5', '=COUNTIFS(Data!$C$2:$C$600,5, Data!$B$2:$B$600,"Centos")'],
['Centos6', '=COUNTIFS(Data!$C$2:$C$600,6, Data!$B$2:$B$600,"Centos")'],
['Centos7', '=COUNTIFS(Data!$C$2:$C$600,7, Data!$B$2:$B$600,"Centos")'],
['Centos8', '=COUNTIFS(Data!$C$2:$C$600,8, Data!$B$2:$B$600,"Centos")'],
['RedHat5', '=COUNTIFS(Data!$C$2:$C$600,5, Data!$B$2:$B$600,"Redhat")'],
['RedHat6', '=COUNTIFS(Data!$C$2:$C$600,6, Data!$B$2:$B$600,"Redhat")'],
['RedHat7', '=COUNTIFS(Data!$C$2:$C$600,7, Data!$B$2:$B$600,"Redhat")'],
['RedHat8', '=COUNTIFS(Data!$C$2:$C$600,8, Data!$B$2:$B$600,"Redhat")'],
['Unknown', '=COUNTIFS(Data!$C$2:$C$600,0)']
]
for row in data:
sheet.append(row)
pie = PieChart()
labels = Reference(sheet, min_col=1, min_row=2, max_row=9)
data = Reference(sheet, min_col=2, min_row=1, max_row=9)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "OS Breakdown"
pie.height = 20
pie.width = 40
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
sheet.add_chart(pie, "A1")
std=workbook.get_sheet_by_name('Sheet')
workbook.remove_sheet(std)
unique_cost_center = set(x for l in base_channel_report for x in l)
ws3 = workbook.create_sheet("Sheet_C")
ws3.title = "Cost Center Breakdown"
sheet = workbook["Cost Centre Breakdown"]
data =[]
for x in unique_cost_center:
countifs = "=COUNTIFS(Data!$H$2:$H$600,%s)" % x
data.append([x,countifs])
for row in data:
sheet.append(row)
pie = PieChart()
labels = Reference(sheet, min_col=1, min_row=2, max_row=len(data))
data = Reference(sheet, min_col=2, min_row=1, max_row=len(data))
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Cost Center Breakdown"
pie.height = 20
pie.width = 40
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
sheet.add_chart(pie, "A1")
# save file
workbook.save(filename)
datetime.fromtimestamp(random.uniform(from_date, to_date))
for i in range(0, count))
dates.sort()
for i in range(0, count):
float = random.random() * 1000
date = dates[i]
yield DemoObject("Object %d" % (i + 1),
"This is a text which\n will wrap by default",
random.choice((True, False)), float, float,
random.randint(1, 3), date, date, date)
bold_true = Rule(type='expression',
dxf=DifferentialStyle(font=Font(bold=True)),
formula=["$C3"])
class ColumnDemoSheet(TableSheet):
table_name = "ColumnDemo"
char = CharColumn(header="CharColumn")
text = TextColumn(header="TextColumn", freeze=True)
boolean = BoolColumn(header="BoolColumn",
row_style="Row, integer",
conditional_formatting=bold_true)
integer = IntColumn(header="IntColumn", group=True)
float = FloatColumn(header="FloatColumn", group=True)
datetime = DatetimeColumn(header="DatetimeColumn", group=True)
date = DateColumn(header="DateColumn")
def _xlsx_create_rpm_details(self, wb, rpm_table):
df = self._rename_rpm_detail_columns(rpm_table)
ws_rd = wb.create_sheet("RPMs details")
(
normal_font,
normal_border,
normal_fill,
) = self._style.get_rpm_xslx_table_normal()
normal_align = Alignment(horizontal="left",
vertical="top",
wrap_text=True)
center_align = Alignment(horizontal="center",
vertical="center",
wrap_text=True)
(
imt_font,
imt_border,
imt_fill,
) = self._style.get_rpm_xslx_table_important_failed()
(
ctc_font,
ctc_border,
ctc_fill,
) = self._style.get_rpm_xslx_table_critical_failed()
ctc_style = self._get_critical_failed_style()
imt_style = self._get_important_failed_style()
(
header_font,
header_border,
header_fill,
) = self._style.get_rpm_xslx_table_header()
cols = df.columns
xlsx_cols = list(string.ascii_lowercase[0:len(cols)])
for i in range(len(xlsx_cols)):
cell_no = xlsx_cols[i] + "1"
ws_rd[cell_no] = cols[i]
ws_rd[cell_no].font = header_font
ws_rd[cell_no].fill = header_fill
ws_rd[cell_no].border = header_border
ws_rd.column_dimensions["J"].hidden = True
curr_row_no = 2
supported_col_no = 0
for i in range(len(cols)):
if cols[i] == "Driver Flag: Supported":
supported_col_no = i
break
vld_lic = self._style.get_valid_licenses()
for i, row in df.iterrows():
rpm_license = row["License"]
for col_idx in range(len(cols)):
cell_no = xlsx_cols[col_idx] + str(curr_row_no)
val = row[cols[col_idx]]
ws_rd[cell_no].font = normal_font
ws_rd[cell_no].fill = normal_fill
ws_rd[cell_no].border = normal_border
ws_rd[cell_no].alignment = normal_align
if cols[col_idx] == "Symbols Check":
val = self._get_sym_check_failed(val)
if val == "":
val = "All passed!"
ws_rd[cell_no] = val
ws_rd[cell_no].alignment = center_align
else:
ws_rd[cell_no] = val
elif cols[col_idx] == "Driver Flag: Supported":
val = "All passed!"
ws_rd[cell_no] = val
ws_rd[cell_no].alignment = center_align
elif cols[col_idx] == "License":
lcs_chk = self._fmt_driver_license_check(
rpm_license, row["Driver Licenses"], vld_lic)
if lcs_chk == "":
if rpm_license == "":
ws_rd[cell_no] = "No License"
ws_rd[cell_no].font = imt_font
ws_rd[cell_no].fill = imt_fill
ws_rd[cell_no].border = imt_border
else:
ws_rd[cell_no] = rpm_license
else:
ws_rd[cell_no] = lcs_chk
ws_rd[cell_no].font = imt_font
ws_rd[cell_no].fill = imt_fill
ws_rd[cell_no].border = imt_border
else:
ws_rd[cell_no] = str(val)
failed_drivers = self._get_supported_driver_failed(
row["Driver Flag: Supported"])
driver_count = len(failed_drivers)
if driver_count > 0: # format supported information
for sp_idx in range(driver_count):
cell_no = xlsx_cols[supported_col_no] + str(curr_row_no +
sp_idx)
ws_rd[cell_no] = failed_drivers[sp_idx]
ws_rd[cell_no].font = ctc_font
ws_rd[cell_no].fill = ctc_fill
ws_rd[cell_no].border = ctc_border
ws_rd[cell_no].alignment = normal_align
if driver_count > 1: # need merge cell
for col_idx in range(len(cols)):
if col_idx != supported_col_no:
start_cell_no = xlsx_cols[col_idx] + str(curr_row_no)
end_cell_no = xlsx_cols[col_idx] + str(curr_row_no +
driver_count -
1)
merge_range = start_cell_no + ":" + end_cell_no
ws_rd.merge_cells(merge_range)
if driver_count == 0:
curr_row_no += 1
else:
curr_row_no += driver_count
records = curr_row_no
empty_vendor = Rule(type="expression", dxf=imt_style)
empty_vendor.formula = ['$C2 = ""']
ws_rd.conditional_formatting.add(f"C2:C{records}", empty_vendor)
sf_rule = Rule(type="expression", dxf=ctc_style)
sf_rule.formula = [
'=OR($H2="All passed!", AND(ISNUMBER(FIND(":", $H2)), ISNUMBER(FIND("external", $H2))))'
]
ws_rd.conditional_formatting.add(f"H2:H{records}", sf_rule)
sig_rule = Rule(type="expression", dxf=ctc_style)
sig_rule.formula = ['=AND($D2 <> "", $D2 <> "(none)")']
ws_rd.conditional_formatting.add(f"D2:D{records}", sig_rule)
sym_rule = Rule(type="expression", dxf=ctc_style)
sym_rule.formula = ['=ISNUMBER(FIND(".ko", $I2))']
ws_rd.conditional_formatting.add(f"I2:I{records}", sym_rule)
def _xlsx_create_vendor_summary(self, wb, rpm_table):
ws_vs = wb.create_sheet("vendor summary")
sm_table = self._get_summary_table(rpm_table)
for row in dataframe_to_rows(sm_table, index=False, header=True):
ws_vs.append(row)
(
header_font,
header_border,
header_fill,
) = self._style.get_rpm_xslx_table_header()
for cell in ws_vs[1]:
cell.font = header_font
cell.border = header_border
cell.fill = header_fill
for row in ws_vs[f"A1:G{len(sm_table.index)+1}"]:
for cell in row:
cell.border = header_border
last_record_row_no = len(sm_table.index) + 1
(
great_font,
great_border,
great_fill,
) = self._style.get_rpm_xslx_table_great_row()
great_row_style = DifferentialStyle(
font=great_font,
border=great_border,
fill=great_fill,
)
great_row = Rule(type="expression", dxf=great_row_style)
great_row.formula = [
'AND($A2 <> "", VALUE(LEFT($C2, FIND(" ",$C2)-1))=$B2, VALUE(LEFT($D2, FIND(" ", $D2) - 1)) = $B2, VALUE(LEFT($E2, FIND(" ", $E2) - 1)) = $B2, VALUE(LEFT($F2, FIND(" ", $F2)-1))=$B2, VALUE(LEFT($G2, FIND(" ", $G2) - 1))=0)'
]
ws_vs.conditional_formatting.add(f"A2:G{last_record_row_no}",
great_row)
ipt_style = self._get_important_failed_style()
ctc_style = self._get_critical_failed_style()
empty_vendor = Rule(type="expression", dxf=ipt_style)
empty_vendor.formula = ['$A2 = ""']
ws_vs.conditional_formatting.add(f"A2:A{last_record_row_no}",
empty_vendor)
supported_failed = Rule(type="expression", dxf=ctc_style)
supported_failed.formula = [
'VALUE(LEFT($C2, FIND(" ", $C2) - 1)) <> $B2'
]
ws_vs.conditional_formatting.add(f"C2:C{last_record_row_no}",
supported_failed)
license_check = Rule(type="expression", dxf=ipt_style)
license_check.formula = ['VALUE(LEFT($D2, FIND(" ", $D2) - 1)) <> $B2']
ws_vs.conditional_formatting.add(f"D2:D{last_record_row_no}",
license_check)
sig_check = Rule(type="expression", dxf=ipt_style)
sig_check.formula = ['VALUE(LEFT($E2, FIND(" ", $E2) - 1)) <> $B2']
ws_vs.conditional_formatting.add(f"E2:E{last_record_row_no}",
sig_check)
wm_check = Rule(type="expression", dxf=ipt_style)
wm_check.formula = ['VALUE(LEFT($F2, FIND(" ", $F2) - 1)) <> $B2']
ws_vs.conditional_formatting.add(f"F2:F{last_record_row_no}", wm_check)
sym_failed = Rule(type="expression", dxf=ctc_style)
sym_failed.formula = ['VALUE(LEFT($G2, FIND(" ", $G2) - 1)) <> 0']
ws_vs.conditional_formatting.add(f"G2:G{last_record_row_no}",
sym_failed)
def placing_excel(master_data_one, master_data_two):
'''
function that places all information into the summary dashboard sheet
:param master_data_one: python dictionary of latest ar data.
:param master_data_two: python dictionary of last ar data.
:return: populated Excel dashboard.
'''
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=2).value
print(project_name)
if project_name in master_data_one.projects:
dca_one = master_data_one.data[project_name]['DCA']
try:
dca_two = master_data_two.data[project_name]['DCA']
change = up_or_down(dca_one, dca_two)
ws.cell(row=row_num, column=4).value = change
except KeyError:
ws.cell(row=row_num, column=4).value = 'NEW'
ws.cell(row=row_num,
column=5).value = master_data_one.data[project_name]['DCA']
start_date_one = master_data_one.data[project_name]['Start Date']
ws.cell(row=row_num, column=6).value = start_date_one
try:
start_date_two = master_data_two.data[project_name][
'Start Date']
s_date_diff = cal_date_difference(start_date_one,
start_date_two)
ws.cell(row=row_num, column=7).value = s_date_diff
except KeyError:
ws.cell(row=row_num, column=7).value = 0
end_date_one = master_data_one.data[project_name]['End Date']
ws.cell(row=row_num, column=8).value = end_date_one
try:
end_date_two = master_data_two.data[project_name]['End Date']
e_date_diff = cal_date_difference(end_date_one, end_date_two)
ws.cell(row=row_num, column=9).value = e_date_diff
except KeyError:
ws.cell(row=row_num, column=9).value = 0
ws.cell(
row=row_num, column=10
).value = master_data_one.data[project_name]['in year baseline']
ws.cell(
row=row_num, column=11
).value = master_data_one.data[project_name]['in year forecast']
ws.cell(
row=row_num, column=12
).value = master_data_one.data[project_name]['in year variance']
wlc_one = master_data_one.data[project_name]['WLC baseline']
ws.cell(row=row_num, column=13).value = wlc_one
try:
wlc_two = master_data_two.data[project_name]['WLC baseline']
wlc_diff = wlc_one - wlc_two
ws.cell(row=row_num, column=14).value = wlc_diff
except KeyError:
ws.cell(row=row_num, column=14).value = 0
except TypeError:
ws.cell(row=row_num, column=14).value = 'Check wlc value/data'
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=2).value
if project_name in master_data_two.data:
ws.cell(row=row_num,
column=3).value = master_data_two[project_name]['DCA']
# Highlight cells that contain RAG text, with background and text the same colour. column E.
ag_text = Font(color="00a5b700")
ag_fill = PatternFill(bgColor="00a5b700")
dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber/Green",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber/Green",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
ar_text = Font(color="00f97b31")
ar_fill = PatternFill(bgColor="00f97b31")
dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber/Red",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber/Red",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
red_text = Font(color="00fc2525")
red_fill = PatternFill(bgColor="00fc2525")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Red",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Red",E1)))']
ws.conditional_formatting.add('E1:E100', rule)
green_text = Font(color="0017960c")
green_fill = PatternFill(bgColor="0017960c")
dxf = DifferentialStyle(font=green_text, fill=green_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Green",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Green",e1)))']
ws.conditional_formatting.add('E1:E100', rule)
amber_text = Font(color="00fce553")
amber_fill = PatternFill(bgColor="00fce553")
dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
# Highlight cells that contain RAG text, with background and black text columns G to L.
ag_text = Font(color="000000")
ag_fill = PatternFill(bgColor="00a5b700")
dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber/Green",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber/Green",G1)))']
ws.conditional_formatting.add('G1:L100', rule)
ar_text = Font(color="000000")
ar_fill = PatternFill(bgColor="00f97b31")
dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber/Red",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber/Red",G1)))']
ws.conditional_formatting.add('G1:L100', rule)
red_text = Font(color="000000")
red_fill = PatternFill(bgColor="00fc2525")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Red",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Red",G1)))']
ws.conditional_formatting.add('G1:L100', rule)
green_text = Font(color="000000")
green_fill = PatternFill(bgColor="0017960c")
dxf = DifferentialStyle(font=green_text, fill=green_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Green",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Green",G1)))']
ws.conditional_formatting.add('G1:L100', rule)
amber_text = Font(color="000000")
amber_fill = PatternFill(bgColor="00fce553")
dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
rule = Rule(type="containsText",
operator="containsText",
text="Amber",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("Amber",G1)))']
ws.conditional_formatting.add('G1:L100', rule)
# highlighting new projects
red_text = Font(color="00fc2525")
white_fill = PatternFill(bgColor="000000")
dxf = DifferentialStyle(font=red_text, fill=white_fill)
rule = Rule(type="containsText",
operator="containsText",
text="NEW",
dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("NEW",F1)))']
ws.conditional_formatting.add('D1:D100', rule)
# assign the icon set to a rule
first = FormatObject(type='num', val=-1)
second = FormatObject(type='num', val=0)
third = FormatObject(type='num', val=1)
iconset = IconSet(iconSet='3Arrows',
cfvo=[first, second, third],
percent=None,
reverse=None)
rule = Rule(type='iconSet', iconSet=iconset)
ws.conditional_formatting.add('D1:D100', rule)
return wb
def placing_excel(dict_one, dict_two):
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=3).value
print(project_name)
if project_name in dict_one:
ws.cell(row=row_num, column=4).value = dict_one[project_name]['Total Forecast']
ws.cell(row=row_num, column=6).value = dict_one[project_name]['Change']
ws.cell(row=row_num, column=7).value = convert_rag_text(dict_one[project_name]['Departmental DCA'])
ws.cell(row=row_num, column=8).value = convert_rag_text(dict_one[project_name]['GMPP - IPA DCA last quarter'])
ws.cell(row=row_num, column=9).value = convert_bc_stage_text(dict_one[project_name]['BICC approval point'])
ws.cell(row=row_num, column=10).value = dict_one[project_name]['Start of Operation']
ws.cell(row=row_num, column=11).value = dict_one[project_name]['Project End Date']
ws.cell(row=row_num, column=12).value = convert_rag_text(dict_one[project_name]['SRO Finance confidence'])
ws.cell(row=row_num, column=13).value = dict_one[project_name]['Last time at BICC']
ws.cell(row=row_num, column=14).value = dict_one[project_name]['Next at BICC']
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=3).value
if project_name in dict_two:
ws.cell(row=row_num, column=5).value = convert_rag_text(dict_two[project_name]['Departmental DCA'])
# Highlight cells that contain RAG text, with background and text the same colour. column E.
ag_text = Font(color="00a5b700")
ag_fill = PatternFill(bgColor="00a5b700")
dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
rule = Rule(type="containsText", operator="containsText", text="A/G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/G",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
ar_text = Font(color="00f97b31")
ar_fill = PatternFill(bgColor="00f97b31")
dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
rule = Rule(type="containsText", operator="containsText", text="A/R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/R",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
red_text = Font(color="00fc2525")
red_fill = PatternFill(bgColor="00fc2525")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText", operator="containsText", text="R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("R",E1)))']
ws.conditional_formatting.add('E1:E100', rule)
green_text = Font(color="0017960c")
green_fill = PatternFill(bgColor="0017960c")
dxf = DifferentialStyle(font=green_text, fill=green_fill)
rule = Rule(type="containsText", operator="containsText", text="G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("G",e1)))']
ws.conditional_formatting.add('E1:E100', rule)
amber_text = Font(color="00fce553")
amber_fill = PatternFill(bgColor="00fce553")
dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
rule = Rule(type="containsText", operator="containsText", text="A", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A",e1)))']
ws.conditional_formatting.add('e1:e100', rule)
# highlight cells in column g
ag_text = Font(color="000000")
ag_fill = PatternFill(bgColor="00a5b700")
dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
rule = Rule(type="containsText", operator="containsText", text="A/G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/G",g1)))']
ws.conditional_formatting.add('g1:g100', rule)
ar_text = Font(color="000000")
ar_fill = PatternFill(bgColor="00f97b31")
dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
rule = Rule(type="containsText", operator="containsText", text="A/R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/R",g1)))']
ws.conditional_formatting.add('g1:g100', rule)
red_text = Font(color="000000")
red_fill = PatternFill(bgColor="00fc2525")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText", operator="containsText", text="R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("R",g1)))']
ws.conditional_formatting.add('g1:g100', rule)
green_text = Font(color="000000")
green_fill = PatternFill(bgColor="0017960c")
dxf = DifferentialStyle(font=green_text, fill=green_fill)
rule = Rule(type="containsText", operator="containsText", text="G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("G",g1)))']
ws.conditional_formatting.add('g1:g100', rule)
amber_text = Font(color="000000")
amber_fill = PatternFill(bgColor="00fce553")
dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
rule = Rule(type="containsText", operator="containsText", text="A", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A",g1)))']
ws.conditional_formatting.add('g1:g100', rule)
# highlight cells in column H
ag_text = Font(color="000000")
ag_fill = PatternFill(bgColor="00a5b700")
dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
rule = Rule(type="containsText", operator="containsText", text="A/G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/G",h1)))']
ws.conditional_formatting.add('h1:h100', rule)
ar_text = Font(color="000000")
ar_fill = PatternFill(bgColor="00f97b31")
dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
rule = Rule(type="containsText", operator="containsText", text="A/R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/R",h1)))']
ws.conditional_formatting.add('h1:h100', rule)
red_text = Font(color="000000")
red_fill = PatternFill(bgColor="00fc2525")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText", operator="containsText", text="R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("R",h1)))']
ws.conditional_formatting.add('h1:h100', rule)
green_text = Font(color="000000")
green_fill = PatternFill(bgColor="0017960c")
dxf = DifferentialStyle(font=green_text, fill=green_fill)
rule = Rule(type="containsText", operator="containsText", text="G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("G",h1)))']
ws.conditional_formatting.add('h1:h100', rule)
amber_text = Font(color="000000")
amber_fill = PatternFill(bgColor="00fce553")
dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
rule = Rule(type="containsText", operator="containsText", text="A", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A",h1)))']
ws.conditional_formatting.add('h1:h100', rule)
# highlight cells in column H
ag_text = Font(color="000000")
ag_fill = PatternFill(bgColor="00a5b700")
dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
rule = Rule(type="containsText", operator="containsText", text="A/G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/G",l1)))']
ws.conditional_formatting.add('l1:l100', rule)
ar_text = Font(color="000000")
ar_fill = PatternFill(bgColor="00f97b31")
dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
rule = Rule(type="containsText", operator="containsText", text="A/R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A/R",l1)))']
ws.conditional_formatting.add('l1:l100', rule)
red_text = Font(color="000000")
red_fill = PatternFill(bgColor="00fc2525")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText", operator="containsText", text="R", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("R",l1)))']
ws.conditional_formatting.add('l1:l100', rule)
green_text = Font(color="000000")
green_fill = PatternFill(bgColor="0017960c")
dxf = DifferentialStyle(font=green_text, fill=green_fill)
rule = Rule(type="containsText", operator="containsText", text="G", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("G",l1)))']
ws.conditional_formatting.add('l1:l100', rule)
amber_text = Font(color="000000")
amber_fill = PatternFill(bgColor="00fce553")
dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
rule = Rule(type="containsText", operator="containsText", text="A", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("A",l1)))']
ws.conditional_formatting.add('l1:l100', rule)
# Highlight cells that contain RAG text, with background and black text columns G to L.
# ag_text = Font(color="000000")
# ag_fill = PatternFill(bgColor="00a5b700")
# dxf = DifferentialStyle(font=ag_text, fill=ag_fill)
# rule = Rule(type="uniqueValues", operator="equal", text="A/G", dxf=dxf)
# rule.formula = ['NOT(ISERROR(SEARCH("A/G",G1)))']
# ws.conditional_formatting.add('G1:L100', rule)
#
# ar_text = Font(color="000000")
# ar_fill = PatternFill(bgColor="00f97b31")
# dxf = DifferentialStyle(font=ar_text, fill=ar_fill)
# rule = Rule(type="uniqueValues", operator="equal", text="A/R", dxf=dxf)
# rule.formula = ['NOT(ISERROR(SEARCH("A/R",G1)))']
# ws.conditional_formatting.add('G1:L100', rule)
#
# red_text = Font(color="000000")
# red_fill = PatternFill(bgColor="00fc2525")
# dxf = DifferentialStyle(font=red_text, fill=red_fill)
# rule = Rule(type="uniqueValues", operator="equal", text="R", dxf=dxf)
# rule.formula = ['NOT(ISERROR(SEARCH("R",G1)))']
# ws.conditional_formatting.add('G1:L100', rule)
#
# green_text = Font(color="000000")
# green_fill = PatternFill(bgColor="0017960c")
# dxf = DifferentialStyle(font=green_text, fill=green_fill)
# rule = Rule(type="uniqueValues", operator="equal", text="G", dxf=dxf)
# rule.formula = ['NOT(ISERROR(SEARCH("Green",G1)))']
# ws.conditional_formatting.add('G1:L100', rule)
#
# amber_text = Font(color="000000")
# amber_fill = PatternFill(bgColor="00fce553")
# dxf = DifferentialStyle(font=amber_text, fill=amber_fill)
# rule = Rule(type="uniqueValues", operator="equal", text="A", dxf=dxf)
# rule.formula = ['NOT(ISERROR(SEARCH("A",G1)))']
# ws.conditional_formatting.add('G1:L100', rule)
# highlighting new projects
red_text = Font(color="00fc2525")
white_fill = PatternFill(bgColor="000000")
dxf = DifferentialStyle(font=red_text, fill=white_fill)
rule = Rule(type="uniqueValues", operator="equal", text="NEW", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("NEW",F1)))']
ws.conditional_formatting.add('F1:F100', rule)
# assign the icon set to a rule
first = FormatObject(type='num', val=-1)
second = FormatObject(type='num', val=0)
third = FormatObject(type='num', val=1)
iconset = IconSet(iconSet='3Arrows', cfvo=[first, second, third], percent=None, reverse=None)
rule = Rule(type='iconSet', iconSet=iconset)
ws.conditional_formatting.add('F1:F100', rule)
# change text in last at next at BICC column
for row_num in range(2, ws.max_row + 1):
if ws.cell(row=row_num, column=13).value == '-2 weeks':
ws.cell(row=row_num, column=13).value = 'Last BICC'
if ws.cell(row=row_num, column=13).value == '2 weeks':
ws.cell(row=row_num, column=13).value = 'Next BICC'
if ws.cell(row=row_num, column=13).value == 'Today':
ws.cell(row=row_num, column=13).value = 'This BICC'
if ws.cell(row=row_num, column=14).value == '-2 weeks':
ws.cell(row=row_num, column=14).value = 'Last BICC'
if ws.cell(row=row_num, column=14).value == '2 weeks':
ws.cell(row=row_num, column=14).value = 'Next BICC'
if ws.cell(row=row_num, column=14).value == 'Today':
ws.cell(row=row_num, column=14).value = 'This BICC'
# highlight text in bold
ft = Font(bold=True)
for row_num in range(2, ws.max_row + 1):
lis = ['This week', 'Next week', 'Last week', 'Two weeks',
'Two weeks ago', 'This mth', 'Last mth', 'Next mth',
'2 mths', '3 mths', '-2 mths', '-3 mths', '-2 weeks',
'Today', 'Last BICC', 'Next BICC', 'This BICC',
'Later this mth']
if ws.cell(row=row_num, column=10).value in lis:
ws.cell(row=row_num, column=10).font = ft
if ws.cell(row=row_num, column=11).value in lis:
ws.cell(row=row_num, column=11).font = ft
if ws.cell(row=row_num, column=13).value in lis:
ws.cell(row=row_num, column=13).font = ft
if ws.cell(row=row_num, column=14).value in lis:
ws.cell(row=row_num, column=14).font = ft
return wb
def export_excel(_path, _df=None, _wb=None, _sheet_name='sheet1', _letter_fmt=None, _append=False, _frz='B2', _auto_flt=True, _auto_sz=False, _header_height=None, _col_width_=[20,20], _header_fmt=None, _header_rot=0, _zoom=100, _heatmap=0, _is_index=True, _index_name='Index', _header_txtcol='000000', _header_fillcol='d9f2f8', _img=None):
import openpyxl as px
from openpyxl.utils import get_column_letter
time_start = time.perf_counter()
if _path==None and _wb==None:
print('[Error] Both of _path and _bw are None.')
return
print('[Exporting Excel file ...] Sheet : "{0}"'.format(_sheet_name))
#-------------------------------------------
# 初期設定
#-------------------------------------------
# Workbook作成
if _wb == None:
if _append: # 既存ファイルにシート追加
try:
wb = px.load_workbook(_path)
except:
_append = False # ファイルが存在しないときは新規作成
if not _append: # 新規ファイル
wb = px.Workbook()
else:
wb = _wb
_append = True
# Worksheet作成
ws = wb.create_sheet(title=_sheet_name)
#-------------------------------------------
# DataFrameをWorksheetに書き込み
#-------------------------------------------
if _df is not None:
#----- 作業用にDataFrameをコピー -----
df = _df.copy()
# Timestampを文字列に変換（そのままだとエラーになるので）
list_timestamp_col = list()
# Timestampのセルが存在する列を探して文字列に変換する
for col_name, col in df.iteritems():
for item in col:
tp = type(item)
if tp is pd._libs.tslibs.timestamps.Timestamp:
list_timestamp_col.append(col_name)
break
for col in list_timestamp_col:
df[col] = df[col].astype(str)
df[col] = df[col].replace('NaT', '')
#----- Excelファイル用フォーマットの作成 -----
base_font = '游ゴシック'
from openpyxl.styles.fonts import Font
from openpyxl.styles import PatternFill
font_header_row = Font(name=base_font, b=True, sz=10, color=_header_txtcol)
font_header_col = Font(name=base_font, b=True, sz=10, color=_header_txtcol)
font_cell = Font(name=base_font, sz=10)
align_header_row = px.styles.Alignment(horizontal="center", vertical="center", wrapText=True, textRotation=_header_rot)
align_header_col = px.styles.Alignment(horizontal="center", vertical="center", wrapText=True)
fill_header_row = PatternFill(patternType='solid', fgColor=_header_fillcol)
fill_header_col = PatternFill(patternType='solid', fgColor=_header_fillcol)
#----- データ出力 -----
# DataFrameをWorksheetにExport
l = df.columns.tolist()
if _is_index:
l.insert(0, _index_name) # 行のindexを先頭列に追加
ws.append(l)
count = 0
for i, row in df.iterrows(): # 一行ずつwsに追加していく
l = row.values.tolist()
if _is_index:
l.insert(0, row.name) # 行のindexを先頭列に追加
ws.append(l)
count += 1
print('\r - データコピー {0}/{1}'.format(count, len(df)), end="")
print('')
#----- Worksheetの書式設定 -----
# ヘッダー行(既定値)
for cell in list(ws.rows)[0]:
cell.font = font_header_row
cell.alignment = align_header_row
cell.fill = fill_header_row
# ヘッダー行(個別)
if _header_fmt != None:
list_cell = list(ws.rows)[0]
for head, fmt in _header_fmt.items():
try:
index = list(df.columns).index(head)
if _is_index:
index += 1
cell = list_cell[index]
except:
continue
# rotation
try:
rotation = fmt['rotation']
cell.alignment = px.styles.Alignment(horizontal="center", vertical="center", wrapText=True, textRotation=rotation)
except:
pass
# 文字色
try:
text_color = fmt['txtcol']
cell.font = Font(name=base_font, b=True, sz=10, color=text_color)
except:
pass
# 背景色
try:
fill_color = fmt['fillcol']
cell.fill = PatternFill(patternType='solid', fgColor=fill_color)
except:
pass
# 列ごとの書式設定用のリスト作成
list_dtxt_pat = list()
list_dfill_pat = list()
if _header_fmt != None:
for head, fmt in _header_fmt.items():
try:
index = list(df.columns).index(head)
if _is_index:
index += 1
except:
continue
# 文字色
try:
text_color = fmt['dtxtcol']
list_dtxt_pat.append([index, Font(name=base_font, sz=10, color=text_color)])
except:
pass
# 背景色
try:
dfill_color = fmt['dfillcol']
list_dfill_pat.append([index, PatternFill(patternType='solid', fgColor=dfill_color)])
except:
pass
# データ行書式設定
count = 0
for row in ws.iter_rows(min_row=2):
# 書式設定
for cell in row:
cell.font = font_cell
# 列ごとの書式設定で上書き
for list_pat in list_dtxt_pat: # 個別設定がある列を順に処理する
idx = list_pat[0]
row[idx].font = list_pat[1]
for list_pat in list_dfill_pat: # 個別設定がある列を順に処理する
idx = list_pat[0]
row[idx].fill = list_pat[1]
# Index列がある場合はIndex用設定
if _is_index:
row[0].font = font_header_col # 先頭列のみ太字
row[0].alignment = align_header_col # 先頭列のみセンタリング
row[0].fill = fill_header_col # 先頭列の塗りつぶし
count += 1
print('\r - 書式設定 {0}/{1}'.format(count, len(df)), end="")
print('')
#----- セルの文字書式 -----
if type(_letter_fmt) is dict: # _header_fmtがあれば不要だが互換性のために残してある
for col in ws.iter_cols():
col_name = col[0].value
if col_name in _letter_fmt:
num_format = _letter_fmt[col_name]
for cell in col:
cell.number_format = num_format
elif type(_letter_fmt) is str:
for col in ws.iter_cols():
for cell in col:
cell.number_format = _letter_fmt
# 列ごとの個別設定で上書き
if _header_fmt != None:
list_col = list(_header_fmt.keys())
for col in ws.iter_cols():
col_name = col[0].value
if col_name in list_col: # 列書式一覧の辞書にこの列が存在する
try:
fmt = _header_fmt[col_name]
num_format = fmt['dtxtformat']
for cell in col:
cell.number_format = num_format
except:
pass
# Worksheetの列幅調整
if _auto_sz: # 自動調整
for col in ws.columns:
max_length = 0
column = col[0].column
column = get_column_letter(column) # 数字をアルファベットに変換
cols = col if _header_rot!=90 else col[1:]
for cell in cols:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
adjusted_width = (max_length + 2) * 1.1
ws.column_dimensions[column].width = adjusted_width
else:
for col in ws.columns:
column = col[0].column # 列番号を取得
col_letter = get_column_letter(column) # 列番号を列記号に変換
width = _col_width_[0] if column == 1 else _col_width_[1] # 列幅
ws.column_dimensions[col_letter].width = width
# 列ごとの個別調整
if _header_fmt != None:
list_col = list(ws.columns)
for head, fmt in _header_fmt.items():
try:
width = fmt['width']
index = list(df.columns).index(head)
if _is_index:
index += 1
col = list_col[index]
column = col[0].column # 列番号を取得
col_letter = get_column_letter(column) # 列番号を列記号に変換
ws.column_dimensions[col_letter].width = width
except:
pass
# Worksheetの行の高さ調整
if _header_height != None:
ws.row_dimensions[1].height = _header_height
# ヒートマップ
from openpyxl.formatting.rule import ColorScale, FormatObject
from openpyxl.styles import Color
if _heatmap == 1: # 赤 → 白 → 青
first = FormatObject(type='min')
last = FormatObject(type='max')
# colors match the format objects:
colors = [Color('F8696B'), Color('5A8AC6')]
# a three color scale would extend the sequences
mid = FormatObject(type='percentile', val=50)
colors.insert(1, Color('FCFCFF'))
cs3 = ColorScale(cfvo=[first, mid, last], color=colors)
# create a rule with the color scale
from openpyxl.formatting.rule import Rule
rule = Rule(type='colorScale', colorScale=cs3)
# 対象範囲を示す文字列を作成
rg = 'A2:' + get_column_letter(ws.max_column)+str(ws.max_row)
ws.conditional_formatting.add(rg, rule)
elif _heatmap == 2: # 白 → 橙 → 赤
first = FormatObject(type='min')
last = FormatObject(type='max')
# colors match the format objects:
colors = [Color('FFFFFF'), Color('F8696B')]
# a three color scale would extend the sequences
mid = FormatObject(type='percentile', val=50)
colors.insert(1, Color('FFEB84'))
cs3 = ColorScale(cfvo=[first, mid, last], color=colors)
# create a rule with the color scale
from openpyxl.formatting.rule import Rule
rule = Rule(type='colorScale', colorScale=cs3)
# 対象範囲を示す文字列を作成
rg = 'A2:' + get_column_letter(ws.max_column)+str(ws.max_row)
ws.conditional_formatting.add(rg, rule)
elif _heatmap == 3: # 赤 → 橙 → 白
first = FormatObject(type='min')
last = FormatObject(type='max')
# colors match the format objects:
colors = [Color('F8696B'), Color('FFFFFF')]
# a three color scale would extend the sequences
mid = FormatObject(type='percentile', val=25)
colors.insert(1, Color('FFEB84'))
cs3 = ColorScale(cfvo=[first, mid, last], color=colors)
# create a rule with the color scale
from openpyxl.formatting.rule import Rule
rule = Rule(type='colorScale', colorScale=cs3)
# 対象範囲を示す文字列を作成
rg = 'A2:' + get_column_letter(ws.max_column)+str(ws.max_row)
ws.conditional_formatting.add(rg, rule)
# 枠固定
if _frz != None:
ws.freeze_panes = _frz
# オートフィルタ
if _auto_flt:
ws.auto_filter.ref = 'A1:' + get_column_letter(ws.max_column)+'1'
# 表示倍率
ws.sheet_view.zoomScale = _zoom
#-------------------------------------------
# Worksheetに画像を挿入
#-------------------------------------------
if _img != None:
from openpyxl.drawing.image import Image
for img in _img:
fpath = img[0] # 挿入する画像ファイル
anchor = img[1] # 挿入位置
px_img = Image(fpath)
px_img.anchor = anchor
ws.add_image(px_img)
#-------------------------------------------
# Excelファイルに書き込み
#-------------------------------------------
# 最後に不要なシートを削除
if 'Sheet' in wb.sheetnames:
wb.remove(wb['Sheet'])
print(' - ファイル書き込み...', end='')
wb.save(_path)
# 画像ファイル削除
if _img != None:
for img in _img:
is_delete = False # ファイルを削除するか否か
if len(img) > 2:
is_delete = img[2]
if is_delete: # ファイル削除
os.remove(fpath)
print ('\n ---> Finished. (処理時間:{0:.3f}[sec])'.format(time.perf_counter() - time_start ))
return wb
def submit(self):
## Create workbook and sheets
global g_w
wb = openpyxl.Workbook()
sheet0 = wb.create_sheet(index=0, title='Read_Me')
sheet1 = wb.create_sheet(index=1, title='2019_2020')
## Create Read me sheet
sheet0[
'B2'].value = 'Hey all. This excel file is the result of a python script'
sheet0['B4'].value = 'The script uses the FPL API and json data to import your history from the ' \
'website and then exports it to this file '
sheet0[
'B6'].value = 'See more info at my site https://www.fezfiles.com/fpl-data-fetcher. Report bugs, contact at [email protected] or Twitter: https://twitter.com/fez9o'
sheet0[
'B8'].value = 'be aware that the code is raw and a lof of improvements can be made.'
sheet0[
'B10'].value = 'Your data is in the next sheet. Change sheet tabs below or hold "CTRL+PgDown"'
## Import history JSON data
url1 = 'https://fantasy.premierleague.com/api/entry/{}/history/'.format(
self.fpl_prompt.get())
url2 = 'https://fantasy.premierleague.com/api/bootstrap-static/'
json_history = requests.get(url1).json()
json_live = requests.get(url2).json()
num_of_gw = len(json_history['current'])
participants = json_live['total_players']
## Import league data and team name from new url due to change in FPL api since 2019-2020 Season
url9 = 'https://fantasy.premierleague.com/api/entry/{}/'.format(
self.fpl_prompt.get())
json_info = requests.get(url9).json()
team_name = json_info['name']
## Import gameweek history and insert data in sheet
header1 = [
'GW', 'GP', 'GW AVG', 'GW HS', 'PB', 'TM', 'TC', 'GR', 'PGR', 'OP',
'OR', 'POR', 'Position', 'TV'
]
headerrow = 1
for key in range(14):
sheet1.cell(row=headerrow,
column=key + 3).value = str(header1[key])
o_r1 = [
] # To make a list of overall rank for inserting change in rank symbols
for each in json_history['current']:
g_w = each['event']
points = each['points']
p_b = each['points_on_bench']
t_m = each['event_transfers']
t_c = each['event_transfers_cost']
g_w_r = each['rank']
o_r = each['overall_rank']
t_v = each['value']
o_r1.append(
o_r) # This is for creating rank symbols in the excel sheet
o_p = each['total_points']
p_g_r = 100 - (((participants - g_w_r) / participants) * 100)
p_o_r = 100 - (((participants - o_r) / participants) * 100)
p_o_s = 1 # placeholder which will be replaced later down the code
history_list = [
g_w, points, p_b, t_m, t_c, g_w_r, p_g_r, o_p, o_r, p_o_r,
p_o_s, t_v / 10
]
for rownum in range(g_w + 1, g_w + 2):
sheet1.cell(row=rownum, column=3).value = g_w
for rownum in range(g_w + 1, g_w + 2):
sheet1.cell(row=rownum, column=4).value = points
for rownum in range(g_w + 1, g_w + 2):
for key in range(2, 12):
sheet1.cell(row=rownum,
column=key + 5).value = history_list[key]
## Appending overall rank changes to a list so it becomes easier to make symbols in the table
p_o_s_1 = []
p_o_s_1.append(0)
for count in range(1, g_w):
if o_r1[count - 1] > o_r1[count]:
p_o_s = 1
elif o_r1[count - 1] == o_r1[count]:
p_o_s = 0
else:
p_o_s = -1
p_o_s_1.append(p_o_s)
for each in json_history['current']:
g_w = each['event']
sheet1.cell(row=g_w + 1, column=15).value = p_o_s_1[g_w - 1]
## Import gameweek average points and highest points
for each in json_live['events']:
g_w = each['id']
h_p = each['highest_score']
a_v_g = each['average_entry_score']
for rownum in range(g_w + 1, g_w + 2):
sheet1.cell(row=rownum, column=5).value = a_v_g
for rownum in range(g_w + 1, g_w + 2):
sheet1.cell(row=rownum, column=6).value = h_p
## Import all football players' data in premier league
player_d = {}
for each in json_live['elements']:
pl_position = each['element_type']
pl_id = each['id']
pl_name = each['web_name']
player_d[pl_id] = pl_name
tot_player = len(player_d)
## Select data for Chip usage and enter in GW history as highlights
wildcardfill = PatternFill(start_color='ffff0000',
end_color='ffff0000',
fill_type='solid')
freehitfill = PatternFill(start_color='ffff00ff',
end_color='ffff00ff',
fill_type='solid')
bboostfill = PatternFill(start_color='ffffa500',
end_color='ffffa500',
fill_type='solid')
triplecapfill = PatternFill(start_color='ff0099ff',
end_color='ff0099ff',
fill_type='solid')
gwh_col = range(3, 17)
for each in json_history['chips']:
chipgw = each['event']
chip = each['name']
while chip == 'wildcard':
for key in gwh_col:
wc = sheet1.cell(row=chipgw + 1, column=key)
wc.fill = wildcardfill
break
while chip == 'bboost':
for key in gwh_col:
wc = sheet1.cell(row=chipgw + 1, column=key)
wc.fill = bboostfill
break
while chip == 'freehit':
for key in gwh_col:
wc = sheet1.cell(row=chipgw + 1, column=key)
wc.fill = freehitfill
break
while chip == '3xc':
for key in gwh_col:
wc = sheet1.cell(row=chipgw + 1, column=key)
wc.fill = triplecapfill
break
## Import weekly team player data and points
gwteamheaderow = 50 #41+9
gwtitle = 3
for gw in range(1, 48): #39+9
sheet1.cell(row=gwteamheaderow,
column=gwtitle).value = str('GW {}'.format(gw))
sheet1.cell(row=gwteamheaderow,
column=gwtitle + 1).value = str('P {}'.format(gw))
gwtitle = gwtitle + 2
capfill = PatternFill(start_color='ff15dd43',
end_color='ff15dd43',
fill_type='solid')
vcapfill = PatternFill(start_color='ff00FFDA',
end_color='ff00FFDA',
fill_type='solid')
benchfill = PatternFill(start_color='ffBA6B12',
end_color='ffBA6B12',
fill_type='solid')
for rownum in range(62, 66): # 62 to 66
for colnum in range(3, 97): # 79+18 to 97
bench = sheet1.cell(row=rownum, column=colnum)
bench.fill = benchfill
gwteamcol = (
(39 + 9) * 2 - int(num_of_gw * 2)
) - 1 # To accomodate people who started late. Don't ask how I came up with this formula.
capfont = Font(underline='single')
# gwteamcol = 1
for each in json_history['current']:
g_w = each['event']
url3 = 'https://fantasy.premierleague.com/api/entry/{}/event/{}/picks/'.format(
self.fpl_prompt.get(), g_w)
json_pick = requests.get(url3).json()
gwteamcol = gwteamcol + 2
gwteamrow = 42 + 9
url4 = 'https://fantasy.premierleague.com/api/event/{}/live/'.format(
g_w)
json_points = requests.get(url4).json()
total_players = len(json_points['elements'])
for each1 in json_pick['picks']:
player_id = each1['element']
player_idnew = int(player_id)
captain = each1['is_captain']
vicecapt = each1['is_vice_captain']
multiplier = each1['multiplier']
pl_name = player_d[player_id]
plist = {player_id: pl_name}
# Assigning points to selected players
for each2 in json_points['elements']:
pointsvid = each2['id']
for t1 in range(0, total_players):
if player_idnew == json_points['elements'][t1]['id']:
pl_points = json_points['elements'][t1]['stats'][
'total_points']
sheet1.cell(row=gwteamrow,
column=gwteamcol + 1).value = pl_points
if multiplier == 2:
sheet1.cell(row=gwteamrow,
column=gwteamcol +
1).value = pl_points * 2
elif multiplier == 3:
sheet1.cell(row=gwteamrow,
column=gwteamcol +
1).value = pl_points * 3
break
# Assigning captaincy and vice-captaincy
for values in plist.values():
sheet1.cell(row=gwteamrow, column=gwteamcol).value = values
if captain == True:
capf = sheet1.cell(row=gwteamrow, column=gwteamcol)
capf.fill = capfill
capf.font = capfont
if vicecapt == True:
vcapf = sheet1.cell(row=gwteamrow, column=gwteamcol)
vcapf.fill = vcapfill
gwteamrow = gwteamrow + 1
## Import classic league history
sheet1.merge_cells('BR1:BS1') # League Rank header
sheet1['BR1'].value = 'League Rank History'
clrow = 2
num_of_leagues = len(json_info['leagues']['classic'])
clheader = ['League Name', 'Rank']
for leaguecolumn in range(2):
sheet1.cell(row=clrow, column=leaguecolumn + 70).value = str(
clheader[leaguecolumn])
for each in json_info['leagues']['classic']:
leaguename = each['name']
leagueposition = each['entry_rank']
league_data = [leaguename, leagueposition]
clrow = clrow + 1
for clcol in range(2):
sheet1.cell(row=clrow,
column=clcol + 70).value = league_data[clcol]
## Import Cup History
sheet1.merge_cells('BV1:BZ1') # Cup History Header
sheet1['BV1'].value = 'FPL Cup History'
url8 = 'https://fantasy.premierleague.com/api/entry/{}/cup/'.format(
self.fpl_prompt.get())
json_cup = requests.get(url8).json()
num_of_cups = len(json_cup['cup_matches'])
cuplist = ['GW', 'Team Name 1', 'Points 1', 'Team Name 2', 'Points 2 ']
cuprow = 2
for col in range(5):
sheet1.cell(row=cuprow, column=col + 74).value = str(cuplist[col])
if num_of_cups > 0:
for each in json_cup['cup_matches']:
cupgw = each['event']
entry_1 = each['entry_1_name']
entry_2 = each['entry_2_name']
entrypoints_1 = each['entry_1_points']
entrypoints_2 = each['entry_2_points']
cup_data = [
cupgw, entry_1, entrypoints_1, entry_2, entrypoints_2
]
cuprow = cuprow + 1
for colnum in range(5):
sheet1.cell(row=cuprow,
column=colnum + 74).value = cup_data[colnum]
else:
sheet1.cell(row=3,
column=74).value = "Failed to qualify for the cup"
## Import h2h details
sheet1.merge_cells('BN1:BO1') # H2H Team Header
sheet1['BN1'].value = 'H2H History'
num_of_h2h = len(json_info['leagues']['h2h'])
h2h_header = ['H2H League', 'Rank']
h2hrow = 2
for h2hcol in range(2):
sheet1.cell(row=h2hrow,
column=h2hcol + 66).value = str(h2h_header[h2hcol])
if num_of_h2h > 0:
for each in json_history['leagues']['h2h']:
h2hname = each['name']
h2hrank = each['entry_rank']
h2h_data = [h2hname, h2hrank]
h2hrow = h2hrow + 1
for colnum in range(2):
sheet1.cell(row=h2hrow,
column=colnum + 66).value = h2h_data[colnum]
else:
sheet1.cell(row=3, column=66).value = "No H2H leagues entered." \
\
## Import Gameweek Transfer history
sheet1.merge_cells('CV1:CZ1')
sheet1['CV1'].value = 'Transfer History'
transferheader = [
'GW', 'Transfer In', 'Value In ', 'Transfer Out', 'Value Out'
]
transferhrow = 2
for tkey in range(5):
sheet1.cell(row=transferhrow, column=tkey + 100).value = str(
transferheader[tkey]) #82+18
url5 = 'https://fantasy.premierleague.com/api/entry/{}/transfers/'.format(
self.fpl_prompt.get())
json_transfer = requests.get(url5).json()
gwtransferrow = 2
gwtransfercol = 82 + 18
num_of_t = len(json_transfer)
if num_of_t == 0:
sheet1.cell(row=gwtransferrow + 1,
column=gwtransfercol).value = "No Transfers Made"
else:
for each in json_transfer:
transferin = each['element_in']
transferout = each['element_out']
incost = each['element_in_cost']
outcost = each['element_out_cost']
transfergw = each['event']
t_in_name = player_d.get(transferin, 0)
t_out_name = player_d.get(transferout, 0)
trans_data = [
transfergw, t_in_name, incost / 10, t_out_name,
outcost / 10
]
gwtransferrow = gwtransferrow + 1
for colnum in range(5):
sheet1.cell(row=gwtransferrow,
column=colnum +
gwtransfercol).value = trans_data[colnum]
## Import Overall Dream Team Data
sheet1.merge_cells('BI1:BJ1') # Dream Team Header
sheet1['BI1'].value = str('Overall Dream Team')
overalldtheader = ['Player Name', 'Total Points']
for odtcol in range(2):
sheet1.cell(row=2, column=odtcol + 61).value = str(
overalldtheader[odtcol])
url6 = 'https://fantasy.premierleague.com/api/dream-team/'
json_dreamteam = requests.get(url6).json()
dtrow = 2
for each in json_dreamteam['team']:
dtpoints = each['points']
dtplayer = each['element']
dt_name = player_d.get(dtplayer, 0)
dt_data = [dt_name, dtpoints]
dtrow = dtrow + 1
for colnum in range(2):
sheet1.cell(row=dtrow,
column=colnum + 61).value = dt_data[colnum]
## Import Weekly Dream Team Data
dtteamheaderrow = 59 + 9
dttitle = 3
for dt in range(1, 39 + 9):
sheet1.cell(row=dtteamheaderrow,
column=dttitle).value = str('GW {}'.format(dt))
sheet1.cell(row=dtteamheaderrow,
column=dttitle + 1).value = str('P {}'.format(dt))
dttitle = dttitle + 2
dtteamcol = 1
for each in range(
1,
39 + 9): ## CHANGE THIS BACK TO 39 TO ACOMMODATE ALL GAMEWEEKS
url7 = 'https://fantasy.premierleague.com/api/dream-team/{}/'.format(
each)
json_weeklydt = requests.get(url7).json()
# print(json_weeklydt)
dtteamrow = 69 #60+9
dtteamcol = dtteamcol + 2
for each1 in json_weeklydt['team']:
dtpl_id = each1['element']
dt_points = each1['points']
pl_name = player_d[dtpl_id]
dtlist = {pl_name: dt_points}
for values in dtlist.values():
sheet1.cell(row=dtteamrow,
column=dtteamcol + 1).value = values
for values2 in dtlist.keys():
sheet1.cell(row=dtteamrow,
column=dtteamcol).value = values2
dtteamrow = dtteamrow + 1
## Creating Legend
legendlist = [
'Legend', 'Wildcard', 'Benchboost', 'Triple Captain', 'Free Hit',
'Captain', 'Vice Captain', 'Bench'
]
legendrow = 5
for lkey in range(8):
sheet1.cell(row=lkey + legendrow,
column=1).value = legendlist[lkey]
lewc = sheet1.cell(row=legendrow + 1, column=1)
lewc.fill = wildcardfill
lebb = sheet1.cell(row=legendrow + 2, column=1)
lebb.fill = bboostfill
letc = sheet1.cell(row=legendrow + 3, column=1)
letc.fill = triplecapfill
lefh = sheet1.cell(row=legendrow + 4, column=1)
lefh.fill = freehitfill
leca = sheet1.cell(row=legendrow + 5, column=1)
leca.fill = capfill
levca = sheet1.cell(row=legendrow + 6, column=1)
levca.fill = vcapfill
leben = sheet1.cell(row=legendrow + 7, column=1)
leben.fill = benchfill
## Creating Team name and FPL ID
sheet1['A2'].value = 'FPL ID: {}'.format(self.fpl_prompt.get())
sheet1['A1'].value = 'Team: {}'.format(team_name)
sheet1['A3'].value = 'Players: {}'.format(participants)
## Cell Styling
headerfont = Font(bold=True)
alignment = Alignment(horizontal='center')
for key in range(74,
79): # FPL CUP 'GW/Team Name/Points/Team Name/Points'
row2 = sheet1.cell(row=2, column=key)
row2.font = headerfont
row2.alignment = alignment
for row in range(1, num_of_cups + 3): # FPL CUP History details
for col in range(74, 79):
cup1 = sheet1.cell(row=row, column=col)
cup1.alignment = alignment
cup2 = sheet1.cell(row=1, column=74) # 'FPL CUP History'
cup2.font = headerfont
cup2.alignment = alignment
for key in range(3, 17): # 'GW/GP/GW AVG/GW HS/PB/......'
row1 = sheet1.cell(row=1, column=key)
row1.font = headerfont
row1.alignment = alignment
for key in range(3, 97): # GW Teams 'GW1/P1/GW2/P2.....' 79+18, 41+9
row41 = sheet1.cell(row=50, column=key)
row41.font = headerfont
row41.alignment = alignment
for key1 in range(2, 49): # GW history full table 40+9
for key2 in range(3, 10):
set1 = sheet1.cell(row=key1, column=key2)
set1.alignment = alignment
for key1 in range(51, 66): # Team history full table 42+9, 57+9, 79+18
for key2 in range(3, 97):
set2 = sheet1.cell(row=key1, column=key2)
set2.alignment = alignment
for key in range(2, 49): # GW history table value format 40+9
col1 = sheet1.cell(row=key, column=11) # Percentile GW Rank
col1.number_format = '0.00000'
col1.alignment = alignment
col2 = sheet1.cell(row=key, column=14) # Percentile Overall Rank
col2.number_format = '0.00000'
col2.alignment = alignment
col3 = sheet1.cell(row=key, column=16) # Team Value
col3.number_format = '0.0'
col3.alignment = alignment
col4 = sheet1.cell(row=key, column=12) # Overall Points
col4.alignment = alignment
col5 = sheet1.cell(row=key, column=10) # Gameweek Rank
col5.number_format = '#,##0'
col5.alignment = alignment
col6 = sheet1.cell(row=key, column=13) # Overall Rank
col6.number_format = '#,##0'
col6.alignment = alignment
col4 = sheet1.cell(row=key, column=15) # Position
col4.alignment = alignment
for key1 in range(1,
3 + num_of_t): # Transfer history table 82+18, 88+18
for key2 in range(100, 106):
set3 = sheet1.cell(row=key1, column=key2)
set3.alignment = alignment
for key in range(100, 106):
set4 = sheet1.cell(row=1, column=key)
set4.alignment = alignment
set4.font = headerfont
for key in range(100,
106): # Transfer history 'GW/Transfer in/Value....'
row1 = sheet1.cell(row=1, column=key)
row1.font = headerfont
for key1 in range(2, num_of_t + 1): # Transfer table value In 84+18
col7 = sheet1.cell(row=key1, column=102)
col7.number_format = '0.0'
for key1 in range(2, num_of_t + 1): # Transfer table value Out 86+18
col7 = sheet1.cell(row=key1, column=104)
col7.number_format = '0.0'
for col8 in range(70, 73): # League Rank table
lrh = sheet1.cell(row=1, column=col8)
lrh.font = headerfont
for row8 in range(2, num_of_leagues + 3):
lr = sheet1.cell(row=row8, column=col8)
lr.alignment = alignment
lr1 = sheet1.cell(row=1, column=70) # League Rank title
lr1.font = headerfont
lr1.alignment = alignment
for col9 in range(
3, 97): # Gameweek Dream Team full table and title 79+18, 59+9
gwdt = sheet1.cell(row=68, column=col9)
gwdt.font = headerfont
for row9 in range(68, 80): # 59+9, 71+9
gwdtt = sheet1.cell(row=row9, column=col9)
gwdtt.alignment = alignment
for col10 in range(61, 63): # Overall dream team table and titles
odt = sheet1.cell(row=1, column=col10)
odt.font = headerfont
odt.alignment = alignment
for col10 in range(61, 63):
for row10 in range(2, 14):
odtt = sheet1.cell(row=row10, column=col10)
odtt.alignment = alignment
for col10 in range(61, 63):
odt = sheet1.cell(row=2, column=col10)
odt.font = headerfont
for col11 in range(66, 68): # Head2Head table and titles
h2ht = sheet1.cell(row=1, column=col11)
h2ht.font = headerfont
h2ht.alignment = alignment
for col11 in range(66, 68):
for row11 in range(2, num_of_h2h + 3):
h2htt = sheet1.cell(row=row11, column=col11)
h2htt.alignment = alignment
for col11 in range(66, 68):
h2ht = sheet1.cell(row=2, column=col11)
h2ht.font = headerfont
## Creating Position symbols for GW history p_o_s using p_o_s_1
first = FormatObject(type='num', val=-1)
second = FormatObject(type='num', val=0)
third = FormatObject(type='num', val=1)
iconset = IconSet(iconSet='3Arrows',
cfvo=[first, second, third],
showValue=None,
percent=None,
reverse=None)
rule = Rule(type='iconSet', iconSet=iconset)
sheet1.conditional_formatting.add('O2:O48', cfRule=rule) #39+9
## Creating Tables
table1 = Table(displayName='GWH', ref='C1:P48') # GW History 39+9
style1 = TableStyleInfo(name="TableStyleMedium11", showRowStripes=True)
table1.tableStyleInfo = style1
sheet1.add_table(table1)
table2 = Table(displayName='GWT',
ref='C50:CR65') # Team History 41+9, BZ to CR 56+ 9
style2 = TableStyleInfo(name="TableStyleLight15", showRowStripes=True)
table2.tableStyleInfo = style2
sheet1.add_table(table2)
if num_of_t > 0:
num_of_trow = num_of_t
else:
num_of_trow = 1
table3 = Table(displayName='TH',
ref='CV2:CZ{}'.format(num_of_trow +
2)) # Transfer History
style3 = TableStyleInfo(name="TableStyleMedium12", showRowStripes=True)
table3.tableStyleInfo = style3
sheet1.add_table(table3)
if num_of_cups > 0:
cup_table_row = 2
else:
cup_table_row = 3
table4 = Table(displayName='CH',
ref='BV2:BZ{}'.format(num_of_cups +
cup_table_row)) # FPL Cup history
style4 = TableStyleInfo(name="TableStyleMedium13", showRowStripes=True)
table4.tableStyleInfo = style4
sheet1.add_table(table4)
table5 = Table(displayName='CLR',
ref='BR2:BS{}'.format(num_of_leagues +
2)) # Classic League Rank
style5 = TableStyleInfo(name="TableStyleMedium10", showRowStripes=True)
table5.tableStyleInfo = style5
sheet1.add_table(table5)
if num_of_h2h > 0:
h2h_table_row = 2
else:
h2h_table_row = 3
table6 = Table(displayName='HTOH',
ref='BN2:BO{}'.format(num_of_h2h +
h2h_table_row)) # H2H Rank
style6 = TableStyleInfo(name="TableStyleMedium11", showRowStripes=True)
table6.tableStyleInfo = style6
sheet1.add_table(table6)
table7 = Table(displayName='ODT', ref='BI2:BJ13') # Overall Dream Team
style7 = TableStyleInfo(name="TableStyleMedium7", showRowStripes=True)
table7.tableStyleInfo = style7
sheet1.add_table(table7)
table8 = Table(displayName='GWDT',
ref='C68:CR79') # GW Dream Team 59+9, 70+9
style8 = TableStyleInfo(name="TableStyleLight17", showRowStripes=True)
table8.tableStyleInfo = style8
sheet1.add_table(table8)
## Creating Chart
chart1 = LineChart()
chart1.title = 'Gameweek Points / Average Points / Points Benched / Highest GW Score'
data1 = Reference(sheet1, min_col=4, max_col=7, min_row=1,
max_row=48) # 39+9
chart1.height = 20
chart1.width = 60
chart1.add_data(data1, titles_from_data=True)
sheet1.add_chart(chart1, "T2")
## Save workbook
wb.save("FPL.Data.19-20.{}.xlsx".format(self.fpl_prompt.get()))
## Clear text box and show success dialog
item_path = str(
path.realpath("FPL.Data.19-20.{}.xlxs".format(
self.fpl_prompt.get())))
messagebox.showinfo(title="Success",
message="Data for \'{}\' imported successfully.\n"
"File saved in {}".format(team_name, item_path))
self.clear()
