Autosize of column-width seems to work invalid

[jjoschyy]

Using Excel 2010, auto-column-width seems to work invalid. There is a column-resize, but it is always about 30% too small.

Furthermore, is there a way to set a default column width?

Thanks a lot, Joachim

[randym]

@joschy

Regarding the 30% undercut in autowidth, can you start by posting a copy of your code?

To specify the column widths (which, by the way is what we do at my day job because of post render formats that axlsx cannot calculate) have a look here:

http://rubydoc.info/gems/axlsx/1.2.3/Axlsx/Worksheet#column_widths-instance_method

I usually use frames on rubydoc:

http://rubydoc.info/gems/axlsx/1.2.3/frames A quick search under "methods" is a great way to find stuff without trying to read all the docs.

[randym]

@joschy Any luck with this?

[josegrad]

Hi, I'm facing the same issue. I use

sheet.column_widths nil, 2, nil, nil, nil

I've tried using it just at the beginning of populating the sheet and at the very end of the sheet population. Columns with longer text are wider but never enough to contain all the text.

Also what is the unit, so what does 2 mean in my example above?

I'm on Snow Leopard using MS Excel 2011, version 14.3.1

Thanks.

[josegrad]

Any comments on this issue?

[randym]

Hi @josegrad

RE: the unit of measurement - from the ECMA docs:

Column width measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, ..., 9 as rendered in the normal style's font. There are 4 pixels of margin padding (two on each side), plus 1 pixel padding for the gridlines.

width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256

[Example: Using the Calibri font as an example, the maximum digit width of 11 point font size is 7 pixels (at 96 dpi). In fact, each digit is the same width for this font. Therefore, if the cell width is 8 characters wide, the value of this attribute must be Truncate([8_7+5]/7_256)/256 = 8.7109375. end example]

You can see how this is applied in Cell#autowidth.

I have to confess I am not 100% satisfied with this as different fonts and font sizes can have different maximum diget width.

[josegrad]

Thanks @randym

Sorry if I missed the point but from your explanation I can´t see what would fix the issue. The fact is that autowidth never seems to provide enough space for the texts. At least in my generated spreadsheet.

[randym]

@josegrad

No worries. I was simply explaining what the 'unit' is for these widths. Would you mind posting one of the strings, and any styles you are applying where autowidth is not calculated properly?

I'd like to experiment a bit and see if I can improve it.

best

[ivanovv]

Is there a way to set the column width in inches or points or mm?

[dsmalko]

Having same issue. Is there a way to get auto-width working?

[instigatorofawe]

Still having this issue.

[toncid]

I don't think that Excel supports column auto-width, thus making the formula simply a best guess.

[jaspertandy]

@toncid I don't think that's true, because double-clicking a cell divider sets the width to be the correct value so it must be able to do it.

[toncid]

@jaspertandy My understanding is that, on double-click, Excel does the column width calculation and saves the width (in pixels or points) in the document. I'm not aware of the "auto" width value that is supported by the XSLX format.

[biju-mouli]

Is there a solution for this? the auto width feature does not seem to work. I have also opened a new issue for this https://github.com/randym/axlsx/issues/609 since this issue seems to have no update for about 1.5 years now.