Ce site est optimisé pour être consulté depuis un navigateur moderne dans lequel JavaScript est activé.

Setting Column width in Apache POI

ayi

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 <a href="http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#setColumnWidth%28int,%20int%29" rel="noopener nofollow ugc">API docs</a>)
Is there a formula for converting points to the width as expected by Excel? Has anyone done this before?
There is a <code>setRowHeightInPoints()</code> method though <img src="//forum-microsoft.fr/images/emoji/twitter/frowning.png?v=15" title=":frowning:" class="emoji" alt=":frowning:" loading="lazy" width="20" height="20"> but none for column.
P.S.: The input XML is in ExcelML format which I have to convert to MS Excel.

ayi_2

Unfortunately there is only the function <a href="http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Sheet.html#setColumnWidth%28int,%20int%29">setColumnWidth(int columnIndex, 
int width)</a> from <code>class Sheet</code>; 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:
<pre><code class="lang-auto">width = Truncate([{NumOfVisibleChar} * {MaxDigitWidth} + {5PixelPadding}] / {MaxDigitWidth}*256) / 256

</code></pre>
You can always use <a href="http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Sheet.html#autoSizeColumn(int,%20boolean)"><code>autoSizeColumn(int column, boolean useMergedCells)</code></a> after inputting the data in your <code>Sheet</code>.