How to type DATA into a cell without affecting the FORMATTING (row height) of the cell

English
#1

I am using the version of Calc that came with Linux Mint 21.2 Cinnamon, namely, Version: 7.3.7.2 / Build ID: 30(Build:2) [Ubuntu package version: 1:7.3.7-0ubuntu0.22.04.4].

test3.xls (16.5 KB)

^When I go to cell D4 and type the word “DONE”, the height of the row is changed. This is aesthetically displeasing because the height of one row now differs from all the other rows.

If I then press Ctrl+Z, the change in data is undone, but the change in formatting (row height) is not undone.

My experience with other software, such as
(1) MS Excel (Windows)
(2) KingSoft spreadsheets (Windows)
(3) Google spreadsheets (online),

is that I am allowed to do data entry without affecting formatting (such as row height).

On Linux I am using Calc, so I am wondering how I can do data entry without having the formatting of the spreadsheet randomly and incomprehensibly modified?

I am not wanting to modify the formatting, so basically I want formatting=read_only, data=write_allowed. Normally in other software this would be the default situation if I just type data (except for conditional formatting, which by definition is supposed to update based on the data in the cells).

It is true that there is conditional formatting in the spreadsheet, but it is only for color; there is nothing in the conditional formatting that should affect the row height. And I do not have this problem with the software (1), (2), (3), mentioned above.

#2

See Row height, especially Default value.

1 Like
#3

The documentation you linked to seems to suggest that if the Default Value checkbox is checked, then “The height no longer increases automatically when you enter larger contents.” I clicked the upper left corner to select all cells in the sheet and then went to Format - Rows - Height. But I observed that the Default Value checkbox was already checked. I tried unchecking it to see if it would help. But when I went back to Format - Rows - Height, it was still checked, implying that: (1) nothing had changed; (2) it is impossible to uncheck the Default Value checkbox.

However, in spite of the fact that the UI suggests that nothing had changed (box checked before, box still checked after), this appears to have fixed the problem: I can now type in the cells without the row height changing. This makes literally no sense and seems buggy as heck, but I’ll take it if it works (as it seems to — so far at least).

However, now I have a new problem: when I save the .xls file and reopen it, the widths of the columns have changed a little bit. Is it possible to keep the column widths from changing? (I only want to enter data, not change formatting.)

I tried the following: I clicked the upper left corner to select all cells in the sheet and then went to Format - Columns - Width. I observed that the Default Value checkbox was not checked. I tried checking it to see if it would help, but it caused the widths of all my columns to change, which is the opposite of what I want.

#4

Save as .odt
.
Saving in .xls (wich is even for MS obsolete) means the data has to be converted to xls on saving and back to .odt internal representation on loading.

2 Likes
#5

I use .xls for maximum compatibility. I still have a computer with Excel 97 on it. Why does “converted to xls on saving and back to .odt internal representation on loading” require that the column widths must change? The .odt format is not capable of storing arbitrary column widths? If no, Why wouldn’t the column widths change upon initial loading of the file? Because it was already in .xls format when it was first opened.

I could try using Google spreadsheets or KingSoft spreadsheets to convert the .xls to .xlsx or .odt before opening it in LibreOffice Calc, but I’m not sure it would solve my problems, because there seems to be, fundamentally, a problem with Calc randomly changing the formatting (particularly the heights of rows and widths of columns), and I don’t know how to turn this behavior off, which is why I posted this question.

Fundamentally, I want to be able to edit the data in a spreadsheet without heights of rows and widths of columns changing. Keep the formatting, edit the data.

Oh well, at least I figured out how to keep the row heights from changing (hopefully). But is there a way to automate the process where I click the upper left corner to select all cells and then Format - Rows - Height and then (not)-un-check the checkbox, so as to avoid having to repeat all this clicking for every sheet of every file that I open? A macro or a Global Permanent Option?

#6

.odt/ods etc is perfectly capable to keep format, but you decided not to use it.
.
It is not required to have different values in Excel and Calc, but you decided to force translation on every open and save. Compare to convert from liter to gallon, cm to inch on every load/save. Can work, but nobody gives guarantees for round-trip.
.
The software with max. compatibility for Excel 97 is Excel97. I helped in the past enough people to install compatibility packages in MS-Office to read files from older Office and Works to have any trust there. If you wish to use it: old licenses are quite cheap…

