images and anchors - controlling image size

[Rob Y]

I've been attempting to write code to fit an image (a variable,
client-specific logo file) into a rectangle within an HSSF spreadsheet.
That will involve scaling the x- and y-dimensions of the image to the
rectangle and then adjusting the rectangle so that both dimensions scale
identically to the new rectangle size.

The problem is controlling the rectangle size on a spreadsheet where it will
be layed out across multiple rows and columns, which are all part of the
normal spreadsheet grid and are sized accordingly.

I've got it kind of working in what seems to be the standard approach. I
scan rows and columns and create an anchor to describe a rectangular set of
cells. I use the anchor's dx,dy offsets to control how much the rectangle
spills into the last row and column. But it's proving really tricky to do
this with any accuracy, and my logos end up getting stretched and somewhat
distorted.

My question. Is this a shortcoming of the XLS file format? Are multi-cell
anchors the only way Excel has of storing image sizes in an XLS file, or is
there another mechanism that POI hasn't implemented yet?

Certainly, Excel lets you resize an image dynamically, and it's able to save
the exact size. What kind of 'magic' is it doing to get around the file
format limitations?

By the way, when I open my spreadsheet in OpenOffice 3.0, the size of the
images is all wrong. So if this anchor sizing is some strange feature of
XLS, apparently OOo doesn't handle it well.

[Anthony Andrews]

Hello Rob

Forgive me for asking what may be stupid question but have you tried specifying the anchor type? From the documentation, it seems that there are three possible values;

0 = Move and size with Cells
2 = Move but don't size with cells
3 = Don't move or size with cells.

This of course still ties the image size to cells and may not provide the full solution you are after.

--- On Thu, 11/6/08, Rob Y <***@yahoo.com> wrote:
From: Rob Y <***@yahoo.com>
Subject: images and anchors - controlling image size
To: ***@poi.apache.org
Date: Thursday, November 6, 2008, 1:27 PM

I've been attempting to write code to fit an image (a variable,
client-specific logo file) into a rectangle within an HSSF spreadsheet.
That will involve scaling the x- and y-dimensions of the image to the
rectangle and then adjusting the rectangle so that both dimensions scale
identically to the new rectangle size.

The problem is controlling the rectangle size on a spreadsheet where it will
be layed out across multiple rows and columns, which are all part of the
normal spreadsheet grid and are sized accordingly.

I've got it kind of working in what seems to be the standard approach. I
scan rows and columns and create an anchor to describe a rectangular set of
cells. I use the anchor's dx,dy offsets to control how much the rectangle
spills into the last row and column. But it's proving really tricky to do
this with any accuracy, and my logos end up getting stretched and somewhat
distorted.

My question. Is this a shortcoming of the XLS file format? Are multi-cell
anchors the only way Excel has of storing image sizes in an XLS file, or is
there another mechanism that POI hasn't implemented yet?

Certainly, Excel lets you resize an image dynamically, and it's able to
save
the exact size. What kind of 'magic' is it doing to get around the
file
format limitations?

By the way, when I open my spreadsheet in OpenOffice 3.0, the size of the
images is all wrong. So if this anchor sizing is some strange feature of
XLS, apparently OOo doesn't handle it well.

[Yegor Kozlov]

Did you try HSSFPicture#resize()? This method sets the original picture size relative to top left corner.

Post by Rob Y
My question. Is this a shortcoming of the XLS file format? Are multi-cell
anchors the only way Excel has of storing image sizes in an XLS file, or is
there another mechanism that POI hasn't implemented yet?

Yes, this is the way Excel anchors images.

Post by Rob Y
By the way, when I open my spreadsheet in OpenOffice 3.0, the size of the
images is all wrong. So if this anchor sizing is some strange feature of
XLS, apparently OOo doesn't handle it well.

The picture size depends on many factors:
- Internally, Excel measures columns in units of 1/256th of a character width of the default font. If you change the
default font, the anchor will be different.
- Translation from "1/256th of a character width" to pixels depends on the system dpi. Insert a picture at 96dpi, then
view this file at 120dpi and the picture size will be different.


Yegor

Post by Rob Y
I've been attempting to write code to fit an image (a variable,
client-specific logo file) into a rectangle within an HSSF spreadsheet.
That will involve scaling the x- and y-dimensions of the image to the
rectangle and then adjusting the rectangle so that both dimensions scale
identically to the new rectangle size.
The problem is controlling the rectangle size on a spreadsheet where it will
be layed out across multiple rows and columns, which are all part of the
normal spreadsheet grid and are sized accordingly.
I've got it kind of working in what seems to be the standard approach. I
scan rows and columns and create an anchor to describe a rectangular set of
cells. I use the anchor's dx,dy offsets to control how much the rectangle
spills into the last row and column. But it's proving really tricky to do
this with any accuracy, and my logos end up getting stretched and somewhat
distorted.
My question. Is this a shortcoming of the XLS file format? Are multi-cell
anchors the only way Excel has of storing image sizes in an XLS file, or is
there another mechanism that POI hasn't implemented yet?
Certainly, Excel lets you resize an image dynamically, and it's able to save
the exact size. What kind of 'magic' is it doing to get around the file
format limitations?
By the way, when I open my spreadsheet in OpenOffice 3.0, the size of the
images is all wrong. So if this anchor sizing is some strange feature of
XLS, apparently OOo doesn't handle it well.

[Rob Y]

Post by Yegor Kozlov
Did you try HSSFPicture#resize()? This method sets the original picture
size relative to top left corner.

Yeah, I looked at that. It uses the original size of the image, and
provides no way to scale it to fit a smaller area on the spreadsheet. I
looked at the code, and internally, it's doing all the same tricks of
mapping the image size to anchor rows, columns and offsets. I was just
hoping there'd be some easier mechanism implemented in the future before
trying to clone the resize() method and modify it for my purposes.

The whole 1/256 of a 'character position' thing is so wacky, and the
combination of points for row heights vs pseudo character positions for
column widths makes the code really hard to follow, but so be it. I guess
that's why it's called Horrible Spread Sheet Format... Gee, thanks
Microsoft.

By the way, I size my columns by character positions too. The online data
grid that I'm converting into an xls is sized based on the width of a
fixed-pitch character in a given font. I don't know where Excel comes up
with the value of 256, but I've found that my columns don't size nicely when
I just multiply by 256. I've been using 312 for a spreadsheet based on an
8-point font setting and scaling that up or down when I choose a different
font size, and that's been working out to columns that hold about the same
amount of text as my online grid does.

I suppose I don't really care about the exact size of the image (since I'm
scaling images to fit in a rectangle). However, I do want to get the aspect
ratio correct. Resize() seems to have some constants defined to map between
cell widths, row point heights and image pixels. Hard to imagine it works
in all cases, but I guess I'll give it a try.

[Rob Y]

I'm trying to write a class to extend HSSFPicture adding a new fitToRect()
method. But the compiler won't let me do it because the HSSFPicture
constructor is not visible.

I then tried to clone HSSFPicture into my package and make the constructor
public, but now it complains that the underlying HSSFSimpleShape class
constructor isn't visible either.

I'm new to java coding (and OO programming in general), but I'd have thought
extending an existing class would be the way to go to add new functionality
like this - especially, since I want access to all the internal methods that
the resize() method uses to convert between the actual image size and cells
in the spreadsheet.

So, what's up with these hidden constructors? Am I misunderstanding some
basic java inheritance concept here?