def addcolor(sheet, totalcolumns, numberRow, firstbound, secondbound,
thirdbound, fourthbound):
sheet.conditional_formatting.add(
'C2:' + get_column_letter(totalcolumns) + str(numberRow),
CellIsRule(operator='greaterThanOrEqual',
formula=firstbound,
stopIfTrue=True,
fill=PurpleFill))
sheet.conditional_formatting.add(
'C2:' + get_column_letter(totalcolumns) + str(numberRow),
CellIsRule(operator='between',
formula=secondbound,
stopIfTrue=True,
fill=RedFill))
sheet.conditional_formatting.add(
'C2:' + get_column_letter(totalcolumns) + str(numberRow),
CellIsRule(operator='between',
formula=thirdbound,
stopIfTrue=True,
fill=YellowFill))
sheet.conditional_formatting.add(
'C2:' + get_column_letter(totalcolumns) + str(numberRow),
CellIsRule(operator='between',
formula=fourthbound,
stopIfTrue=True,
fill=GreenFill))
def conditional_format(ws, cell_loc: str, format_vals: tuple, colors: tuple):
# only multiply if value is not an emptry string
value = ws[cell_loc].value * 1.0 if ws[cell_loc].value else ws[
cell_loc].value
if not isinstance(value, (int, float)):
return
if value < format_vals[0]:
if colors[0]:
ws.conditional_formatting.add(
cell_loc,
CellIsRule(operator='lessThan',
formula=[format_vals[0]],
fill=PatternFill(bgColor=colors[0])))
elif format_vals[0] <= value < format_vals[1]:
if colors[1]:
ws.conditional_formatting.add(
cell_loc,
CellIsRule(operator='lessThan',
formula=[format_vals[1]],
fill=PatternFill(bgColor=colors[1])))
else:
if colors[2]:
ws.conditional_formatting.add(
cell_loc,
CellIsRule(operator='greaterThan',
formula=[format_vals[1]],
fill=PatternFill(bgColor=colors[2])))
def main():
'''Main'''
my_wb = load_workbook(filename='51-SysTstDev-192-SysTstDev-5.xlsx')
print(_col_match(my_wb['Results'], '2:2', 'diff'))
tclm = _col_match(my_wb['Results'], '2:2', 'diff')
thresh_column = tclm + ':' + tclm
for sheet in VERT_ROWS_SHEETS:
try:
set_row_style(my_wb[sheet], '2:2', VERT_STYLE)
except KeyError:
pass
for sheet in my_wb.get_sheet_names():
auto_width(my_wb[sheet])
my_wb['Results'].column_dimensions.group(start='K', end='Q', hidden=True)
my_wb['Results'].conditional_formatting.add(
thresh_column,
CellIsRule(operator='greaterThan',
formula=['5'],
fill=GREEN_FILL,
stopIfTrue=True))
my_wb['Results'].conditional_formatting.add(
thresh_column,
CellIsRule(operator='lessThan',
formula=['-5'],
fill=RED_FILL,
stopIfTrue=True))
my_wb.active = my_wb.index(my_wb['Results']) # TODO Result for report
my_wb.save(filename='51-SysTstDev-192-SysTstDev-5PLUS.xlsx')
def formatResult(ws, row_number, column_result):
ws['{0}'.format(ws.cell(
row=row_number,
column=column_result).coordinate)] = "=({0}+{1}+{2})/3".format(
ws.cell(row=row_number, column=column_result - 5).coordinate,
ws.cell(row=row_number, column=column_result - 3).coordinate,
ws.cell(row=row_number, column=column_result - 2).coordinate)
ws['{0}'.format(
ws.cell(row=row_number,
column=column_result).coordinate)].number_format = '0.###0'
ws.conditional_formatting.add(
'{0}'.format(ws.cell(row=row_number, column=column_result).coordinate),
CellIsRule(operator='equal',
formula=['1'],
stopIfTrue=True,
fill=orangeFill))
ws.conditional_formatting.add(
'{0}'.format(ws.cell(row=row_number, column=column_result).coordinate),
CellIsRule(operator='greaterThan',
formula=['1'],
stopIfTrue=True,
fill=redFill))
ws.conditional_formatting.add(
'{0}'.format(ws.cell(row=row_number, column=column_result).coordinate),
CellIsRule(operator='lessThan',
formula=['1'],
stopIfTrue=True,
fill=greenFill))
def pct_red_green_fmt(ref):
redFill = PatternFill(start_color="EE1111",
end_color="EE1111", fill_type="solid")
greenFill = PatternFill(start_color="007700",
end_color="007700", fill_type="solid")
ws.conditional_formatting.add(ref, CellIsRule(
operator="lessThan", formula=[0], stopIfTrue=False, fill=redFill))
ws.conditional_formatting.add(ref, CellIsRule(
operator="greaterThan", formula=[0], stopIfTrue=False, fill=greenFill))
def test_conditional_formatting(WriteOnlyWorksheet):
from openpyxl.formatting.rule import CellIsRule
ws = WriteOnlyWorksheet
rule = CellIsRule(operator='lessThan', formula=['C$1'], stopIfTrue=True)
ws.conditional_formatting.add("C1:C10", rule)
ws.close()
with open(ws.filename) as src:
xml = src.read()
expected = """
<worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<sheetPr>
<outlinePr summaryRight="1" summaryBelow="1"/>
<pageSetUpPr/>
</sheetPr>
<sheetViews>
<sheetView workbookViewId="0">
<selection sqref="A1" activeCell="A1"/>
</sheetView>
</sheetViews>
<sheetFormatPr baseColWidth="8" defaultRowHeight="15"/>
<sheetData />
<conditionalFormatting sqref="C1:C10">
<cfRule operator="lessThan" priority="1" stopIfTrue="1" type="cellIs">
<formula>C$1</formula>
</cfRule>
</conditionalFormatting>
</worksheet>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def condForm(cell, typ):
Timrapport.conditional_formatting.add(
cell,
CellIsRule(operator='greaterThan',
formula=['0'],
stopIfTrue=False,
fill=typ))
def test_formatting(self, writer):
redFill = PatternFill(
start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType='solid'
)
whiteFont = Font(color=Color("FFFFFFFF"))
ws = writer.ws
ws.conditional_formatting.add('A1:A3',
CellIsRule(operator='equal',
formula=['"Fail"'],
stopIfTrue=False,
font=whiteFont,
fill=redFill)
)
writer.write_formatting()
xml = writer.read()
expected = """
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<conditionalFormatting sqref="A1:A3">
<cfRule operator="equal" priority="1" type="cellIs" dxfId="0" stopIfTrue="0">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
</worksheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_conditional_font(worksheet_with_cf, write_conditional_formatting):
"""Test to verify font style written correctly."""
# Create cf rule
from openpyxl.styles import PatternFill, Font, Color
from openpyxl.formatting.rule import CellIsRule
redFill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType='solid')
whiteFont = Font(color=Color("FFFFFFFF"))
ws = worksheet_with_cf
ws.conditional_formatting.add('A1:A3',
CellIsRule(operator='equal',
formula=['"Fail"'],
stopIfTrue=False,
font=whiteFont,
fill=redFill))
cfs = write_conditional_formatting(ws)
xml = b""
for cf in cfs:
xml += tostring(cf)
diff = compare_xml(xml, """
<conditionalFormatting sqref="A1:A3">
<cfRule operator="equal" priority="1" type="cellIs" dxfId="0" stopIfTrue="0">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def fill_sheet(ws,
num_questions,
num_participants,
range_high,
range_low=1,
copyRowsFromSheet1=False):
# Add headers
ws['A1'] = 'Sub ID'
ws['B1'] = 'Session Date'
ws['C1'] = 'Visit'
ws['D1'] = 'Date Entered'
ws['E1'] = 'Entered By'
for col in range(1, 6 + num_questions):
ws.column_dimensions[get_column_letter(col)].width = "15"
for col in range(6, 6 + num_questions):
question_metadata1 = ws.cell(column=col, row=2)
question_metadata1.font = small_font
question_metadata2 = ws.cell(column=col, row=3)
question_metadata2.font = small_font
if copyRowsFromSheet1:
ws.cell(column=col,
row=1,
value='=Entry1!{0}{1}'.format(get_column_letter(col), 1))
question_metadata1.value = '=Entry1!{0}{1}'.format(
get_column_letter(col), 2)
question_metadata2.value = '=Entry1!{0}{1}'.format(
get_column_letter(col), 3)
else:
ws.cell(column=col, row=1, value="Q{0}".format(col - 5))
ws.cell(column=6 + num_questions, row=1, value="Notes")
# Add subject numbers
for row in range(4, 4 + num_participants):
ws.cell(column=1, row=row, value=(row - 3 + 1000))
# This freezes everything "above and to the left" of the given cell
ws.freeze_panes = "F4"
do_borders(ws, 6 + num_questions, 4 + num_participants)
first_cell = ws.cell(column=6, row=3)
last_cell = ws.cell(column=5 + num_questions, row=2 + num_participants)
# Conditional formatting to do blanks as light gray
cf_blanks(ws, 'F4', last_cell.coordinate)
# Conditional formatting to highlight numbers out of range
if range_high:
yellowFill = PatternFill(start_color='EEEE66',
end_color='EEEE66',
fill_type='solid')
rule = CellIsRule(operator="notBetween",
formula=[str(range_low),
str(range_high)],
fill=yellowFill)
ws.conditional_formatting.add('F4:{0}'.format(last_cell.coordinate),
rule)
return ws
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 to_igschel_hiso(model, name):
input_keys = input_keys_template
if features == 6:
del input_keys[1]
wb = Workbook()
ws = wb.active
for i, x in enumerate(range(features)):
ws.cell(row=i + 1, column=1).value = input_keys[i]
ws.cell(row=i + 1, column=2).value = 1
for x in range(hidden):
cell = chr(ord("A") + x)
ws.cell(row=x + 1, column=4).value = \
"=SUMPRODUCT(B1:B{features}, weights0!{cell}1:{cell}{features}) + weights1!A{idx}" \
.format(cell=cell, features=features, idx=x + 1)
for x in range(outputs):
cell = chr(ord("A") + x)
ws.cell(row=x + 1, column=6).value = 2**x
ws.cell(row=x + 1, column=7).value = \
"=SUMPRODUCT(D1:D{hidden}, weights2!{cell}1:{cell}{hidden}) + weights3!A{idx}" \
.format(cell=cell, hidden=hidden, idx=x + 1)
rule = ColorScaleRule(start_type='percentile',
start_value=40,
start_color='ffb6d7a8',
mid_type='percentile',
mid_value=70,
mid_color='ff9fc5e8',
end_type='percentile',
end_value=95,
end_color='ffea9999')
bold = styles.Font(bold=True)
ws.conditional_formatting.add('D1:D{}'.format(hidden), rule)
ws.conditional_formatting.add('G1:G{}'.format(outputs), rule)
ws.conditional_formatting.add(
'G1:G{}'.format(outputs),
CellIsRule(operator='equal',
formula=['MAX(G$1:G${})'.format(outputs)],
font=bold))
w = model.get_weights()
for y in range(4):
weights = wb.create_sheet("weights{}".format(y))
wy = w[y]
shape = wy.shape
if len(shape) == 2:
for ix, iy in np.ndindex(shape):
weights.cell(row=ix + 1, column=iy + 1).value = float(wy[ix,
iy])
else:
for ix in range(shape[0]):
weights.cell(row=ix + 1, column=1).value = float(wy[ix])
wb.save(name + ".xlsx")
def FormatTable(tablename,maxValues):
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule
maxSiActi = maxValues[0][0]
maxSiWeig = maxValues[0][1]
maxOpGemm = maxValues[1][0]
workbook = load_workbook(filename=tablename)
sheet = workbook['details']#.active
if sheet['A1'].value==None:
sheet.delete_rows(idx=1)
sheet.delete_cols(idx=1)
sheet.freeze_panes = "C2"
# row 0: Grey bkcolor, Bold font
fil = PatternFill("solid", fgColor="00C0C0C0")
ft= Font(b=True)
for cell in list(sheet.rows)[0]:
cell.fill = fil
cell.font = ft
if cell.value=='SizeO':
so=cell.column_letter
if cell.value=='SizeW':
sw=cell.column_letter
if cell.value=='OpGemm':
og=cell.column_letter
# Max activation row with red
background = PatternFill(bgColor="00FF0000")
myrule= CellIsRule(operator='equal', formula=['{}'.format(maxSiActi)], stopIfTrue=True, fill=background)
sheet.conditional_formatting.add(so+'{}:'.format(sheet.min_row)+so+'{}'.format(sheet.max_row), myrule)
# Max activation row with pink
background = PatternFill(bgColor="00FF00FF")
myrule= CellIsRule(operator='equal', formula=['{}'.format(maxSiWeig)], stopIfTrue=True, fill=background)
sheet.conditional_formatting.add(sw+'{}:'.format(sheet.min_row)+sw+'{}'.format(sheet.max_row), myrule)
# Max Ops Gemm row with green
background = PatternFill(bgColor="0000FF00")
myrule= CellIsRule(operator='equal', formula=['{}'.format(maxOpGemm)], stopIfTrue=True, fill=background)
sheet.conditional_formatting.add(og+'{}:'.format(sheet.min_row)+og+'{}'.format(sheet.max_row), myrule)
workbook.save(tablename)
def write_table_to_worksheet(table, worksheet, number_format='0.##',
colour_scale=(0, 100), aggregate=None):
"""
I'll explain the parameters later, can't be buggered atm tbh fam
Yeah okay I guess this is a bit too long in that it does have
too many local variables
"""
column_numbers = {}
row_num = 1
for row, columns in table.items():
row_num += 1
worksheet.cell(row=row_num, column=1).value = row
for column, value in columns.items():
if column in column_numbers:
col_num = column_numbers[column]
else:
col_num = max(column_numbers.values()) + 1 if column_numbers else 2
header = worksheet.cell(row=1, column=col_num)
header.value = column
#I actually want 270, which is perfectly valid in a
#spreadsheet, but openpyxl doesn't think so, and maybe
#I should submit them a bug report for that
#Also I can't autosize rows so I might as well not
#bother doing it in Python
#header.alignment = Alignment(text_rotation=90)
column_numbers[column] = col_num
cell = worksheet.cell(row=row_num, column=col_num)
cell.value = value
cell.number_format = number_format
end_row = row_num
end_col = max(column_numbers.values()) if column_numbers else 1
end_col_letter = worksheet.cell(column=end_col, row=1).column
if aggregate is not None and end_row > 1 and end_col > 1:
for i in range(2, end_row + 1):
aggregate_cell = worksheet.cell(row=i, column=end_col + 1)
aggregate_cell.value = '={0}(B{1}:{2}{1}'.format(aggregate, i, end_col_letter)
aggregate_cell.number_format = number_format
if colour_scale is not None and end_row > 1 and end_col > 1:
rule = ColorScaleRule(
start_type='num', end_type='num',
start_value=colour_scale[0], end_value=colour_scale[1],
start_color='FF0000', end_color='00FF00')
end_cell = worksheet.cell(row=end_row, column=end_col)
address = 'B2:' + end_cell.coordinate
worksheet.conditional_formatting.add(address, rule)
gray_fill = PatternFill(start_color='CCCCCC', end_color='CCCCCC', fill_type='solid')
na_rule = CellIsRule(operator='=', formula=['"N/A"'], fill=gray_fill)
worksheet.conditional_formatting.add(address, na_rule)
def CSVtoExcel(CSV):
Original_CSV = pd.read_csv(CSV)
New_Excel = pd.ExcelWriter(
'C:/Users/adiaz/Desktop/ZoomAttendenceSheet/CSVFile/AttendanceReport_'
+ str(rand.randrange(1000, 9999)) + '.xlsx')
Original_CSV.to_excel(New_Excel, index=False)
New_Excel.save()
wb = load_workbook(New_Excel)
ws = wb['Sheet1']
count = 3
for col_cells in ws.iter_cols(min_row=4, min_col=7, max_col=7):
for cell in col_cells:
count += 1
cell.value = '=E' + str(count) + '/$F$2'
ws['G' + str(count)].style = 'Percent'
ws['G' + str(count)].font = Font(bold=True)
redFill = PatternFill(start_color='EE1111',
end_color='EE1111',
fill_type='solid')
greenFill = PatternFill(start_color='00ff00',
end_color='00ff00',
fill_type='solid')
ws.conditional_formatting.add(
'G4:' + 'G' + str(len(ws['G'])),
CellIsRule('lessThan', formula=['0.7'], stopIfTrue=True, fill=redFill))
ws.conditional_formatting.add(
'G4:' + 'G' + str(len(ws['G'])),
CellIsRule('greaterThan',
formula=['0.7'],
stopIfTrue=True,
fill=greenFill))
wb.save(New_Excel)
return max(glob.iglob('CSVFile/*.xlsx'), key=os.path.getctime)
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 format_cell_style():
red_fill = PatternFill(start_color='EE1111',
end_color='EE1111',
fill_type='solid')
orange_fill = PatternFill(start_color='f77d26',
end_color='f77d26',
fill_type='solid')
yellow_fill = PatternFill(start_color='f7cd26',
end_color='f7cd26',
fill_type='solid')
green_fill = PatternFill(start_color='92f726',
end_color='92f726',
fill_type='solid')
for i in range(2, len(worksheet[constant.DEADLINE_COLUMN]) + 1):
worksheet.conditional_formatting.add(
constant.FORMULAE_COLUMN + str(i),
FormulaRule(formula=[constant.OK_COLUMN + str(i) + '="ok"'],
fill=green_fill))
worksheet.conditional_formatting.add(
constant.FORMULAE_COLUMN + str(i),
CellIsRule(operator='<',
formula=['TODAY()'],
stopIfTrue=True,
fill=red_fill))
worksheet.conditional_formatting.add(
constant.FORMULAE_COLUMN + str(i),
CellIsRule(operator='=',
formula=['TODAY()'],
stopIfTrue=True,
fill=orange_fill))
worksheet.conditional_formatting.add(
constant.FORMULAE_COLUMN + str(i),
CellIsRule(operator='<=',
formula=['TODAY() + 3'],
stopIfTrue=True,
fill=yellow_fill))
def percentile_fill(start_column, start_row, end_column, end_row,
percentile, fill):
format_range = {
'start_column': start_column,
'start_row': start_row,
'end_column': end_column,
'end_row': end_row,
}
worksheet.conditional_formatting.add(
"%(start_column)s%(start_row)d:%(end_column)s%(end_row)d" %
format_range,
CellIsRule(
operator='greaterThan',
formula=[
('PERCENTILE($%(start_column)s$%(start_row)d:'
'$%(end_column)s$%(end_row)d,{})').format(percentile)
% format_range
],
fill=fill))
def test_conditional_font(self):
"""Test to verify font style written correctly."""
ws = self.ws
cf = ConditionalFormattingList()
ws.conditional_formatting = cf
# Create cf rule
redFill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType=fills.FILL_SOLID)
whiteFont = Font(color=Color("FFFFFFFF"))
ws.conditional_formatting.add(
'A1:A3',
CellIsRule(operator='equal',
formula=['"Fail"'],
stopIfTrue=False,
font=whiteFont,
fill=redFill))
from openpyxl.writer.worksheet import write_conditional_formatting
# First, verify conditional formatting xml
cfs = write_conditional_formatting(ws)
xml = b""
for cf in cfs:
xml += tostring(cf)
diff = compare_xml(
xml, """
<conditionalFormatting sqref="A1:A3">
<cfRule dxfId="0" operator="equal" priority="1" type="cellIs" stopIfTrue="0">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def create_sheet(wb, title, width):
ws = wb.create_sheet(title=title)
# Column Number (Coalition)
for r, row in enumerate(reversed(ws['C2:L2'])):
for c, cell in enumerate(row):
cell.value = c + 1
cell.font = Font(bold=True)
# Row Number (Problem)
for r, row in enumerate(reversed(ws['B3:B12'])):
for c, cell in enumerate(row):
cell.value = r + 1
cell.font = Font(bold=True)
# Coalition and Problem Labels
ws['C1'] = 'Coalition'
ws['C1'].alignment = Alignment(horizontal='center', vertical='center')
ws['C1'].font = Font(bold=True)
ws['A3'] = 'Problem'
ws['A3'].alignment = Alignment(horizontal='center',
vertical='center',
text_rotation=90)
ws['A3'].font = Font(bold=True)
ws.merge_cells('C1:L1')
ws.merge_cells('A3:A12')
# Sum over each row (Problem)
ws.conditional_formatting.add(
'M3:M12',
CellIsRule(operator='equal',
stopIfTrue=True,
formula=['10'],
font=Font(bold=True)))
ws.conditional_formatting.add(
'M3:M12',
CellIsRule(operator='equal',
stopIfTrue=True,
formula=['0'],
font=Font(bold=True)))
for row in ws['M3:M12']:
for c in row:
c.value = '=SUM(C%d:L%d)' % (c.row, c.row)
# Sum over each column (Coalition)
ws.conditional_formatting.add(
'C13:L13',
CellIsRule(operator='equal',
stopIfTrue=True,
formula=['10'],
font=Font(bold=True)))
ws.conditional_formatting.add(
'C13:L13',
CellIsRule(operator='equal',
stopIfTrue=True,
formula=['0'],
font=Font(bold=True)))
for row in ws['C13:M13']:
for c in row:
c.value = '=SUM(%s3:%s12)' % (c.column, c.column)
# Column Widths
for col in ws.columns:
ws.column_dimensions[col[0].column].width = width
return ws
wsnew.conditional_formatting.add(
blockrange,
ColorScaleRule(start_type='num',
start_value='$A$3',
start_color='0000FF',
mid_type='num',
mid_value='$B$3',
mid_color='FFFF00',
end_type='num',
end_value='$C$3',
end_color='FF0000'))
wsnew.conditional_formatting.add(
blockrange,
CellIsRule(operator='between',
formula=['0', '0.2'],
stopIfTrue=True,
font=Font(color="FFFFFF")))
wsnew.conditional_formatting.add(
blockrange,
CellIsRule(operator='between',
formula=['0.2', '1.0'],
stopIfTrue=True,
font=Font(color="000000")))
blockrangediff = ulc_diff + str(ulr_diff) + ':' + lrc_diff + str(lrr_diff)
#
blockrangeb = ulc_b + str(ulr_b) + ':' + lrc_b + str(lrr_b)
wsnew.conditional_formatting.add(
blockrangeb,
ColorScaleRule(start_type='num',
start_value='$A$3',
start_color=''
def _format_trigger(wb, sheetname):
if sheetname not in wb.sheetnames:
raise ValueError(
f'{sheetname} not in workbook. Available names are {wb.sheetnames}.'
)
ws = wb[sheetname]
end_cols = False
end_rows = True
col_ctr = 1
max_row = 1
while True:
if ws.cell(max_row + 1, 1).value is None:
break
max_row += 1
while True:
row_ctr = 1
if ws.cell(1, col_ctr).value is None:
break
col_header = ws.cell(row_ctr, col_ctr).value
# Percent format
if '%' in col_header:
fmt = PERCENTAGE_FORMAT
cdn_fmt = True
# Accounting format
else:
fmt = ACCOUNTING_FORMAT
cdn_fmt = False
for i in range(2, max_row + 1):
# if no line items: check first
if ws.cell(i, col_ctr).value is None:
pass
if cdn_fmt:
cell = str(conv_dict[col_ctr]) + str(i)
ws.conditional_formatting.add(
cell,
CellIsRule(operator='lessThan',
formula=['0'],
fill=redFill,
font=red_text))
ws.conditional_formatting.add(
cell,
CellIsRule(operator='greaterThan',
formula=['0'],
fill=greenFill,
font=green_text))
ws.conditional_formatting.add(
cell,
CellIsRule(formula=[f'ISBLANK({cell})'],
fill=blankFill))
#ws.cell(row_ctr, col_ctr).conditional_formatting.add(cell, )
ws.cell(i, col_ctr).number_format = fmt
col_ctr += 1
def writeToExcelFile(suite):
global reportFileName
reportFileName += '.xlsx'
wb = Workbook()
ws = wb.active
header = ('Tests',
'Success Rate',
"# Tests",
"# Failed",
'Duration',
'Failed Scenarios',
'Failed Steps',
'Skipped Steps',
'Manual Testing')
ws.append(header)
subHeader = (suite['name'],"")
ws.append(subHeader)
for node in suite['nodes']:
percent=''
row = ("Suite: " + node['name'][5:],
percent,
'',
'',
duration(node['duration']))
ws.append(row)
for feature in node['features']:
percent = ''
if feature['total'] == 0:
pass
else:
row = (getFeatureFileName(feature['name']),
'',
feature['total'],
'',
duration(feature['duration']), '')
ws.append(row)
count = True
for failure in feature['failureList']:
if count:
ws.cell(row=ws.max_row,
column=COLS.index("FAILED_SCENARIOS"),
value=fixPrefix(feature['failures'][failure]['scenario']))
ws.cell(row=ws.max_row,
column=COLS.index("FAILED_STEPS"),
value=feature['failures'][failure]['step'])
count = False
else:
ws.append(
{COLS.index("FAILED_SCENARIOS"): fixPrefix(feature['failures'][failure]['scenario']),
COLS.index("FAILED_STEPS"): feature['failures'][failure]['step']})
for skip in feature['skipList']:
if count:
ws.cell(row=ws.max_row,
column=COLS.index("SKIPPED_SCENARIOS"),
value=skip)
count = False
else:
ws.append({COLS.index("SKIPPED_SCENARIOS"): skip})
#merging
mergeSheet(ws)
#styling
#all cells
for row in ws.rows:
for cell in row:
cell.style = defaultStyle
#first 2 rows
for row in ws.iter_rows(min_row=1, max_col=ws.max_column, max_row=2):
for cell in row:
cell.style = blueHighlight
#skipped steps rows
for row in ws.iter_rows(min_row=1, min_col=COLS.index("SKIPPED_SCENARIOS"),
max_col=COLS.index("SKIPPED_SCENARIOS"), max_row=2):
for cell in row:
cell.style = cyanHighlight
#test suite rows
suiteCells = getAllSuites(ws)
for suiteCell in suiteCells:
for col in ws.iter_cols(min_row = suiteCell.row,
max_col = ws.max_column,
max_row = suiteCell.row):
for cell in col:
if cell.col_idx == COLS.index("SKIPPED_SCENARIOS"):
cell.style = cyanHighlight
else:
cell.style = suiteStyle
resizeRow(ws, cell.row, 2)
#style the rate column
rateColumn = 'B2:B' + str(ws.max_row)
ws.conditional_formatting.add(rateColumn,
CellIsRule(operator='==', formula=['1'], fill=greenFill, font=Font(color=colors.BLACK)))
ws.conditional_formatting.add(rateColumn,
CellIsRule(operator='!=', formula=['1'], fill=redFill, font=Font(color=colors.WHITE)))
for row in ws[rateColumn]:
for cell in row:
# cell.font = Font(color=colors.WHITE)
cell.number_format ='0.00%'
#style up to the duration column
for row in ws['B:F']:
for cell in row:
cell.alignment = centerAl
#style the skipped scenarios
loc = get_column_letter(COLS.index("SKIPPED_SCENARIOS"))
for row in ws[loc + '1:' + loc + str(ws.max_row)]:
for cell in row:
cell.alignment = skippedStyle.alignment
# style the failed scenarios and failed steps columns
# TODO 'F4:H' to relative pos
failedColumns = 'F4:H' + str(ws.max_row)
for row in ws[failedColumns]:
for cell in row:
cell.alignment = failedStyle.alignment
# style the manual testing column
manualTestColumn = 'I2:I' + str(ws.max_row)
for row in ws[manualTestColumn]:
for cell in row:
cell.alignment = centerAl
ws.conditional_formatting.add(manualTestColumn,
CellIsRule(operator='==', formula=['"PASSED"'], fill=greenFill, font=Font(color=colors.BLACK)))
ws.conditional_formatting.add(manualTestColumn,
CellIsRule(operator='==', formula=['"FAILED"'], fill=redFill, font=Font(color=colors.WHITE)))
#set fixed widths
ws.column_dimensions['F'].width = 60
ws.column_dimensions['G'].width = 60
ws.column_dimensions['H'].width = 60
#size all the columns
for i in range(1, COLS.index("DURATION")):
resizeToFitColumn(ws, i)
#resize failed column
ws.column_dimensions['D'].width = ws.column_dimensions['C'].width + 1
#resize duration column
resizeColumn(ws, COLS.index("DURATION"), DURATION_COL_LEN)
#resize manual testing column
resizeColumn(ws, COLS.index("MANUAL_TEST"), MAN_TEST_COL_LEN)
#resize first row
resizeRow(ws, HEADER_ROW, 2)
#resize rows with merged cells
for row in ws.iter_rows(min_row=3, max_col=1, max_row=ws.max_row):
for cell in row:
failedScenarioCell = cell.offset(column=COLS.index("FAILED_SCENARIOS")-1)
failedStepCell = cell.offset(column=COLS.index("FAILED_STEPS")-1)
skippedScenarioCell = cell.offset(column=COLS.index("SKIPPED_SCENARIOS")-1)
valueLength = max(map(len,
map(str,(failedScenarioCell.value,
failedStepCell.value,
skippedScenarioCell.value))))
if(failedScenarioCell.value or skippedScenarioCell.value):
size = 1 + (valueLength // LINE_WRAP_LEN)
resizeRow(ws, cell.row, size)
# add Success rate formula to suites and features
for row in ws.iter_rows(min_row=2, min_col=2, max_col=2, max_row=ws.max_row):
for cell in row:
tot = cell.offset(column=1)
ng = cell.offset(column=2)
cell.value = '=IF({0}=0,0,({0}-{1})/{0})'.format(tot.coordinate, ng.coordinate)
# add # of tests formula
# refactor to a function
for row in ws.iter_rows(min_row=3,
min_col=COLS.index("TEST_NO"),
max_col=COLS.index("TEST_NO"),
max_row=ws.max_row):
for cell in row:
leadCell = cell.offset(column=-2)
if leadCell in suiteCells:
suiteLength = getSuiteRowLen(leadCell, ws)
offset = 0 if not suiteLength else 1
if suiteLength:
cell.value = "=SUM({0}{1}:{0}{2})".format(cell.column,
str(cell.row+offset),
str(cell.row+suiteLength))
else:
cell.value = 0
# add # of tests formula
# refactor to a function
for row in ws.iter_rows(min_row=3,
min_col=COLS.index("FAILED_NO"),
max_col=COLS.index("FAILED_NO"),
max_row=ws.max_row):
for cell in row:
leadCell = cell.offset(column=-3)
if leadCell in suiteCells:
suiteLength = getSuiteRowLen(leadCell, ws)
offset = 0 if not suiteLength else 1
if suiteLength:
cell.value = "=SUM({0}{1}:{0}{2})".format(cell.column,
str(cell.row+offset),
str(cell.row+suiteLength))
else:
cell.value = 0
# add total # tests formula for suite
# refactor to a function
testCells = []
for row in ws.iter_rows(min_row=3,
min_col=COLS.index("TEST_NO"),
max_col=COLS.index("TEST_NO"),
max_row=ws.max_row):
for cell in row:
leadCell = cell.offset(column=-2)
if leadCell in suiteCells:
testCells.append(cell.coordinate)
totalTest = ws['C2']
totalTest.value = '=SUM({})'.format(','.join(testCells))
# add total # failures formula for suite
# refactor to a function
failedCells = []
for row in ws.iter_rows(min_row=3,
min_col=COLS.index("FAILED_NO"),
max_col=COLS.index("FAILED_NO"),
max_row=ws.max_row):
for cell in row:
leadCell = cell.offset(column=-3)
if leadCell in suiteCells:
failedCells.append(cell.coordinate)
totalFailure = ws['D2']
totalFailure.value = '=SUM({})'.format(','.join(failedCells))
# data validation for the manual testing column
ws.add_data_validation(manualTestingDv)
for row in ws.iter_rows(min_row=4,
min_col=COLS.index("MANUAL_TEST"),
max_col=COLS.index("MANUAL_TEST"),
max_row=ws.max_row):
for cell in row:
leadCell = cell.offset(column=-8)
if not leadCell in suiteCells:
manualTestingDv.ranges.append(cell.coordinate)
#save file
wb.save(reportFileName)
def get_indicators():
wb = Workbook()
ws = wb.active
indicators = Cell.objects.select_related(
'row', 'col', 'row__table',
'col__parent').filter(row__table__id=22).order_by(
'row__id', 'col__number')
groups = dict(
Cell.objects.filter(row__table__id=20).values_list(
'id', 'value__char200_value'))
group = indicators[0].value.ref_value.id
row_id = indicators[0].row.id
ws.title = groups[group][:30]
row_num = add_headers(ws)
first_row = row_num
group_field_id = indicators[0].col.id
min_val = 0
red_fill = PatternFill(start_color='EE1111',
end_color='EE1111',
fill_type='solid')
for cell in indicators:
if cell is None or cell.value is None:
continue
if cell.col.parent is not None and cell.col.parent.id == 20 and group != cell.value.ref_value.id:
ws.cell(row=row_num, column=1, value=(row_num - first_row + 1))
group = cell.value.ref_value.id
ws = wb.create_sheet(groups[group][:30])
row_num = add_headers(ws)
row_id = cell.row.id
first_row = row_num
if cell.row.id > row_id:
ws.cell(row=row_num, column=1, value=(row_num - first_row + 1))
row_id = cell.row.id
row_num += 1
if cell.col.id == group_field_id:
continue
elif cell.col.id == 53:
min_val = cell.get_value()
elif cell.col.id == 54:
max_val = cell.get_value()
current_cell = ws.cell(row=row_num, column=4)
if min_val is not None:
ws.conditional_formatting.add(
current_cell.coordinate,
CellIsRule(operator='lessThan',
formula=[min_val],
stopIfTrue=True,
fill=red_fill))
if max_val is not None:
ws.conditional_formatting.add(
current_cell.coordinate,
CellIsRule(operator='greaterThan',
formula=[max_val],
stopIfTrue=True,
fill=red_fill))
add_comment(current_cell, min_val, max_val)
elif cell.value is not None:
if cell.col.column_type == REFERENCE:
if cell.value.ref_value is not None:
ws.cell(row=row_num,
column=(cell.col.number),
value=cell.value.ref_value.get_value())
else:
c = ws.cell(row=row_num,
column=(cell.col.number),
value=cell.get_value())
c.alignment = Alignment(wrapText=True)
return wb
SN.cell(row=2 + D, column=2).number_format = '0.00'
SN.cell(row=2 + D, column=3).number_format = '0.00'
SN.cell(row=2 + D, column=4).number_format = '0.00'
SN.cell(row=2 + D, column=5).number_format = '0.00'
SN.cell(row=2 + D, column=6).number_format = '0.00'
SN.cell(row=2 + D, column=7).number_format = '0.00'
D += 1
# Todo: CUSTOMIZE FOR EACH YEAR------------------------------------------------
#These formatting rules just make visual changes to the data for faster identification of team performance.
# The values in here will need to be determined by actually watching a couple tournaments and adjusting accordingly.
SN.conditional_formatting.add(
'B2:B75',
CellIsRule(operator='greaterThan',
formula=[20.1],
stopIfTrue=True,
fill=greenFill))
SN.conditional_formatting.add(
'B2:B75',
CellIsRule(operator='between',
formula=[10.1, 20.0],
stopIfTrue=True,
fill=yellowFill))
SN.conditional_formatting.add(
'B2:B75',
CellIsRule(operator='between',
formula=[.01, 10],
stopIfTrue=True,
fill=redFill))
SN.conditional_formatting.add(
'C2:C75',
def upgrade_excel_spreadsheet(spreadsheet_data):
with NamedTemporaryFile(suffix=".xlsx", delete=False) as tmp:
log.debug(f"Saving temp outout to {tmp.name}")
spreadsheet_data.save_as(array=spreadsheet_data, filename=tmp.name)
wb = load_workbook(tmp.name)
ws = wb.active
# nicer columns
ws.column_dimensions["A"].width = "30"
ws.column_dimensions["B"].width = "30"
# Add statistic rows:
ws.insert_rows(0, amount=8)
ws[f'B1'] = "Total"
ws[f'B2'] = "Contains passed"
ws[f'B3'] = "Contains info"
ws[f'B4'] = "Contains warning"
ws[f'B5'] = "Contains failed"
ws[f'B6'] = "Contains good_not_tested"
ws[f'B7'] = "Contains not_tested"
ws[f'B8'] = "Percentage passed (ignoring not_tested)"
# bold totals:
for i in range(1, 9):
ws[f'B{i}'].font = Font(bold=True)
data_columns = [
'H', 'I', 'J', 'K', 'L', "M", "N", 'O', 'P', 'Q', 'R', 'S', 'T',
'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF',
'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ',
'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA', 'BB',
'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM',
'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX',
'BY', 'BZ'
]
for cell in data_columns:
# if header, then aggregate
if ws[f'{cell}11'].value:
# There is a max of 5000 domains per scan. So we set this to something lower.
# There is no good support of headers versus data, which makes working with excel a drama
# If you ever read this code, and want a good spreadsheet editor: try Apple Numbers. It's fantastic.
ws[f'{cell}1'] = f'=COUNTA({cell}12:{cell}5050)'
# todo: also support other values
ws[f'{cell}2'] = f'=COUNTIF({cell}12:{cell}5050, "passed")'
ws[f'{cell}3'] = f'=COUNTIF({cell}12:{cell}5050, "info")'
ws[f'{cell}4'] = f'=COUNTIF({cell}12:{cell}5050, "warning")'
ws[f'{cell}5'] = f'=COUNTIF({cell}12:{cell}5050, "failed")'
ws[f'{cell}6'] = f'=COUNTIF({cell}12:{cell}5050, "good_not_tested")'
ws[f'{cell}7'] = f'=COUNTIF({cell}12:{cell}5050, "not_tested")'
# Not applicable and not testable are subtracted from the total.
# See https://github.com/internetstandards/Internet.nl-dashboard/issues/68
# Rounding's num digits is NOT the number of digits behind the comma, but the total number of digits.
# todo: we should use the calculations in report.py. And there include the "missing" / empty stuff IF
# that is missing.
ws[f'{cell}8'] = f'=ROUND({cell}2/({cell}1 - ({cell}6 + {cell}7)), 4)'
ws[f'{cell}8'].number_format = '0.00%'
# fold port and ip-version (and protocol?) from report as it's not useful in this case?
ws.column_dimensions.group('C', 'E', hidden=True)
# line 9 is an empty line
# make headers bold
ws[f'A11'].font = Font(bold=True)
ws[f'B11'].font = Font(bold=True)
ws[f'F10'].font = Font(bold=True)
ws[f'F11'].font = Font(bold=True)
for cell in data_columns:
ws[f'{cell}10'].font = Font(bold=True)
ws[f'{cell}11'].font = Font(bold=True)
# Freeze pane to make navigation easier.
ws.freeze_panes = ws['H11']
# there is probably a feature that puts this in a single conditional value.
greenFill = PatternFill(start_color='B7FFC8',
end_color='B7FFC8',
fill_type='solid')
redFill = PatternFill(start_color='FFB7B7',
end_color='FFB7B7',
fill_type='solid')
blueFill = PatternFill(start_color='B7E3FF',
end_color='B7E3FF',
fill_type='solid')
orangeFill = PatternFill(start_color='FFD9B7',
end_color='FFD9B7',
fill_type='solid')
grayFill = PatternFill(start_color='99FFFF',
end_color='DBDBDB',
fill_type='solid')
altgrayFill = PatternFill(start_color='99FFFF',
end_color='C0C0C0',
fill_type='solid')
# Set the measurements to green/red depending on value using conditional formatting.
# There is no true/false, but we can color based on value.
ws.conditional_formatting.add(
'H12:CD9999',
CellIsRule(operator='=',
formula=['"passed"'],
stopIfTrue=True,
fill=greenFill))
ws.conditional_formatting.add(
'H12:CD9999',
CellIsRule(operator='=',
formula=['"failed"'],
stopIfTrue=True,
fill=redFill))
ws.conditional_formatting.add(
'H12:CD9999',
CellIsRule(operator='=',
formula=['"warning"'],
stopIfTrue=True,
fill=orangeFill))
ws.conditional_formatting.add(
'H12:CD9999',
CellIsRule(operator='=',
formula=['"info"'],
stopIfTrue=True,
fill=blueFill))
ws.conditional_formatting.add(
'H12:CD9999',
CellIsRule(operator='=',
formula=['"good_not_tested"'],
stopIfTrue=True,
fill=altgrayFill))
ws.conditional_formatting.add(
'H12:CD9999',
CellIsRule(operator='=',
formula=['"not_tested"'],
stopIfTrue=True,
fill=grayFill))
log.debug(ws.title)
wb.save(tmp.name)
return tmp
def MainPerformance(rawdata_path, rawdata_date, withut, utrate, wordtimemarkup,
debug):
try:
print('************ BEGIN ************')
print()
print('Step 1: Processing Template and Raw Data')
try:
#1.import template
wb_file_name = 'Report\AG_Performance_Template.xlsx'
wb_file = Library.getXlsxFile(wb_file_name, [])
print(' -Loading file: %s ' % wb_file, end="")
#wb = load_workbook(filename = wb_file_name, data_only=True)
wb = load_workbook(filename=wb_file)
for checksheet in wb.sheetnames:
if checksheet != "Performance":
wb.remove(wb[checksheet])
wb_sheet = wb["Performance"]
ReportDate = rawdata_date if rawdata_date != None else wb_sheet.cell(
column=1, row=1).value
wb_sheet.cell(column=1, row=1).value = rawdata_date
#ReportDate = wb_sheet.cell(column=1, row=1).value
ReportDateLast = ReportDate - datetime.timedelta(days=1)
ReportDateSimpleArr = str(ReportDate).split(' ',
1)[0].split('-', 2)
ReportDateSimple = ReportDateSimpleArr[1] + ReportDateSimpleArr[2]
ReportDateStr = ReportDateSimpleArr[0] + "/" + ReportDateSimpleArr[
1] + "/" + ReportDateSimpleArr[2]
ReportDateStrNoZero = ReportDateSimpleArr[0] + "/" + str(
int(ReportDateSimpleArr[1])) + "/" + str(
int(ReportDateSimpleArr[2]))
ReportDateLastSimpleArr = str(ReportDateLast).split(' ',
1)[0].split(
'-', 2)
ReportDateLastSimple = ReportDateLastSimpleArr[
1] + ReportDateLastSimpleArr[2]
#wb_sheet = wb[wb.sheetnames[0]]
print(' => Completed')
#2.import data source
print(' -Loading file:', end="")
try:
table_summary_file_name = '客服人員群組總結'
table_summary_file, table_summary_list = Library.getCsvFile2(
rawdata_path + '\**\*%s*.xls' % table_summary_file_name
if rawdata_path != None else None,
'Report\RAWDATA\**\*%s*.xls' % table_summary_file_name,
[[1, 1, ReportDateStrNoZero]])
if table_summary_list != None:
print(' %s => Completed' % table_summary_file)
else:
print(' Finding "%s\" => Failed' % table_summary_file_name)
except Exception as e:
if debug: print(e)
pass
#2.import data source
print(' -Loading file:', end="")
try:
table_loginout_file_name = '客服人員登出登入'
table_loginout_file, table_loginout_list = Library.getCsvFile2(
rawdata_path + '\**\*%s*.xls' % table_loginout_file_name
if rawdata_path != None else None,
'Report\RAWDATA\**\*%s*.xls' % table_loginout_file_name,
[[1, 1, ReportDateStrNoZero]])
if table_loginout_list != None:
print(' %s => Completed' % table_loginout_file)
else:
print(' Finding "%s\" => Failed' %
table_loginout_file_name)
except Exception as e:
pass
#2.import data source
#20190504 Phaseout for this source>>
#print(' -Loading file:', end="")
#try:
# table_mail_file_name='MAIL'
# table_mail_file, table_mail_sheet=Library.getXlsxFile2(rawdata_path + '\**\%s*.xlsx' % table_mail_file_name if rawdata_path!=None else None, 'Report\RAWDATA\**\*%s*.xlsx' % table_mail_file_name, [['Str', 2, 'Closed'], ['DateStr', 4, ReportDateStr]])
# if table_mail_file!=None: print(' %s => Completed' % table_mail_file)
# else: print(' Finding "%s\" => Failed' % table_mail_file_name)
#except Exception as e:
# pass
#20190504 Phaseout for this source<<
#2.import data source
print(' -Loading file:', end="")
try:
table_cts_file_name = 'CTS'
table_cts_file, table_cts_sheet = Library.getXlsxFile2(
rawdata_path + '\**\*%s*.xlsx' % table_cts_file_name
if rawdata_path != None else None,
'Report\RAWDATA\**\*%s*.xlsx' % table_cts_file_name,
[['DateStr', 3, ReportDateStr]])
if table_cts_file != None:
print(' %s => Completed' % table_cts_file)
else:
print(' Finding "%s\" => Failed' % table_cts_file_name)
except Exception as e:
pass
#2.import data source
print(' -Loading file:', end="")
try:
table_survey_file_name = Library.getSurvey(ReportDate)
#table_survey_file_name='Report\RAWDATA\滿意度調查-1227.xls'
if table_survey_file_name == None:
table_survey_file_name = '滿意度調查'
table_survey_file, table_survey_list = Library.getCsvFile2(
None, table_survey_file_name, [])
if table_survey_list != None:
print(' %s => Completed' % table_survey_file)
else:
print(' Finding "%s\" => Failed' % table_survey_file_name)
except Exception as e:
#print(e)
pass
#2.import data source
print(' -Loading file:', end="")
try:
table_working_file_name = '%s年%s月班表' % (ReportDate.year,
ReportDate.month)
table_working_file, table_working_sheet = Library.getXlsxFile2(
rawdata_path + '\**\*%s*.xlsx' % table_working_file_name
if rawdata_path != None else None,
'Report\RAWDATA\**\*%s*.xlsx' % table_working_file_name,
[])
if table_working_file != None:
print(' %s => Completed' % table_working_file)
else:
print(' Finding "%s\" => Failed' % table_working_file_name)
except Exception as e:
pass
#3.Processing Report
print()
print('Step 2: Processing report calculation')
TotalACD = 0.0
TotalACDCount = 0.0
TotalACW = 0.0
TotalACWCount = 0.0
TotalACDTime = 0
TotalACWTime = 0
TotalRingTime = 0
TableACDTime = 0
TableACWTime = 0
TableRingTime = 0
TableLoginTime = 0
newTotalACD = 0.0
newTotalACW = 0.0
newTotalRing = 0.0
newTotalLoginTime = 0.0
newTotal14 = 0.0
TotalLogin = 0
if wb_sheet.max_row > 1:
wb_sheet.cell(column=4, row=3).value = 0
#Process Summary Table 總計 Start
mySummaryRow = Library.getRow(table_summary_list, '總計')
if mySummaryRow != None:
print(' -Loading LoginID = 總計', end="")
wb_sheet.cell(column=15,
row=3).value = int(mySummaryRow[1]) #ACD通話
#wb_sheet.cell(column=13, row=3).value = datetime.timedelta(seconds=float(mySummaryRow[2])) #Total ACDAVG
#wb_sheet.cell(column=14, row=3).value = datetime.timedelta(seconds=float(mySummaryRow[3])) #Total ACWACG
TableACDTime = int(mySummaryRow[10]) #Total ACD
TableACWTime = int(mySummaryRow[11]) #Total ACW
TableRingTime = int(mySummaryRow[12]) #Total Ring
TableLoginTime = int(mySummaryRow[16]) #Total LoginTime
if TableLoginTime > 0 and (TableACDTime + TableACWTime +
TableRingTime) > 0:
wb_sheet.cell(
column=23,
row=3).value = (TableACDTime + TableACWTime +
TableRingTime) / TableLoginTime
try:
wb_sheet.cell(
column=24,
row=3).value = int(mySummaryRow[14]) / int(
mySummaryRow[16]) #文字服務/公務時間比例
except:
wb_sheet.cell(column=24, row=3).value = 0
if table_summary_file != None and mySummaryRow != None:
print(' => Completed')
#Process Summary Table 總計 End
for rows in range(1, wb_sheet.max_row + 1):
try:
LoginID = wb_sheet.cell(column=4, row=rows).value
LoginName = wb_sheet.cell(column=2, row=rows).value
if table_summary_file != None and table_summary_list != None:
mySummaryRow = Library.getRow(
table_summary_list, LoginID)
if mySummaryRow == None:
mySummaryRow = Library.getRow(
table_summary_list, LoginName)
#Process Summary Table
if mySummaryRow != None:
print(' -Loading LoginID = %s' % LoginID,
end="")
if mySummaryRow != None:
#print(mySummaryRow)
#Start ACD Process...
if int(mySummaryRow[1]) > 0:
wb_sheet.cell(column=15,
row=rows).value = int(
mySummaryRow[1]) #ACD通話
if mySummaryRow != None and wb_sheet.cell(
column=1, row=rows).value == 'AG':
TotalACD += float(mySummaryRow[2])
if float(mySummaryRow[2]) > 0:
TotalACDCount += 1
wb_sheet.cell(
column=13,
row=rows).value = datetime.timedelta(
seconds=float(
mySummaryRow[2])) #ACDAVG
wb_sheet.cell(
column=13,
row=3).value = datetime.timedelta(
seconds=TotalACD /
TotalACDCount) #Total ACDAVG
#End ACD Process...
#Start ACW Process...
TotalACW += float(mySummaryRow[3])
if float(mySummaryRow[3]) > 0:
TotalACWCount += 1
wb_sheet.cell(
column=14,
row=rows).value = datetime.timedelta(
seconds=float(
mySummaryRow[3])) #ACWAVG
wb_sheet.cell(
column=14,
row=3).value = datetime.timedelta(
seconds=TotalACW /
TotalACWCount) #Total ACWACG
#End ACW Process...
#Start Time Process...
try:
wb_sheet.cell(
column=23, row=rows).value = (
int(mySummaryRow[10]) +
int(mySummaryRow[11]) +
int(mySummaryRow[12])) / int(
mySummaryRow[16])
newTotalACD += int(mySummaryRow[10])
newTotalACW += int(mySummaryRow[11])
newTotalRing += int(mySummaryRow[12])
newTotalLoginTime += int(mySummaryRow[16])
wb_sheet.cell(column=23, row=3).value = (
newTotalACD + newTotalACW +
newTotalRing) / newTotalLoginTime
except:
wb_sheet.cell(column=23,
row=rows).value = 0
#End Time Process...
#Start TotalLogin Process...
TotalLogin += int(mySummaryRow[16])
wb_sheet.cell(
column=12,
row=rows).value = datetime.timedelta(
seconds=int(
mySummaryRow[16])) #TotalLogin
wb_sheet.cell(
column=12,
row=3).value = datetime.timedelta(
seconds=TotalLogin) #Total TotalLogin
try:
wb_sheet.cell(
column=24, row=rows).value = int(
mySummaryRow[14]) / int(
mySummaryRow[16]) #文字服務/公務時間比例
newTotal14 += int(mySummaryRow[14]) #文字服務
wb_sheet.cell(
column=24, row=3
).value = newTotal14 / newTotalLoginTime #文字服務/公務時間比例
except:
wb_sheet.cell(column=24,
row=rows).value = 0
#End TotalLogin Process...
#Start ACH Process... Phaseout 20181230
#wb_sheet.cell(column=30, row=rows).value = datetime.timedelta(seconds=int(float(mySummaryRow[2])+float(mySummaryRow[3]))) #ACH
#End TotalLogin Process...
#Start 總處理工作時間 Process...2018/12/30
try:
wb_sheet.cell(
column=22, row=rows).value = int(
mySummaryRow[16]) / 3600 #值班時間
except:
wb_sheet.cell(column=22,
row=rows).value = 0
#End TotalLogin Process...
#Process Loginout Table
if table_loginout_file != None:
myLoginoutRow = Library.getRowMerge(
table_loginout_list, LoginID)
if myLoginoutRow == None:
myLoginoutRow = Library.getRowMerge(
table_loginout_list, LoginName)
if myLoginoutRow != None and wb_sheet.cell(
column=1, row=rows).value == 'AG':
#print(myLoginoutRow)
LoginSec = Library.getSec(myLoginoutRow[3])
LogoutSec = Library.getSec(myLoginoutRow[5])
wb_sheet.cell(column=10, row=rows).value = str(
datetime.timedelta(
seconds=LoginSec))[-5:] #Login
wb_sheet.cell(column=11, row=rows).value = str(
datetime.timedelta(
seconds=LogoutSec))[-5:] #Logout
try:
#wb_sheet.cell(column=5, row=rows).value = int((LogoutSec-LoginSec)/60)
wb_sheet.cell(column=5,
row=rows).value = round(
LoginSec / 60, 0)
except Exception as e:
wb_sheet.cell(column=5,
row=rows).value = ""
#print(e)
else:
myRole = wb_sheet.cell(column=1,
row=rows).value
if myRole == 'AG' or myRole == 'SA':
wb_sheet.cell(column=5,
row=rows).value = "休"
#wb_sheet.cell(column=22, row=rows).value = "休"
elif myRole == '管理職':
wb_sheet.cell(column=5, row=rows).value = 9
#Process Mail Table
TotalPaperCounter = 0
#20190504 Phaseout for this source>>
#if rows>3 and table_mail_file!=None:
# myMailCounter = Library.getMailCount(table_mail_sheet, LoginName, ReportDateStr)
# if myMailCounter!=None and myMailCounter>0:
# wb_sheet.cell(column=19, row=rows).value = myMailCounter
# TotalPaperCounter += myMailCounter
#20190504 Phaseout for this source>>
#Process Cts Table
if rows > 3 and table_cts_file != None:
#20190504 Got it from CTS>>
myMailCounter = Library.getCtsCount(
table_cts_sheet, LoginName, ReportDateStr,
'email')
if myMailCounter != None and myMailCounter > 0:
wb_sheet.cell(column=19,
row=rows).value = myMailCounter
TotalPaperCounter += myMailCounter
#20190504 Got it from CTS<<
myFacebookCounter = Library.getCtsCount(
table_cts_sheet, LoginName, ReportDateStr,
'Facebook')
if myFacebookCounter != None and myFacebookCounter > 0:
wb_sheet.cell(
column=20,
row=rows).value = myFacebookCounter
TotalPaperCounter += myFacebookCounter
myTelCounter = Library.getCtsCount(
table_cts_sheet, LoginName, ReportDateStr,
'電話')
if myTelCounter != None and myTelCounter > 0:
wb_sheet.cell(column=16,
row=rows).value = myTelCounter
TotalPaperCounter += myTelCounter
myOutboundCounter = Library.getCtsCount(
table_cts_sheet, LoginName, ReportDateStr,
'Outbound')
if myOutboundCounter != None and myOutboundCounter > 0:
wb_sheet.cell(
column=18,
row=rows).value = myOutboundCounter
TotalPaperCounter += myOutboundCounter
#Process Survey Table
if rows > 3 and table_survey_list != None:
TotalSurveyNo1, TotalSurveyNo2, TotalSurveyNo1Good, TotalSurveyNo2Good, TotalSurveyNo1Ans, TotalSurveyNo2Ans, TotalSurveyBothAns = Library.getSurveyCount(
table_survey_list, LoginID)
try:
wb_sheet.cell(
column=26,
row=rows).value = TotalSurveyNo1 / int(
wb_sheet.cell(column=15,
row=rows).value)
except:
pass
try:
wb_sheet.cell(
column=27,
row=rows).value = TotalSurveyBothAns / int(
wb_sheet.cell(column=15,
row=rows).value)
except:
pass
try:
wb_sheet.cell(
column=28, row=rows
).value = TotalSurveyNo1Good / TotalSurveyNo1
except:
pass
try:
wb_sheet.cell(
column=29, row=rows
).value = TotalSurveyNo2Good / TotalSurveyNo2
except:
pass
#Process 班表
if rows > 3 and table_working_sheet != None:
if wb_sheet.cell(
column=5, row=rows
).value == "休" and TotalPaperCounter > 0:
wb_sheet.cell(column=5, row=rows).value = ""
if table_working_sheet != None:
wb_sheet.cell(
column=5,
row=rows).value = Library.getWorkingHour(
table_working_sheet, LoginName,
ReportDate)
#if str(wb_sheet.cell(column=5, row=rows).value).replace('.','',1).isdigit():
if Library.isNumber(
wb_sheet.cell(column=5, row=rows).value) == 1:
wb_sheet.cell(column=4, row=3).value += 1
if table_summary_file != None and mySummaryRow != None:
print(' => Completed')
except Exception as e:
print(' => Failed')
#print(e)
#Process 文字時間 Markup Start
try:
myUTRate = (newTotalACD + newTotalACW + newTotalRing +
newTotal14) / newTotalLoginTime
except Exception as e:
myUTRate = 9999
if withut and utrate != 0 and myUTRate < utrate:
print(
' -Processing UT Rate check and markup, UTRate=%s, Markup=%s'
% (utrate, wordtimemarkup),
end="")
newTotal14 = 0 #Reset Total
for rows in range(1, wb_sheet.max_row + 1):
try:
LoginID = wb_sheet.cell(column=4, row=rows).value
LoginName = wb_sheet.cell(column=2, row=rows).value
if table_summary_file != None and table_summary_list != None:
mySummaryRow = Library.getRow(
table_summary_list, LoginID)
if mySummaryRow == None:
mySummaryRow = Library.getRow(
table_summary_list, LoginName)
if mySummaryRow != None and wb_sheet.cell(
column=1, row=rows).value == 'AG':
try:
texttime = int(
float(mySummaryRow[14]) *
wordtimemarkup)
if (int(mySummaryRow[10]) +
int(mySummaryRow[11]) +
int(mySummaryRow[12]) +
texttime) > int(
mySummaryRow[16]):
continue
wb_sheet.cell(
column=24,
row=rows).value = texttime / int(
mySummaryRow[16]) #文字服務/公務時間比例
newTotal14 += texttime #文字服務
wb_sheet.cell(
column=24, row=3
).value = newTotal14 / newTotalLoginTime #文字服務/公務時間比例
except Exception as e:
#print(e)
wb_sheet.cell(column=24,
row=rows).value = 0
#End TotalLogin Process...
except Exception as e:
print(' => Failed')
#print(e)
print(' => Completed')
#Process 文字時間 Markup End
#改數值格式後不用計算
#if TotalLogin>0 and TotalACD>0:
# wb_sheet.cell(column=21, row=3).value = TotalACD/TotalLogin # Total TotalACD/TotalLogin
#if TotalLogin>0 and (TotalACDTime+TotalACWTime+TotalRingTime)>0:
# wb_sheet.cell(column=23, row=3).value = (TotalACDTime+TotalACWTime+TotalRingTime)/TotalLogin # Total TotalACD/TotalLogin
print()
print('Step 3: Generating Report')
#Generate
wb_sheet.title = ReportDateSimple
Performance_FilePathName = "Report\OPPO_Agent_Performance%s.xlsx" % ReportDateSimple
Performance_FilePathNameLast = "Report\OPPO_Agent_Performance%s.xlsx" % ReportDateLastSimple
print(' -Creating Report to the %s' % Performance_FilePathName,
end="")
gray_font = styles.Font(color='00A0A0A0')
if not os.path.isfile(Performance_FilePathName) and os.path.isfile(
Performance_FilePathNameLast
) and ReportDateSimple[-2:] != '01':
copyfile(Performance_FilePathNameLast,
Performance_FilePathName)
#if os.path.isfile(Performance_FilePathName):
# wb_Copy = load_workbook(filename = Performance_FilePathName)
# if ReportDateSimple in wb_Copy.sheetnames:
# wb_Copy_Sheet = wb_Copy[ReportDateSimple]
# else:
# wb_Copy_Sheet = wb_Copy.copy_worksheet(wb_Copy[wb_Copy.sheetnames[len(wb_Copy.sheetnames)-1]])
# wb_Copy_Sheet.title = ReportDateSimple
# wb_Copy_Sheet = Library.copyWorksheet(wb_sheet, wb_Copy_Sheet)
# wb_Copy_Sheet.conditional_formatting.add('A1:AB100', CellIsRule(operator='equal', formula=['0'], stopIfTrue=True, font=gray_font))
# wb_Copy.active = len(wb_Copy.sheetnames)-1
# wb_Copy.save(Performance_FilePathName)
# wb_Copy.close()
#else:
wb_sheet.conditional_formatting.add(
'A1:AB100',
CellIsRule(operator='equal',
formula=['0'],
stopIfTrue=True,
font=gray_font))
wb.save(Performance_FilePathName)
wb.close()
Library.correctWorksheet(Performance_FilePathName)
print(' => Completed')
except Exception as e:
print(' -Loading file: => Failed ')
#print(e)
try:
print()
print('Step 4: Generating Monthly Report')
file_report_date = ReportDate
file_report_firstdate = file_report_date.replace(day=1)
file_report_currentdate = file_report_date
Performance_FilePathName = "Report\OPPO_Agent_Performance%s_全月.xlsx" % ReportDateSimple
Performance_FilePathName_Full = os.path.abspath(
Performance_FilePathName)
if os.path.isfile(wb_file_name):
shutil.copy2(wb_file_name, Performance_FilePathName)
xl = Dispatch("Excel.Application")
xl.Visible = False
xl.DisplayAlerts = False
while (file_report_currentdate >= file_report_firstdate):
MonthlyReportDateSimpleArr = str(
file_report_currentdate).split(' ', 1)[0].split('-', 2)
MonthlyReportDateSimple = MonthlyReportDateSimpleArr[
1] + MonthlyReportDateSimpleArr[2]
source = Library.getFilePath(
rawdata_path + '\**\OPPO_Agent_Performance%s.xlsx' %
MonthlyReportDateSimple,
'Report\RAWDATA\**\OPPO_Agent_Performance%s.xlsx' %
MonthlyReportDateSimple)
print(' -Loading Date=%s, File=%s' %
(MonthlyReportDateSimple, source),
end="")
if source != None:
wb1 = xl.Workbooks.Open(Filename=source)
wb2 = xl.Workbooks.Open(
Filename=Performance_FilePathName_Full)
ws1 = wb1.Worksheets(1)
ws1.Copy(Before=wb2.Worksheets(1))
wb2.Close(SaveChanges=True)
wb2 = None
wb1.Close(SaveChanges=False)
wb1 = None
file_report_currentdate = file_report_currentdate - timedelta(
days=1)
print(' => Completed')
wb2 = xl.Workbooks.Open(Filename=Performance_FilePathName_Full)
try:
wb2_sheet = wb2.Sheets("SOP")
wb2_sheet.Delete()
except:
pass
try:
wb2_sheet = wb2.Sheets("Performance")
wb2_sheet.Delete()
except:
pass
try:
wb2_sheet = wb2.Sheets("分時表")
wb2_sheet.Delete()
except:
pass
try:
wb2_sheet = wb2.Sheets("問題解決率")
wb2_sheet.Delete()
except:
pass
wb2.Close(SaveChanges=True)
wb2 = None
xl.Quit()
xl = None
except Exception as e:
print(' => Failed ')
#print(e)
#except Exception as e:
# print(e)
except Exception as e:
print("Error!! Close all excel files and try again.")
finally:
print()
print('************ END ************')
def formatRedGreenFill(ws,
dbd,
row_comparing,
column_comparing,
row_to_compare,
column_to_compare,
testname,
tpch=0):
if tpch == 0:
ws.conditional_formatting.add(
'{0}'.format(
ws.cell(row=row_comparing,
column=column_comparing).coordinate),
CellIsRule(operator='equal',
formula=[
"'DBD_{0}'!${1}".format(
testname,
dbd.cell(row=row_to_compare,
column=column_to_compare).coordinate)
],
stopIfTrue=True,
fill=orangeFill))
ws.conditional_formatting.add(
'{0}'.format(
ws.cell(row=row_comparing,
column=column_comparing).coordinate),
CellIsRule(operator='greaterThan',
formula=[
"'DBD_{0}'!${1}".format(
testname,
dbd.cell(row=row_to_compare,
column=column_to_compare).coordinate)
],
stopIfTrue=True,
fill=redFill))
ws.conditional_formatting.add(
'{0}'.format(
ws.cell(row=row_comparing,
column=column_comparing).coordinate),
CellIsRule(operator='lessThan',
formula=[
"'DBD_{0}'!${1}".format(
testname,
dbd.cell(row=row_to_compare,
column=column_to_compare).coordinate)
],
stopIfTrue=True,
fill=greenFill))
else:
ws.conditional_formatting.add(
'{0}'.format(
ws.cell(row=row_comparing,
column=column_comparing).coordinate),
CellIsRule(operator='equal',
formula=[
"'DBD_{0}-ALL'!${1}".format(
testname,
dbd.cell(row=row_to_compare,
column=column_to_compare).coordinate)
],
stopIfTrue=True,
fill=orangeFill))
ws.conditional_formatting.add(
'{0}'.format(
ws.cell(row=row_comparing,
column=column_comparing).coordinate),
CellIsRule(operator='greaterThan',
formula=[
"'DBD_{0}-ALL'!${1}".format(
testname,
dbd.cell(row=row_to_compare,
column=column_to_compare).coordinate)
],
stopIfTrue=True,
fill=redFill))
ws.conditional_formatting.add(
'{0}'.format(
ws.cell(row=row_comparing,
column=column_comparing).coordinate),
CellIsRule(operator='lessThan',
formula=[
"'DBD_{0}-ALL'!${1}".format(
testname,
dbd.cell(row=row_to_compare,
column=column_to_compare).coordinate)
],
stopIfTrue=True,
fill=greenFill))
ws['{0}'.format(
ws.cell(row=row_comparing, column=column_comparing).coordinate
)].number_format = '### ### ### ### ###'
fllen=len(filelist) specwb=pd.read_excel(finalspecpath, header=None) evidarea=specwb.loc[20:60,2:40] evidarea.to_excel(r"sample") tempwb=px.load_workbook(r"sample") tempws=tempwb.active evidlist=list(range(1)) for col_num in range(1,40): for row_num in range(1,42): if tempws.cell(row=row_num,column=col_num).value == None: tempws.cell(row=row_num,column=col_num,value="temp") jap=is_japanese(str(tempws.cell(row=row_num,column=col_num).value)) if "sample" in str(tempws.cell(row=row_num,column=col_num).value) and jap == False: evidlist.append(tempws.cell(row=row_num,column=col_num).value) del evidlist[0] samplelen=len(evidlist) ws.cell(row=i+2,column=2,value=str(specwb.at[1,38])) ws.cell(row=i+2,column=3,value=samplelen) ws.cell(row=i+2,column=4,value=fllen) ipt=str(i+2) tf="=C"+ipt+"=D"+ipt ws.cell(row=i+2,column=5,value=tf) ws.cell(row=i+2,column=6,value=str(evidlist)) ws.cell(row=i+2,column=7,value=str(filelist)) range="E2:E"+ipt ws.conditional_formatting.add(range, CellIsRule(operator='equal',formula=['FALSE'], fill=PatternFill(start_color='FF0000', end_color='FF0000',fill_type='solid'))) os.remove(r"sample") wb.save(checkbook)
end_color='8BC34A',
fill_type='solid')
orange_fill = PatternFill(start_color='FFC107',
end_color='FFC107',
fill_type='solid')
red_fill = PatternFill(start_color='F44336',
end_color='F44336',
fill_type='solid')
white_fill = PatternFill(start_color='ffffff',
end_color='ffffff',
fill_type='solid')
white_font = Font(bold=True, color='ffffff')
bms_rules = [
CellIsRule(operator='between',
formula=['0', '100'],
stopIfTrue=True,
fill=green_fill),
CellIsRule(operator='between',
formula=['100', '200'],
stopIfTrue=True,
fill=orange_fill),
CellIsRule(operator='greaterThan',
formula=['100'],
stopIfTrue=True,
fill=red_fill,
font=white_font)
]
for rule in bms_rules:
wsBMS.conditional_formatting.add('L:L', rule)