[Python] Openpyxl corrupts Excel workbook after saving with modified external link

I am encountering an issue with openpyxl that appears to be causing corruption in my Excel workbooks. I'm using openpyxl version 3.0.10.

Here is a general outline of my code:

import openpyxl
import glob
from tkinter import Tk
from tkinter.filedialog import askdirectory
from openpyxl import __version__

# function to get existing data sources in a workbook
def get_existing_data_sources(file_path):

# Set data sources
data_sources = {}

# Try getting external links
try:
workbook = openpyxl.load_workbook(excel_file_path)
items = workbook._external_links

# Iterate through list and extract url
for index, item in enumerate(items):

# reformat link string
Mystr = workbook._external_links[index].file_link.Target
Mystr = Mystr.replace("file:///","")

# update key-pair
data_sources[index] = Mystr.replace("%20"," ")

except Exception as e:
print(f"An error occurred while extracting data sources: {str(e)}")

# return statement
return(data_sources, workbook)


# Function to modify the existing data sources in the workbook
def modify_data_source_links(data_sources, workbook):
try:
# Flag false
modified = False

# get workbook external links (unformatted links)
items = workbook._external_links

# Copy data sources to have them relinked
modified_data_sources = data_sources.copy()

# Correct data sources to new pattern
for i in modified_data_sources:
modified_data_sources = modified_data_sources.replace('<OLD LINK>','<NEW LINK>')

# Enumerate through external links and replace values based on the link index in the cell
for index, item in enumerate(items):
link_str = data_sources.get(index)

if link_str:
# Reformat the new link string
new_link = modified_data_sources[index]

# Reformat newlink (make spaces to %20)
new_link = new_link.replace(" ","%20")

# Replace link target
workbook._external_links[index].file_link.Target = new_link

# flag modified as true
modified = True

if modified:
print("Data source links modified successfully.")
else:
print("No data source links matching the old data source found.")

# Return Error
except Exception as e:
print(f"An error occurred: {str(e)}")


if __name__ == "__main__":

# Path to the workbook
path = askdirectory(title='Select Folder') # shows dialog box and return the path
print(path)
excel_file_paths = glob.glob('{}/*.xlsx'.format(path), recursive = True)

# Process each file
for excel_file_path in excel_file_paths:

# get existing data sources
existing_data_sources, workbook = get_existing_data_sources(excel_file_path)

# if data sources do not exist, prompt message
if not existing_data_sources:
print("No data source links found in the workbook. Moving on...")

# else relink the data sources
else:
print("Existing data source links:")
for i, data_source in enumerate(existing_data_sources, 1):
print(f"{i}. {data_source}")

modify_data_source_links(existing_data_sources, workbook)

# Save workbook
excel_file_path = excel_file_path.replace('\\','/')
workbook.save(excel_file_path)
workbook.close()

print('Modified the links in: {}, moving on...'.format(excel_file_path))

print('Script completed!')




The code runs without raising any errors, and the workbook is successfully generated. However, when I try to pen the workbook, excel displays an error message stating the file is corrupted and prompts for recovery. Has anyone else encountered this issue with openpyxl when working with external links in Excel workbooks? Am I missing something in my code or is there a workaround to prevent workbook corruption when modifying external links using openpyxl? Any insights or solutions would be greatly appreciated. Thank you!

Continue reading...