Setting image properties (object positioning)

[Kane]

Hi all,

My understanding is that there is no supported way to set the "Object Positioning" property of an image to "Move and size with cells" - am I correct? The reason this is a problem is that we've created a nice workbook with lots of icons etc - but whenever the worksheet is zoomed, every goes out of whack! Manually setting "Move and size with cells" seems to fix it, but we need to do this programmatically.

Any ideas on how to do this? I'm happy to have a dig through the code, though it'd be great if someone could give me some starting points (especially for the XML). I know xlsxwriter supports this, but I'm struggling to find where it gets implemented ...

Thanks,

Kane

[Charlie Clark]

I'm not entirely sure what you're trying to do. It could be a collection
of things. But I think you probably want to set a different anchor for
images. The default is an anchor to a single cell at the top-left of the
image. But you can also have absolute positioning or anchors top-left and
bottom-right.

If it's just a matter of the anchor then you can have a look at the code
in openpyxl.drawing.spreadsheet_drawing and use one straight away:
ws.add_image(img, anchor)

But you might need to adjust stuff in the drawing object that contains the
image. Openpyxl supports nearly all the possibilities of the underlying
XML but you may indeed need to work your way through the specification and
do some reverse engineering to get exactly what you want. Happy to accept
pull requests for code and documentation.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Kane and Anna O'Donnell]

Thanks Charlie,

As far as I'm aware, anchoring (in version 2.3.5) forces the top left of the image to coincide with the top left of the anchored cell. This won't work for us, as we need exact positioning.

All I want to be able to do is insert an image at an exact position, and have it stay in the same relative position, whatever the zoom is. Excel supports this by: Format Picture -> Properties -> Object Positioning -> "Move and size with cells". Is enabling this option supported? If not, could you point me in the right direction to where you think it could be implemented?

Thanks,

Kane

--
You received this message because you are subscribed to a topic in the Google Groups "openpyxl-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openpyxl-users/1auXBiDlzHk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to openpyxl-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Charlie Clark]

Am .05.2016, 22:51 Uhr, schrieb Kane and Anna O'Donnell
<email.the...@gmail.com>:

> Thanks Charlie,
>
> As far as I'm aware, anchoring (in version 2.3.5) forces the top left of
> the image to coincide with the top left of the anchored cell. This won't
> work for us, as we need exact positioning.

Sounds like you want an AbsoluteAnchor()

> All I want to be able to do is insert an image at an exact position, and
> have it stay in the same relative position, whatever the zoom is. Excel
> supports this by: Format Picture -> Properties -> Object Positioning ->
> "Move and size with cells". Is enabling this option supported? If not,
> could you point me in the right direction to where you think it could be
> implemented?

Sorry, but the whole DrawingML stuff does not map very well to any of the
GUI controls. You're really going to have to dig into the XML of a sample
file and see which attributes are being set. Once you know this it should
be pretty easy to do.

[Kane and Anna O'Donnell]

Thanks Charlie,

I believe we're already using the absolute anchor (it's the default unless anchortype='oneCell' is specified).

I've created some examples in Excel 2010, and the basic inserted image has the following in xl/drawings/drawing1.xml:

<xdr:twoCellAnchor editAs="oneCell">

...

</xdr:twoCellAnchor>

Whereas if I check the "Move and size with cells" option, the only change is to become:

<xdr:twoCellAnchor>

...

</xdr:twoCellAnchor>

That seems pretty simple to tweak then. However, it turns out the the Image class in openpyxl only seems to support oneCell and absolute anchor types (and these are what appear in the XML). I've had a hack trying to get it working with openpyxl.drawing.spreadsheet_drawing.TwoCellAnchor, but no luck. I can keep trying, but do you have any hints before I go further? (For all I know, it might already be supported somehow ...)

Thanks,

Kane

[Charlie Clark]

Am .05.2016, 05:22 Uhr, schrieb Kane and Anna O'Donnell
<email.the...@gmail.com>:

> Thanks Charlie,
>

