Embedded Objects lost on workbook save.

[Lee Carpenter]

In loading a workbook then saving the workbook either to the same file or a different one, if the source workbook contains embedded files, when the workbook is saved using openpyxl the embedded files are removed.

Here is the source:

from openpyxl.worksheet.cell_range import CellRange
from openpyxl_image_loader import SheetImageLoader
from openpyxl.utils import column_index_from_string
from openpyxl.drawing.image import Image
from openpyxl.worksheet.dimensions import SheetFormatProperties
from openpyxl.utils import get_column_letter

source_file = r"20241023.xlsx"
destination_file = r"writer.xlsx"
new_workbook = openpyxl.load_workbook(source_file)
new_workbook.save(destination_file)

[WP141]

Have had the same issue just recently would love any feedback on this.

[Charlie Clark]

On 26 Oct 2024, at 1:57, 'Lee Carpenter' via openpyxl-users wrote:

> In loading a workbook then saving the workbook either to the same file or a
> different one, if the source workbook contains embedded files, when the
> workbook is saved using openpyxl the embedded files are removed.

I guess it depends what you mean by "embedded" files: Openpyxl has reasonable support for images but nothing else.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[WP141]

I know I'm not the OP, but I have actually been having issues with images. Saving the file from a download to a local folder, then opening the workbook/sheet does have the images in the ._images list, but if I pass the download data as BytesIO or store it to a NamedTemporaryFile etc, the images list is empty. 

[Charlie Clark]

On 28 Oct 2024, at 11:21, WP141 wrote:

> I know I'm not the OP, but I have actually been having issues with images.
> Saving the file from a download to a local folder, then opening the
> workbook/sheet does have the images in the ._images list, but if I pass the
> download data as BytesIO or store it to a NamedTemporaryFile etc, the
> images list is empty.

I don't know what you're trying to do but, if you just want the images, just extract them directly from the XLSX file – it's a zip file after all.

[WP141]

I'm trying to copy over the images from one excel file to another. And yes, you can get them directly, but you would lose data on the anchor point, any resized dimensions, the sheet they belonged to etc, which are indeed present if you can get them from ._images when it works

[Lee Carpenter]

What I mean by embedded is when working in Excel the Cell/Object dropdown may say Object1 and in the expression have something like =EMBED("Acrobat Document",""). If you dump the sheet in Python the _rel collection has OleObjects among other things, but it appears that the _rel is not saved, so the workbook cannot be updated without loss of critical information, if all it can handle is images.

Is that correct, and if so is there any short or long term plan on full support of the _rel collection?

[Lee Carpenter]

I did come across an extension to the library https://stackoverflow.com/questions/52878615/how-to-keep-style-format-unchanged-after-writing-data-using-openpyxl-package-in an wondered if there was any experience with this, and if it would be worth pursuit?

[Charlie Clark]

On 28 Oct 2024, at 14:51, 'Lee Carpenter' via openpyxl-users wrote:

> Is that correct, and if so is there any short or long term plan on full
> support of the _rel collection?

It's basically correct, yes: the library only handles what it knows how to handle. I currently have no plans to extend the support but you should be able to add what you need quite easily.

[Charlie Clark]

On 28 Oct 2024, at 17:25, 'Lee Carpenter' via openpyxl-users wrote:

> I did come across an extension to the
> library https://stackoverflow.com/questions/52878615/how-to-keep-style-format-unchanged-after-writing-data-using-openpyxl-package-in
> an wondered if there was any experience with this, and if it would be worth
> pursuit?

No, that's only about supporting rich text in cells. We added support for this a couple of years ago after to Eli submitted PR.

[Charlie Clark]

On 28 Oct 2024, at 12:11, WP141 wrote:

> I'm trying to copy over the images from one excel file to another. And yes,
> you can get them directly, but you would lose data on the anchor point, any
> resized dimensions, the sheet they belonged to etc, which are indeed
> present if you can get them from ._images when it works

