I need xlsx writer to make relative path hyperlinks to local files

Question

Goal: To have a hyperlink in an xlsx that can open a local pdf file via a relative path.

I can use the module to do it via an absolute path generated by os.getcwd()... but I need to take the xlsx, move it to a new directory (along with the pdfs) and not break the links encoded in the xlsx with write_url(). I cannot run the code in the destination directory (which would make this problem disappear), I have to run it on a local machine to produce the xlsx with all it's links, then move it to the new dir so that other users can see and use the links (to the pdfs which will all be moved along with the xlsx).

Unknown:
How "NETWORK" mentioned in the external file url section of the docs works. It is really talking about another xlsx file, not a different type of file, so maybe it won't work at all. (docs link: https://xlsxwriter.readthedocs.io/worksheet.html?highlight=write_url#worksheet-write-url )

Excerpt:

Links to network files are also supported. Network files normally begin with two back slashes as >follows \NETWORK\etc. In order to generate this in a single or double quoted string you will >have to escape the backslashes, '\NETWORK\etc' or use a raw string r'\NETWORK\etc'. Alternatively, you can avoid most of these quoting problems by using forward slashes. These are >translated internally to backslashes:

worksheet.write_url('A14', "external:c:/temp/foo.xlsx")

worksheet.write_url('A15', 'external://NETWORK/share/foo.xlsx')

write_url(row, col, url[, cell_format[, string[, tip]]])

Write a hyperlink to a worksheet cell.

Parameters: row (int) – The cell row (zero indexed). col (int) – The cell column (zero indexed). url (string) – Hyperlink url. cell_format (Format) – Optional Format object. Defaults to the Excel hyperlink style. string (string) – An optional display string for the hyperlink. tip (string) – An optional tooltip.

user_17894907 · Answer

SOLUTION: use relative paths in the in write_url().

So - excel hyperlinks can be relative or absolute. You can provide it either and it will handle adjudication automatically. If relative, then excel will find the path to itself upon clicking the hyperlink, and then will modify a given relative path, or just use the given absolute path.

Example

foo.xlsx is located at C:\Desktop\folder\

bar.pdf is located at C:\Desktop\folder\folder2\

set the hyperlink address in write_url() to open bar.pdf as: 'folder2\bar.pdf'

When you click on the hyperlink in foo.xlsx, excel will determine the path to foo.xlsx (no matter where it is, even if you move it after you run the code to construct the hyperlinks), and then attempt to find the relative path supplied.

So imagine after making foo.xlsx (and its hyperlinks by using write_url() ), you put the contents of folder folder on the desktop and delete folder folder.

Now, foo.xlsx lives at C:\Desktop

bar.pdf lives at C:\Destkop\folder2

You then open the xlsx and click the hyperlink.

Excel will determine the xlsx is at C:\Desktop and will attempt to find bar.pdf at C:\Desktop\folder2\bar.pdf

I need xlsx writer to make relative path hyperlinks to local files

1 Answers