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)