If you think there is a bug then should submit a report with file or files.

[Lee Carpenter]

Thank you Charlie,

I did post the information as an Issue: https://foss.heptapod.net/openpyxl/openpyxl/-/issues/2241

I will also check into that other code, only because the reference the saving the _rel collection which is where these embedded objects appear in the workbook when it is loaded, and maybe it is just a matter of reading the BIN from the source and writing it to the destination. Here is hoping anyway...

[Charlie Clark]

On 28 Oct 2024, at 19:48, 'Lee Carpenter' via openpyxl-users wrote:

> Thank you Charlie,
> I did post the information as an
> Issue: https://foss.heptapod.net/openpyxl/openpyxl/-/issues/2241

I saw it.

> I will also check into that other code, only because the reference the
> saving the _rel collection which is where these embedded objects appear in
> the workbook when it is loaded, and maybe it is just a matter of reading
> the BIN from the source and writing it to the destination. Here is hoping
> anyway...

Once you get over the extremely indirect way that OOXML handles relationships, it really is quite straightforward to extract embedded files: you get a normalised path for extracting from the archive.

Things are a bit different from the perspective of the library as you have to think about an API and where you store the data, which is why this will probably be limited to images (this gets worse when these are deep down in some drawings) for the foreseeable future.

[Lee Carpenter]

My goal is not extracting those attachments, I need to drop a couple of other sheets and make changes and the resulting XLSX needs to then have those attachments going forward. 

[Charlie Clark]

On 29 Oct 2024, at 18:39, 'Lee Carpenter' via openpyxl-users wrote:

> My goal is not extracting those attachments, I need to drop a couple of
> other sheets and make changes and the resulting XLSX needs to then have
> those attachments going forward.

Ugh! It's definitely doable but you'll want to find something quick and dirty that preserves what I generally refer to as the "plumbing".

[Lee Carpenter]

Charlie,

I have been working my way into the code and found the dispatcher and added in a parse_oleObjects, but it looks like the ole.py class is missing one of the levels in the xml. For oleObjects there is an AlternateContent that is missing from the serializer and not sure if that would just be skipped, but my guess is no because instead of getting 2 objects from the xml below I have none. Wondering if you could confirm:

