New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Feature Request: Add support to set the "Hyperlink base" document property #306

Closed
Thhhza opened this Issue Oct 3, 2015 · 9 comments

Comments

2 participants
@Thhhza

Thhhza commented Oct 3, 2015

As seen here:
https://xlsxwriter.readthedocs.org/_images/doc_properties.png

There is a "Hyperlink base" property which can be set but is not currently supported by Xlsxwriter.

@Thhhza

This comment has been minimized.

Show comment
Hide comment
@Thhhza

Thhhza Oct 3, 2015

I had a work colleague report a bug that when he modified and saved an Excel file that I created using Xlsxwriter, all the hyperlinks that use absolute UNC paths turn into relative paths and the URL can no longer be opened by copying and pasting into Windows Explorer.

Anyway, the issue appears to be fixed by setting the "Hyperlink base" to "C:", see here:
http://www.excelforum.com/excel-general/379329-absolute-vs-relative-path.html

Thhhza commented Oct 3, 2015

I had a work colleague report a bug that when he modified and saved an Excel file that I created using Xlsxwriter, all the hyperlinks that use absolute UNC paths turn into relative paths and the URL can no longer be opened by copying and pasting into Windows Explorer.

Anyway, the issue appears to be fixed by setting the "Hyperlink base" to "C:", see here:
http://www.excelforum.com/excel-general/379329-absolute-vs-relative-path.html

@Thhhza

This comment has been minimized.

Show comment
Hide comment
@Thhhza

Thhhza Oct 3, 2015

The "Hyperlink base" is an extended property just like the "Company" and "Manager" properties. These two properties currently get set in app.py with the _write_manager and _write_company functions, which are called by _assemble_xml_file.

The fix would be to add a _write_hyperlink_base function which gets called by _assemble_xml_file and does the same thing as _write_manager except it looks for a hyperlink_base key inside the properties dictionary. The qualified name for the "Hyperlink base" is "HyperlinkBase" which is the first parameter that gets passed into _xml_data_element, see here:
https://msdn.microsoft.com/en-AU/library/documentformat.openxml.extendedproperties.hyperlinkbase(v=office.14).aspx

Thhhza commented Oct 3, 2015

The "Hyperlink base" is an extended property just like the "Company" and "Manager" properties. These two properties currently get set in app.py with the _write_manager and _write_company functions, which are called by _assemble_xml_file.

The fix would be to add a _write_hyperlink_base function which gets called by _assemble_xml_file and does the same thing as _write_manager except it looks for a hyperlink_base key inside the properties dictionary. The qualified name for the "Hyperlink base" is "HyperlinkBase" which is the first parameter that gets passed into _xml_data_element, see here:
https://msdn.microsoft.com/en-AU/library/documentformat.openxml.extendedproperties.hyperlinkbase(v=office.14).aspx

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 3, 2015

Owner

Hi Andrew,

Thanks for the breakdown. It seems like a reasonable feature request. I'll look into it.

I had a work colleague report a bug that when he modified and saved an Excel file that I created using Xlsxwriter

Just so everything is clear, could you post a small example that demonstrates the issue.

John

Owner

jmcnamara commented Oct 3, 2015

Hi Andrew,

Thanks for the breakdown. It seems like a reasonable feature request. I'll look into it.

I had a work colleague report a bug that when he modified and saved an Excel file that I created using Xlsxwriter

Just so everything is clear, could you post a small example that demonstrates the issue.

John

jmcnamara added a commit that referenced this issue Oct 3, 2015

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 3, 2015

Owner

I've pushed a fix for this to the master branch. You can test it when you get a chance. The interface is like this:

        workbook.set_properties({'hyperlink_base': 'C:\\'})
Owner

jmcnamara commented Oct 3, 2015

I've pushed a fix for this to the master branch. You can test it when you get a chance. The interface is like this:

        workbook.set_properties({'hyperlink_base': 'C:\\'})
@Thhhza

This comment has been minimized.

Show comment
Hide comment
@Thhhza

Thhhza Oct 3, 2015

Example:

from xlsxwriter import Workbook

wb = Workbook(r'\\unc\path\results\hyperlink_issue.xlsx')
ws = wb.add_worksheet('Hyperlink Issue')
ws.write_url(0, 0, r'external:\\unc\path\with spaces in the url\test.txt')
wb.close()

