Resize row/column to fit image

[Ketil Froyn]

Hi,

I've been trying to insert some images into a spreadsheet, and after
trying various hacks to get the cell sizes to fit the images, I'm
hoping someone can give me some guidance. I can resize the images down
to some maximum dimension if necessary, but what should that be?

Here are the test images I'm working with:

http://sipi.usc.edu/database/download.php?vol=misc&img=4.1.01
http://sipi.usc.edu/database/download.php?vol=misc&img=4.1.02
http://sipi.usc.edu/database/download.php?vol=misc&img=4.1.03
http://sipi.usc.edu/database/download.php?vol=misc&img=4.1.04
http://sipi.usc.edu/database/download.php?vol=misc&img=4.1.06
http://sipi.usc.edu/database/download.php?vol=misc&img=4.2.03
http://sipi.usc.edu/database/download.php?vol=misc&img=5.1.12
http://sipi.usc.edu/database/download.php?vol=misc&img=5.3.01

Here's sample code. The cells don't fit the images, and when I try to
resize the rows and columns they still don't fit and the images are
usually stretched beyond recognition. I tried reversing the formulas
from Row.__adjust_height() and Column.width_in_pixels(), but that
didn't work either. Look for the HELP comments, that's where I'm
stuck.

####################
#!/usr/bin/env python
import xlwt
import os
import Image
import re

wb = xlwt.Workbook(encoding='utf8')
ws = wb.add_sheet('Table with pictures')

row = 0
for f in os.listdir('.'):

# If file is a tiff image
if re.search('\.tiff$', f):

# Write filename to first column
ws.write(row, 0, f)

# Write image as temp BMP for import
im = Image.open(f).convert('RGB')
out = f + '.bmp'
im.save(out)

## Various attempts to resize cell and place picture

# Try #1: set height and width based on image pixel dimensions
height = im.size[1] # HELP!?! :)
if ws.row(row).height < height:
ws.row(row).height = height
ws.row(row).height_mismatch = 1

width = im.size[0] # HELP!?! :)
if ws.col(1).width < width:
ws.col(1).width = width

# Insert temp BMP
ws.insert_bitmap(out, row, 1)
# ...and delete temp BMP
os.unlink(out)

# increment row
row = row + 1

outxls="output.xls"
if os.path.isfile(outxls):
os.unlink(outxls)
wb.save(outxls)
####################


Thanks for any help.

-Ketil

[John Yeung]

I'm not completely clear what problem you're encountering. Is it that
you don't know how to calculate the widths and heights that must be
fed to xlwt; or that even after successfully sizing the cells, the
images are being messed up?

I never have to work with images in Excel, so if they are being
stretched or squished, I have no idea how to help. What I can tell
you is how to request the proper row and column sizes.

First thing to understand is that the values stored in a .xls file do
not strictly correspond to a number of pixels or inches or
centimeters. They are relative to the default font, and can be
rendered differently on different computers. (The good news is that
unless you really muck around, you can at least count on the default
font in an xlwt-generated .xls file to be Arial 10. This doesn't
change the fact that different computers will render the same sheet
differently.)

So, with that big caveat aside, what works for me when it comes to
column width, if I know the pixels, is

ws.col(x).width = int(pixels * 256 / 7.0)

