Bug 57150 - Calc FORMATTING: "Optimal column width" works incorrectly when column includes a merged cell and wrap-text cells and non-wrap cells (see comment 6 about A20)
Summary: Calc FORMATTING: "Optimal column width" works incorrectly when column include...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.4.2 release
Hardware: All All
: medium minor
Assignee: Not Assigned
Depends on:
Blocks: Calc-Merge-Split
  Show dependency treegraph
Reported: 2012-11-15 10:52 UTC by sasha.libreoffice
Modified: 2023-05-11 16:12 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

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

Note You need to log in before you can comment on or make changes to this bug.
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 (Win7 Home, 64bit), I would agree with Sasha
Comment 5 ign_christian 2013-05-10 02:39:30 UTC
reproducible with LO (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!
Comment 7 QA Administrators 2015-06-08 14:41:43 UTC Comment hidden (obsolete)
Comment 8 Buovjaga 2015-06-21 18:04:01 UTC
Still relevant.

Win 7 Pro 64-bit Version:
Build ID: 3ecef8cedb215e49237a11607197edc91639bfcd
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-06-19_23:16:58
Locale: fi-FI (fi_FI)
Comment 9 QA Administrators 2017-03-06 14:19:58 UTC Comment hidden (obsolete)
Comment 10 QA Administrators 2019-12-03 14:08:04 UTC Comment hidden (obsolete)
Comment 11 QA Administrators 2021-12-03 04:27:50 UTC
Dear sasha.libreoffice,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)

If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword

Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team