Open the file, right click and edit the hyperlink on cell A1. Note how the URL is exactly as specified "\unc\path\with spaces in the url\test.txt". Now modify the worksheet, save and close the file. Re-open the file and right click and edit the hyperlink on cell A1 again. The URL should be a relative path now and the spaces have been escaped with %20.

Thhhza commented Oct 3, 2015

Example:

from xlsxwriter import Workbook

wb = Workbook(r'\\unc\path\results\hyperlink_issue.xlsx')
ws = wb.add_worksheet('Hyperlink Issue')
ws.write_url(0, 0, r'external:\\unc\path\with spaces in the url\test.txt')
wb.close()

Open the file, right click and edit the hyperlink on cell A1. Note how the URL is exactly as specified "\unc\path\with spaces in the url\test.txt". Now modify the worksheet, save and close the file. Re-open the file and right click and edit the hyperlink on cell A1 again. The URL should be a relative path now and the spaces have been escaped with %20.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 3, 2015

Owner

The URL should be a relative path now and the spaces have been escaped with %20.

I don't see this behaviour. An issue with the spaces in external urls was fixed in the last release (0.7.4) so it would be best to upgrade to that version to ensure that isn't causing the issue that you are seeing.

Just to make sure, I created a fie in Excel 2007 with a link similar to the one in your example and wrote a comparison test (see commit 347b46e). The results are identical (apart from file metadata dates).

So I don't see the issue here. Try the upgrade and let me know how you get on.

John

Owner

jmcnamara commented Oct 3, 2015

The URL should be a relative path now and the spaces have been escaped with %20.

I don't see this behaviour. An issue with the spaces in external urls was fixed in the last release (0.7.4) so it would be best to upgrade to that version to ensure that isn't causing the issue that you are seeing.

Just to make sure, I created a fie in Excel 2007 with a link similar to the one in your example and wrote a comparison test (see commit 347b46e). The results are identical (apart from file metadata dates).

So I don't see the issue here. Try the upgrade and let me know how you get on.

John

@Thhhza

This comment has been minimized.

Show comment
Hide comment
@Thhhza

Thhhza Oct 4, 2015

It's not an issue with Xlsxwriter but with Excel itself because I can reproduce the issue without even using Xlsxwriter. It's just that the way to "fix" it is to set this Hyperlink base property.

Here's an example with screenshots:

  1. Created a new empty Excel Workbook and saved it in \poochie\Test Results\DR Kings Way 20141110\5.57.4.2\20141128_105146\results
  2. Add the following hyperlink into cell A1 as seen in the screenshot below
    image
  3. Click OK and save the file
  4. Edit the hyperlink again and see how the URL has changed to a relative path in the screenshot below
    image

Thhhza commented Oct 4, 2015

It's not an issue with Xlsxwriter but with Excel itself because I can reproduce the issue without even using Xlsxwriter. It's just that the way to "fix" it is to set this Hyperlink base property.

Here's an example with screenshots:

  1. Created a new empty Excel Workbook and saved it in \poochie\Test Results\DR Kings Way 20141110\5.57.4.2\20141128_105146\results
  2. Add the following hyperlink into cell A1 as seen in the screenshot below
    image
  3. Click OK and save the file
  4. Edit the hyperlink again and see how the URL has changed to a relative path in the screenshot below
    image
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 4, 2015

Owner

Hi Andrew,

Thanks for the clarification.

I've added the hyperlink_base property and pushed the change to PyPI as part of XlsxWriter 0.7.5.

Regards,

John

Owner

jmcnamara commented Oct 4, 2015

Hi Andrew,

Thanks for the clarification.

I've added the hyperlink_base property and pushed the change to PyPI as part of XlsxWriter 0.7.5.

Regards,

John

@jmcnamara jmcnamara closed this Oct 4, 2015

@Thhhza

This comment has been minimized.

Show comment
Hide comment
@Thhhza

Thhhza Oct 5, 2015

Thanks, setting the hyperlink_base in 0.7.5 works as expected!

Thhhza commented Oct 5, 2015

Thanks, setting the hyperlink_base in 0.7.5 works as expected!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment