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'))
