Geek Answers Handbook

OpenPyXL always returns None for a cell with a hyperlink

(My ultimate goal is append clickable cells to existing XLSX .)

I am using the code below to extract the value of the cell display and the hyperlink.

 from openpyxl import load_workbook xlsFile='hello.xlsx' wbook = load_workbook(xlsFile) wsheet1= wbook.get_sheet_by_name('mysheet') cell1 = wsheet1.cell('A1') print cell1.value print cell1.hyperlink print wsheet1['A1'].value print wsheet1['A1'].hyperlink

But it returns the following things:

 URL1 None URL1 None

Why is hyperlink always None ? I added a manual hyperlink for cell A1, and the hyperlink works in Excel 2013.

+1
source share
1 answer

Unfortunately, this is a mistake.

This is a mistake in 2012 ...

Some related threads:

Extract hyperlinks from Excel (.xlsx) using Python

Some details of my experiment with a hyperlink. I am using OpenPyXL 2.3.3 .

  • I can add a hyperlink to the cells.
 from openpyxl import load_workbook xlsFile='hello.xlsx' wbook = load_workbook(xlsFile) wsheet1= wbook.get_sheet_by_name('mysheet') cell1 = wsheet1.cell('A1') cell1.hyperlink = r'http://www.example.com' cell1.value=r'XXX' wbook.save(xlsFile)

  1. But I cannot download the XLSX file and read the hyperlink as my question said.

  2. And if I just download and save the XLSX file , all existing hyperlinks will be lost. Yes!

 from openpyxl import load_workbook xlsFile='hello.xlsx' wbook = load_workbook(xlsFile) wbook.save(xlsFile)

Workaround!

Use the formula with OpenPyXL .

My goal is append clickable cells to existing XLSX file . Because hyperlink not working. Instead, I use the formula =HYPERLINK(url, displayText) . And, fortunately, the formula is not lost, like the previous experiment.

 from openpyxl import load_workbook xlsFile='hello.xlsx' wbook = load_workbook(xlsFile) wsheet1= wbook.get_sheet_by_name('mysheet') cell1 = wsheet1.cell('A2') cell1.value=r'=HYPERLINK("http://www.example.com","XXX")' wbook.save(xlsFile)

Other (unsuccessful) options I tried:

I looked at XlsxWriter . But he clearly says he cannot modify an existing XLSX file . Therefore, it cannot be used to add.

I also looked at xlrd/xlwt/xlutils , unfortunately, if you want to edit an existing excel, you have to use xlrd to download it as a book to read, and then use xlutils to convert (copy) it to a writable workbook. And BANG! During copying, something will be lost, which includes the hyperlink formula. According to its doc line, this is a known limitation:

 # Copyright (c) 2009-2012 Simplistix Ltd # # This Software is released under the MIT License: # http://www.opensource.org/licenses/mit-license.html # See license.txt for more details. from xlutils.filter import process,XLRDReader,XLWTWriter def copy(wb): """ Copy an :class:`xlrd.Book` into an :class:`xlwt.Workbook` preserving as much information from the source object as possible. See the :doc:`copy` documentation for an example. """ w = XLWTWriter() process( XLRDReader(wb,'unknown.xls'), w ) return w.output[0][1]

In addition, xlwt does not support XLSX, only supports XLS. This is another reason why I decided not to use it.

+1
source

More articles:


All Articles