使用openpyxl可以实现更精确的图像放置吗? (像素坐标而不是像元坐标?) [英] More precise image placement possible with openpyxl? (Pixel coordinates instead of cell coordinates?)

问题描述

我试图在工作中复制和自动化一种常用的做法:我试图在openpyxl的单元格中放置多个图像和文本，但是我只能将图像锚定到单元格的左上角，并具有找不到比这更准确的定位方法.因此，它们最终彼此和文本重叠.

I'm attempting to replicate and automate a well used practice in work: I'm trying to place multiple images and text within a cell in openpyxl, however I can only anchor images to the top left of a cell, and have not found a way to position any more accurately than that. So they end up overlapping with eachother and the text.

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
ws = wb.active
img = Image('image.png')
ws.add_image(img, 'B2') # img.anchor is now 'B2'
wb.save('test.xlsx')

在上述情况下为"B2".我还没有找到让它接受(或从中转换)像素坐标的任何方法.但是，如果我能做到这一点，那么我想我便可以将单元格中的图像和文本格式化为清晰/可以接受(如果不是很漂亮)的水平.

In the above case 'B2'. I haven't found any way yet to have it take (or convert from) pixel coordinates. But if I could do that then I think I could then format images and text within cells to a legible/acceptable (if not pretty) level.

openpyxl.drawing.image 和 openpyxl.worksheet.worksheet 都没有我可以看到的绝对锚点方法. openpyxl.drawing.spreadsheet_drawing 可以，但是我不确定是否或如何使用它.

Neither openpyxl.drawing.image nor openpyxl.worksheet.worksheet have an absolute anchor method that I can see. openpyxl.drawing.spreadsheet_drawing does but I'm not sure if or how to use it.

有没有办法做到这一点?还是我有其他方法可以实现这个目标?

Is there a way to do this? Or is there another way I'm overlooking to achieve this goal?

推荐答案

(使用Python 3.7和openpyxl 2.6.1)

( This was with Python 3.7 and openpyxl 2.6.1)

因此，我打开了将输出excel文件重命名为zip文件的操作，并在其中找到"drawing1.xml"，我可以看到它正在使用OneCellAnchor.因此，按照查理的建议，我更深入地研究了openpyxl代码，发现有一个我可以使用的AbsoluteAnchor类.要设置位置，我需要导入XDR坐标，然后从utils.units导入一些转换功能，将像素/厘米转换为EMU(excel测量单位?).然后，我将图像锚定为绝对，并给出了位置和"ext"(尺寸).

So I opened renamed my output excel file to a zip file and found "drawing1.xml" there I could see it was using OneCellAnchor. So following Charlie's suggestion I dug deeper into the openpyxl code and found there was an AbsoluteAnchor class I could use. To set the positions I needed to import XDR coordinates, and then from utils.units some conversion functions from pixel/cm to EMU (excel measurement units?). Then I just set the image anchor to absolute and gave the position and "ext" (dimensions).

from openpyxl import Workbook
from openpyxl.drawing.image import Image
from openpyxl.drawing.spreadsheet_drawing import AbsoluteAnchor
from openpyxl.drawing.xdr import XDRPoint2D, XDRPositiveSize2D
from openpyxl.utils.units import pixels_to_EMU, cm_to_EMU

wb = Workbook()
ws = wb.active
img = Image('image.png')

p2e = pixels_to_EMU

h, w = img.height, img.width

positie = XDRPoint2D(p2e(500), p2e(500))
size = XDRPositiveSize2D(p2e(h), p2e(w))

img.anchor = AbsoluteAnchor(pos=position, ext=size)
ws.add_image(img) 
wb.save('test.xlsx')

但是这仍然很麻烦，因为我需要知道我想放置的每个图像的绝对坐标...理想情况下，我仍然希望将其锚定到一个单元格上，但随后可以在其中更精细地移动它一个细胞.从那时起，我注意到OneCellAnchor具有offset参数.这是完美的，然后我使用测量的默认excel单元的厘米高度和宽度制作了lambda函数.

But this would still be troublesome, as I'd need to know the absolute coordinates for EVERY image I want to place... ideally I still want it anchored to a cell, but then be able to more finely move it within a cell. That's when I noticed that OneCellAnchor has an offset argument. This was perfect, then I made lambda functions with the measured centimetre height and width of default excel cells.

from openpyxl.drawing.spreadsheet_drawing import OneCellAnchor, AnchorMarker

c2e = cm_to_EMU

# Calculated number of cells width or height from cm into EMUs
cellh = lambda x: c2e((x * 49.77)/99)
cellw = lambda x: c2e((x * (18.65-1.71))/10)

# Want to place image in row 5 (6 in excel), column 2 (C in excel)
# Also offset by half a column.
column = 2
coloffset = cellw(0.5)
row = 5
rowoffset = cellh(0.5)

marker = AnchorMarker(col=column, colOff=coloffset, row=row, rowOff=rowoffset)
img.anchor = OneCellAnchor(_from=marker, ext=size)
ws.add_image(img) 
wb.save('test.xlsx')

哪个产生:

完美！正是我想要的精度:)

Perfect! Exactly the precision I was looking for :)

这篇关于使用openpyxl可以实现更精确的图像放置吗? (像素坐标而不是像元坐标?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！