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