def test_hyperlink(WriteOnlyWorksheet):
ws = WriteOnlyWorksheet
cell = WriteOnlyCell(ws, 'should have hyperlink')
cell.hyperlink = 'http://bbc.co.uk'
ws.append([])
ws.append([cell])
assert cell.hyperlink.ref == "A2"
ws.close()
def serialise(self, worksheet, record, triggered: Triggered):
linked_cell = WriteOnlyCell(worksheet, self.get_internal_id(record))
linked_cell.hyperlink = self.get_noms_ops_url(record)
linked_cell.style = 'Hyperlink'
row = {
'Notification rule': self.rule_description,
'Internal ID': linked_cell,
}
additional_header = self.additional_headers.get(type(self.rule), None)
if additional_header:
row[additional_header['header']] = triggered.kwargs[additional_header['triggered_kwarg']]
return row
def export_email_approval_list(local_filename,
found_attachments,
tdocs_without_emails=None,
tdoc_data=None):
if (local_filename is None) or (local_filename == ''):
return
# found_attachments -> collections.namedtuple('RevisionDoc', 'time tdoc filename absolute_url sender_name sender_address chairman_notes')
print('Starting email approval export: {0} emails'.format(
len(found_attachments)))
# Faster variant writing first most data not using VBA
wb = openpyxl.Workbook(write_only=True)
ws = wb.create_sheet()
ws.title = "Revisions"
# Add title row
ws.append([
'TD#', 'Time', 'Filename mention', 'Sender', 'Company', 'Email', 'AI',
"Chairman's notes"
])
# Add email entries
for idx, item in enumerate(found_attachments, start=2):
filename_cell = WriteOnlyCell(ws, value=item.filename)
sender_name_cell = WriteOnlyCell(ws, value=item.sender_name)
link_cell = WriteOnlyCell(ws, value='Link')
# Link to file. May not always be a path
if item.absolute_url != '':
filename_cell.hyperlink = 'file:///' + item.absolute_url
filename_cell.font = Font(underline="single", color='00EA0A8E')
# Link to author
sender_name_cell.hyperlink = 'mailto:' + item.sender_address
sender_name_cell.font = Font(underline="single", color='00EA0A8E')
# Link to email
link_cell.hyperlink = 'file:///' + item.email_url
link_cell.font = Font(underline="single", color='00EA0A8E')
# Write row
ws.append([
item.tdoc, item.time, filename_cell, sender_name_cell,
get_company_name_based_on_email(item.sender_address), link_cell,
str(item.ai_folder),
str(item.chairman_notes)
])
if tdocs_without_emails is not None and tdoc_data is not None:
ws = wb.create_sheet()
ws.title = "TDocs without emails"
ws.append([
'TD#', 'AI', 'Type', 'Doc For', 'Title', 'Source', 'Rel',
'Work Item', 'Comments'
])
tdocs_info = tdoc_data.tdocs.loc[list(tdocs_without_emails), [
'AI', 'Type', 'Doc For', 'Title', 'Source', 'Rel', 'Work Item',
'Comments'
]]
print('{0} TDocs without matching emails'.format(len(
tdocs_info.index)))
for row_index, row in tdocs_info.iterrows():
ws.append([
row_index,
str(row['AI']),
str(row['Type']),
str(row['Doc For']),
str(row['Title']),
str(row['Source']),
str(row['Rel']),
str(row['Work Item']),
str(row['Comments'])
])
print('Saving Excel table structure')
wb.save(filename=local_filename)
print('Closing Excel File')
wb.close()
print('Excel File closed')
# Only necessary things with VBA (much slower)
try:
print('Applying Excel formatting')
wb = open_excel_document(filename=local_filename)
# ws = wb.ActiveSheet
ws = wb.Sheets("Revisions")
ws.Range("A:A").ColumnWidth = 14
ws.Range("B:B").ColumnWidth = 18
ws.Range(
"C:C"
).ColumnWidth = 30 # Reduced to 30 as we now just send around revision numbers
ws.Range("D:D").ColumnWidth = 40
ws.Range("E:E").ColumnWidth = 17
ws.Range("F:F").ColumnWidth = 9
ws.Range("G:G").ColumnWidth = 25
ws.Range("H:H").ColumnWidth = 60
ws.Range("A:B").HorizontalAlignment = -4108
ws.Range("G:G").HorizontalAlignment = -4108
ws.Range("A:H").VerticalAlignment = -4108
ws.Range("A:H").WrapText = True
ws.Range("H:H").WrapText = True
ws.Range("A1:H1").Font.Bold = True
set_first_row_as_filter(wb, 'Revisions')
ws.AutoFilter.Sort.SortFields.Clear()
# https://docs.microsoft.com/en-us/office/vba/api/excel.xlsortorder
xlAscending = 1
# https://stackoverflow.com/questions/11766118/excel-constants-for-sorting
xlSortOnValues = 0
ws.AutoFilter.Sort.SortFields.Add(Order=xlAscending,
SortOn=xlSortOnValues,
Key=ws.Range("G:G")) # AI
ws.AutoFilter.Sort.SortFields.Add(Order=xlAscending,
SortOn=xlSortOnValues,
Key=ws.Range("A:A")) # TD
ws.AutoFilter.Sort.SortFields.Add(Order=xlAscending,
SortOn=xlSortOnValues,
Key=ws.Range("B:B")) # Time
ws.AutoFilter.Sort.Apply()
if tdocs_without_emails is not None and tdoc_data is not None:
ws = wb.Sheets("TDocs without emails")
# TD, AI, Type, Doc For, Title, source, rel, work item, comments
ws.Range("A:A").ColumnWidth = 11
ws.Range("B:B").ColumnWidth = 7
ws.Range("C:C").ColumnWidth = 11
ws.Range("D:D").ColumnWidth = 12
ws.Range("E:E").ColumnWidth = 50
ws.Range("F:F").ColumnWidth = 45
ws.Range("G:G").ColumnWidth = 7
ws.Range("H:H").ColumnWidth = 7
ws.Range("I:I").ColumnWidth = 50
ws.Range("A:I").HorizontalAlignment = -4108
ws.Range("A:I").VerticalAlignment = -4108
ws.Range("A:I").WrapText = True
ws.Range("A1:I1").Font.Bold = True
set_first_row_as_filter(wb,
'TDocs without emails',
already_activated=True)
ws.AutoFilter.Sort.SortFields.Clear()
ws.AutoFilter.Sort.SortFields.Add(Order=xlAscending,
SortOn=xlSortOnValues,
Key=ws.Range("B:B")) # AI
ws.AutoFilter.Sort.SortFields.Add(Order=xlAscending,
SortOn=xlSortOnValues,
Key=ws.Range("A:A")) # TD
ws.AutoFilter.Sort.Apply()
print('Finished email approval export')
revisions_ws = wb.Worksheets('Revisions').Activate()
add_pivot_table_to_workbook_active_sheet(wb,
cell_range="A:G",
data_field="TD#",
row_field="Sender",
column_field=None,
ws_name="Delegate Summary")
revisions_ws = wb.Worksheets('Revisions').Activate()
add_pivot_table_to_workbook_active_sheet(wb,
cell_range="A:G",
data_field="TD#",
row_field="Company",
column_field=None,
ws_name="Company Summary")
wb.SaveAs(local_filename)
except:
traceback.print_exc()