#7

Likewise, when you open a Lotus 1-2-3 document with Excel, the file will be translated into an Excel document and back when you save it in the foreign format again. For maximum compatibility with dozens of applications on all platforms, I would recommend the Open Document Standard as implemented in your new office suite.

#8

Concerning the settings for .OptimalHeight (rows) and .OptimalWidth (columns) there are inconsistencies in LibreOffice Calc from the beginning:

  • Both these settings (the features) behave basically different.
  • There is no menue path offering a way to switch the features explicitly off.
  • Even resorting to user code it’s strange:
    sheet.Rows.OptimalHeight = False does not work, but also doesn’t throw an error (e.g.).
    Doing it for one row at a time works, but to do it for all the2^20 rows of a current sheet may take 15 minutes.

But: If you want to set a fix row height for all the rows, say 0.45 cm, you can do so, and everything works as expected as long as you not set optimalHeight for rows later explicitly or by pasting formats…

1 Like
#9

To the contrary, the .xls format is not capable of storing arbitrary column widths. It stores in units of characters of the standard font (whatever that might be) with values 0…255.

3 Likes
#10

Hello all, I wanted to mention, something I found out regarding the issue with the column width changes. Before I uploaded the file, I went to File - Properties, and I unchecked “Apply user data” and “Save preview image with this document” (not sure if relevant) and then I clicked Reset Properties and saved a copy with Save As. I thought that this would just remove metadata like the Title of the document, etc., but I have found that if I work with the original file (before I changed it before uploading) and do not do this step with File - Properties - Reset Properties, then the column widths do not change. So that’s interesting. I wish I understood why resetting properties affects the preservation of column widths (on subsequent saves), but at least now I know that it does. If I avoid doing that then column widths appear to be stably fixed to what I set them (at least so far).
.
All that I have to do to keep the row heights from changing is select all cells then go to the Row Height menu and then click OK (without changing anything). After I save, the (non-)change is then permanent as far as I can tell, so I don’t have to repeat every time I open the document. But in general I do have to repeat this for every sheet in any (new) document I open (which isn’t a problem for this particular document since I only have to type in the first sheet).
.
I actually didn’t even mess with the Optimal Row Height menu which is the property that Lupp mentioned in their reply above (which I just now read). But I will re-read that comment more carefully and also maybe investigate ThisComponent.IsAdjustHeightEnabled = NOT ThisComponent.IsAdjustHeightEnabled which is something else that turned up in a Google search. Because the only other thing that I might want to do is automate this better so that I do not have to repeat the thing with the Row Height menu for every sheet, every time I open a file for the first time in Calc.
.
edit:
I think I am maybe starting to understand better. The Row Height and Optimal Row Height menus are perhaps mutually exclusive? So even if I do not change anything in the Row Height menu, I am still applying a change when I click OK, if the rows were previously set to an Optimal Row Height (which apparently they are by default?). This is new to me and was confusing, because the spreadsheet software I worked with in the past does not have “Optimal Row Height”, only Row Height.

#11

Sorry, regarding the talk about “Reset Properties”: I think I may have accidentally provided misinformation.
.
I have discovered that if I copy the original file and then Save it three times, the widths of the columns randomly and unpredictably change. It is not necessary to edit the file or click “Reset Properties”. I just copy the original file with a file manager and repeat the two steps
.

  1. Open file
  2. Ctrl+S
    .
    three times, and the column widths change. No edits required, but it takes more than one Ctrl+S to make it happen: specifically three of Ctrl+S. And you have to close and re-open the file between each of the three.
    .
    I think I need a macro to turn off uncommanded changes in row heights and column widths for every row and every column of every sheet.
    .
    My columns are already different widths (intentionally), so I can’t do Select All and then go to Column Width, because that will cause all my columns to change to one width.
#12

:smiley: :smiley: :smiley:
I can not believe…
…For compatibility… ???
That file format has never been standardized…

#13

but, if he still uses Exel 97:

#14

If only there was a current office suite that could run on a computer so old that it has MS Office 97 on it and could use current file formats.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.