Apache POI Width calculations

Question

I'm having problems getting the exact size I want with my calculations for columnWidth and my calculated row height because I don't understand the documentation.

In terms of columnWidth, I am using the line of code sheet.setColumnWidth(int columnIndex, int width); but I don't understand how to properly calculate width. I get that it says:

width = Truncate([{Number of Visible Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256

So I have an exact Excel sheet example of what I want to create and when I highlight a column and select column width it tells me it is 9.67 (94 pixels). So what does this mean? How do I plug this into my equation to get the value of width I want?

The other problem I am having is that I'm using code I found elsewhere (even on SO) to dynamically calculate row height. But my problem is I don't understand what mergedCellWidth in the below code should be in the line that says nextPos = measurer.nextOffset(mergedCellWidth). I can't seem to get this value quite right and it's messing up how many lines it thinks there should be and therefore my row height isn't right.

java.awt.Font currFont = new java.awt.Font("Calibri", 0, 11);
AttributedString attrStr = new AttributedString(record.getDescription());
attrStr.addAttribute(TextAttribute.FONT, currFont);

// Use LineBreakMeasurer to count number of lines needed for the text
FontRenderContext frc = new FontRenderContext(null, true, true);
LineBreakMeasurer measurer = new LineBreakMeasurer(attrStr.getIterator(), frc);
int nextPos = 0;
int lineCnt = 0;

while (measurer.getPosition() < record.getDescription().length()) {
    System.out.println(measurer.getPosition());
    nextPos = measurer.nextOffset(mergedCellWidth); // mergedCellWidth is the max width of each line
    lineCnt++;
    measurer.setPosition(nextPos);
    System.out.println(measurer.getPosition());
}

row.setHeight((short)(row.getHeight() * lineCnt));

I think in my case examples would be the best answer for me. Thanks!

Answer 1

Well it doesn't seem like anyone really can help me out. After trial and error, I've found a couple of approximations that have worked ok for me.

Concerning my sheet.setColumnWidth(int columnIndex, int width); problem, I found a good approximation is as follows:

1. In Excel, go to "Normal" view.
2. Go to Format -> Column Width... and note the value
3. Use the following formula to calculate width: width = ([number from step 2] * 256) + 200

This actually seems to work fairly well.

Concerning my second problem about what mergedCellWidth should be, I found the following works ok for that value: (float) (sheet.getColumnWidth(columnIndex) / 256 * 4). It works much better for smaller length strings and as the strings get larger, the row starts to get a little too big (so I assume the factor of 4 is a little too much but it works for my purposes).

I also did check out the Apache POI source for auto sizing columns to try to get an idea of what was going on. There isn't a lot of direction though into what the units mean and I wasn't able to follow their calculations and reproduce them in my Excel template.

Answer 2

In Excel 2007, with XSSF file (xlsx), the following code seems to work perfectly:

public static int poiWidthToPixels(final double widthUnits) {
        if (widthUnits <= 256) {
            return (int) Math.round((widthUnits / 28));
        } else {
            return (int) (Math.round(widthUnits * 9 / 256));
        }
}

where widthUnits is the value returned by Sheet.getColumnWidth.