Hi,
I've been using POI to generate spreadsheets and hit the same wall a lot of
people have with finding an accurate way to specify column widths; for our
app, we can't just estimate the width because we need to translate between
pixels and columns widths regularly at runtime. So I've been doing a load
of trial and error research and I think I have an almost complete solution.
When calculating column widths, the POI API (and MS Excel 97 docs) say that
they are specified in 1/256th of a character, where the character width in
pixels is defined as the width of the character '0' (that's a zero, not the
letter 'oh') in the default font (i.e. Arial 10pt plain); however, this does
not seem to be the case.
By trial and error and in Excel, it turns out that Excel considers the
character width to be exactly 7.5 pixels, but adjusts the total firstly by
adding 5 and then adding an adjustment of "5-(0.5*numberOfUnits)" (when you
get to 11 units, this number becomes negative). These numbers can be proven
by defining columns interactively in Excel, noting the pixel sizes, and
performing a simple formula on each.
When specifying via the POI API, use the same pixel formula as above but
multiply by 256 (as documented) ... and then add 182. This "182" is an
additional, one-off padding per cell.
While this is an accurate and reliable calculation, I expected it to be only
a partial solution because surely it would only work for the standard font?
What's great about this is that these dimensions work fine regardless of
what the default font is. I.E. Excel sees a character width as 7.5 whether
you use Arial 10pt plain or 48pt Times New Roman bold italic. The only
thing that does change is the size of the per-cell padding (the "182"
above). The net result is that if, for example, you set the default font to
be 50pt Arial, specifying a column width of 10 units will result in a column
of 10.14 characters, 11 units comes out as 11.14, 12 as 12.14, etc (the
correct lead in for Arial 50pt is 145).
I don't understand enough about fonts and rendering to take this any further
(how does Excel come up with it's leadin?) but hopefully this work is a good
start for someone else.
My code is:
-------- snip --------
private static final double CHARACTER_WIDTH = 7.5;
private static final double PADDING = 5;
private static final double ADJUST_START = 5;
private static final double ADJUST_PER_UNIT = 0.5;
private static final double EXCEL_LEADING = 182d; // Only applies to Arial
10pt
private static final double EXCEL_FACTOR = 256d;
public static double convertColumnUnitsToPixels(double numberOfUnits) {
return (numberOfUnits * CHARACTER_WIDTH) + PADDING + (ADJUST_START -
(ADJUST_PER_UNIT * numberOfUnits));
}
public static double convertPixelsToColumnUnits(double numberOfPixels) {
numberOfPixels -= PADDING;
double last = Math.floor(numberOfPixels / CHARACTER_WIDTH);
while (convertColumnUnitsToPixels(last) < numberOfPixels)
last++;
numberOfPixels -= (ADJUST_START - (ADJUST_PER_UNIT * last));
return numberOfPixels / CHARACTER_WIDTH;
}
...
...
// Specify a width of 500 pixels
double width = convertPixelsToColumnUnits(500);
width = EXCEL_LEADING + (width * EXCEL_FACTOR);
sheet.setColumnWidth(columnIndex, (short)width);
-------- snip --------
John
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