<ns0:oleObjects xmlns:ns0="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:ns1="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:ns2="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:ns3="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"><ns1:AlternateContent><ns1:Choice Requires="x14"><ns0:oleObject progId="Packager Shell Object" shapeId="1025" ns2:id="rId4"><ns0:objectPr defaultSize="0" autoPict="0" ns2:id="rId5"><ns0:anchor moveWithCells="1"><ns0:from><ns3:col>3</ns3:col><ns3:colOff>0</ns3:colOff><ns3:row>4</ns3:row><ns3:rowOff>190500</ns3:rowOff></ns0:from><ns0:to><ns3:col>7</ns3:col><ns3:colOff>209550</ns3:colOff><ns3:row>11</ns3:row><ns3:rowOff>28575</ns3:rowOff></ns0:to></ns0:anchor></ns0:objectPr></ns0:oleObject></ns1:Choice><ns1:Fallback><ns0:oleObject progId="Packager Shell Object" shapeId="1025" ns2:id="rId4" /></ns1:Fallback></ns1:AlternateContent><ns1:AlternateContent><ns1:Choice Requires="x14"><ns0:oleObject progId="Package2" shapeId="1026" ns2:id="rId6"><ns0:objectPr defaultSize="0" ns2:id="rId7"><ns0:anchor moveWithCells="1"><ns0:from><ns3:col>6</ns3:col><ns3:colOff>0</ns3:colOff><ns3:row>15</ns3:row><ns3:rowOff>0</ns3:rowOff></ns0:from><ns0:to><ns3:col>8</ns3:col><ns3:colOff>523875</ns3:colOff><ns3:row>17</ns3:row><ns3:rowOff>133350</ns3:rowOff></ns0:to></ns0:anchor></ns0:objectPr></ns0:oleObject></ns1:Choice><ns1:Fallback><ns0:oleObject progId="Package2" shapeId="1026" ns2:id="rId6" /></ns1:Fallback></ns1:AlternateContent><ns1:AlternateContent><ns1:Choice Requires="x14"><ns0:oleObject progId="Package2" shapeId="1027" ns2:id="rId8"><ns0:objectPr defaultSize="0" ns2:id="rId9"><ns0:anchor moveWithCells="1"><ns0:from><ns3:col>10</ns3:col><ns3:colOff>533400</ns3:colOff><ns3:row>14</ns3:row><ns3:rowOff>76200</ns3:rowOff></ns0:from><ns0:to><ns3:col>15</ns3:col><ns3:colOff>390525</ns3:colOff><ns3:row>17</ns3:row><ns3:rowOff>19050</ns3:rowOff></ns0:to></ns0:anchor></ns0:objectPr></ns0:oleObject></ns1:Choice><ns1:Fallback><ns0:oleObject progId="Package2" shapeId="1027" ns2:id="rId8" /></ns1:Fallback></ns1:AlternateContent><ns1:AlternateContent><ns1:Choice Requires="x14"><ns0:oleObject progId="Packager Shell Object" shapeId="1028" ns2:id="rId10"><ns0:objectPr defaultSize="0" ns2:id="rId11"><ns0:anchor moveWithCells="1"><ns0:from><ns3:col>9</ns3:col><ns3:colOff>85725</ns3:colOff><ns3:row>22</ns3:row><ns3:rowOff>171450</ns3:rowOff></ns0:from><ns0:to><ns3:col>13</ns3:col><ns3:colOff>552450</ns3:colOff><ns3:row>25</ns3:row><ns3:rowOff>114300</ns3:rowOff></ns0:to></ns0:anchor></ns0:objectPr></ns0:oleObject></ns1:Choice><ns1:Fallback><ns0:oleObject progId="Packager Shell Object" shapeId="1028" ns2:id="rId10" /></ns1:Fallback></ns1:AlternateContent></ns0:oleObjects>

[Charlie Clark]

On 1 Nov 2024, at 15:26, 'Lee Carpenter' via openpyxl-users wrote:

> Charlie,
> I have been working my way into the code and found the dispatcher and added
> in a parse_oleObjects, but it looks like the ole.py class is missing one of
> the levels in the xml. For oleObjects there is an AlternateContent that is
> missing from the serializer and not sure if that would just be skipped, but
> my guess is no because instead of getting 2 objects from the xml below I
> have none. Wondering if you could confirm:

Any code there is certainly incomplete. If you're taking this on, I recommend you work from the 3.2 branch.

Looks like you've come across what is poor design in my view as XML consumers are allowed to ignore elements they're not familiar with; otherwise it wouldn't be very extensible. I came across things a bit like this when working with controls and, yes, I just ignored the packaging as alternativeContent, a bit messy, but easier to work with.

[Lee Carpenter]

Thank you Charlie for pointing to the 3.2 branch, I found in the control.py how the mc:AlternateContent was being handled and the classes in the 3.2 were more refined for ole.py objects. I still had to make some changes to the 3.2 code because it failed with the sheet xml that I had. If I had time I would have worked on updates to the 3.2 branch, but I am passing along what I have so it can be updated. The sheet1.xml file came right out of a test xmlx zip, and the test_xml.py I pulled in the openpyxl/drawing/anchor.py and the openpyxl/worksheet/ole.py I did not have to make changes to the anchor.py code but it is part of the 3.2 branch pending release. The changes to the ole.py I have marked with comments, and included a test element saved off from processing the sheet1.xml

Thanks again for the help and maybe I will have time shortly to pull down the 3.2 branch and make proper updates and tests.

Well, I was going to post the files here, but I am getting a forbidden message.

[Charlie Clark]

