Setting Column width in Apache POI

Question

I am writing a tool in Java using Apache POI API to convert an XML to MS Excel. In my XML input, I receive the column width in points. But the Apache POI API has a slightly queer logic for setting column width based on font size etc. (refer API docs)

Is there a formula for converting points to the width as expected by Excel? Has anyone done this before?

There is a setRowHeightInPoints() method though :( but none for column.

P.S.: The input XML is in ExcelML format which I have to convert to MS Excel.

score 101 · Answer 1 · edited Aug 22 '19 at 18:34

101

Unfortunately there is only the function setColumnWidth(int columnIndex, int width) from class Sheet; in which width is a number of characters in the standard font (first font in the workbook) if your fonts are changing you cannot use it. There is explained how to calculate the width in function of a font size. The formula is:

width = Truncate([{NumOfVisibleChar} * {MaxDigitWidth} + {5PixelPadding}] / {MaxDigitWidth}*256) / 256

You can always use autoSizeColumn(int column, boolean useMergedCells) after inputting the data in your Sheet.

edited Aug 22 '19 at 18:34

halfer

19,824
17
99
186

answered Jul 20 '12 at 07:17

ArtiBucco

2,199
1
20
26

2

Careful with autoSizeColumn(); it can throw an error on some systems, because it relies on the presence of fonts that might not be present. – mcv Jul 12 '22 at 13:29

score 34 · Answer 2 · edited May 23 '17 at 10:31

34

Please be carefull with the usage of autoSizeColumn(). It can be used without problems on small files but please take care that the method is called only once (at the end) for each column and not called inside a loop which would make no sense.

Please avoid using autoSizeColumn() on large Excel files. The method generates a performance problem.

We used it on a 110k rows/11 columns file. The method took ~6m to autosize all columns.

For more details have a look at: How to speed up autosizing columns in apache POI?

edited May 23 '17 at 10:31

Community

1
1

answered Oct 21 '15 at 08:16

Arthur bauer

633
7
11

2

This is a comment on the accepted answer, and not a self-contained answer to the original question. – Jeremy Caney Sep 23 '21 at 01:47
Also, autoSizeColumn() can throw an error on some systems, because it needs access to fonts that might not be installed. It just cost me two days to figure that out. – mcv Jul 12 '22 at 13:28

score 17 · Answer 3 · edited Sep 10 '19 at 13:16

You can use also util methods mentioned in this blog: Getting cell witdth and height from excel with Apache POI. It can solve your problem.

Copy & paste from that blog:

static public class PixelUtil {

    public static final short EXCEL_COLUMN_WIDTH_FACTOR = 256;
    public static final short EXCEL_ROW_HEIGHT_FACTOR = 20;
    public static final int UNIT_OFFSET_LENGTH = 7;
    public static final int[] UNIT_OFFSET_MAP = new int[] { 0, 36, 73, 109, 146, 182, 219 };

    public static short pixel2WidthUnits(int pxs) {
        short widthUnits = (short) (EXCEL_COLUMN_WIDTH_FACTOR * (pxs / UNIT_OFFSET_LENGTH));
        widthUnits += UNIT_OFFSET_MAP[(pxs % UNIT_OFFSET_LENGTH)];
        return widthUnits;
    }

    public static int widthUnits2Pixel(short widthUnits) {
        int pixels = (widthUnits / EXCEL_COLUMN_WIDTH_FACTOR) * UNIT_OFFSET_LENGTH;
        int offsetWidthUnits = widthUnits % EXCEL_COLUMN_WIDTH_FACTOR;
        pixels += Math.floor((float) offsetWidthUnits / ((float) EXCEL_COLUMN_WIDTH_FACTOR / UNIT_OFFSET_LENGTH));
        return pixels;
    }

    public static int heightUnits2Pixel(short heightUnits) {
        int pixels = (heightUnits / EXCEL_ROW_HEIGHT_FACTOR);
        int offsetWidthUnits = heightUnits % EXCEL_ROW_HEIGHT_FACTOR;
        pixels += Math.floor((float) offsetWidthUnits / ((float) EXCEL_ROW_HEIGHT_FACTOR / UNIT_OFFSET_LENGTH));
        return pixels;
    }
}

So when you want to get cell width and height you can use this to get value in pixel, values are approximately.

PixelUtil.heightUnits2Pixel((short) row.getHeight())
PixelUtil.widthUnits2Pixel((short) sh.getColumnWidth(columnIndex));

@Nelda.techspiress: I concur. This code is so good it should be added to Apache POI! — kevinarpe, Mar 30 '18 at 15:35
I second that @kevinarpe this piece of code saved me so much time! lu_ko Bless you :) — Just Another Developer, May 20 '20 at 17:14
Actually this code has 2 issues. Using "short" will give negative values around 900 pixels for pixel2WidthUnits -- int is recommended. Secondly, there's no need for the convoluted calculations with OFFSET MAP array. This formula gives the exact same result for pixel2WidthUnits: `return pxs * EXCEL_COLUMN_WIDTH_FACTOR / UNIT_OFFSET_LENGTH` — Vijay Jagdale, Jun 14 '23 at 14:01

score 3 · Answer 4 · answered Sep 20 '18 at 15:15

With Scala there is a nice Wrapper spoiwo

You can do it like this:

Workbook(mySheet.withColumns(
      Column(autoSized = true),
      Column(width = new Width(100, WidthUnit.Character)),
      Column(width = new Width(100, WidthUnit.Character)))
    )

score 3 · Answer 5 · answered May 10 '19 at 17:41

3

I answered my problem with a default width for all columns and cells, like below:

int width = 15; // Where width is number of caracters 
sheet.setDefaultColumnWidth(width);

answered May 10 '19 at 17:41

Sham Fiorin

403
4
16

Setting Column width in Apache POI

5 Answers5

Linked