I am trying to fetch issues key with its hyperlink in excel sheet using python.

Jeelben Manojbhai Bhuptani
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
February 23, 2024

I am unable to fetch the link of issue in my excel sheet.

e.g - In Image, Instead of Err:510 I want EXTTHSE-1957  with its hyperlink. The link is shown in equation box in image.

23.02.2024_14.25.53_REC.png

Code :

 

url = 'https://vvdntech.atlassian.net/jira/software/c/projects/EXTTHSE/issues/'

path = os.getcwd()
files = os.listdir(path)

Option = {'server': "https://vvdntech.atlassian.net/"}
Jira_1 = JIRA(options=Option, basic_auth=("xxxxx","yyyy"))
version = str(input(
"Please enter latest release version to list all the closed issues & Open issue in the latest release \n (Provide "
"version enclosed with '') :"))

priority = ['Highest', 'High', 'Medium', 'Low', 'Lowest']


def QAOpenIssues():
SNo = 0
lstSNo = []
lstkey = []
# lstSummary = []
# lstPriority = []

for p in priority:
for singleIssue in Jira_1.search_issues(
jql_str=f'project = "EXTTHSE" AND type = Bug '):

k = str(singleIssue.key)
Issue_link = f"=HYPERLINK({url + singleIssue.key})"
lstkey.append(Issue_link)
SNo += 1
lstSNo.append(SNo)
#
data = {
'Sr.No': lstSNo,
'Key': lstkey,

}

df = pd.DataFrame(data)
df.to_excel('QA_OpenIssues.xlsx', index=False)

print("QA_OpenIssues.xlsx successfully saved.")


QAOpenIssues()


def formating():
for file in files:
if file.endswith('.xlsx'):
wb = openpyxl.load_workbook(file)
ws = wb['Sheet1']
for cell in ws['1:1']:
cell.font = Font(bold=True, size=11)
cell.fill = PatternFill(patternType='solid', start_color='9bb7d5')

for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=None):
for cell in row:
border = Side(border_style='thin', color='000000')
cell.border = Border(top=border, left=border, right=border, bottom=border)

wb.save(file)

print("Fomatting is done in all the sheets.")


formating()

1 answer

0 votes
Mohamed Benziane
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 31, 2024

Hi,

The issue is in the EXCEL file, you need to pass an url surronding by double quote <">. So you code need to be something like this :

Issue_link = f'=HYPERLINK("{url + singleIssue.key }")'

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
TAGS
AUG Leaders

Atlassian Community Events