Bug 57150 - Calc FORMATTING: "Optimal column width" works incorrectly when column includes a merged cell
Summary: Calc FORMATTING: "Optimal column width" works incorrectly when column include...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Spreadsheet (show other bugs)
Version: 3.4.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-11-15 10:52 UTC by sasha.libreoffice
Modified: 2014-04-23 17:47 UTC (History)
3 users (show)

See Also:
i915 platform:
i915 features:


Attachments
test case for Optimal column width problem (14.90 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-11-15 10:52 UTC, sasha.libreoffice
Details

Description sasha.libreoffice 2012-11-15 10:52:24 UTC
Created attachment 70105 [details]
test case for Optimal column width problem

in Calc UI context menu "Optimal column width" for column header works incorrectly

Steps to reproduce:
0. Open first attachment in Calc
1. Select first column and right mouse click on it's header
2. Select in context menu "Optimal column width" and click "Ok"
Expected: width of column becomes enough to see all names completely
Actually: seen only 3 characters from names

Reproduced in 3.6.3 on RFR 17 64 bit and in 3.4.2 on Windows XP 32 bit

Workarounds: unmerge merged cell or remove cell property "Wrap text automatically"
Comment 1 sasha.libreoffice 2012-11-16 06:31:46 UTC
@ billhook@y7mail.com 
Thanks for interesting in this bug and reproducing. Please, when changing status, write some comments. Or otherwise other people will think that somebody changed something accidentally.
Comment 2 GerardF 2012-11-16 10:50:22 UTC
Optimal column width works fine even with merged cells.

In your attachment column A is not resized because cells in column A are formated to wrap text (see alignment tab) not because you have merged cells.
Comment 3 sasha.libreoffice 2012-11-16 12:35:56 UTC
> cells in column A are formated to wrap text
Yes. And text is wrapped. But cells have no room to fit all text. Expected that width of column will be enough for all text in cells will be seen on screen.
Comment 4 A (Andy) 2013-04-20 10:04:46 UTC
reproducible with LO 4.0.2.2 (Win7 Home, 64bit), I would agree with Sasha
Comment 5 ign_christian 2013-05-10 02:39:30 UTC
reproducible with LO 4.0.3.3 (Win 7 Home Premium 32bit)

Similar to Bug 59820
Comment 6 Daniel Lopez 2014-04-23 17:47:15 UTC
I've been trying to determine the logic behind the existing optimal column width calculation by experimenting. It seems that it boils down to considering the non-blank cells in the column, and whether they are non-wrapped or wrapped (via Format Cells -> Alignment -> Wrap text automatically).

If there are any non-wrapped cells in the column then the optimal width is calculated as the widest of those. Any wrapped cells are ignored in this case. It only takes one non-wrapped (and non-blank) cell to exist, to trigger this mode.

For example in the spreadsheet attached above, most of the cells in the first column are wrapped, except for A20 which only has two letters in it (sorry, I can't type Russian) and is not wrapped, so this cell alone causes the optimal width to be so narrow.

(Well actually, A1 is also non-wrapped and wider than A20 but it doesn't seem to be affecting the result, which is because it is merged. I haven't investigate merged cells much but it seems like they are simply ignored in all cases whether wrapped or not.)

If there are no non-wrapped cells in the column, then the optimal column width is calculated as the default column width. I'm not sure where you set this in LibreOffice (I had a quick Google and found something about templates?) but it's the same width as if you select Format -> Column -> Width and then tick the box for "Default value", which in my case currently happens to be 2.26cm. So, the wrapped cells are ignored in this case too. To be clear, the widths of wrapped cells are never considered in the optimal column width calculation. (Just realised that while typing this out.)

Now, here's how I think Excel does it (from memory), which I always thought was useful and I wouldn't mind if LibreOffice copied its behaviour on this point.

All non-blank cells in the column are considered. If a cell is non-wrapped, its "optimal width" (ie. just of that cell, not the whole column yet) is the width of the content. If a cell is wrapped, then there are two cases in working out the cell's "optimal width" which, interestingly, brings the current column width into consideration as well as the cell's contents:

If the current column width is so narrow that the longest word in the cell cannot fit completely, then the cell's optimal width is the width of that longest word. Else, if the current column width is wider than the longest word, then the cell's optimal width is the current column width reduced as much as possible while not changing the existing word wrapping of the cell.

(This way in which Excel considers the current column width in order to change it as little as possible was always handy to me in giving the program a 'hint' that it could take up a bit more space rather than make the column as narrow as technically possible; firstly by resizing the column roughly to the width I would like, then double-clicking to 'trim' the right edge to be neat and tidy.)

Finally, after calculating the "optimal width" for every non-blank cell in the column, the optimal width for the column itself is the maximum of all those.

I don't know from memory how Excel deals with merged cells, but feel like that's getting to the stage of asking too much of the program to make sense of your spreadsheet and am happy with the optimal width algorithm just ignoring them. I would love it if LibreOffice implemented the Excel behaviour as far as described above, though, as I used to tidy totally messed up sheets (typically received from coworkers) very quickly with that!


Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.