Autosize of column-width seems to work invalid #125

Open
jjoschyy opened this Issue Sep 10, 2012 · 13 comments

Comments

Projects
None yet
8 participants
@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

This comment has been minimized.

Show comment
Hide comment
@randym

randym Sep 11, 2012

Owner

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

Owner

randym commented Sep 11, 2012

@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

This comment has been minimized.

Show comment
Hide comment
@randym

randym Oct 19, 2012

Owner

@joschy Any luck with this?

Owner

randym commented Oct 19, 2012

@joschy Any luck with this?

@josegrad

This comment has been minimized.

Show comment
Hide comment
@josegrad

josegrad Apr 3, 2013

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 commented Apr 3, 2013

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

This comment has been minimized.

Show comment
Hide comment
@josegrad

josegrad Apr 22, 2013

Any comments on this issue?

Any comments on this issue?

@randym

This comment has been minimized.

Show comment
Hide comment
@randym

randym Apr 23, 2013

Owner

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.

Owner

randym commented Apr 23, 2013

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

This comment has been minimized.

Show comment
Hide comment
@josegrad

josegrad Apr 23, 2013

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.

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

This comment has been minimized.

Show comment
Hide comment
@randym

randym Apr 23, 2013

Owner

@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

Owner

randym commented Apr 23, 2013

@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

This comment has been minimized.

Show comment
Hide comment
@ivanovv

ivanovv Mar 10, 2014

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

ivanovv commented Mar 10, 2014

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

@dsmalko

This comment has been minimized.

Show comment
Hide comment
@dsmalko

dsmalko May 2, 2015

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

dsmalko commented May 2, 2015

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

@Absox

This comment has been minimized.

Show comment
Hide comment
@Absox

Absox Jul 12, 2016

Still having this issue.

Absox commented Jul 12, 2016

Still having this issue.

@toncid

This comment has been minimized.

Show comment
Hide comment
@toncid

toncid May 17, 2017

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

toncid commented May 17, 2017

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

@jaspertandy

This comment has been minimized.

Show comment
Hide comment
@jaspertandy

jaspertandy Jul 14, 2017

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

This comment has been minimized.

Show comment
Hide comment
@toncid

toncid Jul 14, 2017

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

toncid commented Jul 14, 2017

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment