Row Height- disable Automatic Row Height (permanently)?

[GoodMovie3]

The Question

I am trying to move from Excel to LO Calc (… again …) especially since I’ve been a Linux fan for years. (I’ve been using Excel for over 25 years.) However, in particular the Automatic Row Height problems, e.g. extremely slow performance, are likely to prevent this. The key question is "How to disable the Automatic Row Height"? Especially if this can be done by default on a permanent basis.

The Reasons(s)

1. In my work this automatic function is simply annoying, and (for me) only wastes productive time because I have to undo what it does (i.e. the “damage” it causes). Fortunately in Excel it is turned off by default.

2. Since it is sooooo slow - for my current worksheet taking 10 minutes or more !!! each time it starts up. This makes it impossible to contemplate using LibreOffice Calc. (In comparison Excel takes only 2 or 3 seconds for this size worksheet.)

Background

The current worksheet has 169,548 rows and 9 columns - in total 1,525,932 cells with data. Yes, it is a large spreadsheet but it is handled by Excel and should be within the scope of Calc. I found following posts with related problems:

Calc: Frequent "Adapt row height" anomaly - takes 7 minutes

Working with large voter data spreadsheet for an entire county - 157,000 voters and lines in spreadsheet. Every thing was working fine until I started adding columns into which I would cut and paste columns from to put them where I wanted them to appear. It’s mostly happening when I do operations with columns. I had spent a couple of hours adjusting the size (width) of columns with no problems at all. Takes it 7 minutes to do its “Adapt row height” thing! Driving me nuts and I can’t finish …

Calc needs very long to load, hangs at Adapt Row Height

Loading a Calc sheet (< 1 MB) takes almost 8 minutes to load. It appears to come from ‘Adapt row height’ that takes so long to do. A solution in [Solved] How to stop "Adapt Row Height"? (View topic) • Apache OpenOffice Community Forum does not work (this is adding code to standard module1 macro: TL;DR Run “LoadCalcDocumentWithoutAdjustHeight” ). The problem seems to be caused by one worksheet with some 8000 rows. Nothing fancy in these cells, just data and a linked graph.

Any way to disable 'adjust row height'? Bug?

In a largish spreadsheet with wrapped text, there is a significant delay on selecting any cell while the program automatically ‘adjusts row height’ , even though no changes have been made to content. Disabling text wrap removes the problem, but the nature of the spreadsheet needs large blocks of text to be worked with, so that is impractical Is there any way to disable this function? Is it a bug that its taking up so much time to constantly calculate the heights? The lag is getting so bad tha…

HOW TO KEEP ROW HEIGHT

I KEEP LOSING MY ROW HEIGHT WITH WRAP AROUND TEXT?

Calc Restore Row Heights to "Automatic" ("to max content height")?

Hi. I am noticing a puzzling/vexing behavior which I think has been also vexing others in several forums, but I haven’t seen a relevant reply so far (no solution, nor even confirmation that this is in fact the current behavior, or whether it is the intended behavior): When I first start entering data into a new spreadsheet (usually by typing, and setting Word Wrap on) I enjoy the fact that the Row Height increases and decreases automatically to fit cell contents as I edit. Then, for some reason…

How to optimize "optimize row height" ?)

So I created a fresh, empty Calc file in ODS format and pasted the data points in using CTRL-SHIFT-V to avoid any formatting or other unwanted artifacts. Just one single column of 169,548 rows with long text entries took 6 minutes to complete! The same operation using a number between 0 and 20 was almost instantaneous.

What can be done to permanently disable the automatic Row Height adjustment (which I would never actually use even if it wasn’t so slow)?

System #1

• AMI Motherboard with a quadcore i5
• CPU (16GB RAM)
• Linux Mint 18.3 MATE (64bit)
• LibreOffice Calc 6.2.6.2

++++++++++++++++++++++++++++++++++++++

Edit #1

I tried with the latest version of LibreOffice Calc, which is LO 6.3.3. Since this version doesn’t install on LM 18.3 a virtual machine was used (VirtualBox 6.0.14). Unfortunately, after over 35min the ODS file was still stuck on “Adapt Row Height”. So updating doesn’t seem a solution. As virtual machines often run at close to bare metal speeds if graphics are not an issue I am assuming that bare metal speeds would be similar.

Suggested Solutions

1. My favoured solution is to turn automatic Row Height adjustment off by default. Since I wouldn’t want it even if it did work well, and since it seems to be causing real problems (evidenced by the posts above and my own experience) to me there seems no reason to have this on by default.

2. During these really long Row Height adjustments only one single core is running at 100%, all others are idling. Since I am providing either 4 or 6 cores for use then multi-threading this operation would greatly reduce delays. My graphic card also has over 1,000 CUDA cores so potentially also accessible for this task. Since the row height for each row (indeed every cell) is independent of others this task must lend itself admirably to multi-threading, wouldn’t it?

System #2

• hardware as above
• VM: VirtualBox 6.0.14
• Host: Linux Mint 18.3 MATE (64bit)
• Guest in VM : Linux Mint 19.1 MATE (64bit)
• LibreOffice Calc 6.3.3

++++++++++++++++++++++++++++++++++++++

Edit #2

