[Python] Corruption of data when trying to replace a part of the link in excel file

I am trying to write a code that will recursively go through a folder on the local drive, find *.xlsx and *.xls files, scan for links and replace part of the link with a input text...

I'm new to Python... It's like my 2th day... Please help

What I'm trying to do is in fact to replace a part of the link with the input given by the user... I tried with this code but It seems like I'm doing something wrong... The output file gets corrupted meaning excel can't open it.

Those are linked values, links between the sheets.

For example A link as shown in the screenshot.

='Z:\FINANCE\link synk test\Some Other Folder\[Document 2.xlsx]Sheet1'!$B$8


Execution with params

When the script is executed I get the corrupted excel as an output;

Error from Excel

Corupted links


import os
import openpyxl

FileDIR = input( "Folder: " )
ReplaceMe = input( "Replace: " )
ReplaceWith = input( "With: " )

FileCounter=0
for root, dirs, files in os.walk(FileDIR):
for file in files:

XLSXFileDIR = os.path.join(root, file)

if file.lower().endswith(('.xlsx', '.xls')):

FileCounter = FileCounter+1

wb=openpyxl.load_workbook(XLSXFileDIR)

links=wb._external_links

LinkCounter=0
for index, item in enumerate(links):
Mystr =wb._external_links[index].file_link.Target
Mystr=Mystr.replace("file:///","")
Mystr=Mystr.replace("%20"," ")

Mystr=Mystr.replace(ReplaceMe,ReplaceWith)

LinkCounter=LinkCounter+1
print("Link to: "+Mystr+" Found in: "+file)

wb.save(os.path.join(root, "NEW_"+file))

print (str(FileCounter)+" Excel Files scaned /"+str(LinkCounter)+" Links found")




-------------- Update ------------------

A quick update. I tried something silly (code below)

from openpyxl.workbook import Workbook from openpyxl import load_workbook
File = "sampleexcel.xlsx"
create workbook object wb = Workbook()
create active worksheet ws = wb.active
load spredsheet wb = load_workbook(File)
get the stupid links links = wb._external_links
wb.save(File)


This makes no changes to the xlsx file but I still get an error when trying to open it indicating the file corruption....

Is there anything in "openpyxl" that makes it incompatible with excel/office 365 etc?????

---------- UPDATE ----------------- 31/03/2023

This issue (file corruption) happens ONLY if the xlsx file contains external links. Those are not stored in the cell values and it seems openpyxl is not able to process them.

Code below:

import os
from openpyxl.workbook import Workbook
from openpyxl import load_workbook

#file location
FileDIR = r".\\"
File = "sampleexcel.xlsx"
### Create a list of files in the entered directory that end with '.xlsx'
files = [f for f in os.listdir(FileDIR) if f.endswith('.xlsx')]

#create workbook object
wb = Workbook()

#create active worksheet
ws = wb.active

#load spredsheet
wb = load_workbook(File)

#get the stupid links
links = wb._external_links

for index, item in enumerate(links):
linkTarget = wb._external_links[index].file_link.Target
linkType = wb._external_links[index].file_link.Type
print(linkTarget)
print(linkType)

for row in wb:
for cell in row:
for value in cell:
print (f'Cell: {value} value is: {value.value}')


Outpoot

file:///Z:\FINANCE\link%20synk%20test\Some%20Other%20Folder\Document%202.xlsx
http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath
file:///Z:\FINANCE\link%20synk%20test\Some%20Folder\Document1.xlsx
http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath
Cell: <Cell 'Employees'.A1> value is: None
Cell: <Cell 'Employees'.B1> value is: None
Cell: <Cell 'Employees'.C1> value is: None
Cell: <Cell 'Employees'.A2> value is: asd
Cell: <Cell 'Employees'.B2> value is: asd
Cell: <Cell 'Employees'.C2> value is: sd
Cell: <Cell 'Test'.A1> value is: Test
Cell: <Cell 'Test'.B1> value is: 1
Cell: <Cell 'Test'.C1> value is: None
Cell: <Cell 'Test'.D1> value is: =[1]Sheet1!$B$6
Cell: <Cell 'Test'.A2> value is: LALALA
Cell: <Cell 'Test'.B2> value is: 2
Cell: <Cell 'Test'.C2> value is: None
Cell: <Cell 'Test'.D2> value is: =[1]Sheet1!$B$7
Cell: <Cell 'Test'.A3> value is: I love trains
Cell: <Cell 'Test'.B3> value is: 3
Cell: <Cell 'Test'.C3> value is: None
Cell: <Cell 'Test'.D3> value is: =[1]Sheet1!$B$3
Cell: <Cell 'Test'.A4> value is: None
Cell: <Cell 'Test'.B4> value is: =SUM(B1:B3)
Cell: <Cell 'Test'.C4> value is: None
Cell: <Cell 'Test'.D4> value is: =[2]Sheet1!$B$7


The file I get as output after simply opening and saving to a NEW_*.xlsx appears smaller in size suggesting data being lost in the process

The code I run to open and save as NEW_*.xlsx

File = "sampleexcel.xlsx"
wb = Workbook()
ws = wb.active
wb = load_workbook(File)
wb.save("NEW_"+File)


I'm running: Openpyxl version is 3.1.2. Python version is 3.11.2

1. I have checked on 2 Win PCs,
2. I have asked my friend to run this in his environment.
3. I have checked on various excel files

Continue reading...