XLSX file needs repairing when it has two images with different internal hyperlinks
When I load an existing XLSX file created by Excel and save it using openpyxl:
import openpyxl
wb = openpyxl.load_workbook('file.xlsx')
wb.save('file2.xlsx')
In some cases, the file2.xlsx, when opened on Excel, needs repairing. This happens in this case :
- The file has 2 images, and each of the images is a link to another sheet in the same workbook.
This is a simple way to recreate the problem :
- Create a new workbook using Excel
- Put 3 sheets in this workbook (let's call them Sheet1, Sheet2 and Sheet3)
- On Sheet1, add 2 images (let's call them Img1 and Img2)
- On Img1, create a hyperlink to Sheet2
- On Img2, create a hyperlink to Sheet3
- Save the file (as file.xlsx for exemple. This file is provided with this issue.file.xlsx)
- Using Openpyxl, load the file.xlsx workbook
- Save the Openpyxl workbook (for exemple as file2.xlsx)
- Open file2.xlsx. Excel should tell you that the file needs repairing.
- Once the file has been repaired, Excel will tell you there was a problem with xl/drawings/drawing1.xml. The file will then open but the links on the image will not work. Both of the links will link to Sheet1 (which is already the active sheet).
Some intel that might help:
- This behavior is identical for XLSM (with VBA) files
- If there is only one hyperlink (if only Img1 has a link, but not Img2), the file will be opened without needed to be repaired, however the link won't work (it will link to Sheet1)
- If we don't have images but hyperlinks directly on cells, the file will not need to be repaired, however the links still won't work.
- If both images have an hyperlink but this time to the same worksheet (Sheet2 for instance), the file will not need to be repaired, but the links won't work
Some other changes I made to the XML to try and make it works:
- In drawing1.xml of file2.xlsx, I noticed that for each image, the <a:blip r:embed> value was equal to the <a:hlinkClick r:id> value, which is not the case in the original file. So I changed drawing1.xlsx to reproduce the rIdx values of the original file. With this, I needed to add two lines in the drawing1.xml.rels file because the two hyperlinks to the other sheets were absent in the file2.xlsx. After doing this, the file2.xlsx was still corrupted and needed repairs, HOWEVER, once Excel repaired the file, this time the two links did work.
Is this bug known ? Is there a way to fix it (via Openpyxl or by directly modifying the output XML) ?
Is there at least a way to open the file without the need to repair it ? Even if the links don't work. It's not ideal but the fact that Excel says the file is corrupted is a huge problem (way more than the missing links).
Thank you.