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

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

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

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

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  amp  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

[excel] Setting Column width in Apache POI

Examples related to excel

Examples related to apache-poi