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

Jeelben Manojbhai Bhuptani

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.

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

Mohamed Benziane

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 }")'