Yet another Calc lag question (specifically on mouse clicking)...

English
, , ,
#1

I’m using LO 6.4.4.2 on MacOS Mojave 10.14.6, trying to interact with a spreadsheet I’ve used for my banking for over almost 20 years. It’s got about 9700 rows, and really only one simple formula which calculates a “Balance” column from “Credits” and “Debits” columns. It’s always been spry and worked fine in Excel.

In Calc, though, it’s fine when I open the spreadsheet (which, btw, is now an .ods file), but once I do anything like hit Save, clear an AutoFilter, etc, any mouse click interaction is agonizingly laggy. Click into a cell, cell is selected at least 2 seconds later. Click into a cell and move the mouse pointer, the cell where the mouse pointer moved to is now selected (2-3 seconds later), not the original cell.

I’ve tried several troubleshooting tips already: disabled OpenGL, imported the sheet into a fresh new spreadsheet, deleted all comments (there were many), open LO in Low Resolution…none of this fixes the problem. Lag city. This occurs as an .ods file and in the original .xlsx format.

Trying to love LO, but I’ve got a foot out the door. This is driving me mental. Any suggestions?

OPENING CALC SPREADSHEET IS VERY SLOW
#2

RoyalScam,

The issue begin after installing LO 6.4.4.2?

#3

Nope. Began many versions ago, but I keep updating with the hope of a fix that never arrives.

#4

RoyalScam,
Have you tried to uninstall, previously to a new installation?

#5

I’d suggest to reset your user profile (and if the user profile is really causing your issue, a reinstall or waiting for next releases won’t help).

To reset the user profile perform:

  • Start LibreOffice
  • Start in Safe Mode using Help -> Restart in Safe Mode and confirm the restart using button: Restart
  • Archive your profile expanding Advanced (click on the word Advanced right above Help button) and click Archive User Profile button
  • Select option Reset to factory settings
  • Activate [x] Reset entire user profile
  • Click button Apply Changes and Restart
#6

@anon73440385,

Can you make a new question like “How to reset the user profile” and post your previous comment as an answer?
I see that is more info at LibreOffice user profile - The Document Foundation Wiki, but not so clear for all.

#7

@LeroyG - I won’t do something like that. From my experience too many people don’t look for potential solutions of their respective issue, so helpers here are forced to repeat the ever same things anyway. If FAQ is not clear - then FAQ should be modified.

#8

@anon73440385,

But if posted, we can comment “Reset your user profile, see instructions (link)”, with no need to repeat. And if instructions change some day, can add “For version X.X and up …”.

#9

@LeroyG - No, I did not try an uninstall before reinstalling, because from what I’ve read it doesn’t usually fix anything like a User Profile reset…

@anon73440385 - I did follow all your instructions. When I clicked “Archive User Profile”, I kept getting an error that the zip file could not be created. So, I wound up clicking “Show User Profile”, creating my own zip of the User folder, and dragging it to my Desktop for safe keeping.

Went through the rest of your very clear instructions (they really should be pinned somewhere) and relaunched the spreadsheet. As usual, I clicked around with no lag…then I hit “Save” and the lag reappeared as before. No fix.

#10

When I clicked “Archive User Profile”, I kept getting an error that the zip file could not be created.

This sounds a bit like LibreOffice doesn’t have Full Disk Access on macOS and this is also known to cause problems. Please follow instructions at How to Give System Permissions for Apps on MacOS Catalina (Should work similar on Mojave) to provide Full Disk Access to LibreOffice.app.

Alternatively you may also try to disable Lockfile creation:
Go to LibreOffice -> Preferences -> LibreOffice -> Advanced -> Button: Open Expert Configuration -> Search: UseLocking and set org.openoffice.Office.Common | Misc | UseLocking to false
(Don’t recommend to use that setting permanently, but for testing purposes it seems to be reasonable to find the cause of your problem).

#11

I granted LibreOffice.app Full Disk Access (thought I had done that previously, guess not). Started LO (it had been quit), opened the spreadsheet, clicked around, no lag, hit save, lag returned as always.

Should I now disable Lockfile creation, or is it a moot point?

#12

I’d try it now.

Have you ever tried to

  • Create a new .ods file
  • Copy your data from the old file
  • Edit -> Paste Special -> Paste Special... with only selected options
    [x] Text
    [x] Numbers
    [x] Date & time
    [x] Formulas

into new file?

#13

RoyalScam,

Adding to @anon73440385: Do not copy all cells, only the range with data.

Another idea: Do you have compared size of recent file and backup one? With other .ods happens the same?

#14

@anon73440385 - I just disabled Lockfile creation. No change in behavior. I will now follow your copy/paste suggestion. I did previously start a new spreadsheet and imported this sheet in via Sheet > Insert Sheet. But I’ll go clean with your method, and take @LeroyG 's advice on data range. Will post update shortly.

@LeroyG - Sizes of all files are virtually identical, give or take a few KB.

#15

PROGRESS!!!

I believe the problem is with Comments. I followed @anon73440385 and @LeroyG suggestions above and here’s what I found with further experimenting (creating new spreadsheets each time and changing the paste options):

  • Pasting only the options @anon73440385
    suggested above: No lag after saving.

    Pasting @anon73440385’s suggestions plus
    Comments: Lag RETURNED after saving.

    Pasting @anon73440385’s suggestions plus
    Formats: No Lag after saving.

Now, previously, I did experiment with deleting all Comments from a copy of the original sheet, as I’d read suggestions in other posts that Comments might be to blame. It didn’t fix the lag, so I discounted it. It seems creating a new spreadsheet and pasting in virtually anything EXCEPT Comments does the trick.

Not going to lie, this SUCKS. Excel handles Comments just fine, apparently LO does not.

I guess my next step is to add a new column to paste in Comments text? Hopefully there’s a fast way to do it.

#16

Sooo…I guess we’re done here? :wink:

Seriously, thanks for the help, @anon73440385 and @LeroyG. If this is a bug I should report, let me know. I just don’t see why LO can’t handle comments.

I’m going to re-enable Lockfile creation, I guess. Maybe experiment with re-adding my old User Profile, though I don’t think I had any weird settings anyway. Maybe I’ll leave well enough alone.

If anyone knows of a quick way to get text from multiple (or all) comments to paste into a new cell on the same row, let me know. I’m going to start getting this spreadsheet back in fighting shape tonight.

#17

So, it appears that if you have major lag in a Calc spreadsheet - and that spreadsheet has Comments inserted in various cells - those Comments are likely causing the lag. At least, they are in mine.

Simply deleting the Comments does not fix the lag. Creating a new spreadsheet and pasting in all the data EXCEPT THE COMMENTS seems to be the fix. Here are instructions pasted from various comments in the thread (but you really should read the entire thread for context):

Create a new .ods file
Copy your data from the old file
Edit -> Paste Special -> Paste Special... with only selected options
[x] Text
[x] Numbers
[x] Date & time
[x] Formulas

Do not copy all cells, only the range with data. Use Edit → Select → Select Data Area to accomplish this.

BTW, I was also able to tick the [x] Formats option as well, in the Paste Special options above.

As long as the Comments are NOT ticked in those options, and do not copy over to the new spreadsheet, you should be golden. At least I am.

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