On 1 Nov 2024, at 21:40, 'Lee Carpenter' via openpyxl-users wrote:

> Well, I was going to post the files here, but I am getting a forbidden
> message.

Probably because they're too big. Mailing lists generally have fairly restrictive limits on file size to reduce abuse.

[Lee Carpenter]

Well, even a 3K or 6K zip does not post, so here is the code block for your reference:

import xml.etree.ElementTree as ET
from pprint import pprint


### from 3.2.0 openpyxl/drawing/anchor.py
from openpyxl.descriptors.serialisable import Serialisable
from openpyxl.descriptors import (
    Typed,
    Bool,
    NoneSet,
    Alias,
)
from openpyxl.descriptors.nested import (
    NestedText,
)
from openpyxl.descriptors.excel import Relation

###LFC: added openpyxl.drawing for the following imports, because the file was pulled in locally
from openpyxl.drawing.xdr import (
    XDRPoint2D,
    XDRPositiveSize2D,
)
from openpyxl.drawing.connector import Shape
from openpyxl.drawing.graphic import (
    GroupShape,
    GraphicFrame,
    )
from openpyxl.drawing.picture import PictureFrame
###LFC: EOF added openpyxl.drawing for the following imports


class AnchorClientData(Serialisable):

    fLocksWithSheet = Bool(allow_none=True)
    fPrintsWithSheet = Bool(allow_none=True)

    def __init__(self,
                 fLocksWithSheet=None,
                 fPrintsWithSheet=None,
                 ):
        self.fLocksWithSheet = fLocksWithSheet
        self.fPrintsWithSheet = fPrintsWithSheet


class AnchorMarker(Serialisable):

    tagname = "marker"

    col = NestedText(expected_type=int)
    colOff = NestedText(expected_type=int)
    row = NestedText(expected_type=int)
    rowOff = NestedText(expected_type=int)

    def __init__(self,
                 col=0,
                 colOff=0,
                 row=0,
                 rowOff=0,
                 ):
        self.col = col
        self.colOff = colOff
        self.row = row
        self.rowOff = rowOff


class _AnchorBase(Serialisable):

    #one of
    sp = Typed(expected_type=Shape, allow_none=True)
    shape = Alias("sp")
    grpSp = Typed(expected_type=GroupShape, allow_none=True)
    groupShape = Alias("grpSp")
    graphicFrame = Typed(expected_type=GraphicFrame, allow_none=True)
    cxnSp = Typed(expected_type=Shape, allow_none=True)
    connectionShape = Alias("cxnSp")
    pic = Typed(expected_type=PictureFrame, allow_none=True)
    contentPart = Relation()

    clientData = Typed(expected_type=AnchorClientData)

    __elements__ = ('sp', 'grpSp', 'graphicFrame',
                    'cxnSp', 'pic', 'contentPart', 'clientData')

    def __init__(self,
                 clientData=None,
                 sp=None,
                 grpSp=None,
                 graphicFrame=None,
                 cxnSp=None,
                 pic=None,
                 contentPart=None
                 ):
        if clientData is None:
            clientData = AnchorClientData()
        self.clientData = clientData
        self.sp = sp
        self.grpSp = grpSp
        self.graphicFrame = graphicFrame
        self.cxnSp = cxnSp
        self.pic = pic
        self.contentPart = contentPart


    @property
    def _content(self):
        """
        What kind of content does the anchor contain
        """
        if self.pic is not None:
            return self.pic
        elif self.groupShape is not None:
            return self.groupShape
        elif self.connectionShape is None:
            return self.connectionShape
        elif self.shape is not None:
            return self.shape


