LibreOffice 7.5.3.2 - In Calc, XLSX file, increasing a column size creates text display problems

English
, ,
#1

Nice, requiring people to create an account just to get help…

ANYWAY… Installed LO version 7.5.3.2 (as per “about” section) in Win 10 pro 64 bit.
Annnnnd, after solving a myriad of problems, I’ve encountered a problem which I couldn’t find a WORKING solution for:

In LO calc, a file originally created in Office 2007 (XLSX) everything was working fine UNTIL, I naively resized column C with my mouse. Nothing special, maybe the most basic thing people do with a spreadsheet software.

And then I returned to column F where I enter text, but this time, when I type text, it is invisible (WAIT FOR IT MR GRAPHICS MAN), well invisible at least for a few words. If the sentence is long enough, the words begin to appear (but not the ones at the beginning of the sentence).

All of the text does appear once I click another cell, but when double clicking again the former cell with the text I’ve just written, half of the text again becomes invisible…

Besides that, double clicking in column F, on a cell with text that was written before the resize, makes THAT text invisible in its entirety (until I click somewhere else).

None of this UFO phenomena happens in columns B and A, so the effect is only on columns with a higher letter than the one we are resizing.

BTW, in the “Formula” rectangle I can see the entire text all the time (and BTW2, there are no formulas in this specific file).

Anyway, resizing the former column to it’s original size, and problem disappears…

Resizing it again to some OTHER bigger size than original, and a different portion of an entered text is invisible. Seems there’s a correlation between how much you increase the column, and how much text will be invisible at the beginning of the sentence.

Faild attempts to solve:

  1. I found this post telling me to go to Options —> Libreoffice —> View —> Remove checkmark from OpenGL for all rendering.

That didn’t work.

Instead of OpenGL there was only a thing call “Skia”, but I fearlessly fiddled with that and all kinds of combinations in that settings window, and as said, it didn’t work.
Yes I restarted LO as requested. Nothing. So I returned graphics options to original.

  1. Saving the file in ODS format. Problem persists.

  2. Creating a new spreadsheet in ODS format, and then copying over there only the values from the XLSX file does solve the problem but some values are not copied correctly, and anyway, this solution doesn’t solve the ton of work you need to do now, in order to reconstruct your former spreadsheet.

AND IF THAT IS NOT ENOUGH, problem disappears in ORIGINAL file (XLSX) if choosing “Align left” instead of right. Just discovered that as I’m writing this post…

When choosing “Align right” problem returns.

