10

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!

Jon Rubins
  • 4,323
  • 9
  • 32
  • 51
  • 1
    Have you tried looking at the Apache POI code that implements Column Auto-Sizing, to see how POI goes about doing that calculation? – Gagravarr Mar 08 '14 at 06:23

3 Answers3

16

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.

Jon Rubins
  • 4,323
  • 9
  • 32
  • 51
  • 3
    You get width by using the above formula, for example the value I get in step 2 is 8.43 and then I use your formula and get 2358.08, What is the unit of this number and what should I do with this? I need the size in pixels or in some manner that I can use. Could you please elaborate. – Saurabh Gupta Jun 20 '17 at 17:46
4

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.

Tony BenBrahim
  • 7,040
  • 2
  • 36
  • 49
0

I'm working with version 5.0.0 of apache poi.

In this version (maybe also in previous versions) there's a built-in function to get the column width in pixels, instead of Tony BenBrahim's suggested function.

while (measurer.getPosition() < wrapText.length()) {
    nextPos = measurer.nextOffset(cell.getSheet().getColumnWidthInPixels(cell.getColumnIndex())); 
    lineCnt++;
    measurer.setPosition(nextPos);
}

row.setHeight((short)(row.getHeight() * lineCnt));
AryehSa
  • 11
  • 2