class AbsoluteAnchor(_AnchorBase):

    tagname = "absoluteAnchor"

    pos = Typed(expected_type=XDRPoint2D)
    ext = Typed(expected_type=XDRPositiveSize2D)

    sp = _AnchorBase.sp
    grpSp = _AnchorBase.grpSp
    graphicFrame = _AnchorBase.graphicFrame
    cxnSp = _AnchorBase.cxnSp
    pic = _AnchorBase.pic
    contentPart = _AnchorBase.contentPart
    clientData = _AnchorBase.clientData

    __elements__ = ('pos', 'ext') + _AnchorBase.__elements__

    def __init__(self,
                 pos=None,
                 ext=None,
                 **kw
                ):
        if pos is None:
            pos = XDRPoint2D(0, 0)
        self.pos = pos
        if ext is None:
            ext = XDRPositiveSize2D(0, 0)
        self.ext = ext
        super().__init__(**kw)


class OneCellAnchor(_AnchorBase):

    tagname = "oneCellAnchor"

    _from = Typed(expected_type=AnchorMarker)
    ext = Typed(expected_type=XDRPositiveSize2D)

    sp = _AnchorBase.sp
    grpSp = _AnchorBase.grpSp
    graphicFrame = _AnchorBase.graphicFrame
    cxnSp = _AnchorBase.cxnSp
    pic = _AnchorBase.pic
    contentPart = _AnchorBase.contentPart
    clientData = _AnchorBase.clientData

    __elements__ = ('_from', 'ext') + _AnchorBase.__elements__


    def __init__(self,
                 _from=None,
                 ext=None,
                 **kw
                ):
        if _from is None:
            _from = AnchorMarker()
        self._from = _from
        if ext is None:
            ext = XDRPositiveSize2D(0, 0)
        self.ext = ext
        super().__init__(**kw)


class TwoCellAnchor(_AnchorBase):

    tagname = "twoCellAnchor"

    editAs = NoneSet(values=(['twoCell', 'oneCell', 'absolute']))
    _from = Typed(expected_type=AnchorMarker)
    to = Typed(expected_type=AnchorMarker)

    sp = _AnchorBase.sp
    grpSp = _AnchorBase.grpSp
    graphicFrame = _AnchorBase.graphicFrame
    cxnSp = _AnchorBase.cxnSp
    pic = _AnchorBase.pic
    contentPart = _AnchorBase.contentPart
    clientData = _AnchorBase.clientData

    __elements__ = ('_from', 'to') + _AnchorBase.__elements__

    def __init__(self,
                 editAs=None,
                 _from=None,
                 to=None,
                 **kw
                 ):
        self.editAs = editAs
        if _from is None:
            _from = AnchorMarker()
        self._from = _from
        if to is None:
            to = AnchorMarker()
        self.to = to
        super().__init__(**kw)
### EOF from 3.2.0 openpyxl/drawing/anchor.py


### from 3.2.0 openpyxl/worksheet/ole.py
from openpyxl.descriptors.serialisable import Serialisable
from openpyxl.descriptors import (
    Typed,
    Integer,
    String,
    Set,
    Bool,
    Sequence,
)
from openpyxl.descriptors.nested import NestedText

### LFC: include above --- from openpyxl.drawing.anchor import AnchorMarker
from openpyxl.xml.constants import SHEET_DRAWING_NS


class AnchorMarker(AnchorMarker):

    ## XDR namespace for child elements only

    col = NestedText(expected_type=int, namespace=SHEET_DRAWING_NS)
    colOff = NestedText(expected_type=int, namespace=SHEET_DRAWING_NS)
    row = NestedText(expected_type=int, namespace=SHEET_DRAWING_NS)
    rowOff = NestedText(expected_type=int, namespace=SHEET_DRAWING_NS)


class ObjectAnchor(Serialisable):

    tagname = "anchor"

    _from = Typed(expected_type=AnchorMarker)
    to = Typed(expected_type=AnchorMarker)
    moveWithCells = Bool(allow_none=True)
    sizeWithCells = Bool(allow_none=True)
    z_order = Integer(allow_none=True, hyphenated=True)


    def __init__(self,
                 _from=None,
                 to=None,
                 moveWithCells=False,
                 sizeWithCells=False,
                 z_order=None,
                ):
        self._from = _from
        self.to = to
        self.moveWithCells = moveWithCells
        self.sizeWithCells = sizeWithCells
        self.z_order = z_order


