in static/watool/utilities/Code/exportAnswersToXLSX.py [0:0]
def lensTabCreation(
WACLIENT,
workloadId,
lens,
workbook,
allQuestionsForLens,
workloadName="",
AWSAccountId="",
workloadDescription=""
):
# Setup some formatting for the workbook
bold = workbook.add_format({'bold': True})
bold_border = workbook.add_format({
'border': 1,
'border_color': 'black',
'text_wrap': True
})
bold_border_bold = workbook.add_format({
'border': 1,
'border_color': 'black',
'text_wrap': True,
'font_size': 20,
'bold': True
})
heading = workbook.add_format({
'font_size': 24,
'bold': True
})
lineA = workbook.add_format({
'border': 1,
'border_color': 'black',
'bg_color': '#E0EBF6',
'align': 'top',
'text_wrap': True
})
lineB = workbook.add_format({
'border': 1,
'border_color': 'black',
'bg_color': '#E4EFDC',
'align': 'top',
'text_wrap': True
})
lineAnoborder = workbook.add_format({
'border': 0,
'top': 1,
'left': 1,
'right': 1,
'border_color': 'black',
'bg_color': '#E0EBF6',
'align': 'top',
'text_wrap': True
})
lineBnoborder = workbook.add_format({
'border': 0,
'top': 1,
'left': 1,
'right': 1,
'border_color': 'black',
'bg_color': '#E4EFDC',
'align': 'top',
'text_wrap': True
})
lineAhidden = workbook.add_format({
'border': 0,
'left': 1,
'right': 1,
'border_color': 'black',
'bg_color': '#E0EBF6',
'align': 'top',
'text_wrap': False,
'indent': 100
})
lineBhidden = workbook.add_format({
'border': 0,
'left': 1,
'right': 1,
'border_color': 'black',
'bg_color': '#E4EFDC',
'align': 'top',
'text_wrap': False,
'indent': 100
})
sub_heading = workbook.add_format()
sub_heading.set_font_size(20)
sub_heading.set_bold(True)
small_font = workbook.add_format()
small_font.set_font_size(9)
# Get the current version of Lens
logger.debug("Getting lens version for '"+lens+"'")
versionString = getCurrentLensVersion(WACLIENT,lens)
logger.debug("Adding worksheet using version "+versionString)
lensName = lens[0:18]
worksheet = workbook.add_worksheet((lensName+' v'+versionString))
# Print in landscape
worksheet.set_landscape()
# Set to 8.5x11 paper size
worksheet.set_paper(1)
# Set the column widths
worksheet.set_column('A:A', 11)
worksheet.set_column('B:B', 32)
worksheet.set_column('C:C', 56)
worksheet.set_column('D:D', 29)
worksheet.set_column('E:E', 57)
worksheet.set_column('F:F', 18)
worksheet.set_column('G:G', 70)
# Top of sheet
worksheet.merge_range('A1:G1', 'Workload Overview', heading)
worksheet.merge_range('A3:B3', 'Workload Name', bold_border_bold)
worksheet.merge_range('A4:B4', 'AWS Account ID', bold_border_bold)
worksheet.merge_range('A5:B5', 'Workload Description', bold_border_bold)
# If we are using an existing workload, then display the Name, ID, and Description at the top
# or else just make it blank
if WORKLOADID:
worksheet.write('C3', workloadName, bold_border)
accountIdParsed = AWSAccountId.split(':')[4]
worksheet.write('C4', accountIdParsed, bold_border)
worksheet.write('C5', workloadDescription, bold_border)
else:
worksheet.write('C3', '', bold_border)
worksheet.write('C4', '', bold_border)
worksheet.write('C5', '', bold_border)
worksheet.write('D3', 'Enter the name of system', small_font)
worksheet.write('D4', 'Enter 12-degit AWS account ID', small_font)
worksheet.write('D5', 'Briefly describe system architecture and workload, flow etc.', small_font)
# Subheadings for columns
worksheet.write('A8', 'Pillar', sub_heading)
worksheet.write('B8', 'Question', sub_heading)
worksheet.write('C8', 'Explanation', sub_heading)
worksheet.write('D8', 'Choice (Best Practice)', sub_heading)
worksheet.write('E8', 'Detail', sub_heading)
worksheet.write('F8', 'Response', sub_heading)
worksheet.write('G8', 'Notes (optional)', sub_heading)
# Freeze the top of the sheet
worksheet.freeze_panes(8,0)
# AutoFilter on the first two columns
worksheet.autofilter('A8:B8')
# Make it easier to print
worksheet.repeat_rows(1, 8)
worksheet.fit_to_pages(1, 99)
# Starting point for pillar questions
cellPosition = 8
# Starting cell look with lineA. Will switch back and forth
myCell = lineA
myCellhidden = lineAhidden
myCellnoborder = lineAnoborder
for pillar in PILLAR_PARSE_MAP:
# This is the question number for each pillar (ex: OPS1, OPS2, etc)
qNum = 1
# The query will return all questions for a lens and pillar
jmesquery = "[?PillarId=='"+pillar+"']"
allQuestionsForPillar = jmespath.search(jmesquery, allQuestionsForLens)
# For each of the possible answers, parse them and put into the Worksheet
for answers in allQuestionsForPillar:
# List all best practices
questionTitle = PILLAR_PARSE_MAP[answers['PillarId']]+str(qNum)+" - "+answers['QuestionTitle']
qDescription, qImprovementPlanUrl, qHelpfulResourceUrl, qNotes = getQuestionDetails(WACLIENT,workloadId,lens,answers['QuestionId'])
# Some of the questions have extra whitespaces and I need to remove those to fit into the cell
qDescription = qDescription.replace('\n ','').replace(' ','').replace('\t', '').replace('\n', '')
qDescription = qDescription.rstrip()
qDescription = qDescription.strip()
logger.debug("Working on '"+questionTitle+"'")
logger.debug("It has answers of: "+json.dumps(answers['SelectedChoices']))
cellID = cellPosition + 1
# If the question has been answered (which we do for the TEMP workload) we grab the URL and parse for the HTML content
if qImprovementPlanUrl:
jmesquery = "[?QuestionId=='"+answers['QuestionId']+"'].Choices[].ChoiceId"
choiceList = jmespath.search(jmesquery, allQuestionsForLens)
ipList = getImprovementPlanItems(WACLIENT,workloadId,lens,answers['QuestionId'],answers['PillarId'],qImprovementPlanUrl,choiceList)
else:
ipList = []
startingCellID=cellID
# If its the first time through this particular pillar question:
# I want to only write the name once, but I need to fill in
# each cell with the same data so the autosort works properly
# (else it will only show the first best practice)
firstTimePillar=True
for choices in answers['Choices']:
# Write the pillar name and question in every cell for autosort, but only show the first one
cell = 'A'+str(cellID)
if firstTimePillar:
worksheet.write(cell, PILLAR_PROPER_NAME_MAP[pillar], myCellnoborder)
cell = 'B'+str(cellID)
worksheet.write(cell, questionTitle, myCellnoborder)
firstTimePillar=False
else:
worksheet.write(cell, PILLAR_PROPER_NAME_MAP[pillar], myCellhidden)
cell = 'B'+str(cellID)
worksheet.write(cell, questionTitle, myCellhidden)
# Start writing each of the BP's, details, etc
cell = 'D'+str(cellID)
Title = choices['Title'].replace(' ','').replace('\t', '').replace('\n', '')
if any(choices['ChoiceId'] in d for d in ipList):
worksheet.write_url(cell, ipList[choices['ChoiceId']], myCell, string=Title)
ipItemHTML, questionIdText = getImprovementPlanHTMLDescription(ipList[choices['ChoiceId']],answers['PillarId'])
htmlString = ipItemHTML.text
htmlString = htmlString.replace('\n ','').replace(' ','').replace('\t', '').strip().rstrip()
# print(htmlString)
worksheet.write_comment(cell, htmlString, {'author': 'Improvement Plan'})
else:
worksheet.write(cell,Title,myCell)
# Add all Details for each best practice/choice
cell = 'E'+str(cellID)
# Remove all of the extra spaces in the description field
Description = choices['Description'].replace('\n ','')
Description = Description.replace('\n ','')
Description = Description.replace(' ','').replace('\t', '').replace('\n', '')
Description = Description.rstrip()
Description = Description.strip()
worksheet.write(cell, Description ,myCell)
# If this is an existing workload, we will show SELECTED if the have it checked
# I would love to use a XLSX checkbox, but this library doesn't support it
cell = 'F'+str(cellID)
responseText = ""
if choices['ChoiceId'] in answers['SelectedChoices']:
responseText = "SELECTED"
else:
responseText = ""
worksheet.write(cell, responseText ,myCell)
cellID+=1
# We are out of the choice/detail/response loop, so know how many rows were consumed
# and we can create the explanation and notes field to span all of them
# Explanantion field
cellMerge = 'C'+str(startingCellID)+':C'+str(cellID-1)
worksheet.merge_range(cellMerge, qDescription,myCell)
# Notes field
cellMerge = 'G'+str(startingCellID)+':G'+str(cellID-1)
if WORKLOADID:
worksheet.merge_range(cellMerge, qNotes, myCell)
else:
worksheet.merge_range(cellMerge, "", myCell)
cellID-=1
# Increase the question number
qNum += 1
# Reset the starting cellPosition to the last cellID
cellPosition = cellID
# Reset the cell formatting to alternate between the two colors
if myCell == lineA:
myCell = lineB
myCellhidden = lineBhidden
myCellnoborder = lineBnoborder
else:
myCell = lineA
myCellhidden = lineAhidden
myCellnoborder = lineAnoborder