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