class ObjectPr(Serialisable):

    tagname = "objectPr"

    anchor = Typed(expected_type=ObjectAnchor)
    locked = Bool(allow_none=True)
    defaultSize = Bool(allow_none=True)
    _print = Bool(allow_none=True)
    disabled = Bool(allow_none=True)
    uiObject = Bool(allow_none=True)
    autoFill = Bool(allow_none=True)
    autoLine = Bool(allow_none=True)
    autoPict = Bool(allow_none=True)
    macro = String()
    altText = String(allow_none=True)
    dde = Bool(allow_none=True)

    __elements__ = ('anchor',)

    def __init__(self,
                 anchor=None,
                 locked=True,
                 defaultSize=True,
                 _print=True,
                 disabled=False,
                 uiObject=False,
                 autoFill=True,
                 autoLine=True,
                 autoPict=True,
                 ###lfc macro=None,
                 altText=None,
                 dde=False,
                ):
        self.anchor = anchor
        self.locked = locked
        self.defaultSize = defaultSize
        self._print = _print
        self.disabled = disabled
        self.uiObject = uiObject
        self.autoFill = autoFill
        self.autoLine = autoLine
        self.autoPict = autoPict
        ###lfc self.macro = macro
        self.altText = altText
        self.dde = dde


class OleObject(Serialisable):

    tagname = "oleObject"

    objectPr = Typed(expected_type=ObjectPr, allow_none=True)
    progId = String(allow_none=True)
    dvAspect = Set(values=(['DVASPECT_CONTENT', 'DVASPECT_ICON']))
    link = String(allow_none=True)
    oleUpdate = Set(values=(['OLEUPDATE_ALWAYS', 'OLEUPDATE_ONCALL']))
    autoLoad = Bool(allow_none=True)
    shapeId = Integer()

    __elements__ = ('objectPr',)

    def __init__(self,
                 objectPr=None,
                 progId=None,
                 dvAspect='DVASPECT_CONTENT',
                 link=None,
                 ###LFC: oleUpdate=None,
                 oleUpdate='OLEUPDATE_ONCALL',
                 autoLoad=False,
                 shapeId=None,
                ):
        self.objectPr = objectPr
        self.progId = progId
        self.dvAspect = dvAspect
        self.link = link
        self.oleUpdate = oleUpdate
        self.autoLoad = autoLoad
        self.shapeId = shapeId

### lfc: copied from control.py
class Choice(Serialisable):
    """Markup compatiblity choice"""

    tagname = "choice"

    oleObject = Typed(expected_type=OleObject)
    Requires = String()


    def __init__(self, oleObject=None, Requires=None):
        self.oleObject = oleObject

class AlternateContent(Serialisable):
    """Markup AlternateContent
    """

    tagname = "AlternateContent"

    Choice = Typed(expected_type=Choice)


    def __init__(self, Choice=None):
        self.Choice = Choice
### lfc: EOF copied from control.py

class OleObjects(Serialisable):

    tagname = "oleObjects"

    AlternateContent = Sequence(expected_type=AlternateContent)
    oleObject = Sequence(expected_type=OleObject)

    __elements__ = ('oleObject',)

    def __init__(self,
                 AlternateContent=None,
                 oleObject=(),
                ):
        ### lfc: if xml contains AlternateContent markup-compatibility/2006 get the choice from the alternate as the initial object
        if AlternateContent:
            for ac in AlternateContent:
                pprint(vars(ac))
            oleObject = [ac.Choice.oleObject for ac in AlternateContent]
        self.oleObject = oleObject
### from 3.2.0 openpyxl/worksheet/ole.py


xml = """

element = ET.fromstring(xml)

oles = OleObjects.from_tree(element)
pprint(vars(oles))