Identify External Workbook Links Using 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(ce

Solution 1:

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 inenumerate(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     

Solution 2:

I decided to veer outside of openpyxl in order to achieve my goal - even though openpyxl has numerous functions that refer to external links I was unable to find a simple way to achieve my goal.

Instead I decided to use ZipFile to open the workbook in memory, then search for the externalLink1.xml file. If it exists, then the workbook contains external links:

import tkinter as tk
from tkinter import filedialog
from zipfile import ZipFile
Import xml.etree.ElementTree

root = tk.Tk()
root.withdraw()
file_path = filedialog.askopenfilename()

with ZipFile(file_path) as myzip:
    try:
        my_file = myzip.open('xl/externalLinks/externalLink1.xml')
        e = xml.etree.ElementTree.parse(my_file).getroot()
        print('Has external references')
    except:
        print('No external references')

Once I have the XML file, I can proceed to identify the cell address, value and other information by running through the XML tree using ElementTree.