Broken xlsx
Hi guys,
When I open a .xlsx (see attachment 1) and add an image via a placeholder, the saved file seems to be broken. It opens perfectly fine with LibreOffice but results in a warning on Excel Microsoft 365 (see attachment 2). Any idea?
Cheers Chris
The used code:
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from openpyxl.drawing.spreadsheet_drawing import OneCellAnchor, AnchorMarker
from openpyxl.drawing.xdr import XDRPoint2D, XDRPositiveSize2D
from openpyxl.utils.cell import coordinate_from_string, column_index_from_string
from openpyxl.utils.units import pixels_to_EMU, cm_to_EMU
file = <PATH_TO_FILE>
password = <ANYTHING>
logo_path = <PATH_TO_A_GIF>
keep_vba = file.endswith('.xlsm')
workbook = load_workbook(filename=file, keep_vba=keep_vba)
for sheet in workbook:
sheet.protection.password = password
if logo_path:
defined_names = workbook.defined_names.definedName
for defined_name in defined_names:
if defined_name.name.startswith('Logo'):
for sheetname, coord in defined_name.destinations:
if coord:
c2e = cm_to_EMU
p2e = pixels_to_EMU
cellh = lambda x: c2e((x * 49.77) / 99)
cellw = lambda x: c2e((x * (18.65 - 1.71)) / 10)
img = Image(logo_path)
img.width = img.width * 0.55
img.height = img.height * 0.55
size = XDRPositiveSize2D(p2e(img.width), p2e(img.height))
xy = coordinate_from_string(coord)
col = column_index_from_string(xy[0]) - 1
coloffset = cellw(0.2)
row = xy[1] - 1
rowoffset = cellh(0.2)
# col and row from AnchorMarker seem to be 0-based, so -1 is needed
marker = AnchorMarker(col=col, colOff=coloffset, row=row, rowOff=rowoffset)
anchor = OneCellAnchor(_from=marker, ext=size)
workbook[sheetname].add_image(img, anchor)
workbook.save(<PATH_TO_EXPORT_FILE>)
Version:
openpyxl-3.0.5-py2.py3-none-any