使用openpyxl识别外部工作簿链接 [英] Identify external workbook links using openpyxl
问题描述
我正在尝试使用Python 3.4中的openpyxl识别包含外部工作簿引用的所有单元格.但是我失败了.我的第一次尝试包括:
I am trying to identify all cells that contain external workbook references, using openpyxl in Python 3.4. But I am failing. My first try consisted of:
def find_external_value(cell):
# identifies an external link in a given cell
if '.xls' in cell.value:
has_external_reference = True
return has_external_value
但是,当我将具有外部值的单元格值打印到控制台时,它会产生以下结果:
However, when I print the cell values that have external values to the console, it yields this:
=[1]Sheet1!$B$4
=[2]Sheet1!$B$4
因此,openpyxl显然不会像我想象的那样解析包含外部值的公式,并且由于方括号用于表公式,因此尝试以这种方式选择外部链接没有任何意义.
So, openpyxl obviously does not parse formulas containing external values in the way I imagined and since square brackets are used for table formulas, there is no sense in trying to pick up on external links in this manner.
我更深入地挖掘了一下,然后在openpyxl.workbook.names.external模块(
I dug a little deeper and found the detect_external_links function in the openpyxl.workbook.names.external module (reference). I have no idea if one can actually call this function to do what I want.
从控制台结果看,似乎openpyxl理解有引用,并且似乎将它们包含在各种列表中.但是可以访问此列表吗?还是检测是否存在这样的列表?
From the console results it seems as if openpyxl understands that there are references, and seems to contain them in a list of sorts. But can one access this list? Or detect if such a list exists?
无论哪种方式-我所需要的只是弄清楚一个单元格是否包含指向外部工作簿的链接.
Whichever way - all I need is to figure out if a cell contains a link to an external workbook.
推荐答案
我已经找到了解决方案.使用openpyxl库以
I have found a solution to this. Use the openpyxl library for load the xlsx file as
import openpyxl
wb=openpyxl.load_workbook("Myworkbook.xlsx")
"""len(wb._external_links) *Add this line to get count of linked workbooks*"""
items=wb._external_links
for index, item in enumerate(items):
Mystr =wb._external_links[index].file_link.Target
Mystr=Mystr.replace("file:///","")
print(Mystr.replace("%20"," "))
----------------------------
Out[01]: ##Indicates that the workbook has 4 external workbook links##
/Users/myohannan/AppData/Local/Temp/49/orion/Extension Workpapers_Learning Extension Calc W_83180610.xlsx
/Users/lmmeyer/AppData/Local/Temp/orion/Complete Set of Workpapers_PPS Workpapers 123112_111698213.xlsx
\\SF-DATA-2\IBData\TEMP\ie5\Temporary Internet Files\OLK8A\LBO Models\PIGLET Current.xls
/WINNT/Temporary Internet Files/OLK3/WINDOWS/Temporary Internet Files/OLK8304/DEZ.XLS
这篇关于使用openpyxl识别外部工作簿链接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!