> I believe we're already using the absolute anchor (it's the default
> unless anchortype='oneCell' is specified).
>
> I've created some examples in Excel 2010, and the basic inserted image
> has the following in xl/drawings/drawing1.xml:
>
> <xdr:twoCellAnchor editAs="oneCell">
> ...
> </xdr:twoCellAnchor>
>
> Whereas if I check the "Move and size with cells" option, the only change
> is to become:
>
> <xdr:twoCellAnchor>
> ...
> </xdr:twoCellAnchor>
>
> That seems pretty simple to tweak then. However, it turns out the the
> Image class in openpyxl only seems to support oneCell and absolute
> anchor types
> (and these are what appear in the XML). I've had a hack trying to get it
> working with openpyxl.drawing.spreadsheet_drawing.TwoCellAnchor, but no
> luck. I can keep trying, but do you have any hints before I go further?
> (For all I know, it might already be supported somehow ...)

It looks like you'll need to add support for TwoCellAnchors to the image
class. This is currently a bit of a mess as the general handling of
drawings was completely rewritten in 2.3 for better chart support, with
which the existing image implementation was adapted to work with while
preserving compatibility. Anchor support is still pretty minimal as it was
done just to make things work, though the full specification is supported.

My preference would be to remove the special handling of anchors in the
image class and to delegate anchor handling to client code, though with a
default anchor and position as with charts. This will require
documentation of how the anchors work and examples of how and when to use
each type. Supporting twoCellAnchors isn't quite as easy as a
oneCellAnchor because of the assumption we can make with the positioning.
But this can easily be handled with an appropriate factory function.

If this is done quickly it could go into 2.4 but it might have to wait
until 2.5. Don't do any work using the 2.3 code base.

