CODEWITHSUNDEEP
pythonpandasxlsxwriter
Joao Vitorino
Joao Vitorino

Reputation: 3256

hyperlink in pandas (dataframe to excel)

I'm trying to create a excel file with url link in a specific column. Like this

Table with lionk

def fill_table(table):
    if elapsed_time > datetime.timedelta(minutes=args.mtime):
        table.loc[len(table)] = [hostname, int(trigger_id), description, eventStartDate, eventEndDate, elapsed_time, message, useralias, link]
...
writer = pd.ExcelWriter(args.output, engine='xlsxwriter')

I tried to use the excel hyperlink formula in the link variable

link = '=HYPERLINK(\"{0}/tr_events.php?triggerid={1}&eventid={2}\"; \"{3}\")'.format(args.url, trigger_id,event['eventid'], event['name'])

But I get a error message when open the file and the column 'link' fill with zero's

Upvotes: 1

Views: 6626

Answers (3)

pymen
pymen

Reputation: 6539

How to write hyperlinks to excel by Pandas with honor to blank/null values

Helpers

def df_convert_to_url(df, column):
    df[column] = df[column].apply(df_apply_hyperlink, axis=1)


def df_apply_hyperlink(row_value):
    if pd.notnull(row_value) and row_value != '':
        return f'=HYPERLINK("{row_value}")'
    else:
        return row_value

Usage

df = pd.DataFrame(data)
df_convert_to_url(df, "your_column1")
df_convert_to_url(df, "your_column2")
df.to_excel(excel_data, index=False)

Upvotes: 0

jmcnamara
jmcnamara

Reputation: 41544

Probably you need a comma (,) instead of a semi-colon (;) in the formula. This is because Excel stores formulas in US-style syntax (see Non US Excel functions and syntax in the XlsxWriter docs).

When I run your formula through XlsxWriter I get an Excel warning about "We found a problem with some content in 'demo.xlsx'" and when I click on "yes" to recover the formula is zero, as your described.

Changing the semi-colon to a comma makes the program work without warning and as expected:

import xlsxwriter

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

link = '=HYPERLINK(\"{0}/tr_events.php?triggerid={1}&eventid={2}\", \"{3}\")'.format('www.foo.com', 'abc', 'def', 'event1')

worksheet.write('A1', link)

# Or with a hyperlink format.
url_format = workbook.get_default_url_format()
worksheet.write('A2', link, url_format)

workbook.close()

Output:

enter image description here

Upvotes: 3

d_kennetz
d_kennetz

Reputation: 5359

Use xlwt which has a formula module which will store as a formula object in your dataframe.

You can then write this to excel with pandas using df.to_excel like so:

import xlwt
... # your other code here
link = '=HYPERLINK(\"{0}/tr_events.php?triggerid={1}&eventid={2}\"; \"{3}\")'.format(args.url, trigger_id,event['eventid'], event['name'])
excel_formatted = xlwt.Formula(link)

Then when this is passed to excel it should appear as the formula of whatever passed. I only tested it with the LEN() function but it worked fine.

Upvotes: 1

Related Questions