对于具有超链接的单元格，OpenPyXL始终返回无 [英] OpenPyXL always return None for a cell with hyperlink

问题描述

(我的最终目的是append clickable cells to existing XLSX.)

我使用下面的代码提取单元格的显示值和超链接.

I use the code below to extract the cell's display value 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

但是它返回以下内容:

URL1
None
URL1
None

为什么hyperlink总是None?我确实为单元格A1手动添加了超链接，并且该超链接在Excel 2013中有效.

Why the hyperlink always None? I did add hyperlink manually for cell A1 and the hyperlink works in Excel 2013.

推荐答案

不幸的是，这是一个错误.

这是一个错误在2012年...

Unfortunately, it's a bug.

It's a bug in 2012...

一些相关线程:

使用Python从Excel(.xlsx)提取超链接

我的超链接实验的一些细节.我正在使用OpenPyXL 2.3.3.

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

1. 我可以添加超链接到单元格.

1. I can add hyperlink to 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. 但是我不能按照我的问题加载XLSX文件并读取超链接.

如果我只是加载并重新保存XLSX文件，所有现有的超链接都将丢失. 是的！

And If I just load and re-save the XLSX file, ALL existing hyperlinks will be lost. Yeah!

from openpyxl import load_workbook

xlsFile='hello.xlsx'
wbook = load_workbook(xlsFile)
wbook.save(xlsFile)

一种解决方法！

在OpenPyXL中使用该公式.

我的目的是append clickable cells to existing XLSX file.由于hyperlink不起作用.我改用公式=HYPERLINK(url, displayText).幸运的是，该公式不会像以前的实验3一样丢失.

My purpose is to append clickable cells to existing XLSX file. Since hyperlink doesn't work. I use the formula =HYPERLINK(url, displayText) instead. And luckily, the formula is not lost like previous experiment 3.

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)

我尝试过的

其他(失败)选项:

我查看了 XlsxWriter .但是它明确表示它无法修改现有的XLSX文件.因此它不能用于追加.

Other (failed) options I tried:

I looked into the XlsxWriter. But it explicitly says it cannot modify existing XLSX file. So it cannot be used for appending.

我也研究了xlrd/xlwt/xlutils，不幸的是，如果要编辑现有的Excel，则必须使用xlrd将其加载为只读工作簿，然后使用xlutils将其转换(复制)为可写的工作簿.和B！在复制过程中，包括HYPERLINK公式在内的某些内容将丢失.根据其文档字符串，这是一个已知限制:

I also looked into the xlrd/xlwt/xlutils, unfortunately, if you want to edit an existing excel, you have to use xlrd to load it as a read-only workbook, and then use xlutils to convert(copy) it into a writable workbook. And BANG! during the copy, something will be lost which includes the HYPERLINK formula. According to its doc string, 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]

而且，xlwt不支持XLSX，仅支持XLS.这是我决定不使用它的另一个原因.

And also, xlwt doesn't support XLSX, only supports XLS. That's another reason I decided not to use it.

这篇关于对于具有超链接的单元格，OpenPyXL始终返回无的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！