[Kane and Anna O'Donnell]

Hi Charlie,

Unfortunately, we need this pretty much by tomorrow, and the extent of the changes (plus tests and documentation) won't happen before then. That said, I've got a hack which works, which I'll add here for the sake of completeness. Using 2.3.5 or the 2.4 branch, I can do the following:

from openpyxl import Workbook

from openpyxl.drawing.spreadsheet_drawing import TwoCellAnchor, AnchorMarker

from openpyxl.drawing.drawing import Drawing

@property

def anchor(self):

    anchor = self._anchor

    return anchor

Drawing.anchor = anchor

from openpyxl.drawing.image import Image

def _anchor(self, anchor):

    self.anchortype = 'twoCell'

    self.drawing._anchor = anchor

Image.anchor = _anchor

wb = Workbook()

ws = wb.active

anchor = TwoCellAnchor(_from=AnchorMarker(row=2, col=2), to=AnchorMarker(row=3, col=3))

img = Image('test.png')

img.anchor(anchor)

ws.add_image(img)

wb.save('test.xlsx')

However, to get this working on the 2.4 branch, I had to switch the lines:

        self._write_images(archive)

        self._write_worksheets(archive)

to

        self._write_worksheets(archive)

        self._write_images(archive)

As otherwise the images weren't being added correctly (i.e. not added to media/image1.png).

Kane

[moret.pie...@gmail.com]

Hi Charlie and all,

I am trying to add an image bottom-right in a cell with text, but without success until now. I found this message with Google and Charlie says that with anchor we can put the image bottom-right, but I did not find anything in documentation about that. Can someone give me an example ? Thant you very much in advance for any help.

P.-A. Moret

[Charlie Clark]

Am .12.2016, 08:50 Uhr, schrieb <moret.pie...@gmail.com>:

> I am trying to add an image bottom-right in a cell with text, but without
> success until now. I found this message with Google and Charlie says that
> with anchor we can put the image bottom-right, but I did not find
> anything
> in documentation about that. Can someone give me an example ? Thant you
> very much in advance for any help.

I don't think you can add an image bottom-right. Anyway, all you need to
do is to create your own anchor and use it when adding the image. The
documentation for this is the OOXML specification.

[moret.pie...@gmail.com]

Hi Charlie,

Thank you very much for your fast answer.

I tried in a first step to write an image in a cell with an offset in a column with this small test program adapted from that of Kane and Anna O'Donnell just above. So Itried

this one :

from openpyxl import Workbook

from openpyxl.drawing.spreadsheet_drawing import OneCellAnchor, AnchorMarker

from openpyxl.drawing.drawing import Drawing

property

def anchor(self):

    anchor = self._anchor

    return anchor

Drawing.anchor = anchor

from openpyxl.drawing.image import Image

def _anchor(self, anchor):

    self.anchortype = 'oneCell'

    self.drawing._anchor = anchor

Image.anchor = _anchor

wb = Workbook()

ws = wb.active

anchor = OneCellAnchor(_from=AnchorMarker(row=2, col=2, colOff=0))

img = Image('test.png')

img.anchor(anchor)

ws.add_image(img)

wb.save('test.xlsx')

but I got:

Traceback (most recent call last):

  File "test2.py", line 26, in <module>

    wb.save('test.xlsx')

  File "/Library/Python/2.7/site-packages/openpyxl/workbook/workbook.py", line 339, in save

    save_workbook(self, filename)

  File "/Library/Python/2.7/site-packages/openpyxl/writer/excel.py", line 268, in save_workbook

    writer.save(filename)

  File "/Library/Python/2.7/site-packages/openpyxl/writer/excel.py", line 250, in save

    self.write_data()

  File "/Library/Python/2.7/site-packages/openpyxl/writer/excel.py", line 81, in write_data

    self._write_worksheets()

  File "/Library/Python/2.7/site-packages/openpyxl/writer/excel.py", line 206, in _write_worksheets

    self._write_drawing(ws._drawing)

  File "/Library/Python/2.7/site-packages/openpyxl/writer/excel.py", line 145, in _write_drawing

    self.archive.writestr(drawing.path[1:], tostring(drawing._write()))

  File "/Library/Python/2.7/site-packages/openpyxl/drawing/spreadsheet_drawing.py", line 274, in _write

    anchor.pic = self._picture_frame(idx)

AttributeError: 'instancemethod' object has no attribute 'pic'

Has someone any idea ?

Thanks in advance for any clue.

P.-A. Moret

[Charlie Clark]

Am .12.2016, 23:00 Uhr, schrieb <moret.pie...@gmail.com>:

> AttributeError: 'instancemethod' object has no attribute 'pic'
> Has someone any idea ?

You can't do the trick Kane suggested using Python 2.x

[Gary Lin]

Hi Charlie,

It's been a while since the last update.

Just wondering is "Setting image property" feature available in latest 2.6.2 version?

Gary

Charlie Clark於 2016年12月28日星期三 UTC+8下午4時30分00秒寫道：

[Bálint Takács]

in Openpyxl 3.07 it is solved already.

could not find it in the documentation, but the image property settings are, I believe, equal to that of the Xlswriter package.

the 'object_position' property of the image object needs to be set in line with the following specifications:

{1: "Move and size with cells",

 2: "Move but don't size with cells",

 3: "Don't move or size with cells"}

Full example code:

--------------------------------

import openpyxl

from openpyxl import load_workbook

from openpyxl.workbook import Workbook

from openpyxl.drawing.image import Image

wb = load_workbook( "example.xlsx"  )

ws = wb['Sheet1']

img = Image("logo1.png")

img.object_position = 1

ws.add_image(img, "A1")

wb.save(io)

[Antonio Amo Sánchez]

img.object_position does not seem to do anything (Openpyxl 3.0.9). Property is not set in images inside generated Excel.

[Nicoscent]

object_position doesn't work for me both  Openpyxl 3.0.9 or 3.0.7. 

In Xlsxwriter we can do this

worksheet.insert_image('B3', 'python.png', {'object_position': 1})

but in Openpyxl I don't see anything in code



[Wei Zong]

TwoCellAnchor(editAs="twoCell", ...) work for me

example code:

--------------------------------

```

# *- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl.drawing.image import Image
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, TwoCellAnchor

wb = Workbook()
ws = wb.active

ws.column_dimensions["A"].width = 18
ws.row_dimensions[1].height = 140
col, row = 0, 0
offset = 30000
img = Image("test.jpg")
_from = AnchorMarker(
    col=col,
    row=row,
    colOff=offset,
    rowOff=offset,
)
to = AnchorMarker(
    col=col + 1,
    row=row + 1,
    colOff=-offset,
    rowOff=-offset,
)
img.anchor = TwoCellAnchor(editAs="twoCell", _from=_from, to=to)
ws.add_image(img)
wb.save("test.xlsx")

```