def test_write_conditional_formatting(self):
ws = self.ws
cf = ConditionalFormattingList()
ws.conditional_formatting = cf
fill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType=fills.FILL_SOLID)
font = Font(name='Arial',
size=12,
bold=True,
underline=Font.UNDERLINE_SINGLE)
border = Border(top=Side(border_style=borders.BORDER_THIN,
color=Color(colors.DARKYELLOW)),
bottom=Side(border_style=borders.BORDER_THIN,
color=Color(colors.BLACK)))
cf.add(
'C1:C10',
FormulaRule(formula=['ISBLANK(C1)'],
font=font,
border=border,
fill=fill))
cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill))
from openpyxl.writer.worksheet import write_conditional_formatting
for _ in write_conditional_formatting(ws):
pass # exhaust generator
wb = ws.parent
assert len(wb._differential_styles.styles) == 2
ft1, ft2 = wb._differential_styles.styles
assert ft1.font == font
assert ft1.border == border
assert ft1.fill == fill
assert ft2.fill == fill
def conditional_formatting():
blue = '0000FF'
pink = 'FF00FF'
black = '000000'
apply_format = 'A3:' + ws.dimensions.split(':')[1]
o_rule = FormulaRule(formula=['=$J3="O"'], font=Font(color=blue))
w_rule = FormulaRule(formula=['=$J3="W"'], font=Font(color=pink))
r_rule = FormulaRule(formula=['=$J3="R"'], font=Font(color=black))
n_rule = FormulaRule(formula=['$J3="N"'], font=Font(color=black))
ws.conditional_formatting.add(apply_format, o_rule)
ws.conditional_formatting.add(apply_format, w_rule)
ws.conditional_formatting.add(apply_format, r_rule)
ws.conditional_formatting.add(apply_format, n_rule)
ws.auto_filter.ref = ws.dimensions
def prettify(self):
sheet = self.workbook.active
# Define some background fill styles that we'll use for conditional formatting
red = 'ffc7ce'
green = 'c6efce'
red_fill = PatternFill(start_color=red, end_color=red, fill_type='solid')
green_fill = PatternFill(start_color=green, end_color=green, fill_type='solid')
# Iterate over the first row doing the following:
# Set bold font for all cells in the first row
# Set static column sizes since our auto resize isn't useful for this data set
# Collect letters of the cells where we want to apply conditional formatting
columns_to_format = []
for i, cell in enumerate(sheet[1]):
column_letter = get_column_letter(i + 1)
cell.font = Font(bold=True)
if cell.value == 'School':
sheet.column_dimensions[column_letter].width = 33
elif cell.value == 'Class':
sheet.column_dimensions[column_letter].width = 18
elif cell.value in ['Description', 'Notes']:
sheet.column_dimensions[column_letter].width = 66
elif cell.value in ['Offered', 'Online', 'Synchronous']:
sheet.column_dimensions[column_letter].width = 5
columns_to_format.append(column_letter)
# Conditional formatting rules to set background red if cell contains 'n', green if cell contains 'y'
for column_letter in columns_to_format:
sheet.conditional_formatting.add(f'{column_letter}2:{column_letter}1000',
FormulaRule(formula=[f'NOT(ISERROR(SEARCH("N",{column_letter}2)))'],
stopIfTrue=True,
fill=red_fill))
sheet.conditional_formatting.add(f'{column_letter}2:{column_letter}1000',
FormulaRule(formula=[f'NOT(ISERROR(SEARCH("Y",{column_letter}2)))'],
stopIfTrue=True,
fill=green_fill))
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 test_formula_rule(worksheet_with_cf, write_conditional_formatting):
from openpyxl.formatting.rule import FormulaRule
ws = worksheet_with_cf
ws.conditional_formatting.add(
'C1:C10', FormulaRule(formula=['ISBLANK(C1)'], stopIfTrue=True))
cfs = write_conditional_formatting(ws)
xml = b""
for cf in cfs:
xml += tostring(cf)
diff = compare_xml(
xml, """
<conditionalFormatting sqref="C1:C10">
<cfRule type="expression" stopIfTrue="1" priority="1">
<formula>ISBLANK(C1)</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
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 get_xls(self):
day_set = self.get_day_set()
deals = self.get_deals()
filename = '{branch} {service} {day}.xlsx'.format(
# city=self.service.branch.city.name,
branch=self.service.branch.name,
service=self.service.name,
day=self.day_start.strftime('%Y%m%d'))
wb = Workbook()
ws = wb.active
ws.title = '%s %s' % (self.service.name,
self.day_start.strftime('%Y%m%d'))
title_text = '{day} {branch} {service}'.format(
# city=self.service.branch.city.name,
branch=self.service.branch.name,
service=self.service.name,
day=self.day_start.strftime('%d.%m.%Y'))
title = ws.cell(row=1, column=1, value=title_text)
title.alignment = Alignment(horizontal='left')
title.font = Font(name='Calibri', size=11, bold=True)
ws.merge_cells('A1:C1')
labels = [
'Время', 'Клиент', 'День рождения', 'Телефон', 'Email', 'Статус',
'Стоимость', 'Предоплата', 'Безнал', 'Правщик', 'Администратор',
'Комментарий'
]
# names = [i[1] for i in data['tabs'][tab_key]['labels']]
columns = []
for index, label in enumerate(labels):
cell = ws.cell(row=2, column=index + 1, value=label)
cell.font = Font(name='Calibri', size=11, bold=True)
columns.append(cell.column)
row = 3
# import ipdb; ipdb.set_trace()
for time_key in sorted(day_set['timing'].keys()):
item = day_set['timing'][time_key]
# time_key = time['key']
time_key_str = '%s%s' % (day_set['key'], time_key)
if time_key in day_set['groups']:
row += 1
_ = ws.cell(row=row,
column=1,
value=day_set['groups'][time_key]['name'])
_ = ws.cell(row=row,
column=2,
value=day_set['groups'][time_key]['persons'])
# has deals
if time_key in day_set['timing'] and 'deals' in item:
# print('item', item)
if item.get('empty'):
row += 1
_ = ws.cell(row=row, column=1, value=item['label'])
for deal_key in item['deals']:
try:
deal = deals[deal_key]
except KeyError:
continue
# row += 1
# deal_text = '{step} ({cost} / {paid}), правщик: {master}'.format(
# step=deal['step_label'],
# cost=deal['cost'],
# paid=deal['paid'],
# master=deal['master__full_name'],
# )
# _ = ws.cell(row=row, column=1, value='%s (%s)' % (deal['start_string'], deal['minutes']))
# _ = ws.cell(row=row, column=2, value=deal_text)
# ws.merge_cells('B{row}:C{row}'.format(row=row))
# if deal['comment']:
# row += 1
# _ = ws.cell(row=row, column=2, value=deal['comment'])
# ws.merge_cells('B{row}:C{row}'.format(row=row))
for index, person in enumerate(deal['persons']):
row += 1
person_text = '%s' % (person['full_name'])
if person['control']:
person_text = '(Контроль) ' + person_text
if index == 0:
_ = ws.cell(
row=row,
column=1,
value='%s (%s)' %
(deal['start_string'], deal['minutes']))
_ = ws.cell(row=row,
column=6,
value=deal['step_label'])
_ = ws.cell(row=row, column=7, value=deal['cost'])
_ = ws.cell(row=row, column=8, value=deal['paid'])
_ = ws.cell(row=row,
column=9,
value=deal['paid_non_cash'])
_ = ws.cell(row=row,
column=10,
value=deal['master__full_name'])
_ = ws.cell(row=row,
column=11,
value=deal['manager__full_name'])
_ = ws.cell(row=row,
column=12,
value=deal['comment'])
_ = ws.cell(row=row, column=2, value=person_text)
_ = ws.cell(row=row, column=3, value=person['age'])
_ = ws.cell(row=row, column=4, value=person['phone'])
# _ = ws.cell(row=row, column=5, value=person['emails'])
if 'empty_finish' in item:
row += 1
_ = ws.cell(row=row,
column=1,
value=item['empty_finish']['label'])
border = Border(bottom=Side(border_style='thin', color='000000'))
ws.conditional_formatting.add(
'A1:K%s' % row, FormulaRule(formula=['E1=0'], border=border))
ws.column_dimensions['A'].width = 13
ws.column_dimensions['B'].width = 30
ws.column_dimensions['C'].width = 10
# row += 2
# for cellObj in ws['%s2:%s%s' % (columns[0], columns[-1], items_count)]:
# for cell in cellObj:
# import ipdb; ipdb.set_trace()
# print(cell.coordinate, cell.column)
# ws.column_dimensions[cell.column].bestFit = True
# ws[cell.coordinate].alignment = Alignment(horizontal='left')
response = HttpResponse(save_virtual_workbook(wb),
content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'inline; filename=%s' % urlquote(
filename).lower()
return response
for cell in rows:
cell.number_format = "m"
cell.value = '=IF(DAY({0})=1, {1},"")'.format(sheet.cell(column=cell.column, row=cell.row + 1).coordinate,
sheet.cell(column=cell.column, row=cell.row + 1).coordinate)
def colorMake(types, start, end):
return PatternFill(fill_type=types, start_color=start, end_color=end)
grayfill = colorMake('solid', 'd3d3d3', 'd3d3d3')
planfill = colorMake('solid', '1e90ff', '1e90ff') #計画セル色。デフォルトは青。
actualfill = colorMake('solid', 'E4007f', 'E4007f') #実績セル色。デフォルトはマゼンダ。
for rows in sheet.iter_rows(min_row=6, min_col=2, max_row=100, max_col=7):
for cell in rows:
cell.number_format = "m/d"
sheet.conditional_formatting.add('C6:G100', FormulaRule(formula=['NOT($F6="")'], stopIfTrue=True, fill=grayfill))
sheet.conditional_formatting.add('I4:ME100', FormulaRule(formula=['OR(WEEKDAY(I$5)=1, WEEKDAY(I$5)=7)'], stopIfTrue=True, fill=grayfill))
sheet.conditional_formatting.add('I6:ME100', FormulaRule(formula=['AND($D6<=I$4, $F6>=I$4)'], stopIfTrue=True, fill=actualfill))
sheet.conditional_formatting.add('I6:ME100', FormulaRule(formula=['AND($C6<=I$4, $E6>=I$4)'], stopIfTrue=True, fill=planfill))
sheet.freeze_panes = 'I6'
# desktop_path = Desktopの絶対パス。実行環境によって変更する.
## Windowsの場合 'C:\Users\ユーザー名\Desktop'
## Macの場合 '/Users/ユーザ名/Desktop/'
desktop_path = '/Users/seki/Desktop/'
wb.save(desktop_path + '進捗管理表.xlsx')
def create_ncr_tab(worksheet: Worksheet, ncr_data: list):
"""
Function to specifically create the Non Compliant Resource worksheet. Modified passed in workbook.
Parameters:
worksheet (Worksheet): The worksheet to modify.
ncr_data (list): The ncr data to be dropped into the worksheet.
"""
formatting = NcrTabFormatting()
# add header
worksheet.append(formatting.get_header_names())
# sort data, since adding a sort to the filter has no effect until excel sorts it
sort_header = ([h for h in formatting.headers if h.get('sort')] + [None])[0]
if sort_header:
ncrs = sorted(ncr_data, key=lambda ncr: get_value(sort_header, ncr))
else:
ncrs = ncr_data
for ncr in ncrs:
if ncr.get('isHidden'): # We've marked this one for hiding
continue
worksheet.append(formatting.format_resource(ncr))
# no footer
worksheet.title = formatting.TITLE
worksheet.freeze_panes = formatting.FREEZE
# starting_column = ord('A') + scorecard.NCR_STARTING_COLUMN
for idx, header in enumerate(formatting.headers):
worksheet.column_dimensions[get_column_letter(idx + 1)].width = header['width']
# bold header row
for header_cell in worksheet[1]:
header_cell.font = Font(bold=True)
starting_column = 'A'
# add filtering capability
worksheet.auto_filter.ref = 'A1:{}{:d}'.format(
get_column_letter(worksheet.max_column),
worksheet.max_row
)
italics_max_row = max(worksheet.max_row, 3)
# add conditional formatting for resource rows with a valid exclusion (italics)
cell_range = '{}2:{}{:d}'.format(starting_column, get_column_letter(worksheet.max_column), italics_max_row)
exclusion_valid_column = get_column_letter(formatting.get_exclusion_applied_header_index() + 1)
worksheet.conditional_formatting.add(
cell_range,
FormulaRule(
formula=['AND(${0}2=TRUE, NOT(ISBLANK(${0}2)))'.format(exclusion_valid_column)],
font=formatting.VALID_EXCLUSION_FONT
)
)
# add conditional formatting for resource rows with an expired exclusion (italic red text)
worksheet.conditional_formatting.add(
cell_range,
FormulaRule(
formula=['AND(${0}2=FALSE, NOT(ISBLANK(${0}2)))'.format(exclusion_valid_column)],
font=formatting.INVALID_EXCLUSION_FONT,
)
)
return worksheet
redFill = PatternFill(start_color='EE1111',
end_color='EE1111',fill_type='solid')
ws.conditional_formatting.add('A1:A10',ColorScaleRule(start_type='min', start_color='AA0000',
end_type='max', end_color='00AA00'))
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'))
ws.conditional_formatting.add('C2:C10',CellIsRule(operator='lessThan', formula=['C$1'], stopIfTrue=True, fill=redFill))
ws.conditional_formatting.add('D2:D10',CellIsRule(operator='between', formula=['1','5'], stopIfTrue=True, fill=redFill))
ws.conditional_formatting.add('E1:E10',FormulaRule(formula=['ISBLANK(E1)'], stopIfTrue=True, fill=redFill))
myFont = Font()
myBorder = Border()
ws.conditional_formatting.add('E1:E10',FormulaRule(formula=['E1=0'], font=myFont, border=myBorder, fill=redFill))
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)
from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import FormulaRule
wb = Workbook()
ws = wb.active
ws.cell(2, 1).value = '空白ではない'
ws.cell(4, 1).value = 5.3529
orange_fill = PatternFill('solid', start_color='FFA500', end_color='FFA500')
is_blank_rule = FormulaRule( #←Excelの数式を用いた設定
formula=['ISBLANK(INDIRECT(ADDRESS(ROW(), COLUMN())))'],
stopIfTrue=True,
fill=orange_fill)
ws.conditional_formatting.add(f'A1:A5', is_blank_rule)
ws.title = 'クラシック(数式)'
wb.save('classic_formula.xlsx')
def writeexcel(box, cutoffDate):
wb = Workbook()
ws = wb.active
bld = Font(bold=True)
ws.title = "Lag Report"
# '"Boat","Fab Start","Fab End","Canvas Start","Lag 1","Canvas End","Paint Start","Lag 2","Paint End","Outfit Start","Lag 3","Outfit End"'
props = [ ['A', 'Boat', 10.2602040816327], ['B', 'Firstday\nFab', 12.6887755102041], ['C', 'Lastday\nFab', 12.6887755102041], \
['D', 'Firstday\nCanvas', 12.6887755102041], ['E', 'Lag', 10.2602040816327], ['F', 'Lastday\nCanvas', 12.6887755102041], \
['G', 'Firstday\nPaint', 12.6887755102041], ['H', 'Lag', 10.2602040816327], ['I', 'Lastday\nPaint', 12.6887755102041], \
['J', 'Firstday\nOutfitting', 12.6887755102041], ['K', 'Lag', 10.2602040816327], ['L', 'Lastday\nOutfitting', 12.6887755102041] ]
for col, text, width in props:
ws['%s1' % col] = text
ws.column_dimensions[col].width = width
currentCell = ws.cell(1, ord(col) - 64)
currentCell.alignment = Alignment(horizontal='center')
currentCell.font = bld
# set header row height
ws.row_dimensions[1].height = 35.05
row_index = 2
for boat in sorted(box, key=lambda k: k['outfitStart']):
if boat["outfitEnd"] != "":
column_index = 1
if datetime.datetime.strptime(boat["outfitStart"],
"%Y-%m-%d").date() > cutoffDate:
if boat["canvasStart"] == "":
# no canvas stage only output for paint and outfitting
lag1 = ""
lag2 = max(workdays.networkdays(datetime.datetime.strptime(boat["fabEnd"],"%Y-%m-%d").date(), \
datetime.datetime.strptime(boat["paintStart"],"%Y-%m-%d").date(), holidays) -2, 0)
else:
lag1 = max(workdays.networkdays(datetime.datetime.strptime(boat["fabEnd"],"%Y-%m-%d").date(), \
datetime.datetime.strptime(boat["canvasStart"],"%Y-%m-%d").date(), holidays) -2, 0)
lag2 = max(workdays.networkdays(datetime.datetime.strptime(boat["canvasEnd"],"%Y-%m-%d").date(), \
datetime.datetime.strptime(boat["paintStart"],"%Y-%m-%d").date(), holidays) -2, 0)
lag3 = max(workdays.networkdays(datetime.datetime.strptime(boat["paintEnd"],"%Y-%m-%d").date(), \
datetime.datetime.strptime(boat["outfitStart"],"%Y-%m-%d").date(), holidays) -2, 0)
ws["A%s" % (row_index)] = boat["job"]
ws["B%s" % (row_index)] = boat["fabStart"]
ws["C%s" % (row_index)] = boat["fabEnd"]
ws["D%s" % (row_index)] = boat["canvasStart"]
ws["E%s" % (row_index)] = lag1
ws["F%s" % (row_index)] = boat["canvasEnd"]
ws["G%s" % (row_index)] = boat["paintStart"]
ws["H%s" % (row_index)] = lag2
ws["I%s" % (row_index)] = boat["paintEnd"]
ws["J%s" % (row_index)] = boat["outfitStart"]
ws["K%s" % (row_index)] = lag3
ws["L%s" % (row_index)] = boat["outfitEnd"]
row_index += 1
greyFill = PatternFill(start_color='FFCCCCCC',
end_color='FFCCCCCC',
fill_type='solid')
ws.conditional_formatting.add(
'A2:L%s' % (row_index - 1),
FormulaRule(formula=['ISEVEN(ROW())'], stopIfTrue=True, fill=greyFill))
wb.save('/tmp/LagReport-%s.xlsx' % (datetime.date.today()))
ws.append([
"pid", "9:00", "9:30", "10:00", "10:30", "11:00", "11:30", "12:00",
"12:30", "13:00", "13:30", "14:00", "14:30", "15:00", "15:30", "16:00",
"16:30", "17:00"
])
for row in people:
ws.append(
[row[0], '=VLOOKUP(-1*VLOOKUP($A2,assignment,COLUMN()),works,3)'])
ws.conditional_formatting.add(
"A2:R" + str(len(people) + 1),
FormulaRule(formula=[
'=EXACT(VLOOKUP(-1*VLOOKUP($A2,' + "'Assignment'!$A$2:$R$" +
str(len(assignment) + 1) + ',COLUMN()),' + "'Works'!$A$2:$D$" +
str(len(works) + 3) + ',2),"absent")'
],
stopIfTrue=True,
fill=PatternFill(patternType='solid',
fgColor='FF000000',
bgColor='FF000000')))
ws.conditional_formatting.add(
"A2:R" + str(len(people) + 1),
FormulaRule(formula=[
'=EXACT(VLOOKUP(-1*VLOOKUP($A2,' + "'Assignment'!$A$2:$R$" +
str(len(assignment) + 1) + ',COLUMN()),' + "'Works'!$A$2:$D$" +
str(len(works) + 3) + ',2),"parasol")'
],
stopIfTrue=True,
fill=PatternFill(patternType='solid',
fgColor='FFFFDDDD',
bgColor='FFFFDDDD')))
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 to_xlsx(self):
"""
Exports to xlsx file and formats it with correct column widths, top row freeze pane and conditional formatting
based on genotype.
"""
sheet = self.get_sheet_name()
if not self.xlsx_file:
self.xlsx_file = os.path.splitext(self.inp)[0] + '.xlsx'
if os.path.isfile(self.xlsx_file):
self.multi = True
book = load_workbook(self.xlsx_file)
writer = pd.ExcelWriter(self.xlsx_file, engine='openpyxl')
writer.book = book
if sheet in book.sheetnames: # if the sheet already exists, add a digit on the end.
for i in range(1, 100):
sheet1 = sheet + str(i)
if len(sheet1) > 31:
sheet1 = sheet[:30] + str(i)
if len(sheet1) > 31:
sheet1 = sheet[:29] + str(i)
if sheet1 not in book.sheetnames:
sheet = sheet1
break
else:
writer = pd.ExcelWriter(self.xlsx_file, engine='openpyxl')
self.samples.to_excel(writer,
sheet_name=sheet,
index=False,
freeze_panes=(1, 0)) # Write dataframe to excel
"""Formatting for a pretty output"""
wb = writer.book
ws = wb[sheet]
col_width = {
'A': 5,
'C': 11,
'J': 11,
'K': 9.14,
'N': 12.57,
'O': 10,
'P': 18,
'R': 10,
'S': 15.14,
'W': 11.43,
'Y': 13.43
}
for col in col_width: # set column widths
ws.column_dimensions[col].width = col_width[col]
for cell in ws[col]: # set center align
cell.alignment = Alignment(horizontal='center')
conditions = {
'Het':
PatternFill(patternType='solid', bgColor='DCE6F0'),
'Hom':
PatternFill(patternType='solid', bgColor='B8CCE4'),
'Hemi':
PatternFill(patternType='lightUp',
bgColor='DCE6F0',
fgColor='B8CCE4'),
'Fail':
PatternFill(patternType='solid', bgColor='FFC7CE'),
'Retest':
PatternFill(patternType='solid', bgColor='FFC7CE')
}
for genotype in conditions: # Add conditional formatting
ws.conditional_formatting.add(
'K2:K' + str(self.samples.shape[0] + 2),
FormulaRule(
formula=['NOT(ISERROR(SEARCH("' + genotype + '",K2)))'],
stopIfTrue=True,
fill=conditions[genotype]))
wb.active = ws
writer.save() # Save xlsx.
if self._multi_export:
print(Message(' Added sheet ' + sheet).timestamp(machine='Export'))
else:
print(
Message(' ' + os.path.split(self.xlsx_file)[1]).timestamp(
machine='Export'))
self._last_file = self.xlsx_file
os.startfile(self.xlsx_file)
self.xlsx_file = None
wb = openpyxl.load_workbook(datestring + '.xlsx')
Plan = wb.active
Plan.column_dimensions['B'].width = 23
Plan.column_dimensions['C'].width = 13
Plan.column_dimensions['E'].width = 13
Plan.column_dimensions['F'].width = 13
Plan.column_dimensions['H'].width = 18
Plan.column_dimensions['L'].width = 23
Plan.column_dimensions['M'].width = 13
Plan.column_dimensions['G'].width = 13
Plan.column_dimensions['I'].width = 12
Plan.freeze_panes = 'A2'
blueFill = PatternFill(start_color='6f9bed', end_color='6f9bed', fill_type='solid')
greenFill = PatternFill(start_color='38c272', end_color='38c272', fill_type='solid')
Plan.conditional_formatting.add('I1:I5000', FormulaRule(formula=['countif($I$2:$I$5000,I1)>1'], stopIfTrue=True, fill=blueFill))
row_count = Plan.max_row
column_count = Plan.max_column
rows = range(1, row_count+1)
columns = range(1, column_count+1)
for row in rows:
for col in columns:
Plan.cell(row, col).alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
sheet2 = wb['No Bus Detail']
sheet2.column_dimensions['B'].width = 23
sheet2.column_dimensions['C'].width = 13
sheet2.column_dimensions['E'].width = 13
sheet2.column_dimensions['F'].width = 13
sheet2.column_dimensions['H'].width = 18
sheet2.column_dimensions['L'].width = 23
def infer_schema(data, fname, output_root='', sample_size=1.0, type_threshold=0.5, n_jobs=1, base_schema=None):
"""
Infer data types for all columns for the input table
Parameters
----------
data: pandas DataFrame
data table to infer
fname: string
the output file name
output_root: string, default=''
the root directory for the output file
sample_size: int or float(<= 1.0), default=1.0
int: number of sample rows to infer the data type (useful for large tables)
float: sample size in percentage
type_threshold: float(<= 1.0), default=0.5
threshold for inferring data type
n_jobs: int, default=1
the number of jobs to run in parallel
base_schema: pandas DataFrame, default=None
data schema to base on
"""
# check sample_size
if sample_size > 1:
if int(sample_size) != sample_size:
raise ValueError('sample_size: only accept integer when it is > 1.0')
if sample_size > data.shape[0]:
print("sample_size: %d is larger than the data size: %d" % (sample_size, data.shape[0]))
# check output_root
if output_root != '':
if not os.path.isdir(output_root):
raise ValueError('output_root: root not exists')
# check type_threshold
if (type_threshold <= 0) or (type_threshold > 1):
raise ValueError('type_threshold: should be in (0, 1]')
# check base_schema
if base_schema is not None:
if type(base_schema) != pd.core.frame.DataFrame:
raise ValueError('base_schema: only accept pandas DataFrame')
# open a new workbook to store all result
wb = openpyxl.Workbook()
ws = wb['Sheet']
ws.title = 'schema'
# calculate sample size
if sample_size <= 1.0:
sample_size = int(data.shape[0] * sample_size)
# dictionary to store dropna sample data values
data_dropna_sample_values = {}
for col in data.columns.values:
if len(data[col].dropna()) <= sample_size:
data_dropna_sample_values[col] = data[col].dropna().values
else:
data = data.sample(sample_size).reset_index(drop=True)
data_dropna_sample_values[col] = data[col].dropna().values
# use data_dropna_sample_values to infer data type for each column
_n_jobs = np.min([n_jobs, len(data.columns.values)])
type_infos = Parallel(n_jobs=_n_jobs)(delayed(_infer_dtype)(data_dropna_sample_values[col], col, type_threshold)
for col in data.columns.values)
type_infos_df = pd.DataFrame(type_infos)[['column', 'type']]
# dtype mapping for basic stat calculation
data_types = {}
for col in data.columns.values:
data_types[col] = type_infos_df.loc[type_infos_df['column']==col, 'type'].values[0]
# get basic statistic information for all columns
stat_infos = Parallel(n_jobs=_n_jobs)(delayed(_cal_column_stat)
(data_dropna_sample_values[col], col, data_types[col]) for col in data.columns.values)
stat_infos_df = pd.DataFrame(stat_infos)
# merge dtype infomation with stat information
full_infos_df = type_infos_df.merge(stat_infos_df, on='column', how='left')
# add include column
full_infos_df['include'] = 1
full_infos_df = full_infos_df[['column', 'type', 'include', 'sample_value', 'sample_num_uni',
'sample_uni_percentage', 'sample_min', 'sample_median', 'sample_max', 'sample_std']]
# if base_schema is provided, we can compare with base schema
if base_schema is not None:
base_schema = base_schema[['column', 'type', 'include']]
base_schema.columns = ['base_%s' %(col) for col in base_schema.columns.values]
full_infos_df = full_infos_df.merge(base_schema, left_on='column', right_on='base_column', how='outer')
# compare with the base schema
full_infos_df['base_column'] = full_infos_df['base_column'].apply(lambda x : 'column not in base table' if pd.isnull(x) else x)
full_infos_df['column'] = full_infos_df['column'].apply(lambda x : 'column not in current table' if pd.isnull(x) else x)
# reorder the column
full_infos_df['include'] = base_schema['base_include']
full_infos_df = full_infos_df[['column', 'base_column', 'type', 'base_type', 'include', 'sample_value',
'sample_num_uni', 'sample_uni_percentage', 'sample_min', 'sample_median',
'sample_max', 'sample_std']]
# add data validation for type column
val_type = DataValidation(type="list", formula1='"key,numeric,str,date"', allow_blank=False)
ws.add_data_validation(val_type)
# add data validation for include column
val_include = DataValidation(type="list", formula1='"0,1"', allow_blank=False)
ws.add_data_validation(val_include)
# get col_name, excel column mapping
column_mapping = {}
for i, col in enumerate(full_infos_df.columns):
column_mapping[col] = xlsxwriter.utility.xl_col_to_name(i)
# write everything into the worksheet
for r_idx, r in enumerate(dataframe_to_rows(full_infos_df, index=False, header=True)):
ws.append(r)
for cell_idx, cell in enumerate(ws.iter_cols(max_col=ws.max_column, min_row=ws.max_row, max_row=ws.max_row)):
cell = cell[0]
if r_idx != 0:
val_type.add(ws['%s%d' %(column_mapping['type'], ws.max_row)])
val_include.add(ws['%s%d' % (column_mapping['include'], ws.max_row)])
if cell_idx == 0:
cell.font = Font(bold=True)
else:
cell.style = 'Accent5'
# add conditional formating
red_fill = PatternFill(bgColor="FFC7CE")
red_font = Font(color="9C0006")
green_fill = PatternFill(bgColor="C6EFCE")
green_font = Font(color="006100")
blue_fill = PatternFill(bgColor="9ECAE1")
blue_font = Font(color="08306B")
orange_fill = PatternFill(bgColor="FDD0A2")
orange_font = Font(color="A63603")
purple_fill = PatternFill(bgColor="DADAEB")
purple_font = Font(color="3F007D")
# red highlight if there is any inconsistent between base and the target
if base_schema is not None:
col1 = column_mapping['column']
col2 = column_mapping['base_column']
ws.conditional_formatting.add(
'%s2:%s%d' %(col1, col1, ws.max_row),
FormulaRule(formula=['%s2<>%s2' %(col1, col2)], stopIfTrue=True, fill=red_fill, font=red_font))
ws.conditional_formatting.add(
'%s2:%s%d' %(col2, col2, ws.max_row),
FormulaRule(formula=['%s2<>%s2' %(col1, col2)], stopIfTrue=True, fill=red_fill, font=red_font))
col1 = column_mapping['type']
col2 = column_mapping['base_type']
ws.conditional_formatting.add(
'%s2:%s%d' %(col1, col1, ws.max_row),
FormulaRule(formula=['%s2<>%s2' %(col1, col2)], stopIfTrue=True, fill=red_fill, font=red_font))
ws.conditional_formatting.add(
'%s2:%s%d' %(col2, col2, ws.max_row),
FormulaRule(formula=['%s2<>%s2' %(col1, col2)], stopIfTrue=True, fill=red_fill, font=red_font))
# yellow highlight column type and include (which need to be modified)
ws['%s1' %(column_mapping['type'])].style = 'Neutral'
ws['%s1' % (column_mapping['include'])].style = 'Neutral'
# green highlight for the mkey type and red highlight for the error type
type_cols = [column_mapping['type']]
if 'base_type' in column_mapping.keys():
type_cols.append(column_mapping['base_type'])
for col in type_cols:
ws.conditional_formatting.add(
'%s2:%s%d' %(col, col, ws.max_row),
FormulaRule(formula=['%s2="error"' %(col)], stopIfTrue=True, fill=red_fill, font=red_font))
ws.conditional_formatting.add(
'%s2:%s%d' %(col, col, ws.max_row),
FormulaRule(formula=['%s2="key"' %(col)], stopIfTrue=True, fill=green_fill, font=green_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col, col, ws.max_row),
FormulaRule(formula=['%s2="numeric"' % (col)], stopIfTrue=True, fill=blue_fill, font=blue_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col, col, ws.max_row),
FormulaRule(formula=['%s2="str"' % (col)], stopIfTrue=True, fill=orange_fill, font=orange_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col, col, ws.max_row),
FormulaRule(formula=['%s2="date"' % (col)], stopIfTrue=True, fill=purple_fill, font=purple_font))
# red highlight for include = 0
ws.conditional_formatting.add(
'%s2:%s%d' % (column_mapping['include'], column_mapping['include'], ws.max_row),
FormulaRule(formula=['%s2=0' % (column_mapping['include'])], stopIfTrue=True, fill=red_fill, font=red_font))
# red highlight for sample_num_uni = 0 or 1, only one unique value
ws.conditional_formatting.add(
'%s2:%s%d' %(column_mapping['sample_num_uni'], column_mapping['sample_num_uni'], ws.max_row),
FormulaRule(formula=['%s2=0' %(column_mapping['sample_num_uni'])], stopIfTrue=True, fill=red_fill, font=red_font))
ws.conditional_formatting.add(
'%s2:%s%d' %(column_mapping['sample_num_uni'], column_mapping['sample_num_uni'], ws.max_row),
FormulaRule(formula=['%s2=1' %(column_mapping['sample_num_uni'])], stopIfTrue=True, fill=red_fill, font=red_font))
# adjust the column format for the worksheet
_adjust_ws(ws=ws, row_height=20)
wb.save(filename = os.path.join(output_root, 'data_schema_%s.xlsx' %(fname)))
file['sheet_name'],
header_row_cell_value=file['header_row_cell_value']
if 'header_row_cell_value' in file else '')
print('Processing results')
db = QueryDB('SERVER', 'DATABASE', 'USERNAME', 'PASSWORD')
results = db.exec_sql('EXEC dbo.Lorenzo_DCS_to_Domain')
if results:
XlsxTools().create_document(results, 'DCS vs Domain', results_file)
else:
print('No results were returned')
print('Add conditional formatting')
wb = load_workbook(results_file)
ws = wb.active
ws.conditional_formatting.add(
'A:N', FormulaRule(formula=['$A1="Domain"'], font=Font(color=RED)))
ws.conditional_formatting.add(
'A:N', FormulaRule(formula=['$A1="DCS"'], font=Font(color='008000')))
pattern_yellow = PatternFill(start_color=YELLOW,
end_color=YELLOW,
fill_type='solid')
ws.conditional_formatting.add(
'G:G',
FormulaRule(formula=['AND($A1<>$A2,$E1=$E2,$G1<>$G2)'],
fill=pattern_yellow))
ws.conditional_formatting.add(
'H:H',
FormulaRule(formula=['AND($A1<>$A2,$E1=$E2,$G1=$G2,$H1<>$H2)'],
fill=pattern_yellow))
ws.conditional_formatting.add(
'I:I',
def write(self, cues: List[Cue], filepath: str):
wb = Workbook()
ws = wb.active
ws.title = "Cue"
font8 = Font(name='メイリオ', size=8)
font11 = Font(name='メイリオ', size=11)
font12 = Font(name='メイリオ', size=12)
purple_fill = PatternFill(start_color='CCCCFF',
end_color='CCCCFF',
fill_type='solid')
black_txt = Font(color='000000')
ws.conditional_formatting.add(
f'A3:K{len(cues) + 3}',
FormulaRule(formula=['MOD(ROW(),2)=0'],
stopIfTrue=True,
fill=purple_fill,
font=black_txt))
ws["A2"] = "キュー"
ws["B2"] = "区間距離"
ws["C2"] = "PC毎距離"
ws["D2"] = "総距離"
ws["E2"] = "進路"
ws["F2"] = "交差点"
ws["H2"] = "信号"
ws["I2"] = "道標の方面"
ws["J2"] = "道路"
ws["K2"] = "ランドマーク(注意事項)"
ws.merge_cells("F2:G2")
for col in ws["A2":"K2"][0]:
col.font = font8
row = 3
ws[f"A{row}"] = 1
ws[f"B{row}"] = 0.0
ws[f"B{row}"].number_format = '0.0'
ws[f"C{row}"] = 0.0
ws[f"C{row}"].number_format = '0.0'
ws[f"D{row}"] = 0.0
ws[f"D{row}"].number_format = '0.0'
ws[f"E{row}"] = ""
ws[f"G{row}"] = ""
ws[f"I{row}"] = ""
ws[f"J{row}"] = ""
ws[f"K{row}"] = "スタート"
for i, cue in enumerate(cues):
row = i + 4
ws[f"A{row}"] = f"=A{row - 1}+1"
ws[f"B{row}"] = f"=D{row}-D{row - 1}"
ws[f"B{row}"].number_format = '0.0'
ws[f"C{row}"].number_format = '0.0'
ws[f"D{row}"] = cue.distance
ws[f"D{row}"].number_format = '0.0'
ws[f"E{row}"] = cue.direction
ws[f"G{row}"] = cue.intersection
ws[f"I{row}"] = cue.sign
ws[f"J{row}"] = cue.road
ws[f"K{row}"] = cue.comment
ws[f"L{row}"] = cue.src
align1 = Alignment(horizontal="left",
vertical="top",
wrap_text=True,
shrink_to_fit=False)
align2 = Alignment(horizontal="center",
vertical="center",
wrap_text=True,
shrink_to_fit=False)
align3 = Alignment(horizontal="center",
vertical="center",
wrap_text=False,
shrink_to_fit=True)
align4 = Alignment(horizontal="center",
vertical="center",
wrap_text=False,
shrink_to_fit=False)
for row in ws["A3":f"K{len(cues) + 3}"]:
for cell in row:
if cell.column in (7, 9, 11): # G, I, K
ws[cell.coordinate].font = font11
else:
ws[cell.coordinate].font = font12
if cell.column == 11: # K
ws[cell.coordinate].alignment = align1
elif cell.column in (9, 10): # I, J
ws[cell.coordinate].alignment = align2
elif cell.column == 7: # G
ws[cell.coordinate].alignment = align3
else:
ws[cell.coordinate].alignment = align4
# Write border
thin = Side(border_style="thin", color="000000")
thin_border = Border(top=thin, left=thin, right=thin, bottom=thin)
for row in ws["A2":f"K{len(cues) + 3}"]:
for cell in row:
ws[cell.coordinate].border = thin_border
ws.column_dimensions["A"].width = 7
ws.column_dimensions["B"].width = 9.5
ws.column_dimensions["C"].width = 9.5
ws.column_dimensions["D"].width = 9.5
ws.column_dimensions["E"].width = 6.4
ws.column_dimensions["F"].width = 4.5
ws.column_dimensions["G"].width = 16
ws.column_dimensions["H"].width = 4.5
ws.column_dimensions["I"].width = 14
ws.column_dimensions["J"].width = 11
ws.column_dimensions["K"].width = 36
wb.save(filepath)
def create_excel_comparison(old_mdd, new_mdd, xl_output):
old_wave_variables = get_mdd_data(old_mdd)
new_wave_variables = get_mdd_data(new_mdd)
# filling master variables
master_variables = []
# 1. new variables
for name, variable in new_wave_variables.items():
if name not in old_wave_variables:
master_variables.append(
MasterVariableInfo(name,
variable.data_type,
v_new=True,
v_dropped=False,
c_new=False,
c_dropped=False,
v_label=False,
c_label=False))
# 2. dropped variables
for name, variable in old_wave_variables.items():
if name not in new_wave_variables:
master_variables.append(
MasterVariableInfo(name,
variable.data_type,
v_new=False,
v_dropped=True,
c_new=False,
c_dropped=False,
v_label=False,
c_label=False))
# 3. changed variables
for new_variable in new_wave_variables.values():
old_variable = old_wave_variables.get(new_variable.name)
if old_variable:
# 3.1 check variable labels
v_label = old_variable.label != new_variable.label
# 3.2 check categories
for new_name, new_label in new_variable.categories.items():
old_label = old_variable.categories.get(new_name)
if old_label and new_label != old_label:
c_label = True
break
else:
c_label = False
# 3.3 new categories
c_new = bool([
c for c in new_variable.categories
if c not in old_variable.categories
])
c_dropped = bool([
c for c in old_variable.categories
if c not in new_variable.categories
])
if v_label or c_label or c_new or c_dropped:
master_variables.append(
MasterVariableInfo(name=new_variable.name,
data_type=new_variable.data_type,
v_new=False,
v_dropped=False,
c_new=c_new,
c_dropped=c_dropped,
v_label=v_label,
c_label=c_label))
# export in excel
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Side, PatternFill
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import colors
from openpyxl.styles import Font, Color
wb = Workbook()
ws1 = wb.active
ws1.title = "overview"
# report header (row 1-2)
ws1.append(['New', new_mdd])
ws1.append(['Old', old_mdd])
ws1.append([])
if master_variables:
# variable header (row 4-5)
ws1.append([
'name', 'data_type', 'variable', '', 'categories', '',
'label(s) changed', '', 'Remarks'
])
ws1.append([
'', '', 'new', 'dropped', 'new', 'dropped', 'variable',
'categories', ''
])
for v in master_variables:
booleans = map(lambda x: 'x' if x else '', v[2:])
# booleans = ['x' if x else '' for x in v[2:]]
ws1.append((v.name, v.data_type, *booleans))
#formatting
ws1.column_dimensions['A'].width = 30
ws1.column_dimensions['B'].width = 20
# alignment - header
for row in range(4, 6):
for col in range(1, 12):
ws1.cell(column=col,
row=row).alignment = Alignment(horizontal='center',
vertical='center')
ws1.merge_cells('A4:A5')
ws1.merge_cells('B4:B5')
ws1.merge_cells('C4:D4')
ws1.merge_cells('E4:F4')
ws1.merge_cells('G4:H4')
ws1.merge_cells('I4:I5')
# borders
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
# create fill
colour_fill = PatternFill(start_color='FFFF7F',
end_color='FFFF7F',
fill_type='solid')
for row in range(4, len(master_variables) + 6):
for col in range(1, len(master_variables[0]) + 2):
ws1.cell(row=row, column=col).border = thin_border
# alignment - table
for col in range(3, len(master_variables[0]) + 2):
ws1.cell(row=row,
column=col).alignment = Alignment(horizontal='center',
vertical='center')
# freeze header
ws1.freeze_panes = ws1.cell(row=6, column=1)
# conditional formatting
ws1.conditional_formatting.add(
f'B6:B{len(master_variables) + 5}',
FormulaRule(formula=['B6="mtText"'],
stopIfTrue=True,
fill=colour_fill))
# auto filter
ws1.auto_filter.ref = f'A5:I{len(master_variables) + 4}'
else:
ws1.append(['... no changes in variables'])
ws1.append(['... check routing, use toolbox.bat'])
a1 = ws1['A4']
a2 = ws1['A5']
ft = Font(color=colors.RED)
a1.font = ft
a2.font = ft
wb.save(filename=xl_output)
files.sort(key=lambda f: int(filter(str.isdigit, f)))
# Iterate through files
for f in files:
# Create sheets and export csv contents to it
print f
ws = wb.create_sheet(f)
# Read CSV file contents
# Call this if CSV is latin-1 encoding
#reader = unicode_csv_reader(open(os.path.join(basePath, f)))
reader = csv.reader(open(os.path.join(basePath, f)))
for row in reader:
ws.append(row)
ws['B1'] = 'Enter Word'
#format_cell_range(worksheet, '1', fmtHeader)
black_format = openpyxl.styles.colors.Color(rgb='000000')
hide_fill = openpyxl.styles.fills.PatternFill(patternType='solid',
bgColor=black_format)
for rows in ws.iter_cols(min_col=1, max_col=1, min_row=2):
for cell in rows:
cell.fill = hide_fill
green_format = openpyxl.styles.colors.Color(rgb='00FF00')
green_fill = openpyxl.styles.fills.PatternFill(
patternType='solid', bgColor=green_format)
ws.conditional_formatting.add(
'B2:B151', FormulaRule(formula=['A2=B2'], fill=green_fill))
# Delete default(first) sheet
wb.remove(wb['Sheet'])
# Save
print 'saving file = ' + subdir + '.xlsx'
wb.save(subdir + '.xlsx')
def colorMake(types, start, end):
return PatternFill(fill_type=types, start_color=start, end_color=end)
grayfill = colorMake('solid', 'd3d3d3', 'd3d3d3')
planfill = colorMake('solid', '1e90ff', '1e90ff') #計画セル色。デフォルトは青。
actualfill = colorMake('solid', 'E4007f', 'E4007f') #実績セル色。デフォルトはマゼンダ。
for rows in sheet.iter_rows(min_row=6, min_col=2, max_row=100, max_col=8):
for cell in rows:
cell.number_format = "m/d"
# 完了日の実績が入力されたら、その行のタスクをグレー色にする
sheet.conditional_formatting.add(
'A6:H100',
FormulaRule(formula=['NOT($G6="")'], stopIfTrue=True, fill=grayfill))
# 土曜日と日曜日をグレー色にする
sheet.conditional_formatting.add(
'J4:MF100',
FormulaRule(formula=['OR(WEEKDAY(J$5)=1, WEEKDAY(J$5)=7)'],
stopIfTrue=True,
fill=grayfill))
# 着手日と完了日の実績が入力されたら、着手日～完了日のセルを実績色にする（デフォルト色：マゼンタ）
sheet.conditional_formatting.add(
'J6:MF100',
FormulaRule(formula=['AND($E6<=J$4, $G6>=J$4)'],
stopIfTrue=True,
fill=actualfill))
# 着手日と完了日の予定が入力されたら、着手日～完了日のセルを予定色にする（デフォルト色：青）
sheet.conditional_formatting.add(
'J6:MF100',
def get_report(input):
concdict = {
k: [d.get(k) for d in input if k in d]
for k in set().union(*input)
}
IpaddList = concdict.get("ipAddress")
AbusescoreList = concdict.get("abuseConfidenceScore")
PublicList = concdict.get("isPublic")
IpverList = concdict.get("ipVersion")
IswlList = concdict.get("isWhitelisted")
CountrycList = concdict.get("countryCode")
UsageList = concdict.get("usageType")
IspList = concdict.get("isp")
DomainList = concdict.get("domain")
TotalreportsList = concdict.get("totalReports")
LastreportList = concdict.get("lastReportedAt")
wb = openpyxl.Workbook()
ws = wb.active
ws['A1'] = 'ipAddress'
ws['B1'] = 'abuseConfidenceScore'
ws['C1'] = 'isPublic'
ws['D1'] = 'ipVersion'
ws['E1'] = 'isWhitelisted'
ws['F1'] = 'countryCode'
ws['G1'] = 'usageType'
ws['H1'] = 'isp'
ws['I1'] = 'domain'
ws['J1'] = 'totalReports'
ws['K1'] = 'lastReportedAt'
border_style = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
clrrule = ColorScaleRule(start_type='num',
start_value='0',
start_color='00B050',
mid_type='num',
mid_value='25',
mid_color='FCA904',
end_type='num',
end_value='100',
end_color='CC0000')
ws.conditional_formatting.add('B2:B500', clrrule)
ws.conditional_formatting.add(
'A1:K500',
FormulaRule(formula=['NOT(ISBLANK(A1))'],
stopIfTrue=False,
border=border_style))
dataframeIpaddList = pd.DataFrame({'ipAddress': IpaddList})
for index, row in dataframeIpaddList.iterrows():
cell = 'A%d' % (index + 2)
ws[cell] = row[0]
dataframeAbusescoreList = pd.DataFrame(
{'abuseConfidenceScore': AbusescoreList})
for index, row in dataframeAbusescoreList.iterrows():
cell = 'B%d' % (index + 2)
ws[cell] = row[0]
dataframePublicList = pd.DataFrame({'isPublic': PublicList})
for index, row in dataframePublicList.iterrows():
cell = 'C%d' % (index + 2)
ws[cell] = row[0]
dataframeIpverList = pd.DataFrame({'ipVersion': IpverList})
for index, row in dataframeIpverList.iterrows():
cell = 'D%d' % (index + 2)
ws[cell] = row[0]
dataframeIswlList = pd.DataFrame({'isWhitelisted': IswlList})
for index, row in dataframeIswlList.iterrows():
cell = 'E%d' % (index + 2)
ws[cell] = row[0]
dataframeCountrycList = pd.DataFrame({'countryCode': CountrycList})
for index, row in dataframeCountrycList.iterrows():
cell = 'F%d' % (index + 2)
ws[cell] = row[0]
dataframeUsageList = pd.DataFrame({'usageType': UsageList})
for index, row in dataframeUsageList.iterrows():
cell = 'G%d' % (index + 2)
ws[cell] = row[0]
dataframeIspList = pd.DataFrame({'isp': IspList})
for index, row in dataframeIspList.iterrows():
cell = 'H%d' % (index + 2)
ws[cell] = row[0]
dataframeDomainList = pd.DataFrame({'domain': DomainList})
for index, row in dataframeDomainList.iterrows():
cell = 'I%d' % (index + 2)
ws[cell] = row[0]
dataframeTotalreportsList = pd.DataFrame(
{'totalReports': TotalreportsList})
for index, row in dataframeTotalreportsList.iterrows():
cell = 'J%d' % (index + 2)
ws[cell] = row[0]
dataframeLastreportList = pd.DataFrame({'lastReportedAt': LastreportList})
for index, row in dataframeLastreportList.iterrows():
cell = 'K%d' % (index + 2)
ws[cell] = row[0]
dims = {}
for i in ws.rows:
for cell in i:
if cell.value:
dims[cell.column_letter] = max(
(dims.get(cell.column_letter, 0), len(str(cell.value))))
for x, y in dims.items():
ws.column_dimensions[x].width = y
wb.save("results.xlsx")
def ConditionnalFormat(Onglet, Range, SearchText, Line=False, Color=None):
DefColor = {}
# Colors definitions
DefColor['RED'] = {
'pattern_fill':
PatternFill(start_color="FFC7CE",
end_color="FFC7CE",
fill_type='solid'),
'text':
Font(color="9C0103"),
'fill':
PatternFill(bgColor="FFC7CE")
}
DefColor['GREEN'] = {
'pattern_fill':
PatternFill(start_color="C6EFCE",
end_color="C6EFCE",
fill_type='solid'),
'text':
Font(color="006100"),
'fill':
PatternFill(bgColor="C6EFCE")
}
DefColor['ORANGE'] = {
'pattern_fill':
PatternFill(start_color="FFEB9C",
end_color="FFEB9C",
fill_type='solid'),
'text':
Font(color="9C5700"),
'fill':
PatternFill(bgColor="FFEB9C")
}
# Conditionnal rules
# Red if not find - green if found
if Color is None and Line is False:
Onglet.conditional_formatting.add(
Range,
FormulaRule(formula=[f'ISERROR(SEARCH("{SearchText}",{Range}))'],
stopIfTrue=True,
fill=DefColor['RED']['fill'],
font=DefColor['RED']['text']))
Onglet.conditional_formatting.add(
Range,
FormulaRule(
formula=[f'NOT(ISERROR(SEARCH("{SearchText}",{Range})))'],
stopIfTrue=True,
fill=DefColor['GREEN']['fill'],
font=DefColor['GREEN']['text']))
elif Color is not None and Line is False:
Onglet.conditional_formatting.add(
Range,
FormulaRule(
formula=[f'NOT(ISERROR(SEARCH("{SearchText}",{Range})))'],
stopIfTrue=True,
fill=DefColor[Color]['fill'],
font=DefColor[Color]['text']))
elif Color is not None and Line == True:
for row in range(2, Onglet.max_row + 1):
if Onglet.cell(row=row,
column=ord(Range.lower()) - 96).value == SearchText:
for row_cells in Onglet.iter_rows(min_row=row, max_row=row):
for cell in row_cells:
cell.fill = DefColor[Color]['pattern_fill']
cell.font = DefColor[Color]['text']
def main():
if not crt.Session.Connected:
crt.Dialog.MessageBox(
"This script currently requires a valid connection to a "
"Cisco Pix firewall or other similar device.\n\n"
"Please connect and then run this script again.")
return
script_tab = crt.GetScriptTab()
script_tab.Screen.Synchronous = True
script_tab.Screen.IgnoreEscape = True
screenRow = script_tab.Screen.CurrentRow
screenCol = script_tab.Screen.CurrentColumn
prompt = script_tab.Screen.Get(screenRow, 1, screenRow, screenCol).strip()
if 'config' in prompt:
crt.Dialog.MessageBox('Run script from user or priviliged exec only.')
return
switch_name = prompt[:-1]
# switch_name = 'test'
# Gather data
script_tab.Screen.Send("term len 0\n")
script_tab.Screen.WaitForString('\n')
mac_output = CaptureOutputOfCommand('show mac add dyn', prompt)
arp_output = CaptureOutputOfCommand('show arp | ex Incomplete', prompt)
script_tab.Screen.Send("term no len\n")
# Prep the output file
filename = switch_name + '.xlsx'
filename = os.path.join(os.environ['TMPDIR'], filename)
wb = Workbook()
wb.save(filename=filename)
ws1 = wb.active
# Prep MAC table output
ws1.title = 'MAC_Table'
ws1['A1'] = 'Vendor'
ws1['B1'] = 'MAC Address'
ws1['C1'] = 'Vlan'
ws1['D1'] = 'Interface'
ws1.column_dimensions['A'].width = 35
ws1.column_dimensions['B'].width = 13
ws1.column_dimensions['E'].width = 18
ws1['E1'] = datetime.datetime.now()
mac_index = 1
for row in mac_output.splitlines():
if 'dynamic' in row.lower():
mac_index += 1
row = row.split()
vlan = row[0]
mac_addr = row[1]
mac_interface = row[3]
ws1['A' + str(mac_index)] =\
'=VLOOKUP(LEFT(B%s,7),\
\'/Users/bklotz/Documents/OUI_Table.xlsx\'!Vendor_Table,2,FALSE)' \
% mac_index
ws1['B' + str(mac_index)] = mac_addr
ws1['C' + str(mac_index)] = vlan
ws1['D' + str(mac_index)] = mac_interface
# Prep ARP output
ws2 = wb.create_sheet(title='ARP_Table')
ws2['A1'] = 'Vendor'
ws2['B1'] = 'Address'
ws2['C1'] = 'Hardware Addr'
ws2['D1'] = 'Interface'
ws2.column_dimensions['A'].width = 35
ws2.column_dimensions['B'].width = 13
ws2.column_dimensions['C'].width = 13
ws2.column_dimensions['E'].width = 18
ws2['E1'] = datetime.datetime.now()
arp_index = 1 # Start index at 1 so that data starts in row 2
for row in arp_output.splitlines():
if 'Internet' in row:
arp_index += 1
row = row.split()
ip_addr = row[1]
arp_mac = row[3]
interface = row[5]
ws2['A' + str(arp_index)] =\
'=VLOOKUP(LEFT(C%s,7),\
\'/Users/bklotz/Documents/OUI_Table.xlsx\'!Vendor_Table,2,FALSE)' \
% arp_index
ws2['B' + str(arp_index)] = ip_addr
ws2['C' + str(arp_index)] = arp_mac
ws2['D' + str(arp_index)] = interface
# Add conditional formatting to highlight MACs not in ARP table
red_text = Font(color="660000")
red_fill = PatternFill(bgColor="FFCCCC")
frmla = 'ISNA(VLOOKUP(B2,ARP_Table!$C:$C,1,0))'
ws1.conditional_formatting.add(
'$B2:$B' + str(mac_index),
FormulaRule(formula=[frmla], font=red_text, fill=red_fill))
# Save spreadsheet
wb.save(filename)
# Open spreadsheet
os.system('open %s' % filename)
def get(self, request, *args, **kwargs):
deals = self.get_deals()
filename = '{day}_{branch}_{master}.xlsx'.format(
day=self.start.strftime('%Y-%m-%d'),
branch=self.branch.name,
master=self.masters[0].last_name)
wb = Workbook()
ws = wb.active
ws.title = '%s %s' % (self.branch.name, self.start.strftime('%Y%m%d'))
title_text = '{day} {branch}'.format(
branch=self.branch.name, day=self.start.strftime('%d.%m.%Y'))
title = ws.cell(row=1, column=1, value=title_text)
title.alignment = Alignment(horizontal='left')
title.font = Font(name='Calibri', size=11, bold=True)
ws.merge_cells('A1:C1')
labels = [
'Время', 'Клиент', 'День рождения', 'Телефон', 'Email', 'Статус',
'Стоимость', 'Предоплата', 'Безнал', 'Комментарий'
]
# names = [i[1] for i in data['tabs'][tab_key]['labels']]
columns = []
for index, label in enumerate(labels):
cell = ws.cell(row=2, column=index + 1, value=label)
cell.font = Font(name='Calibri', size=11, bold=True)
columns.append(cell.column)
row = 3
for deal in sorted(self.get_deals(), key=lambda x: x['start_iso']):
# has deals
for index, person in enumerate(deal['persons']):
row += 1
person_text = '%s' % (person['cache'].get('full_name'))
if person['control']:
person_text = '(Контроль) ' + person_text
if index == 0:
_ = ws.cell(row=row,
column=1,
value='%s (%s)' %
(deal['start_iso'][11:17], deal['minutes']))
_ = ws.cell(row=row,
column=6,
value=self.stages[deal['stage']]['label'])
_ = ws.cell(row=row, column=7, value=deal['cost'])
_ = ws.cell(row=row, column=8, value=deal['paid'])
_ = ws.cell(row=row, column=9, value=deal['paid_non_cash'])
_ = ws.cell(row=row, column=10, value=deal['comment'])
_ = ws.cell(row=row, column=2, value=person_text)
_ = ws.cell(row=row,
column=3,
value=person['cache'].get('age', ''))
_ = ws.cell(row=row,
column=4,
value=person['cache'].get('phone', ''))
_ = ws.cell(row=row,
column=5,
value=person['cache'].get('emails', ''))
border = Border(bottom=Side(border_style='thin', color='000000'))
ws.conditional_formatting.add(
'A1:K%s' % row, FormulaRule(formula=['E1=0'], border=border))
ws.column_dimensions['A'].width = 13
ws.column_dimensions['B'].width = 30
ws.column_dimensions['C'].width = 10
# row += 2
# for cellObj in ws['%s2:%s%s' % (columns[0], columns[-1], items_count)]:
# for cell in cellObj:
# import ipdb; ipdb.set_trace()
# print(cell.coordinate, cell.column)
# ws.column_dimensions[cell.column].bestFit = True
# ws[cell.coordinate].alignment = Alignment(horizontal='left')
response = HttpResponse(save_virtual_workbook(wb),
content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'inline; filename=%s' % urlquote(
filename).lower()
return response