(Note that the 7.0 comes from the fact that a "standard" character is
7 pixels wide on my machine. You can do experiments to determine how
many pixels are in one "character unit" of width. Modern versions of
Excel are particularly helpful in this regard because they display
both character units and pixels when you try to adjust the size by
dragging with the mouse. You just have to take the difference in
pixels between columns of width 1 and 2, or 9 and 10, or any other
adjacent POSITIVE integer widths. There is extra padding between
widths 0 and 1, so don't use that interval!)

For row height, I use

ws.row(x).height = pixels * 15
ws.row(x).height_mismatch = True

John Y.

[Ketil Froyn]

Hi John,

Thanks for getting back to me. I've been having trouble with both the
calculations and messed up image dimensions, actually.

I tried your formulas, but that hasn't made things look much better
than I've managed before. I'm sure your pixel calculations are much
more correct that anything I've come up with, but the pictures still
stretch without keeping height:width ratio, and they certainly don't
fit in the cells I try to place them in. I'm beginning to think that
putting pictures in excel is just a bad idea, unless perhaps if I
resize the images to some smallish set size first. If I could scale
the images in excel so that the actual image is unchanged but excel
only displays it as a scaled down version, that might be the best fix
for me, but I'm not sure xlwt supports that.

Regards, Ketil

> --
> You received this message because you are subscribed to the Google Groups "python-excel" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to python-excel...@googlegroups.com.
> To post to this group, send an email to python...@googlegroups.com.
> Visit this group at http://groups.google.com/group/python-excel.
> For more options, visit https://groups.google.com/d/optout.

[John Yeung]

On Wed, Apr 23, 2014 at 5:32 AM, Ketil Froyn <ke...@froyn.name> wrote:
> Thanks for getting back to me. I've been having trouble with both the
> calculations and messed up image dimensions, actually.

OK. I've just done a few experiments with the python.bmp and image.py
included with xlwt in the examples directory. It would appear that
the final rendering of the images has vertical scaling which is
dependent on row height. If you expand a row that is "occupied" by
the image, then the image also expands. You can sort of compensate by
setting a vertical scale factor on the insert_image() call that is
sufficiently small. But it's tricky. If you change the height of
multiple occupied rows, both affect the rendered height of the image.

Futzing around with column widths appears to be benign (I didn't
notice any effect on the rendered image width).

I don't know if the interaction between row height and rendered image
height is intrinsic to the file format, or if it's a bug in xlwt, or
perhaps a bug in the documentation for the file format. In any case,
right now I would say it's too difficult to properly size rows to
match images with xlwt.

So you've got a few choices. If sticking to the (genuine, binary)
.xls format is a requirement, and you must use Python to generate it,
then xlwt is pretty much the only game in town. If this fits your
situation, then my recommendation is to not mess with the row heights,
and move your other data around instead. In other words, if you have
an image that is 5 standard rows high, then instead of making a single
row that is 5 times the standard height to hold it, just leave the row
heights the way they are and move the rest of your data down by 4
rows.

Now, I say "genuine, binary" .xls format because in plenty of
contexts, you can use some other format (like HTML) and just slap
'.xls' at the end of the file name, and things will work fine. I
haven't had to spoof the file extension myself, so I don't have much
to offer if you go that route.

If you are not tied to .xls and are open to generating .xlsx, then you
have a very attractive option in XlsxWriter. Its API is not
drastically different from xlwt (and usually where they differ,
XlsxWriter is friendlier), and it supports a lot more functionality
than xlwt. In particular, you can insert PNG, JPEG, or BMP; and from
my quick, informal testing, adjusting row or column sizes does not
affect the rendering of images.

If you go with XlsxWriter, you have to use different sizing
calculations, but that is a relatively minor issue (and I can help
with that if it comes to it).

John Y.

[Ketil Froyn]

John,

Thanks for the insights.

I don't remember right now why this project landed on xlwt and not
xlsxwriter, perhaps xlsxwriter didn't support images 2 years ago?
Anyway, I'll look into it and see if a rewrite with xlsxwriter works
better, thanks!

Ketil

> --
> You received this message because you are subscribed to the Google Groups "python-excel" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to python-excel...@googlegroups.com.
> To post to this group, send an email to python...@googlegroups.com.
> Visit this group at http://groups.google.com/group/python-excel.
> For more options, visit https://groups.google.com/d/optout.

--
-Ketil

[John Yeung]

On Fri, Apr 25, 2014 at 6:19 AM, Ketil Froyn <ke...@froyn.name> wrote:
> I don't remember right now why this project landed on xlwt and not
> xlsxwriter, perhaps xlsxwriter didn't support images 2 years ago?

Actually, XlsxWriter didn't even exist two years ago. Its first
public release was in February 2013. It quickly achieved a high level
of functionality and maturity in large part because it started life as
a reimplementation of an established, full-featured Perl package. The
Python version is written by the same guy who wrote the Perl one.

John Y.

P.S. Incidentally, I try to always sign with my last initial because
the author of xlwt is John Machin and the author of XlsxWriter is John
McNamara, both of whom occasionally post to this list.

[Adi Khanna]

Hi ketil,
Its probably too late , but still wanted to share so that somebody else could benifit, i was also facing the same problem of resizing the rows as per the height of the image ,the solution that worked for me is to set the row height = image_height *.75 , how is .75 arrived at well it so happens that 1 pixel is 1/75 part of an inch and excel row height are in inches.

btw -> i am using xlsxwriter

hope this helps

Adi