Skip to main content

Calc v Excel: the second test

Following on from the first test, it seemed unlikely that Calc 6.0.3.2 and Excel 2016 could share data cross-platform without damaging the data.

A second test confirmed that Calc and Excel do indeed damage data relative to each other's data specifications.

This second test used the same two files - "dbase" and "cashbook" - reset back to their fully operational level in Excel. And the same two machines, "Legolas" the Windows 10 and "Gandalf" the Linux Mint Xfce 18.3.  The files reside in Google Drive, so are synced to each machine using Google Backup & Sync for Windows and Grive2 respectively.

Actions

On Gandalf, the two files were opened and updated in line with normal practice.  Backups of both files taken (copy & paste & rename on Gandalf).

Findings

Using Calc is quite OK, quite fluid, quite normal, as it should be.  Getting used to different menu commands is fine; once learnt, it's learnt, just get on with it.

In both cases, Gandalf's Calc complained about the maximum size of the sheet and asked about links, as per test 1.

Updating the data in both sheets on Gandalf was fine - some keyboard limitations notwithstanding - and seemed to operate normally.  Both files saved from Calc as both XLSX in ODS.

On opening both files in Excel on Legolas, Excel ran a repair process immediately:

  • in cashbook, Excel reported the correct number of sheets, but one sheet was completely blank.  The sheet in question contained nothing special: two lists used as a lookup with named ranges to define the data ranges.
  • in dbase, Excel found the same "corruption" of the pivot table that it did in test one.  The pivot table was reformatted, but otherwise apparently functional, other than Excel complained the pivot data had no underlying data, so needed a refresh to retrieve the underlying data.

The linked files were also scribble.

  • In the XLSX version of both files, Legolas reported the links as a strange hybrid of both Windows and Linux directory structures.  In both cases, the filepath started as Windows until it got to "users", then turned into Linux from the user folder onwards.  Gandalf's Calc had saved the XLSX to a folder "grive", which exists in Windows as "Google Drive".
  • In the ODS version of both files, Legolas reported the links wholly as Linux filepaths.
In the XLSX file, Calc had created a number of new named ranges.  None of the named ranges were objectively necessary, but only a selection of them could the user delete.  In particular, three named sheets prefixed with "___anonymous" could the user not delete using the user interface.  It might need a macro to delete these (macros are outside the scope of this project).

Conclusion

The conclusion remains steered in the direction of test 1.

Test 2 revealed one other option for the cross-platform user migrating from one platform to another:

  • choice 3 (following on from test 1, "What does this test say about how the Windows user could migrate to Linux Mint?"): to open data in Mint's Calc from XLSX, but always save in ODS, update the data, grive, then open both XLSX and ODS in Windows's Excel, copy the data from ODS to XLSX, save the XLSX and delete the now-redundant ODS.
This would be awfully painful, even for small workloads.

There are more workflow-related choices which the cross-platform/migrating user could consider:

  • choice 4: a "hard" version of choice 1, migrate as a "big bang" from Excel to Calc in Windows.
  • choice 5: another "hard" version of choice 1, migrate as a "big bang" from Excel to Google Docs in either Windows or Linux.
  • choice 6: having migrated to Google Docs, save all files as either XLSX or ODS as backups to the local machine for another program - such as Duplicati - to back up to the data to a backup storage solution (e.g. Amazon S3).

All of these are fiddly, and all of these are triggered by Calc and Excel disagreeing over how to handle XLSX files!

Completed May 2018.

Comments

Popular posts from this blog

An attempt at full-disk encryption: Vera Crypt

The project is to build a Linux Mint machine to have the identical functionality and ergonomics as the existing Windows 10 machine. This stage relates to testing full-disk encryption using VeraCrypt . Environment & required functionality Full-disk encryption needs to run on the following machines: The Linux Mint Xfce 18.3 laptop " Gandalf "; The Windows 10 laptop " Legolas ". The objective requirement is to protect user data from the physical theft of the physical machine, to provide an additional line of defence against data loss. This is probably more important for Windows than for Linux Mint.   Even so, in both cases, the operating system is likely to log activity which can reveal personal data and user (meta)data. Full-disk encryption does not mitigate against Microsoft’s sinister telemetry functionality, for which the main solutions seem to be: Either to use tools whose developers are constantly on the prowl, hunting for t

The Big Bang: Microsoft Windows goes for good, positive adaptations required

On 27Mar2021, Linux Mint ate Microsoft Windows 10 on Legolas. Three months on, I conclude beyond any doubt that wiping out Windows was the best decision I ever made. The second best decision I ever made was to test Linux Mint in Virtual Box five years ago. The third best decision I ever made was to take ownership of the learning curve that migrating in Windows really entails. A quick reminder: what’s Microsoft Windows like nowadays? I still need to use Windows at work. I cannot easily describe how painful it now is to use Windows. So I’ll try to describe it difficultly. My work machine is a powerful beast, but it exhibits constant latency. For a keyboard-orientated power user, this means that some keystrokes go walkabouts when other services on the Windows machine go to nuclear war with each other, scrambling to feed their narcissistic self-importance for besieged system resources wholly at the user’s expense. Something on Windows tends to clear the keyboard buffer randomly, resulting

Partial decommissioning of Gandalf

The project is to build a Linux Mint machine to have the identical functionality and ergonomics as the existing Windows 10 machine. After a few months of continuous use of Linux Mint – mainly for simple browsing during workday lunchtimes and weekdays –   the headline of the project remains that wholesale migration from Windows to Linux is not functionally possible , for the reasons cited therein.   In this instance, the short story is no Microsoft Excel, no Foxit PDF browser for Windows, no on-the-fly VPN client. Consequently, there is no further development use of Gandalf.   Gandalf has other issues as well: even in last days as a Windows machine, the hard drive had developed a nasty habit of intensive operation, wasting time and resources on misadventure not commanded by the user.   The habit has returned to Gandalf as a Linux machine, meaning that the user needs to sit around for up to fifteen minutes from time-to-time while Gandalf faffs around for no good reason.   There