This is irrespective of Right to left / Left to right mode (the two most right side buttons at the top bar, next to “Conditional” and “Border color” icons (original menu layout).

OK, another update: Now problem DOES NOT RETURN when choosing “Align right”.
Either “Align right” or “Align left” - problem is gone. Don’t know why, or how, as I did nothing in between.

Wait… Problem returned. Now again only “align left” solves it. The only thing I did before that was to delete data from that ODS test file I created to copy values to, since I thought the problem was gone.

Reminder: This happens only after I increase the size of a column. When returning the column to its original (more or less) size, problem disappears, regardless of alignment.

#2

My guess is that you have your cell with Word Wrap turned on AND the row has been closed up to a single line AND the cell is Vertically aligned to the Bottom.

You could click on the cell and press Format > Clear direct formatting (Ctrl+M) to return to default settings. Or right click on cell, choose Format Cell and change settings in the Alignment tab to suit. Or select row, right click and select Optimal Height

Otherwise, please post a small anonymised sample.

[Edit] There does appear to be a bug in Excel 2010 where although the cell C3 is aligned to Bottom it does not show the bottom of the text but somewhere in the middle. Screenshot from Excel of this file,
HiddenContents.xlsx (8.1 KB)

OnlySpacesShouldBeSeenExcel

#3

OK, tried your suggestion:

  1. In the xlsx file opened in Libre, I selected all the cells with the problem (I know you said to select only one cell, but there shouldn’t be a difference right?).

  2. Selected “Format” from the upper menu, and clicked on “Clear direct formatting”.
    Now everything was fine, even after I changed alignment again, and Right to left, Left to right buttons etc’.

  3. Out of curiosity, I decreased the size of column C, but no text problems manifested when I entered new text in column F.

  4. ONCE I increased the size of column C above the size it was at the time of paragraph 2 (where I applied “Clear direct formatting”), and THEN tried to enter new text in column F, the problem returned.

So, to summarize the problem so far:

In XLSX files opened in Libre, INCREASING THE SIZE OF A COLUMN, seems to cause text display problems, when entering new text (either English or Hebrew) in columns with a higher letter than the column which size was increased, and ONLY when text is aligned to the right…

And it becomes weirder even more:

Choosing “Align left” solves the problem, but only after

  1. I enter new text in the aligned to left cell,
  2. AND scroll horizontally to see if the text does appear on the left side of the cell.
  3. AND choose “Align right” after I see the text does appear.

But once I scroll back (horizontally) and choose a different cell, the problem is back, including at the former cell (WHEN I double click on it again).

#4

I changed the bolding in your quoted text to place emphasis where it belongs

Select the entire column F and press Ctrl+M to remove the direct formatting in the rest of the column

#5

Now it doesn’t solve the problem, unless I scroll a bit horizontally, towards F.
When I scroll back towards C, problem returns.

If I resize column C to it’s original small size when I first opened the XLSX file in Libre, problem disappears, regardless of scrolling.

#6

Upload a small anonymised sample. You can add it by clicking the up-pointing arrow.

1 Like
#7

Deals - sample file - 2021-2-27.xlsx (16.4 KB)

This is a copy of the file I’m having the problem with.

The file is for keeping watch over my credit card deals
(provided English translations/explanations for columns
in the first tab, to save you time, in case it is relevant).

No formulas in the file, I use a spreadsheet here just because it is more comfortable than building a table in Word / Writer.

Text is mostly in Hebrew (Right2left).
Text direction (and date hierarchy - D/M/Y) was properly ordered in Excel, but when opened in Libre, it is sadly a bit messed up in some places. I’m mentioning this in case it is relevant.

Anyways, I hope this will help.

Peace.

Edit: I divided the text above into paragraphs, to make it easier to read. How come the forum system groups it up into one paragraph with no spaces?

#8

Although you don’t have formulas in the spreadsheet it is poor practice to have empty rows between data rows; better to create a cell style with extra height.

Starting with the easiest, some dates are text. See How to convert number text to numeric data for converting or easier for the few shown is to manually delete apostrophes where they appear.
DateAsText

All your dates appear as M/D/YYYY on my PC which is how they have been formatted (default here is DMY) so they should appear that way on your computer. Better to use ISO standard, YYYY-MM-DD which never changes order.


To force separation of paragraphs here you can enter <BR>


I don’t see the issues of slow or disappearing text that caused the question. I even set Skia to default although I normally have Force Skia software rendering ticked to avoid screen artefacts (which aren’t yet appearing either).

I suspect it could still be a graphics issue. Update your graphics drivers, check if they really are the latest. My Acer laptop software said I had the latest drivers but I went to Acer site and found updated drivers which I installed. After that I get regular updates from Intel and many issues went away.

Also, tick the box Force Skia software rendering

You could also go to Control Panel\All Control Panel Items\Programs and Features, right click LibreOffice 7.5.3.2 and select Modify Repair. Restart Windows afterwards even if not asked to do so.

Cheers, Al

#9

  1. Updated to latest display driver = Bug remains.

  2. Force Skia software render = Bug remains

  3. Modify Libre installation… Modify what exactly?

  4. Regarding date order problem, solution offered is too complicated for regular people.
    I just use: 25//1/2023 instead of 25/1/2023, and it works (also works in Excel, whenever Excel thinks it can save me time by being “smart”). I don’t use formulas on dates in spreadsheets, I’m just a simple dude, trying to make Libre do simple stuff.

At the specific document I didn’t use the double slash dating, because Excel, at the time, wasn’t feeling like being “smart”.

After installing win10 pro, thought about giving Libre a chance, despite having really bad experience with Linux and its apps (~2years of using it as default OS + apps), so Doc was opened in Libre, and Libre of course wanted to “educate” me how to write dates properly.

#10

Sorry, I should have written Repair.

The reason the ISO standard of YYYY-MM-DD came into being was to avoid confusion between US and other date formats. Without further context it is impossible to tell if 1/2/23 is 1st February or 2nd January. Spreadsheets are more likely to suffer from this confusion as they have often less context and the implications of such an error can have greater consequences if finances rely on them

#11

OK, repaired installation, but bug remains.
My hunch is that it is something related to graphics, but disabling all graphics options altogether in Libre (hardware, force skia, etc’) + restarting windows in Safe mode didn’t eliminate the problem.

I can live with it, since I don’t do anything even remotely sophisticated in Calc (or Excel).

#12

I see it now, if RTL is set AND column F is wide AND is right-aligned AND the column is only partially visible at the edge of the screen, then if I try to edit a cell in F with a lot of text, the text in the cell disappears.
I don’t see a bug with that description so I think you might be best to report a bug, see How to Report Bugs in LibreOffice - The Document Foundation Wiki
I have attached a much shorter sample with column split in F for 1920 x 1080 display. The contents disappear in the yellow cell if double-clicked to edit.
DisplayDisappearsInColumnF.xlsx (6.9 KB)

Note: if Remove direct formatting is implemented (green cell) then no problem and the text remains right-aligned

1 Like

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