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

User · Answer

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));

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[excel] Setting Column width in Apache POI

Examples related to excel

Examples related to apache-poi