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 apply_cell_color(p_sheet, p_row, p_col, p_code):
my_blue = colors.Color(rgb='00003096')
col_white = colors.Color(rgb='00FFFFFF')
try:
if p_code == "HEADER":
p_sheet.cell(row=p_row, column=p_col).fill = PatternFill(fgColor=my_blue, patternType='solid')
p_sheet.cell(row=p_row, column=p_col).font = Font(b=True, color=col_white, size=10)
# print(f" size : {len(p_sheet.cell(row=p_row, column=p_col).value)}")
try:
l_size = ( len(p_sheet.cell(row=p_row, column=p_col).value) + 5 )
except:
l_size = 40
# print(f"Warning Unable to get the size. Default to 40")
p_sheet.column_dimensions[get_column_letter(p_col)].width = l_size
except:
print(f"Error in Applying Color[{p_row}:{p_col}]\n Error:{err.format_exc()}")
def red_error(cell):
"""Окрашивание ошибки в красный цвет"""
# from openpyxl.styles import colors
# from openpyxl.styles import Font
cell.value = error_txt
# красный цвет
color_font = colors.Color(rgb='FFFF0000')
cell.font = Font(color=color_font)
class ColorEnum(MultiValueEnum):
"""
顏色代碼aenum
"""
# 外資、投信同步 買或賣超
Yello = 0, PatternFill(fill_type='solid', fgColor=colors.YELLOW)
# 外資、投信不同步 買或賣超
Green = 1, PatternFill(fill_type='solid', fgColor=colors.GREEN)
# 5日內累積買或賣超 2天
Cnt2Days = 2, PatternFill(fill_type='solid',
fgColor=colors.Color(rgb='95E1D3'))
# 5日內累積買或賣超 3天
Cnt3Days = 3, PatternFill(fill_type='solid',
fgColor=colors.Color(rgb='EAFFD0'))
# 5日內累積買或賣超 4天
Cnt4Days = 4, PatternFill(fill_type='solid',
fgColor=colors.Color(rgb='FCE38A'))
# 5日內累積買或賣超 5天
Cnt5Days = 5, PatternFill(fill_type='solid',
fgColor=colors.Color(rgb='F38181'))
def rank_cells(self, rank, quality):
c = Cell(self.ws, column="A", row=1, value=rank)
color = methods[rank]
if quality == 0:
# If only 1 locale consists of translation, make color "Reddish".
color = "FF" + color[2:]
elif rank != "EQUAL_LOWERCASE" and quality >= 1:
# If translation is consistent across 2 or more locales, then GREEN.
color = "00FF00"
elif quality > 1:
color = "00FF00"
# Fill background of cell with color
pf = PatternFill("solid", fgColor=colors.Color(color))
c.fill = pf
return c
def test_font_color(self):
wb = Workbook()
ws = wb.active
a1 = ws['A1']
a1_font = a1.font
default_color = colors.Color(indexed=None,
type='theme',
rgb=None,
tint=0.0,
theme=1,
auto=None)
self.assertEqual(a1_font.color, default_color)
a1.font = Font(color="FF000000")
self.assertEqual(a1.font.color.rgb, "FF000000")
a1 = index.set_font_color_red(wb)
self.assertEqual(a1.font.color.rgb, colors.RED)
def format_excel_sheet(report, ltp_file):
# Create a workbook
workbook = Workbook()
sheet = workbook.active
bold_font = Font(bold=True, color=colors.DARKYELLOW, size=20)
# set the width of the column
sheet.column_dimensions['A'].width = 30
sheet.column_dimensions['B'].width = 40
sheet.column_dimensions['C'].width = 10
sheet['A1'].font = bold_font
sheet.merge_cells('A1:D1')
sheet['A1'] = 'LTP Test report'
my_yellow = colors.Color('e6e600')
my_fill_yellow = fills.PatternFill(patternType='solid',
fgColor=my_yellow)
sheet['A3'] = 'Module'
sheet.cell(row=3, column=1).fill = my_fill_yellow
sheet['B3'] = 'Test Case'
sheet.cell(row=3, column=2).fill = my_fill_yellow
sheet['C3'] = 'Result'
sheet.cell(row=3, column=3).fill = my_fill_yellow
sheet['D3'] = 'Exit Code'
sheet.cell(row=3, column=4).fill = my_fill_yellow
Generator.append_data_into_cells(sheet, report)
filename_ltp = ltp_file.split('\\')
filename_ltp_no_ext = (filename_ltp[-1].split('.'))[0]
output_file = 'l4b-software___testReport' + '___' + filename_ltp_no_ext + '.xlsx'
try:
workbook.save(filename=output_file)
except PermissionError as e:
print("\n\n\n Excel file is open. Please close the excel file !!!")
# See openpyxl.styles.fonts for all elements of Font
font_cell.font = Font(name='Arial',
size=18,
b=True,
i=True,
color=colors.COLOR_INDEX[12])
# openpyxl.styles.borders
borders = Border(left=Side(border_style=BORDER_THIN, color='00000000'),\
right=Side(border_style=BORDER_THICK, color='00000000'),\
top=Side(border_style=BORDER_DASHDOT, color='00000000'),\
bottom=Side(border_style=BORDER_DOUBLE, color='00000000'))
border_cell.border = borders
# openpyxl.styles.fills
red_color = colors.Color(rgb='00FF0000')
solid_red_fill = PatternFill(patternType='solid', fgColor=red_color)
fill_cell.fill = solid_red_fill
# openpyxl.styles.numbers
number_format_cell.number_format = FORMAT_PERCENTAGE_00
# Create a Workbook password and lock the structure
wb2 = Workbook()
wb2.security.workbookPassword = '******'
wb2.security.lockStructure = True
wb2.save("Cell formatting2.xlsx")
# Protect the sheet
ws1.protection.sheet = True
ws1.protection.password = '******'
def to_excel(df:pd.DataFrame, file:Union[str,Path], sheet:str="Sheet",
libreoffice:bool=True, style:str="TableStyleMedium9"):
"""
Save the given DataFrame into an Excel file.
The reason why we do not use pd.DataFrame.to_excel is the style.
Basically we give a style to the table through openpyxl, but
The given style can be ignored on LibreOffice. Therefore we set
the standard colors to the cells if libreoffice option is True.
But in this case, we ignore the option style.
The index will be ignored.
TODO: option whether the index should also be written
:param df: DataFrame
:param file: path to the excel file (xlsx)
:param sheet: name of the sheet for the table
:param libreoffice: arrange style for libreOffice
:param style: name of the style. If libreoffice is True, this is ignored.
"""
if not Path(file).exists():
## if the file hat not been created we choose the default sheet
wb = Workbook()
ws = wb.active
ws.title = sheet
else:
wb = load_workbook(str(file))
if sheet in wb.sheetnames:
## if the specified sheet exits, then we overwrite it.
ws_index = wb.sheetnames.index(sheet)
wb.remove(wb[sheet])
ws = wb.create_sheet(title=sheet, index=ws_index)
else:
## otherwise we create a new sheet.
ws = wb.create_sheet(title=sheet)
## header
ws.append(df.columns.tolist())
## values
for i,row in enumerate(df.iterrows()):
ws.append(row[1].tolist())
## Direct settings for LibreOffice
## We can choose a style of a table through TableStyleInfo,
## but it is not applied in LibreOffice.
if libreoffice:
style = "TableStyleMedium9"
color_header = colors.Color(rgb='4f81bd')
colors_row = ['b8cce4', 'dce6f1']
for i in range(1,df.shape[0]+2):
color = color_header if i == 1 else colors_row[i % 2]
for j in range(1,df.shape[1]+1):
if i == 1:
## header (bold, white)
ws.cell(column=j, row=i).font = Font(bold=True, color="FFFFFF")
ws.cell(column=j, row=i).fill = PatternFill(fgColor=color, fill_type="solid")
## table
## https://openpyxl.readthedocs.io/en/stable/worksheet_tables.html
last_cell = "%s%s" % (ws.cell(row=df.shape[0]+1, column=df.shape[1]).column_letter,
df.shape[0]+1)
tab = Table(displayName=sheet, ref="A1:%s" % last_cell)
style = TableStyleInfo(name=style, showFirstColumn=False, showLastColumn=False,
showRowStripes=True, showColumnStripes=False)
tab.tableStyleInfo = style
ws.add_table(tab)
wb.save(filename=str(file))
sheet["B2"].font = bold_font
sheet["B3"].font = big_red_text
sheet["C4"].alignment = center_aligned_text
sheet["C5"].border = square_border
# work_book.save(filename="styled.xlsx")
sheet["B7"].alignment = center_aligned_text
sheet["B7"].font = big_red_text
sheet["B7"].border = square_border
# work_book.save(filename="styled.xlsx")
custom_style = NamedStyle(name="header")
custom_style.font = Font(bold=True)
custom_style.border = Border(bottom=Side(border_style="thin"))
custom_style.alignment = Alignment(horizontal="center", vertical="center")
header_row = sheet[1]
for cell in header_row:
cell.style = custom_style
# work_book.save(filename="styled.xlsx")
one_more_style = NamedStyle(name="highlight")
one_more_style.fill = PatternFill(fgColor=colors.Color("d7abcc"), patternType="lightHorizontal")
for cell in sheet["A"]:
cell.style = one_more_style
# work_book.save(filename="styled.xlsx")
def append_data_into_cells(worksheet, report):
current_row = 5
current_column = 1
my_red = colors.Color(colors.RED)
my_fill_red = fills.PatternFill(patternType='solid', fgColor=my_red)
my_green = colors.Color(colors.GREEN)
my_fill_green = fills.PatternFill(patternType='solid',
fgColor=my_green)
my_pink = colors.Color(rgb='FF9999')
my_fill_pink = fills.PatternFill(patternType='solid', fgColor=my_pink)
for module in report._listModules:
worksheet.cell(row=current_row,
column=current_column).value = module._name
for tca in module._listTestCases:
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._testCaseName
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._result
if Generator.pass_str in tca._result:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
elif Generator.fail_str in tca._result:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
else:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._exitCode
current_column = 1
current_row += 1
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Summary:'
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.total_tests
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.nrTotalTest
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.skipped_test
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.nrTotalSkipped
current_row += 1
current_column = 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.total_failures
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.nrTotalFailures
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Pass'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.percentagePass
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Fail'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.percentageFail
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Conf'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.percentageConf
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_row -= 2
current_column = 1
# at the end create a chart
chart = DoughnutChart()
labels = Reference(worksheet,
min_col=current_column,
min_row=current_row,
max_row=current_row + 2)
data = Reference(worksheet,
min_col=current_column + 1,
min_row=current_row,
max_row=current_row + 2)
chart.add_data(data)
chart.set_categories(labels)
chart.title = "LTP test results"
# Change bar filling and line color
# serie1= chart.series[0];
# serie1.graphicalProperties.solidFill = "7E3F00"
# serie2 = chart.series[1];
chart.style = 2
worksheet.add_chart(chart, "F3")
def append_data_into_cells(worksheet):
"""append data into the worksheet"""
current_row = 5
current_column = 1
my_red = colors.Color(colors.RED)
my_fill_red = fills.PatternFill(patternType='solid', fgColor=my_red)
my_green = colors.Color(colors.GREEN)
my_fill_green = fills.PatternFill(patternType='solid',
fgColor=my_green)
my_pink = colors.Color(rgb='FF9999')
my_fill_pink = fills.PatternFill(patternType='solid', fgColor=my_pink)
for module in Generator.report_csv._listModules:
for tca in module._listTestCases:
worksheet.cell(row=current_row,
column=current_column).value = tca._module_git
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._testCaseName
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._result
if Generator.pass_str in tca._result:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
elif Generator.fail_str in tca._result:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
else:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_column += 3
worksheet.cell(
row=current_row, column=current_column
).value = f"=VLOOKUP(A{current_row},'ES6 - LTP Test Results'!A:B,2)"
current_column += 1
worksheet.cell(
row=current_row, column=current_column
).value = f'=IF(AND(C{current_row}<>F{current_row},F{current_row}<>"N/A"),"Different","OK")'
# current_column += 1
# worksheet.cell(row=current_row, column=current_column).value = tca._exitCode
current_column = 1
current_row += 1
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Summary:'
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.total_tests
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.nrTotalTest
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.skipped_test
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.nrTotalSkipped
current_row += 1
current_column = 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.total_failures
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.nrTotalFailures
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Pass'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.percentagePass
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Fail'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.percentageFail
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Skipped'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.percentageConf
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_row -= 2
current_column = 1
# at the end create a chart
chart = DoughnutChart()
labels = Reference(worksheet,
min_col=current_column,
min_row=current_row,
max_row=current_row + 2)
data = Reference(worksheet,
min_col=current_column + 1,
min_row=current_row,
max_row=current_row + 2)
# worksheet.auto_filter.ref = 'A5:C1766'
# worksheet.auto_filter.add_sort_condition('A{0}:A{1}'.format(5, 1766))
chart.add_data(data)
chart.set_categories(labels)
chart.title = "LTP test results"
chart.style = 10
worksheet.add_chart(chart, "I8")
def fusionner_classeur(filename):
"""Fusionne les données du classeur Excel (document .xlsx).
La première feuille du tableur doit contenir des noms sur la colonne A.
Chaque autre feuille doit contenir :
- des noms sur la colonne A,
- des notes sur la colonne B.
Ces colonnes ne doivent pas avoir d'entête.
Les autres colonnes sont ignorées.
S'il y a un espace dans la colonne des noms, la lecture des noms s'interrompt.
Une feuille est générée en fin de document, et contient le résultat de la fusion.
"""
if not filename.endswith('.xlsx'):
raise RuntimeError(f"File {filename} does not seem to be a .xlsx file.")
spreadsheet = load_workbook(filename)
scores_nb = []
for num, sheet in enumerate(spreadsheet, start=1):
print(f'Reading {sheet.title!r} sheet...')
# Guess format: one column for name and surname, or two distinct columns.
name_has_2_cols = (isinstance(sheet['B1'].value, str) and sheet['B1'].value != '')
# Detect the table height.
for height, cell in enumerate(sheet['A']):
val = cell.value
if not isinstance(val, str) or val.strip() == '':
break
print(' -', height, 'lines')
if name_has_2_cols:
names = [f'{a[0].value} {b[0].value}' for a, b in zip(sheet[f'A1:A{height}'],
sheet[f'B1:B{height}'])]
else:
names = [cell[0].value for cell in sheet[f'A1:A{height}']]
if num == 1:
fusion = Fusion(names)
# No scores on 1st sheet
continue
scores = []
for j, col in enumerate(sheet.iter_cols(), start=1):
if j == 1 or (j == 2 and name_has_2_cols):
# Skip names columns
continue
# Collect scores in the current column.
vals = [cell.value for cell in col[:height]]
#If a column is empty, disgard all following columns.
if all(val is None for val in vals):
break
scores.append(vals)
print(' -', len(scores), 'column(s) of scores')
scores_nb.append(len(scores))
data = {}
for student, student_scores in zip(names, zip(*scores)):
data[student] = student_scores
fusion.importer(data)
new = spreadsheet.create_sheet('Fusion')
for i, name in enumerate(sorted(fusion.names), start=1):
new[f'A{i}'] = name
# Format for cells that need special attention.
my_red = colors.Color(rgb='00FF1111')
my_fill = fills.PatternFill(patternType='solid', fgColor=my_red)
# Calcul des positions de chaque série de données dans le tableur
# On mémorise une fois pour toute la position de la 1re colonne correspondant
# à chaque série de données, pour éviter de la recalculer ensuite.
positions = []
pos = 2
for n in range(len(fusion.imported)):
positions.append(pos)
pos += scores_nb[n] + 1
# Fusions réussies
for i, name in enumerate(sorted(fusion.names), start=1):
for n, found in enumerate(fusion.imported):
j = positions[n]
if found[name] is not None:
old_name, scores, fiability = found[name]
new.cell(i, j).value = old_name
if fiability >= 2:
new.cell(i, j).fill = my_fill
for k, score in enumerate(scores, start=1):
new.cell(i, j + k).value = score
if fiability >= 2:
new.cell(i, j + k).fill = my_fill
# Éléments pour lesquels la fusion n'a pas fonctionné
i0 = len(fusion.names) + 2
all_merged = True
for n, remaining in enumerate(fusion.not_imported):
j = positions[n]
for i, (old_name, scores) in enumerate(sorted(remaining.items()), start=i0):
all_merged = False
new.cell(i, j).value = old_name
new.cell(i, j).fill = my_fill
for k, score in enumerate(scores, start=1):
new.cell(i, j + k).value = score
new.cell(i, j + k).fill = my_fill
if not all_merged:
new.cell(i0, 1).value = \
"Attention, certaines données n'ont pas pu être fusionnées :"
my_font = fonts.Font(color=my_red, bold=True, italic=True)
new.cell(i0, 1).font = my_font
for i, _ in enumerate(new.iter_cols()):
new.column_dimensions[get_column_letter(i+1)].width = 25
spreadsheet.active = len(spreadsheet.sheetnames) - 1
assert '.' in filename
base, ext = filename.split('.')
spreadsheet.save(f'{base}_output.{ext}')