def __init__(self, start_type, start_value, start_color, end_type, end_value, end_color, mid_type=None, mid_value=None, mid_color=None, columns_range=None): self.columns = columns_range # checking against None explicitly since mid_value may be 0 if all(val is not None for val in (mid_type, mid_value, mid_color)): self.rule = ColorScaleRule(start_type=start_type, start_value=start_value, start_color=OpenPyColor(start_color), mid_type=mid_type, mid_value=mid_value, mid_color=OpenPyColor(mid_color), end_type=end_type, end_value=end_value, end_color=OpenPyColor(end_color)) else: self.rule = ColorScaleRule(start_type=start_type, start_value=start_value, start_color=OpenPyColor(start_color), end_type=end_type, end_value=end_value, end_color=OpenPyColor(end_color))
def setCondFormatting(worksheet, resH, resW): #bgr not rgb because of opencv2 redRule = ColorScaleRule(start_type='num', start_value=0, start_color='00000000', end_type='num', end_value=255, end_color='00FF0000') greenRule = ColorScaleRule(start_type='num', start_value=0, start_color='00000000', end_type='num', end_value=255, end_color='0000FF00') blueRule = ColorScaleRule(start_type='num', start_value=0, start_color='00000000', end_type='num', end_value=255, end_color='000000FF') for i in range(1, resW * 3 + 1): #assign b, g, r condformatting to every 3 columns rng = f'{get_column_letter(i)}1:{get_column_letter(i)}{resW}' if (i + 2) % 3 == 0: ws.conditional_formatting.add(rng, blueRule) elif (i + 1) % 3 == 0: ws.conditional_formatting.add(rng, greenRule) else: ws.conditional_formatting.add(rng, redRule)
def get_color_rule(metric: str) -> Any: red = 'F85D5E' yellow = 'FAF52E' green = '58C144' if metric in ['geomean', 'time[s]']: return ColorScaleRule(start_type='num', start_value=0.5, start_color=red, mid_type='num', mid_value=1, mid_color=yellow, end_type='num', end_value=5, end_color=green) if metric == 'average': return ColorScaleRule(start_type='num', start_value=-3, start_color=red, mid_type='num', mid_value=0, mid_color=yellow, end_type='num', end_value=3, end_color=green) return ColorScaleRule(start_type='percentile', start_value=10, start_color=red, mid_type='percentile', mid_value=50, mid_color=yellow, end_type='percentile', end_value=90, end_color=green)
def format_color_cells(sheet): sheet.conditional_formatting.add( 'B1:B52', ColorScaleRule(start_type='percentile', start_value=10, start_color='ea7d7d', mid_type='percentile', mid_value=50, mid_color='C0C0C0', end_type='percentile', end_value=90, end_color='9de7b1')) sheet.conditional_formatting.add( 'D1:D52', ColorScaleRule(start_type='percentile', start_value=10, start_color='ea7d7d', mid_type='percentile', mid_value=50, mid_color='C0C0C0', end_type='percentile', end_value=90, end_color='9de7b1')) sheet.conditional_formatting.add( 'F1:F52', ColorScaleRule(start_type='percentile', start_value=10, start_color='AA0000', mid_type='percentile', mid_value=50, mid_color='C0C0C0', end_type='percentile', end_value=90, end_color='00AA00'))
def conditionalFormatting(ws, redRow, greenRow, blueRow): #formats the red row by assigning the 0 to black and 255 as red ws.conditional_formatting.add(redRow, ColorScaleRule(start_type='num', start_value=0, start_color='000000', end_type='num', end_value=255, end_color='AA0000')) #formats the green row by assigning the 0 to black and 255 as green ws.conditional_formatting.add(greenRow, ColorScaleRule(start_type='num', start_value=0, start_color='000000', end_type='num', end_value=255, end_color='00AA00')) #formats the blue row by assigning the 0 to black and 255 as blue ws.conditional_formatting.add(blueRow, ColorScaleRule(start_type='num', start_value=0, start_color='000000', end_type='num', end_value=255, end_color='0000AA'))
def write(self, first_row, query, query_index, columns, sheet_name, table_names): self.cursor.execute(query) data = self.cursor.fetchall() last_row = first_row + len(data) + 1 # Create Pandas dataframe from the data df = pd.DataFrame(data, columns=columns) worksheet = self.book[sheet_name] df[columns[1]] = df[columns[1]].astype(float) current_row = first_row for r in dataframe_to_rows(df, index=False, header=True): worksheet.cell(row=current_row, column=1).value = r[0] worksheet.cell(row=current_row, column=2).value = r[1] current_row += 1 # Add table title worksheet.cell(row=first_row - 1, column=1, value=table_names[query_index]) # Add colouring to values worksheet.conditional_formatting.add( 'B{}:B{}'.format(first_row, last_row), ColorScaleRule(start_type='min', start_color=Color('AA0000'), end_type='max', end_color=Color('00AA00'))) return last_row
def create_accounts_tab(worksheet: Worksheet, rows: list): """ Function to specifically create the Non Compliant Resource worksheet. Modified passed in workbook. Parameters: worksheet (Worksheet): The worksheet to modify. rows (list): Accounts to be dropped into the worksheet. """ formatting = Formatter( title='Accounts List', freeze='A2', headers=[ Header(header_text='Account Name', width=42, key=['account_name', 'accountId']), Header(header_text='Account ID', width=14, key=['accountId']), Header( header_text='Score', width=9, key=['score'], conditional_formatting=ColorScaleRule(start_type='num', start_value=0, start_color='92D002', end_type='max', end_color='FF0000') ), ], excel_filter=True, ) create_tab(worksheet, rows, formatting) return worksheet
def add_result_data(self, results, sheet, row_offset): ros = str(row_offset) sheet['A' + ros] = 'reslevel' for class_ix in range(1, self.sim.num_classes + 1): sheet.cell(row=row_offset, column=1 + class_ix).value = 'n_' + str(class_ix) sheet.cell(row=row_offset, column=1 + self.sim.num_classes + 1).value = 'profit' row = row_offset + 1 ctr = 0 for r in results: sheet.cell(row=row, column=1).value = ctr for class_ix in range(self.sim.num_classes): sheet.cell(row=row, column=1 + class_ix + 1).value = r[class_ix] sheet.cell(row=row, column=1 + self.sim.num_classes + 1).value = r[self.sim.num_classes] row += 1 ctr += 1 rule = ColorScaleRule(start_type='min', start_value=0, start_color='FFFF0000', end_type='max', end_value=100, end_color='FF00FF00') sheet.conditional_formatting.add( 'D' + str(row_offset + 1) + ':D' + str(row), rule)
def conditionalFormatter(self, sheet, results): rule = ColorScaleRule(start_type='percentile', start_value=0, start_color='F8696B', mid_type = 'percentile', mid_value = 50, mid_color = 'FFEB84', end_type = 'percentile', end_value = 100, end_color = '63BE7B') for i in range(1, len(results)): cellRange = string.ascii_uppercase[i] + str(2) + ':' + string.ascii_uppercase[i] + str(len(results[i]) - 2) sheet.conditional_formatting.add(cellRange, 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 init_month(worksheet, year, month, current_month_row): month_range = calendar.monthrange(year, month) merge_row = current_month_row + 3 merge_col = 2 day = 1 worksheet.merge_cells(start_row=current_month_row, start_column=2, end_row=current_month_row, end_column=10) worksheet.cell(row=current_month_row, column=2).value = calendar.month_name[month] worksheet.cell(row=current_month_row, column=2).font = Font(bold=True) worksheet.merge_cells(start_row=current_month_row + 1, start_column=2, end_row=current_month_row + 1, end_column=10) worksheet.cell(row=current_month_row + 1, column=2).value = "Hours online" worksheet.merge_cells(start_row=current_month_row + 1, start_column=11, end_row=current_month_row + 1, end_column=22) worksheet.cell(row=current_month_row + 1, column=11).font = Font(bold=True) for i in range(0, month_range[1]): worksheet.merge_cells(start_row=merge_row, start_column=merge_col, end_row=merge_row, end_column=merge_col + 1) worksheet.merge_cells(start_row=merge_row + 1, start_column=merge_col, end_row=merge_row + 1, end_column=merge_col + 1) worksheet.cell(row=merge_row, column=merge_col).value = day worksheet.cell( row=merge_row, column=merge_col).alignment = Alignment(horizontal="center") worksheet.cell( row=merge_row + 1, column=merge_col).alignment = Alignment(horizontal="center") merge_col += 2 day += 1 range_string = "{0}{1}:{2}{3}".format(get_column_letter(2), str(current_month_row + 4), get_column_letter(merge_col - 2), str(current_month_row + 4)) worksheet.conditional_formatting.add( range_string, ColorScaleRule(start_type='min', start_value=None, start_color='FFFFFF', end_type='max', end_value=None, end_color='1F497D'))
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 AddRedGreenColorGradient(worksheet, header, df, hi, lo): d = dict(zip(range(25), list(string.ascii_uppercase)[1:])) excel_header = str(d[df.columns.get_loc(header) - 1]) len_df = df.shape[0] + 1 rng = excel_header + '2:' + excel_header + str(len_df) worksheet.conditional_formatting.add(rng, ColorScaleRule(start_type ='num', start_value = lo, start_color = '0000FF00',\ mid_type = 'num', mid_value = np.average([lo, hi]), mid_color = '00FFFF00',\ end_type = 'num', end_value = hi, end_color = '00FF0000')) return worksheet
def full_customization(sheet_name='Full customization'): # Unpacking arguments using '**' for dictionary of keyword arguments # see: https://docs.python.org/3/tutorial/controlflow.html#more-on-defining-functions writer_args = { 'path': output_filename, 'mode': 'a', 'engine': 'openpyxl'} # https://openpyxl.readthedocs.io/en/stable/formatting.html#colorscale percentile_rule = ColorScaleRule( start_type='percentile', start_value=10, start_color='ffaaaa', # red-ish mid_type='num', mid_value=0, mid_color='ffffff', # value zero==white end_type='percentile', end_value=90, end_color='aaffaa') # green-ish # create a custom named style for the index index_style = NamedStyle( name="Index Style", number_format='YYYY-MM-DD, DDD', font=Font(color='999999', italic=True), alignment=Alignment(horizontal='left')) with pd.ExcelWriter(**writer_args) as xlsx: df.to_excel(xlsx, sheet_name) # worksheets that have been created with this ExcelWriter can be accessed # by openpyxl using its API. `ws` is now a openpyxl Worksheet object ws = xlsx.sheets[sheet_name] # cell ranges title_row = '1' value_cells = 'B2:{col}{row}'.format( col=get_column_letter(ws.max_column), row=ws.max_row) index_column = 'A' ws.column_dimensions[index_column].width = 21 # which offers, among other things, a conditional_formatting facility ws.conditional_formatting.add(value_cells, percentile_rule) # for general styling, one has to iterate over all cells individually for row in ws[value_cells]: for cell in row: cell.number_format = '0.00' # builtin or named styles can be applied by using the object or their name # https://openpyxl.readthedocs.io/en/stable/styles.html#using-builtin-styles for cell in ws[index_column]: cell.style = index_style # style header line last, so that headline style wins in cell A1 for cell in ws[title_row]: cell.style = 'Headline 2'
def export_to_xlsx(self, path): """Generate XLSX version of the detection data model""" wb = Workbook() ws = wb.active ws.append([ 'ATT&CK Data Source', 'Sub Data Source', 'Source Data Object', 'Relationship', 'Destination Data Object', 'EventID', 'Data Channel', 'Coverage', 'Timeliness', 'Retention', 'Structure', 'Consistency', 'Score', 'Comment' ]) rows = 0 for entry in self.ddm_list: rows += 1 ws.append([ entry['att&ck data source'], entry['sub data source'], entry['source data object'], entry['relationship'], entry['destination data object'], entry['eventid'], entry['data channel'], entry['coverage'], entry['timeliness'], entry['retention'], entry['structure'], entry['consistency'], entry['score'], entry['comment'] ]) #add table table = Table(displayName="DDM", ref="A1:N{}".format(rows + 1)) style = TableStyleInfo(name="TableStyleLight15", showRowStripes=True) table.tableStyleInfo = style ws.add_table(table) #add conditional formating ws.conditional_formatting.add( 'H2:M10000', ColorScaleRule(start_type='min', start_color='F8696B', mid_type='percentile', mid_value=50, mid_color='FFEB84', end_type='max', end_color='63BE7B')) #write new ddm entry dt = datetime.now().strftime("%Y%m%d_%H%M%S") if not os.path.exists(path): os.makedirs(path) wb.save('{}ddm_enriched_{}.xlsx'.format(path, dt)) print('[*] Saved Excel to {}ddm_enriched_{}.xlsx'.format(path, dt))
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 create_balance_predictor_xlsx(balance_predictor_df: pd.DataFrame, predictor_path: Path, current_balance): wb = Workbook() ws = wb.active amount_column_number = balance_predictor_df.columns.get_loc('Amount') + 1 amount_column_letter = get_column_letter(amount_column_number) needs_attention_column_number = balance_predictor_df.columns.get_loc( 'Needs Attention') + 1 needs_attention_column_letter = get_column_letter( needs_attention_column_number) balance_column_number = len(balance_predictor_df.columns) + 1 balance_column_letter = get_column_letter(balance_column_number) bottom_row_number = len(balance_predictor_df) + 1 for index, row in enumerate(dataframe_to_rows(balance_predictor_df, False)): row_number = index + 1 if index == 0: # header row.append('Running Balance') elif index == 1: # Starting balance row.append(current_balance) else: formula = f'={balance_column_letter}{row_number - 1} + {amount_column_letter}{row_number}' row.append(formula) ws.append(row) ws.auto_filter.ref = f"A1:{get_column_letter(balance_column_number - 1)}{bottom_row_number}" rule = ColorScaleRule(start_type='percentile', start_color='ff0000', mid_type='percentile', mid_value=50, mid_color='ffff00', end_type='percentile', end_color='009900') ws.conditional_formatting.add( f'{balance_column_letter}1:{balance_column_letter}{bottom_row_number}', rule) for cell in ws[f'A1:A{bottom_row_number}']: cell[0].number_format = 'mm/dd/yy' for cell in ws[ f'{amount_column_letter}1:{amount_column_letter}{bottom_row_number}']: cell[0].number_format = '[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00' for cell in ws[ f'{balance_column_letter}1:{balance_column_letter}{bottom_row_number}']: cell[0].number_format = '[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00' for cell in ws[ f'{needs_attention_column_letter}1:{needs_attention_column_letter}{bottom_row_number}']: if cell[0].value == 'True': cell[0].font = Font(bold=True) wb.save(predictor_path)
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 add_info(worksheet, name, time, start, obs): worksheet.cell(row=2, column=9).value = name worksheet.cell(row=3, column=9).value = seconds_to_time(time) worksheet.cell(row=2, column=33).value = start worksheet.cell(row=3, column=33).value = obs worksheet.conditional_formatting.add( 'B9:CS9', ColorScaleRule(start_type='min', start_value=None, start_color='63BE7B', mid_type='percentile', mid_value=50, mid_color='FFEB84', end_type='max', end_value=None, end_color='F8696B'))
def apply_colors(ws, n_rows, n_cols): """ Apply colors to the worksheet by conditional formatting. Args: ws: the write-only worksheet. n_rows: the number of rows in the worksheet. n_cols: the number of columns in the worksheet. """ rgb = ['FF0000', '00FF00', '0000FF'] # red, green, and blue in hex end_col = get_col_name(n_cols) for r in range(1, n_rows + 1): rule = ColorScaleRule( start_type='num', start_value=0, start_color='000000', end_type='num', end_value=255, end_color=rgb[(r - 1) % 3] ) ws.conditional_formatting.add(f'A{r}:{end_col}{r}', rule)
def format_excel(file_path, x_max, y_max): """ format_excel(file_path, x_max, y_max) file_path = the full file path x_max, y_max = the dimensions of the file Given an excel file and its dimensions, this function formats the sheet following the expected BT output form. RETURNS None""" wb = pyxl.load_workbook(file_path) ws = wb['Data'] ws.freeze_panes = 'A2' ws.insert_cols(2) formula = '=((A2:A' + str(y_max + 1) + '- 14400000)/86400000) + DATE(1970,1,1)' ws['B2'] = formula ws.formula_attributes['B2'] = {'t': 'array', 'ref': 'B2:B' + str(y_max + 1)} wb.save(file_path) wb = pyxl.load_workbook(file_path) ws = wb['Data'] col = ws.column_dimensions['B'] col.number_format = 'h:mm:ss AM/PM' ws['B2'].number_format = 'h:mm:ss AM/PM' ws['B1'].number_format = 'General' ws['B1'].font = Font(bold=True) ws['B1'] = 'Time' ws['A1'] = 'Datetime' rule = ColorScaleRule(start_type='min', start_color='6589C1', mid_type='percentile', mid_value=50, mid_color='FFFFFF', end_type='max', end_color='E5726F') ws.conditional_formatting.add('C2:' + excel_column_name(x_max + 1) + str(y_max + 1), rule) for i in range(3, x_max + 2): column = excel_column_name(i) ws[column + str(y_max + 2)] = '=SUM(' + column + '2:' + column + str(y_max + 1) + ')' ws[column + str(y_max + 2)].font = Font(bold=True) ws['A' + str(y_max + 2)] = 'Sum per movement' ws['A' + str(y_max + 2)].font = Font(bold=True) wb.save(file_path)
def set_color_scale(map_ws, area_header, area_cell_info): # the following is for the color scale color_start_value = 00 # percentage (between 0-100) color_start_value_color = 'ED5F49' # light red color_mid_value = 60 # percentage (between 0-100) color_mid_value_color = 'CEE740' # yellow color_end_value = 100 # percentage (between 0-100) color_end_value_color = '22910C' # green colorscale_rule = ColorScaleRule(start_type='percentile', start_value=color_start_value, start_color=color_start_value_color, mid_type='percentile', mid_value=color_mid_value, mid_color=color_mid_value_color, end_type='percentile', end_value=color_end_value, end_color=color_end_value_color) top_left_col = area_cell_info['top_left_col'] top_left_row = area_cell_info['top_left_row'] bottom_right_col = area_cell_info['bottom_left_col'] + (area_header['ncols'] - 1) bottom_right_row = area_cell_info['bottom_left_row'] top_left_cell_in_range = get_cell_address(top_left_col, top_left_row) bottom_right_cell_in_range = get_cell_address(bottom_right_col, bottom_right_row) range_to_format = top_left_cell_in_range + ":" + bottom_right_cell_in_range map_ws.conditional_formatting.add(range_to_format, colorscale_rule)
import openpyxl import random from openpyxl.formatting.rule import ColorScaleRule wb = openpyxl.Workbook() sh = wb.active values = random.sample(range(50, 150), 10) for i, value in enumerate(values): sh.cell(i + 1, 1).value = value two_color_scale = ColorScaleRule(start_type="min", start_color="FF0000", end_type="max", end_color="FFFFFF") sh.conditional_formatting.add("A1:A10", two_color_scale) wb.save(r"..\data\color_scale.xlsx")
# Now let's apply this to all first row (header) cells header_row = sheet[1] for cell in header_row: cell.style = header workbook.save(filename="sample_styles.xlsx") red_background = PatternFill(bgColor=colors.RED) diff_style = DifferentialStyle(fill=red_background) rule = Rule(type="expression", dxf=diff_style) rule.formula = ["$H1<3"] sheet.conditional_formatting.add("A1:O100", rule) workbook.save("sample_conditional_formatting.xlsx") color_scale_rule = ColorScaleRule(start_type="min",start_color=colors.RED,end_type="max",end_color=colors.GREEN) # Again, let's add this gradient to the star ratings, column "H" sheet.conditional_formatting.add("H2:H100", color_scale_rule) workbook.save(filename="sample_conditional_formatting_color_scale.xlsx") color_scale_rule = ColorScaleRule(start_type="num",start_value=1,start_color=colors.RED, mid_type="num",mid_value=3,mid_color=colors.YELLOW,end_type="num", end_value=5,end_color=colors.GREEN) # Again, let's add this gradient to the star ratings, column "H" sheet.conditional_formatting.add("H2:H100", color_scale_rule) workbook.save(filename="sample_conditional_formatting_color_scale_3.xlsx") icon_set_rule = IconSetRule("5Arrows", "num", [1, 2, 3, 4, 5]) sheet.conditional_formatting.add("H2:H100", icon_set_rule)
from openpyxl.formatting.rule import ColorScaleRule from openpyxl.worksheet.table import TableStyleInfo from src.data.setting import HOME_DIR REPORT_DIR = HOME_DIR / 'reports' TABLE_STYLE = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=False, showColumnStripes=True) COLOR_RULE = ColorScaleRule(start_type='min', start_color='32CD32', mid_type='percentile', mid_value=50, mid_color='FFFF00', end_type='max', end_color='FF6347') PERCENT_FORMAT = '0.00%' PERCENT0_FORMAT = '0%' COMMA0_FORMAT = '#,##0' DATE_FORMAT = 'mm-dd-yy'
print(sheet.calculate_dimension()) sheet.conditional_formatting.add(sheet.calculate_dimension(), rule) # work_book.save("sales_basic_conditional.xlsx") # color_scale_rule = ColorScaleRule(start_type="min", start_color="00FFFF00", end_type="max", end_color="00ff0000") work_book = openpyxl.load_workbook("sales_record.xlsx") sheet = work_book.active for row in sheet["K2:N101"]: for cell in row: cell.number_format = "#,##0" # sheet.conditional_formatting.add("M2:N101", color_scale_rule) # work_book.save("sales_profit_colorscale.xlsx") color_scale_rule = ColorScaleRule(start_type="percentile", start_value=0, start_color="F2B5EC", mid_type="percentile", mid_value=50, mid_color="FFFF66", end_type="percentile", end_value=90, end_color="81DC3B") sheet.conditional_formatting.add("M2:N101", color_scale_rule) # work_book.save("sales_profit_colorscale.xlsx")
def write_excel(self, excel_file_name, minutes=True): """ @summary: Writes the alignment to an excel file, with colouring showing possible mis-alignments @param excel_file_name: The name for the retention time alignment file @type excel_file_name: StringType @param minutes: An optional indicator whether to save retention times in minutes. If False, retention time will be saved in seconds @type minutes: BooleanType @author: David Kainer """ wb = Workbook() ws = wb.active ws.title = "Aligned RT" # create header row ws['A1'] = "UID" ws['B1'] = "RTavg" for i, item in enumerate(self.expr_code): currcell = ws.cell(row=1, column=i + 3, value="%s" % item) comment = Comment('sample ' + str(i), 'dave') currcell.comment = comment # for each alignment position write alignment's peak and area for peak_idx in range(len( self.peakpos[0])): # loop through peak lists (rows) new_peak_list = [] for align_idx in range(len( self.peakpos)): # loops through samples (columns) peak = self.peakpos[align_idx][peak_idx] if peak is not None: if minutes: rt = peak.get_rt() / 60.0 else: rt = peak.get_rt() area = peak.get_area() new_peak_list.append(peak) # write the RT into the cell in the excel file currcell = ws.cell(row=2 + peak_idx, column=3 + align_idx, value=round(rt, 3)) # get the mini-mass spec for this peak, and divide the ion intensities by 1000 to shorten them ia = peak.get_ion_areas() ia.update((mass, int(intensity / 1000)) for mass, intensity in ia.items()) sorted_ia = sorted(ia.iteritems(), key=operator.itemgetter(1), reverse=True) # write the peak area and mass spec into the comment for the cell comment = Comment( "Area: %.0f | MassSpec: %s" % (area, sorted_ia), 'dave') currcell.number_format currcell.comment = comment else: rt = 'NA' area = 'NA' currcell = ws.cell(row=2 + peak_idx, column=3 + align_idx, value='NA') comment = Comment("Area: NA", 'dave') currcell.number_format currcell.comment = comment compo_peak = composite_peak(new_peak_list, minutes) peak_UID = compo_peak.get_UID() peak_UID_string = ('"%s"' % peak_UID) currcell = ws.cell(row=2 + peak_idx, column=1, value=peak_UID_string) currcell = ws.cell(row=2 + peak_idx, column=2, value="%.3f" % float(compo_peak.get_rt() / 60)) # colour the cells in each row based on their RT percentile for that row i = 0 for row in ws.rows: i += 1 cell_range = ("{0}" + str(i) + ":{1}" + str(i)).format( utils.get_column_letter(3), utils.get_column_letter(len(row))) ws.conditional_formatting.add( cell_range, ColorScaleRule(start_type='percentile', start_value=1, start_color='E5FFCC', mid_type='percentile', mid_value=50, mid_color='FFFFFF', end_type='percentile', end_value=99, end_color='FFE5CC')) wb.save(excel_file_name)
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=6) ws[f'B1'] = "Total" ws[f'B2'] = "Contains 1" ws[f'B3'] = "Contains 0" ws[f'B4'] = "Contains not_applicable" ws[f'B5'] = "Contains not_testable" ws[f'B6'] = "Percentage 1 (reducing not_testable and not_applicable from total)" for cell in [ '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' ]: # if header, then aggregate if ws[f'{cell}9'].value: ws[f'{cell}1'] = f'=COUNTA({cell}10:{cell}9999)' ws[f'{cell}2'] = f'=COUNTIF({cell}10:{cell}9999, 1)' ws[f'{cell}3'] = f'=COUNTIF({cell}10:{cell}9999, 0)' ws[f'{cell}4'] = f'=COUNTIF({cell}10:{cell}9999, "not_applicable")' ws[f'{cell}5'] = f'=COUNTIF({cell}10:{cell}9999, "not_testable")' # 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}6'] = f'=ROUND({cell}2/({cell}1 - ({cell}4 + {cell}5)), 4)' ws[f'{cell}6'].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) # make headers bold for cell in [ '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' ]: ws[f'{cell}8'].font = Font(bold=True) ws[f'{cell}9'].font = Font(bold=True) # Freeze pane to make navigation easier. ws.freeze_panes = ws['H10'] # Set the measurements to green/red depending on value using conditional formatting. ws.conditional_formatting.add( 'H10:CD9999', ColorScaleRule(start_type='min', start_color='FFDDDD', end_type='max', end_color='DDFFDD')) log.debug(ws.title) wb.save(tmp.name) return tmp
for row, (name, value) in enumerate(header): info_sheet.cell(row + 1, 1, value=name).font = Font(bold=True) info_sheet.cell(row + 1, 2, value=value) # Create a sheet for each field direction. Each sheet will have a grid of XY points. field_dirs = ('Bx', 'By', 'Bz') field_sheets = [workbook.create_sheet(name) for name in field_dirs] array_range = 'C3:{}{}'.format( openpyxl.utils.get_column_letter(len(line_scan.pos_values) + 3), len(ax2_values) + 3) # Generate some nice conditional formatting using Peter Kovesi's colour maps # See https://peterkovesi.com/projects/colourmaps/ for more details scale = colorcet.blues # light-blue colour scale - black text should be visible for all colours rule = ColorScaleRule(start_color=scale[0][1:], start_type='min', mid_color=scale[128][1:], mid_type='percentile', mid_value=50, end_color=scale[-1][1:], end_type='max') thin = Side(border_style="thin", color="000000") for sheet, name in zip(field_sheets, field_dirs): # Insert axis titles and axes into the sheet cell = sheet.cell(1, 1, value=f'{name} [{field_units}]') cell.font = Font(bold=True, size=14) cell.alignment = Alignment(horizontal="center", vertical="center") sheet.merge_cells('A1:B2') cell = sheet.cell(1, 3, value=f"{axis1} [mm]") cell.font = Font(bold=True) cell.alignment = Alignment(horizontal="center") sheet.merge_cells(start_row=1,
def make_excel(data_out: pd.DataFrame) -> None: wb = Workbook() wb.add_named_style(headers_style) wb.add_named_style(base_style) wb.add_named_style(text_wrap_style) ws = wb.active ws.auto_filter.ref = 'A1:M1' # Add data for r in dataframe_to_rows(data_out, index=False, header=True): ws.append(r) # Set Base style for columns in ws['A:M']: for cell in columns[1:]: cell.style = base_style # Set style for Headers cells = ws['A1:M1'] for cell in cells[0]: cell.style = headers_style # Set style for Hyperlink for cell in ws['B:B'][1:]: cell.hyperlink = cell.value cell.style = 'Hyperlink' for column in ['D', 'E', 'J']: for cell in ws[f'{column}:{column}'][1:]: cell.style = text_wrap_style # Set width for columns columns = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M'] for column in columns: ws.column_dimensions[column].width = 13 for row in range(len(ws['A'])): ws.row_dimensions[row].height = 25.5 start = 2 list_to_merge = [] for i in data_out['Дата добавления'].value_counts().sort_index( ascending=False).values: list_to_merge.append([start, start + i - 1]) start += i flag = 2 for i, j in list_to_merge: ws.merge_cells(f'A{i}:A{j}') if flag % 2 == 0: ws[f'A{i}'].fill = headers_style.fill ws[f'A{i}'].font = headers_style.font ws[f'A{i}'].alignment = headers_style.alignment flag += 1 ws.conditional_formatting.add( f'M{i}:M{j}', ColorScaleRule(start_type='max', end_type='min', start_color='CAFFBF', end_color='FFD6A5')) wb.save(BASE_PATH.joinpath('report.xlsx'))