Writing hyperlinks into XlsxWriter tables

Question

I'm working on a script that gets its data from the Jira REST API and presents relevant details to the user as a table in an Excel file generated by XlsxWriter. Ideally, I would like the first column to display a hyperlink that leads the user back to the page in Jira where the information originated. Preferably, I would do that by creating hyperlinks that use just the issue key as the link text (rather than the whole URL).

The Working with Worksheet Tables documentation provides the following example of how to pass data into tables:

data = [
    ['Apples', 10000, 5000, 8000, 6000],
    ['Pears',   2000, 3000, 4000, 5000],
    ['Bananas', 6000, 6000, 6500, 6000],
    ['Oranges',  500,  300,  200,  700],

]

worksheet.add_table('B3:F7', {'data': data})

The write_url() method documentation also provides these examples:

worksheet.write_url(0, 0, 'https://www.python.org/')
worksheet.write_url('A2', 'https://www.python.org/')

What I would like to do, though, is provide the hyperlink details as part of the data list. In the example above, I'm envisioning the hyperlink details taking the place of the Apples, Pears, Bananas and Oranges strings (such that each might have link text like 'KEY-1' associated with a URL like 'https://jiraserver/browse/KEY-1' and so on). Is there a convenient way to do that?

user_13084905 · Answer

Ah, rather than relying on XlsxWriter for this purpose, I see that the HYPERLINK function in Excel will provide the desired effect.

I simply need to provide something like the following at the desired position in the list.

issue_hyperlink = f'=HYPERLINK("{issue_url}", "{issue_key}")'

This will work for what I have in mind.

user_10238 · Answer

Here is one way to do it:

import xlsxwriter

workbook = xlsxwriter.Workbook('table_with_links.xlsx')
worksheet = workbook.add_worksheet()


# Some sample data for the table.
data = [
    ['KEY-1', 10000, 5000, 8000, 6000],
    ['KEY-2', 2000, 3000, 4000, 5000],
    ['KEY-3', 6000, 6000, 6500, 6000],
    ['KEY-4', 500, 300, 200, 700],

]

# Set the columns widths.
worksheet.set_column('A:E', 10)

# Add a table to the worksheet.
worksheet.add_table('A1:E5')

# Write the data to the table.
for row_num, row_data in enumerate(data):
    for col_num, col_data in enumerate(row_data):
        if col_num == 0:
            worksheet.write_url(row_num + 1, col_num,
                                f'https://jiraserver/browse/{col_data}',
                                None, col_data)
        else:
            worksheet.write(row_num + 1, col_num, col_data)

workbook.close()

Output:

I prefer using the real hyperlink instead of the HYPERLINK formula because I think it looks less confusing to the end user.

Writing hyperlinks into XlsxWriter tables

2 Answers