The same extremely slow row height (and also column width) adjustments occurs with the Windows version of LibreOffice, whereas Excel on this same system performed without issue. On LibreOffice I can wait a half hour or longer on both the Windows and the Linux versions, whereas Excel takes about a second if asked asked to do the same thing (which in Excel is thankfully off by default).

System #3

Version: 6.3.3.2 (x64) (Calc:threaded) Windows Server 2012 R2
(VMWare with Tools ver.9.10.1) (Xeon CPU E5)

• Xeon CPU E5
• VM: VMWare with 6GB RAM (Running Tools ver.9.10.1)
• Host: unknown
• Guest in VM : Windows Server 2012 R2 (64bit)
• LibreOffice Calc ver. 6.3.3.2 (x64) (Calc:threaded)
• Microsoft Office Excel 2016 MSO (32bit) with threaded calcs

[mikekaganski]

There’s no “limit application to only a subset of its functions” mode (of course, I don’t mean things like autocorrection, which are unrelated to the document behaviour). You may select all rows and set their height to a fixed value per sheet; and you may use that as a template; but not disable the function at program level (unless you hack to make changes in the code, and create your own build).

Doing height adjustments in parallel could be made an enhancement request (along with a sample document showing long processing that could benefit from that).

[keme]

Long shot: Could it be that the main resource hog is in converting from Excel context to Calc?

• Is it equally slow if you save your file as ODS from Excel and try to open that?
• What about the paste test you did? Did you save and reopen that?

[GoodMovie3]

Thank you, Mike and keme.

1. I will post this as a bug and as an “enhancement” (off by default).

2. Yes, I did try both directly from Excel and also via Notepad to strip out formatting. Just now I copied from LibreOffice Calc into Xed and back into a fresh LO spreadsheet. The result remains the same.

3. The thought that by default automatic row height and column width is a function everybody wants is mistaken. I’ve been using spreadsheets for decades esp. Lotus123 and later Excel (even dabbled with Multiplan). Not one of them had this function on by default (they could all do this).

The width adjustment is indeed sometimes useful, but I don’t use row height adjustment even once/year on average through decades of quite intensive spreadsheet use. Not being able to turn it off permanently would result in enough waste of time at work for it to be easily economic for me to remain paying for multiple copies of Excel - else I’d too often have to manually correct back to what’s required.

[sebsebseb123]

I’d just like to +1 this request. My files are only a few thousand lines so I don’t experience a huge CPU drain, but I do see it hang for a few seconds. Mostly, it’s just real annoying when that’s not the behaviour I was expecting… plus, when I do “fix” it by manually adjusting the hieghts again they all look awful, the tops and sides of text are poking into the cell… just makes it hard to read.

[maxcala]

+1
(just because I can’t add 1e9)

[m2ts]

@GoodMovie3, do you know of any progress in this? I have an ods-spreadsheet, completely made in LibreOffice Calc with some sheets. The two largest ones have about 32633 lines.

Open

• If I open in LO 7.0.4.2 it takes 2minutes, 24 seconds to load (10 s ‘load’, 15 s ‘calculate’ and the rest is to adjust row height).
• If I open open the same osd in Excel 365 it opens in 20 seconds. That is more than 7 times faster!

Change: If I change a cell, some others cells need to be recalculated.

• In Lo it takes 2 minutes, 10 seconds. This time the status bar doesn’t mention ‘load’, ‘calculate’ or ‘adjust row height’.
• In Excel 365 it takes 1 second. That is 130 times faster!

And that’s why I’m interested in the progress of this “issue”…

[Lupp]

May this be related ???

Using the API we can set for any row
row.OptimalHeight = False.
with the expected result. We can also explicitly set a height for all the rows of a sheet in one go by
sheet.Rows.Heigth = myPreferredHeight
If we try to disable .OptimalHeight for all the rows of a sheet in one go by
sheet.Rows.OptimalHeight = False
no error is thrown, but the statement is without an effect.

Sub tryDisableOptimalRowHeight()
doc = ThisComponent  
REM A document with only 1 sheet (traditional 2^20 rows) when testing. 
sheets = doc.Sheets
For Each sheet In sheets
 For Each row In sheet.Rows
  row.OptimalHeight = False
 Next row
Next sheet
row00 = sheets(0).rows(0)
Print row00.OptimalHeight
End Sub  

yields the expected result but takes about 8 min on my system (10y, Win 10, LibO V 7.2.0.2).
(Also: .OptimalColumnWidth is treated differently.)

[sokol92]

From a post on the Russian forum.
Let oRows be a range of consecutive rows in a Calc sheet. Experiments show that:

• The oRows.optimalHeight property returns the optimalHeight property of the first row of the range
• Assignment oRows.optimalHeight = True assigns the optimalHeight = True property for all rows in the range
• Assignment oRows.optimalHeight = False has no effect

Besides:

• Assigning the oRows.Height property to a positive value, in addition to changing the row height, also sets optimalHeight = False for each row in the range
• Assigning oRows.Height = 0 for rows with the optimalHeight = True property causes the row heights to be adjusted. This does not change the optimalHeight property for any rows.

[suokunlong]

This is bug 124098:
https://bugs.documentfoundation.org/show_bug.cgi?id=124098

There is already a propsal in the bug report comment, but is not currently fixed yet.

One workaroud would be select all the rows and set a manual row height.

[m2ts]

@erAck, thank you for pointing that out!