def parse_cell(self, ws, row, col):
cell = ws.cell(row, col)
style_name = None
if cell.has_style:
style_key = (
copy.copy(cell.font),
copy.copy(cell.fill),
copy.copy(cell.border),
copy.copy(cell.alignment),
copy.copy(cell.number_format),
copy.copy(cell.protection),
)
if style_key not in self.styles:
new_style_name = "style{}".format(len(self.styles))
new_style = styles.NamedStyle(new_style_name)
new_style.font = style_key[0]
new_style.fill = style_key[1]
new_style.border = style_key[2]
new_style.alignment = style_key[3]
new_style.number_format = style_key[4]
new_style.protection = style_key[5]
self.styles[style_key] = new_style
style_name = new_style_name
else:
style_name = self.styles[style_key].name
col_letter = utils.col_int_to_str(col)
col_width = ws.column_dimensions[col_letter].width
col_index = col
while not col_width:
col_letter = utils.col_int_to_str(col_index)
col_width = ws.column_dimensions[col_letter].width
col_index -= 1
node_kwargs = {
"base_cell": (row, col),
"style": style_name,
"row_height": nodes.Const(value=ws.row_dimensions[row].height),
"col_width": nodes.Const(value=col_width),
}
node_class = nodes.CellOutput
if cell.value:
if isinstance(cell.value, str) and cell.value.startswith("="):
value = cell.value
node_kwargs["args"] = self.parse_func_args(value)
node_class = nodes.FuncCellOutput
else:
value = self.parse_value(cell.value)
else:
value = None
node_kwargs["value"] = value
if (row, col) in self.merged_cells:
node_kwargs["merge"] = nodes.Merge(
rows=nodes.Const(value=self.merged_cells[(row, col)][0]),
cols=nodes.Const(value=self.merged_cells[(row, col)][1]),
)
cell_output = node_class(**node_kwargs)
return cell_output
## (i.e.- f"{kwarg.method(*args,**kw)}" -> "{result}".format(result = kwarg.method(*args,**kw)) -> "result")
def MONTHTABLENAME(month):
return f"{month.strftime('%B_%Y')}_Inventory"
def MONTHTITLE(month):
return f"{month.strftime('%B %Y')}"
####################################################################
"""-----------------------------------------------------------------
STYLE SETUP
-----------------------------------------------------------------"""
####################################################################
TITLESTYLE = xlstyle.NamedStyle(name='TITLESTYLE')
TITLESTYLE.font = xlstyle.Font(bold=True, size=16)
TITLESTYLE.alignment = xlstyle.Alignment(horizontal="center")
TOTALCELLSTYLE = xlstyle.NamedStyle(name="TOTALCELLSTYLE")
TOTALCELLSTYLE.font = xlstyle.Font(bold=True, size=12, color='FFFFFF')
ALLTHICKBORDERS = xlstyle.Border(
**{
side: xlstyle.Side(border_style='thick', color='000000')
for side in ['left', 'right', 'top', 'bottom']
})
TOTALCELLSTYLE.border = ALLTHICKBORDERS
TOTALCELLSTYLE.fill = xlstyle.PatternFill(fill_type="solid",
start_color='BFBFBF')
MONTHTABLESTYLE = xltable.TableStyleInfo(name="TableStyleMedium4",
def __init__(self,
filename=None,
model_dir=None,
license_summary=False,
params=None):
self.wb = None
self.filename = filename
self.params = params
if self.params is None:
self.params = {}
if filename is not None:
self.wb = openpyxl.load_workbook(filename=filename)
else:
self.wb = openpyxl.Workbook()
self.ws_models = self.wb.active
self.ws_models.title = 'Index'
thin = styles.Side(
border_style=self.params.get('side_border', 'thin'),
color=self.params.get('side_color', '999999'))
for i in range(1, len(group_styles) + 1):
key = 'suns_group_%s' % i
name = 'suns_group_entry_%s' % i
style = styles.NamedStyle(name=name)
color = group_styles[key]['group_color']
# self.params.get('group_color', color)
style.fill = styles.PatternFill('solid', fgColor=color)
style.font = styles.Font()
style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
style.alignment = styles.Alignment(horizontal='center',
wrapText=True)
self.wb.add_named_style(style)
name = 'suns_group_text_%s' % i
style = styles.NamedStyle(name=name)
style.fill = styles.PatternFill('solid', fgColor=color)
style.font = styles.Font()
style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
style.alignment = styles.Alignment(horizontal='left',
wrapText=True)
self.wb.add_named_style(style)
name = 'suns_point_entry_%s' % i
style = styles.NamedStyle(name=name)
color = group_styles[key]['point_color']
# self.params.get('group_color', color)
style.fill = styles.PatternFill('solid', fgColor=color)
style.font = styles.Font()
style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
style.alignment = styles.Alignment(horizontal='center',
wrapText=True)
self.wb.add_named_style(style)
name = 'suns_point_text_%s' % i
style = styles.NamedStyle(name=name)
style.fill = styles.PatternFill('solid', fgColor=color)
style.font = styles.Font()
style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
style.alignment = styles.Alignment(horizontal='left',
wrapText=True)
self.wb.add_named_style(style)
if 'suns_hdr' not in self.wb.named_styles:
hdr_style = styles.NamedStyle(name='suns_hdr')
hdr_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('hdr_color', 'dddddd'))
hdr_style.font = styles.Font(bold=True)
hdr_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
hdr_style.alignment = styles.Alignment(horizontal='center',
wrapText=True)
self.wb.add_named_style(hdr_style)
if 'suns_group_entry' not in self.wb.named_styles:
model_entry_style = styles.NamedStyle(
name='suns_group_entry')
model_entry_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('group_color', 'fff9e5'))
model_entry_style.font = styles.Font()
model_entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
model_entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(model_entry_style)
if 'suns_group_text' not in self.wb.named_styles:
model_text_style = styles.NamedStyle(
name='suns_group_text')
model_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('group_color', 'fff9e5'))
model_text_style.font = styles.Font()
model_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
model_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(model_text_style)
if 'suns_point_entry' not in self.wb.named_styles:
fixed_entry_style = styles.NamedStyle(
name='suns_point_entry')
fixed_entry_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('point_color', 'e6f2ff'))
fixed_entry_style.font = styles.Font()
fixed_entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
fixed_entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(fixed_entry_style)
if 'suns_point_text' not in self.wb.named_styles:
fixed_text_style = styles.NamedStyle(
name='suns_point_text')
fixed_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('point_color', 'e6f2ff'))
fixed_text_style.font = styles.Font()
fixed_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
fixed_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(fixed_text_style)
if 'suns_point_variable_entry' not in self.wb.named_styles:
fixed_entry_style = styles.NamedStyle(
name='suns_point_variable_entry')
fixed_entry_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('point_variable_color',
'ecf9ec'))
fixed_entry_style.font = styles.Font()
fixed_entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
fixed_entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(fixed_entry_style)
if 'suns_point_variable_text' not in self.wb.named_styles:
fixed_text_style = styles.NamedStyle(
name='suns_point_variable_text')
fixed_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('point_variable_color',
'ecf9ec'))
fixed_text_style.font = styles.Font()
fixed_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
fixed_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(fixed_text_style)
if 'suns_symbol_entry' not in self.wb.named_styles:
repeating_entry_style = styles.NamedStyle(
name='suns_symbol_entry')
repeating_entry_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('symbol_color', 'fafafa'))
repeating_entry_style.font = styles.Font()
repeating_entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
repeating_entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(repeating_entry_style)
if 'suns_symbol_text' not in self.wb.named_styles:
repeating_text_style = styles.NamedStyle(
name='suns_symbol_text')
repeating_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('symbol_color', 'fafafa'))
repeating_text_style.font = styles.Font()
repeating_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
repeating_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(repeating_text_style)
if 'suns_comment' not in self.wb.named_styles:
symbol_text_style = styles.NamedStyle(name='suns_comment')
symbol_text_style.fill = styles.PatternFill(
'solid',
fgColor=self.params.get('comment_color', 'dddddd'))
# fgColor=self.params.get('symbol_color', 'fffcd9'))
symbol_text_style.font = styles.Font()
symbol_text_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
symbol_text_style.alignment = styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(symbol_text_style)
if 'suns_entry' not in self.wb.named_styles:
entry_style = styles.NamedStyle(name='suns_entry')
entry_style.fill = styles.PatternFill('solid',
fgColor='ffffff')
entry_style.border = styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
entry_style.alignment = styles.Alignment(
horizontal='center', wrapText=True)
self.wb.add_named_style(entry_style)
if 'suns_text' not in self.wb.named_styles:
text_style = styles.NamedStyle(name='suns_text')
text_style.font = styles.Font()
text_style.alignment = styles.Alignment(horizontal='left',
wrapText=True)
self.wb.add_named_style(text_style)
if 'suns_hyper' not in self.wb.named_styles:
hyper_style = openpyxl.styles.NamedStyle(name='suns_hyper')
hyper_style.font = openpyxl.styles.Font(color='0000ee',
underline='single')
hyper_style.alignment = openpyxl.styles.Alignment(
horizontal='left', wrapText=True)
self.wb.add_named_style(hyper_style)
for i in range(len(models_hdr)):
self.set_cell(self.ws_models, 1, i + 1, models_hdr[i][0],
'suns_hdr')
if models_hdr[i][1]:
self.ws_models.column_dimensions[chr(
65 + i)].width = models_hdr[i][1]
def project_xlsx(request, pk):
response = None
try:
project = Project.objects.get(pk=pk)
if project.is_user_can_view(request.user):
wb = Workbook()
ws = wb.active
#Define column size
wb.active.column_dimensions['A'].width = 28
wb.active.column_dimensions['B'].width = 10
wb.active.column_dimensions['C'].width = 10
wb.active.column_dimensions['D'].width = 50
wb.active.column_dimensions['E'].width = 30
wb.active.column_dimensions['F'].width = 50
#Add project data.
ws['A1'] = "Project Name:"
ws['A2'] = "Date:"
ws['A3'] = "Auditors:"
ws['B1'] = project.name
ws['B2'] = project.added
#Construct the auditor string
pentester_str = ""
previous = ""
for pentester in project.pentesters.all():
pentester_str = "{}{}{} - {} {}".format(
pentester_str, previous, pentester.username,
pentester.first_name, pentester.last_name)
previous = ", "
ws['B3'] = pentester_str
#Beautify project data
ws.merge_cells('B1:F1')
ws.merge_cells('B2:F2')
ws.merge_cells('B3:F3')
projectHeaderStyle = styles.NamedStyle(name='project_header_style')
projectHeaderStyle.font = styles.Font(name='Calibri',
size=14,
bold=True,
color='000000')
projectHeaderStyle.fill = styles.PatternFill(patternType='solid',
fgColor='00B0F0')
projectHeaderStyle.alignment = styles.Alignment(horizontal='left')
ws['A1'].style = projectHeaderStyle
ws['A2'].style = projectHeaderStyle
ws['A3'].style = projectHeaderStyle
projectValueStyle = styles.NamedStyle(name='project_value_style')
projectValueStyle.font = styles.Font(name='Calibri',
size=14,
italic=True,
color='000000')
projectValueStyle.fill = styles.PatternFill(patternType='solid',
fgColor='00B0F0')
projectValueStyle.alignment = styles.Alignment(horizontal='left')
ws['B1'].style = projectValueStyle
ws['B2'].style = projectValueStyle
ws['B3'].style = projectValueStyle
ws['B2'].number_format = 'YYYY MMM DD'
#Add column header.
ws['A5'] = "Assessment"
ws['B5'] = "Sev"
ws['C5'] = "CVSS"
ws['D5'] = "Title"
ws['E5'] = "Asset"
ws['F5'] = "Labels"
columnHeaderStyle = styles.NamedStyle(name='column_header_style')
columnHeaderStyle.font = styles.Font(name='Calibri',
size=12,
bold=True,
color='000000')
columnHeaderStyle.fill = styles.PatternFill(patternType='solid',
fgColor='92D050')
columnHeaderStyle.alignment = styles.Alignment(horizontal='center')
ws['A5'].style = columnHeaderStyle
ws['B5'].style = columnHeaderStyle
ws['C5'].style = columnHeaderStyle
ws['D5'].style = columnHeaderStyle
ws['E5'].style = columnHeaderStyle
ws['F5'].style = columnHeaderStyle
#Fill the report
criticalStyle = styles.NamedStyle(name='critical_style')
criticalStyle.font = styles.Font(name='OCR A Extended',
color='FFFFFF')
criticalStyle.fill = styles.PatternFill(patternType='solid',
fgColor='343a40')
criticalStyle.alignment = styles.Alignment(horizontal='center')
highStyle = styles.NamedStyle(name='high_style')
highStyle.font = styles.Font(name='OCR A Extended', color='FFFFFF')
highStyle.fill = styles.PatternFill(patternType='solid',
fgColor='dc3545')
highStyle.alignment = styles.Alignment(horizontal='center')
mediumStyle = styles.NamedStyle(name='medium_style')
mediumStyle.font = styles.Font(name='OCR A Extended',
color='212529')
mediumStyle.fill = styles.PatternFill(patternType='solid',
fgColor='ffc107')
mediumStyle.alignment = styles.Alignment(horizontal='center')
lowStyle = styles.NamedStyle(name='low_style')
lowStyle.font = styles.Font(name='OCR A Extended', color='FFFFFF')
lowStyle.fill = styles.PatternFill(patternType='solid',
fgColor='28a745')
lowStyle.alignment = styles.Alignment(horizontal='center')
infoStyle = styles.NamedStyle(name='info_style')
infoStyle.font = styles.Font(name='OCR A Extended', color='FFFFFF')
infoStyle.fill = styles.PatternFill(patternType='solid',
fgColor='6c757d')
infoStyle.alignment = styles.Alignment(horizontal='center')
line = 6
for assessment in project.assessment_set.all():
for hit in assessment.displayable_hits():
ws.cell(row=line, column=1).value = assessment.name
ws.cell(row=line,
column=2).value = "P{}".format(hit.severity)
ws.cell(row=line, column=3).value = hit.get_cvss_value()
ws.cell(row=line, column=4).value = hit.title
ws.cell(row=line, column=5).value = hit.asset
label_str = ""
previous = ""
for label in hit.labels.all():
label_str = "{}{}{}".format(label_str, previous,
label.title)
previous = ", "
ws.cell(row=line, column=6).value = label_str
#Apply style from value.
if hit.severity == 1:
ws.cell(row=line, column=2).style = criticalStyle
elif hit.severity == 2:
ws.cell(row=line, column=2).style = highStyle
elif hit.severity == 3:
ws.cell(row=line, column=2).style = mediumStyle
elif hit.severity == 4:
ws.cell(row=line, column=2).style = lowStyle
elif hit.severity == 5:
ws.cell(row=line, column=2).style = infoStyle
try:
if float(hit.get_cvss_value()) < 4.0:
ws.cell(row=line, column=3).style = infoStyle
elif float(hit.get_cvss_value()) < 4.0:
ws.cell(row=line, column=3).style = lowStyle
elif float(hit.get_cvss_value()) < 7.0:
ws.cell(row=line, column=3).style = mediumStyle
elif float(hit.get_cvss_value()) < 9.0:
ws.cell(row=line, column=3).style = highStyle
else:
ws.cell(row=line, column=3).style = criticalStyle
except ValueError:
ws.cell(row=line, column=3).style = infoStyle
line = line + 1
ws.auto_filter.ref = "A5:F{}".format(line)
#Prepare HTTP response.
response = Response(save_virtual_workbook(wb))
response.content_type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
response[
'Content-Disposition'] = 'attachment; filename=' + project.name + ".xlsx"
response.accepted_renderer = BinaryRenderer()
response.accepted_media_type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
response.renderer_context = {}
wb.close()
else:
response = Response(status=status.HTTP_403_FORBIDDEN)
except Flag.DoesNotExist:
response = Response(status=status.HTTP_404_NOT_FOUND)
return response