My basic question is this... Why does Libreoffice Calc seem to prefer Excel files to it's own .ods files?. Read on for more.....

For a while now I've been wanting to make the switch from Windows to Linux. Currently I run both, however the last stumbling block stopping me from going "all-in" is my Excel spreadsheets. I have Libreoffice Calc installed but it runs WAYYY slower than Excel!. To compare the two I made an Excel workbook of 5 sheets, each with 4000 rows and 25 columns, just numbers and dates. As a .xls file it opens in Calc pretty much instantaneously. I then saved this as a .ods file and to my surprise it took Calc two to three times longer to open it!. To test further I then made, from scratch, a similar workbook using Calc, just numbers and dates again. It still takes Calc 2 to 3 times longer to open it's own file than the Excel file!!. Update: Just to add to the mystery, if I make a new Libreoffice Calc workbook from scratch, and then save it in xls format, Calc will open the xls version quicker than it's own native ods version!!.

So, is the .ods format just inherently slow or is something else going on here?. Any thoughts or suggestions much appreciated.

Interesting observation!
I had noticed (in all versions since the OpenOffice split) that the startup time is far longer for CALC than for EXCEL, but I am not seeing much longer document load times in CALC for different file formats. (Other than cst, it loads faster than a finished document in many cases.) Now that I have measured some test cases I find that there IS a difference, just not much.
I find it interesting that you have noticed the difference. On my machine I would never have noticed, as it is a small percentage of the average load time.

If you do not mind sharing: what version of LO are you running? What version of Windows, at what patch level? And can we talk about your storage (what kind of disk and do you use compression or encryption)? How much memory do you have? Also, what version of Java?

All of these underlie the running software, and can affect speed: perhaps some load or translation functions in LO respond to different restrictions.
I would love to push this question to the LO foundation community! Have you?

Thanks for the feedback. I've only done the side-by-side comparisons on my Linux laptop with LO ver 4.4.5.2, 4GB RAM and 128GB SSD, no compression or encryption, Java openjdk 1.8.0_65. I hadn't thought to see if I get the same results running LO in Windows (Win 7 SP1, same RAM and processor (2.4GHz), but regular HDD). Might try this later today. And no, I haven't asked about this in the LO foundation community - pretty new to Linux - didn't even know there was a LO foundation!.

Try Gnumeric, it's fast and more accurate than Excel. I don't like Libreoffice Calc, it is indeed slow.

I never tried a format in either Calc or Excel other than the old .xls format (which is a supported obsolete format in each). Calc is vastly slower than Excel at just about everything, including opening .xls file.

But over the years, calc hasn't gotten slower at the same rate that computers have gotten faster. So at this point the rotten performance is just any annoyance, not a major problem.

Both Excel and Calc randomly corrupt my large spreadsheets, which is a big problem. Excel corruption tends to be much less common and much more serious. But for either, I'd prefer more reliable to faster if I had a choice.

Until recently, I also used the same files in an actual super obsolete version of Excel, which was both more reliable and faster than any recent Excel or Calc.

I don't know about its accuracy, but Gnumeric is so much, much faster than Calc for me. Gumeric is probably much more limited, but those limitations have not been problematic. Obviously, YMMV

Why is Gnumeric limited ? What features is it missing ? (none that I know of)

As far as I know Gnumeric has no support for any macro capability - a feature I'm likely to need. I played around with it for a while and I agree it's quicker than Calc. At one point I thought it was the better option but eventually ruled it out for a) lack of macro capability, and b) lack of toolbar customizability.

You can use a plugin and then script your macros in Python or Pure.

Sadly the only Python I know is the one with the forked tongue, but I get your drift and I had read about that option. Figured that porting VBA macros into Calc would be the easier option than learning a scripting language from scratch, but if today's experience is anything to go by I may have to reconsider. Gnumeric would be a lot more appealing if I could do something about those toolbars - seems they're either on or off, with no way to [edit] customize them. Saw a manual online that said the toolbars should have a grab-handle to move them, but mine don't. [edit] They can be moved to the sides - possibility.

Well, you can find an xml file that describes the layout of the toolbars and menus at '/usr/share/gnumeric/1.12.14' or '/usr/local/share/gnumeric/1.12.14'. Just edit those a little bit and you'll have your toolbars the way you want them.

Ok, so I understand that Gnumeric is not Excel, nor Calc, it is more primitive, but honestly it works a lot better IMO. Fast, accurate, lots of flexibility as it lets you code in Python an actual Turing complete programming language.

I like LibreOffice a lot. It is the most powerful option I use, and quite close to MS Excel in many ways. What it is not is fast. I have always been bothered by the long initial load time.

But again, I do not use it for speed. I use it because it does exactly what I need it to do, handles more file formats than any other spreadsheet tool I have encountered, and does it all for FREE.

I tend to use it for things involving up to 300 lines of not more than 10240 columns (I had to play some tricks to get that many columns in version 4 - it has not yet come up in version 5), and most things FAR smaller. When you get to that size or near it, you are better off using a database. MariaDB or PostgreSQL will serve, with a little programming help and decent design.

All of that aside, if the difference in speed of loading/saving workbooks bothers you, why not continue to save the files in the faster format? After all the format is not important, it is the DATA that matters.

Well I eventually opted for gnumeric, mainly due to Calc's handling of comments. But now I've run into a serious problem - cell formatting, specifically the font, reverts back to default when you insert or delete lines above it. The formatting is still in place but doesn't display as such until the cell is refreshed. Anyone else seen this behaviour?.

Not seeing it here (I just tried it) gnumeric-1.12.23-1.fc24.x86_64; Fedora Rawhide

If I make a new sheet with formatting then I can't repeat the problem either. Only happens with my main sheet. Possibly something to do with the size? (4000x65). There's no conditional formatting on the affected cells, and after the formatting disappears, if I highlight them and change their colour (or do anything else to force them to redraw) then the original formatting re-appears.