How python get excel hyperlink address?

[peter]

Dear Experts,

I have one requirement: I need to get hyperlink address from excel
spreadsheet. I just got hyperlink text, but can not get hyperlink
address url.

Could you help us? Thanks so much!

[bijendra]

bijendra

   you used  hyperlink with site_url  

--
You received this message because you are subscribed to the Google Groups "python-excel" group.
To post to this group, send an email to python...@googlegroups.com.
To unsubscribe from this group, send email to python-excel...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/python-excel?hl=en-GB.

[John Machin]

On Wednesday, April 20, 2011 12:07:27 PM UTC+10, peter wrote:

I have one requirement: I need to get hyperlink address from excel
spreadsheet. I just got hyperlink text, but can not get hyperlink
address url.

I have just now committed xlrd code to extract data from hyperlink records. This is based on a patch supplied by John Morrissey. It appears to work correctly on all possible types of hyperlink that I could create using the Excel UI.

See these parts of the latest docs:

https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html#sheet.Hyperlink-class
https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html#sheet.Sheet.hyperlink_list-attribute
https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html#sheet.Sheet.hyperlink_map-attribute

You should be able to get the latest (4773) SVN revision and try it out.

Please let us know how you get on.

Cheers,
John

[Tony Tony]

Hi John,

Thanks so much for your comments. 

I really appreciate it if you could show some example to implement it?

Regards,

Peter

--

[John Machin]

On Wednesday, April 20, 2011 6:03:49 PM UTC+10, peter wrote:

I really appreciate it if you could show some example to implement it?

SVN URL:   https://secure.simplistix.co.uk/svn/xlrd/trunk
revision: 4774 ***CHANGED*** (bug fix needed) 
Installation: cd to your checkout directory and do /wherever/it/is/python setup.py install
sample xls file: attached
sample interactive session:

    >>> import xlrd
    >>> b = xlrd.open_workbook('hlink_example.xls')
    >>> s = b.sheet_by_index(0)
    >>> len(s.hyperlink_list)
    9
    >>> for h in s.hyperlink_list:
    ...     h.dump(header = 60 * '-')
    ...
    ------------------------------------------------------------
    desc: u'example description (e-mail)'
    fcolx: 1
    frowx: 1
    lcolx: 1
    lrowx: 1
    type: u'url'
    url_or_path: u'mailto:f...@bar.com?subject=xlrd%20better%20than%20sliced%20bread%20and%20WD40'
    ------------------------------------------------------------
    desc: u'python.org website'
    fcolx: 1
    frowx: 2
    lcolx: 1
    lrowx: 2
    type: u'url'
    url_or_path: u'http://www.python.org/'
    ------------------------------------------------------------
    desc: u'top left cell in 2nd sheet'
    fcolx: 1
    frowx: 3
    lcolx: 1
    lrowx: 3
    textmark: u'Sheet2!A1'
    type: u'workbook'
    ------------------------------------------------------------
    desc: u'sample local file'
    fcolx: 1
    frowx: 4
    lcolx: 1
    lrowx: 4
    type: u'local file'
    url_or_path: 'dummy.txt'
    ------------------------------------------------------------
    desc: u'desc of UNC file'
    fcolx: 1
    frowx: 5
    lcolx: 1
    lrowx: 5
    type: u'unc'
    url_or_path: u'\\\\MACHINENAME\\c:\\xlrd\\hyperlinks\\dummy.txt'
    ------------------------------------------------------------
    desc: u'struct format parameters'
    fcolx: 1
    frowx: 6
    lcolx: 1
    lrowx: 6
    textmark: u'format-characters'
    type: u'url'
    url_or_path: u'http://docs.python.org/library/struct.html'
    ------------------------------------------------------------
    desc: u'4th char of name is U+00E4'
    fcolx: 1
    frowx: 7
    lcolx: 1
    lrowx: 7
    type: u'local file'
    url_or_path: u'fub\xe4r.txt'
    ------------------------------------------------------------
    desc: u'Sheet3!A1:Z99'
    fcolx: 1
    frowx: 8
    lcolx: 1
    lrowx: 8
    textmark: u'Sheet3!A1:Z99'
    type: u'workbook'
    ------------------------------------------------------------
    desc: u'http://www.foo.com'
    fcolx: 1
    frowx: 9
    lcolx: 1
    lrowx: 9
    type: u'url'
    url_or_path: u'http://www.foo.com/'
    >>>

[Tony Tony]

Hi John,

Great! Thanks for your help!

--

[赵祺慧]

I use xlrd to read and extract the hyperlink of a excel cell, however, if the link includes special character, say, here it includes "#", the extracted link is truncated after #.

For example: I want to extract www.google.com#aa

the extracted address is www.google.com.

Here is my code.

If there is anyone can help?

import xlrd

book = xlrd.open_workbook(reg_file, formatting_info=True)

our_sheet = book.sheet_by_index(0)

link = our_sheet.hyperlink_map.get((0, 0))

url = '(No URL)' if link is None else link.url_or_path

Thank you sooooo much!!

[John Machin]

On Jun 20, 4:00 am, 赵祺慧 <rebecca08...@gmail.com> wrote:
> I use xlrd to read and extract the hyperlink of a excel cell, however, if
> the link includes special character, say, here it includes "#", the
> extracted link is truncated after #.
> For example: I want to extractwww.google.com#aa
> the extracted address iswww.google.com.

> link = our_sheet.hyperlink_map.get((0, 0))
> url = '(No URL)' if link is None else link.url_or_path

Look at (a) link.target (b) docs for the Hyperlink class