excel - 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.

Answer 1

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.