How to right align an image in excel cell using Python and openpyxl

Introduction

A request came across my desk to update an existing Excel report (currently generated in C# using EPPlus. I had originally developed the multi-tabbed report by using a tool a found online which would convert an existing spreadsheet to EPPlus C# specific code.

The code generated was a great foundation to start with as it gave all the code required to generate styles, add borders to cells, remove gridlines etc. I was then able to customize the code and get a really slick looking report generated in Excel.

The update to the report required adding 3 extra tabs (really detailed and fancy) tabs to the Report which included company logos, a section of each tab for dynamic disclaimers which were editable on the web application UI as richtext controls (included numbered lists, bold, italic and underlining) and more. The existing report code was embedded in the web solution and I wanted to externalize it so they could be worked on completely independently from the web site.

I thought that Azure Serverless Python Functions would be a great fit. Lightweight, easy to deploy from vscode and extremely scalable. Converting from C# to Python was super simple, the openpyxl library is really simple to work with (in my opinion, simpler than the EPPlus library).

Everything was going great, until…..

The Logo Alignment Challenge

The logos needed to be aligned to the edge of a cell on their right border

As these reports were specifically developed to be A4 print ready business reports, the logo needed to be aligned on the right edge of the cell which was the outside edge of the printout (column H in the sample image).

As the web solution was a multi-tenanted solution, I had no real way of knowing how big or small the logo would be as these were client specific logos uploaded into their specific tenant profile. I needed the solution to:

• work on any sized logo
• resize the logo and maintain the aspect ratio while fitting into the row provided
• and of course ensure the right edge is aligned to the cell

In the code sample, I have maintained the same row heights and column widths specific to the report I was generating.

Developing The Code (the [wr]long way)

I will just assume if you have arrived at this point, you have already been working with openpyxl and that you already have your Python environment configured and all the required packages installed.

Let’s go ahead by importing the required packages into your Python code.

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

What are EMUs I hear you ask?

English Metric Units and Open XML | Polymath Programmer

According to the articled referenced above, there are 914400 EMUs per inch, and there are 96 pixels to an inch making the pixel to EMU formula:

EMU = pixel * 914400 / 96

There’s a flaw, in that the dots per inch (DPI) may be different for different monitors. For example, there could be 72 pixels in an inch. The best I could do is to assume that, the image created with one monitor, will be used on another monitor with the same DPI. Thus:

EMU = pixel * 914400 / Resolution

This all becomes very relevant when trying to accurately position an element in a cell where the imported image size is in pixels but the cell width is measure in characters.

The number 34.29 actually shows that the cell can hold 34.29 characters of the default font of the spreadsheet (Arial 11).

The relevance being that we need to convert the characters to pixels and the pixels to EMUs to perform the calculations for image placement. The AnchorMarker method of openpyxl uses EMUs for offsetting an image.

def ch_px(v):
    """
    Convert between Excel character width and pixel width.
    """
    return v * 7.5

In all honesty, I cannot tell you how many blogs and stack overflow answers I read before I stumbled across this magic number: 7.5
(don’t fight it ¯\_(ツ)_/¯ )

Give it your column width and it will return you the number of pixels.

wb = Workbook()
ws = wb.active
column_lens = [4, 22, 33, 33, 22, 4, 35, 3]
for ch, c in zip('ABCDEFGH', column_lens):
    ws.column_dimensions[ch].width = c
row_heights = [(1, 65), (2, 65), (3, 25), (4, 33), (5, 27), (6, 27), (7, 20), (8, 36), (9, 27), (10, 27),(11, 27), (12, 27), (13, 20), (14, 33), (15, 27), (16, 27), (17, 27), (18, 27), (19, 27), (20, 18), (21, 30), (22, 55), (23, 30), (24, 70), (26, 20), (27, 30)]
for r, ch in row_heights:
    ws.row_dimensions[r].height = ch

Create your workbook and apply some sheet formatting by adjusting the column widths and row heights to your needs.

img = Image('99gen_towers.png')

Load up a dummy logo to test (try with different sized logos).

p2e = pixels_to_EMU
MAXHEIGHT = 84
h, w = img.height, img.width
ratio = MAXHEIGHT/h
h, w = h*ratio, w*ratio
###################################################################
image_width_emu = p2e(w)
cellg_width_px = ch_px(34)-4 # there are 4 extra pixels for grid lines and padding 
cellh_width_px = ch_px(2)
cellg_width_emu = pixels_to_EMU(cellg_width_px)
cellh_width_emu = pixels_to_EMU(cellh_width_px)
cut_image_width = image_width_emu - cellh_width_emu
empty_space_width = cellg_width_emu - cut_image_width
###################################################################

To ensure the logo fits the height you require, we need to scale the image accordingly. We define a max height and use it to create a ratio to scale the image:

ratio = MAXHEIGHT/h   # h = actual image height

As per my example, my column H was really narrow and my column G was really wide. To prevent working with a negative offset, I decided to place my logo in column G and let it overlap into H. So I needed to find:

cell space left = (width of column H + width of column G) — image width

remembering that the grid lines take up space and the excel cell is also padded with space. “commented inside openpyxl.utils.units.py”

#constants
DEFAULT_ROW_HEIGHT = 15.  # Default row height measured in point size.
BASE_COL_WIDTH = 8 # in characters
DEFAULT_COLUMN_WIDTH = BASE_COL_WIDTH + 5
#  = baseColumnWidth + {margin padding (2 pixels on each side, totalling 4 pixels)} + {gridline (1pixel)}

Then we can finish it off with:

size = XDRPositiveSize2D(p2e(w), p2e(h))
c2e = cm_to_EMU
# Calculated number of cells width or height from cm into EMUs
cellh = lambda x: c2e((x * 65)/99)
# Want to place image in row 5 (6 in excel), column 2 (C in excel)
# Also offset by half a column.
column = 6
coloffset = empty_space_width #cellw(distance_ratio)
row = 0
rowoffset = cellh(0)
marker = AnchorMarker(col=column, colOff=coloffset, row=row, rowOff=rowoffset)
img.anchor = OneCellAnchor(_from=marker, ext=size)
ws.add_image(img)
wb.save('sample.xlsx')

Developing The Code (the simple way)

After figuring out a long winded complicated way to turn on the light:

I decided it would be much easier to keep-it-simple-stupid.

from openpyxl import Workbook
from openpyxl.drawing.image import Image
from openpyxl.drawing.xdr import XDRPositiveSize2D
from openpyxl.utils.units import pixels_to_EMU, cm_to_EMU
from openpyxl.drawing.spreadsheet_drawing import OneCellAnchor, AnchorMarker
import datetime
def get_filename() -> str:
    basename = "output"
    suffix = datetime.datetime.now().strftime("%y%m%d_%H%M%S")
    filename = "_".join([basename, suffix]) # e.g. 'mylogfile_120508_171442'
return filename
# create a workbook and prep the sheet layout
wb = Workbook()
ws = wb.active
column_lens = [4, 22, 33, 33, 22, 4, 35, 3]
for ch, c in zip('ABCDEFGH', column_lens):
    ws.column_dimensions[ch].width = c
row_heights = [(1, 65), (2, 65), (3, 25), (4, 33), (5, 27), (6, 27), (7, 20), (8, 36), (9, 27), (10, 27),
                (11, 27), (12, 27), (13, 20), (14, 33), (15, 27), (16, 27), (17, 27), (18, 27), (19, 27), (20, 18),
                (21, 30), (22, 55), (23, 30), (24, 70), (26, 20), (27, 30)]
for r, ch in row_heights:
    ws.row_dimensions[r].height = ch
    
# load up an image from disk
img = Image('image1.png')
# scale the image to fit your requirements
MAXHEIGHT = 84
h, w = img.height, img.width  # original size
ratio = MAXHEIGHT/h
h, w = h*ratio, w*ratio # scaled down/up size based on the ratio
p2e = pixels_to_EMU
# create the size profile of the image (scaled version)
size = XDRPositiveSize2D(p2e(w), p2e(h))
# Set image position on the top right of the Worksheet:
column = 8 # last column index (!!!starts from 0)
row = 0  # first row index (!!!starts from 0)
coloffset = -p2e(w) # we align the image with the right limit of the worksheet
marker = AnchorMarker(col=column, colOff=coloffset, row=row)
img.anchor = OneCellAnchor(_from=marker, ext=size)
ws.add_image(img)
wb.save('{}.xlsx'.format(get_filename()))

One line of code to the rescue!

coloffset = -p2e(w)

If you would like to download the code sample, it can be found here

If you have any suggestions, thoughts or just want to connect, feel free to reach out on LinkedIn.

Thanks for reading!