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

OnlyOffice: keyboard inaccessible, so not useable, therefore not tested

I installed OnlyOffice https://www.onlyoffice.com/. I had intended to test it with my now-standard test suite of two linked workbooks.

Unfortunately, in spite of a promising look, I quickly discovered that - with one exception - everything was navigable only by mouse.

That makes it a child's toy.  Unfit for purpose!  No point in testing it further.

I uninstalled it within 10 minutes of installing it.


Adjusting screen brightness

The machine on which Linux Mint is installed an old Acer Aspire 5732Z ("Gandalf")

It has buttons to adjust the brightness of the screen's backlight.  When the user uses these buttons, Linux Mint correctly presented a fading-popup box (a slider bar) to denote relative brightness.  But Linux Mint did not actually adjust the brightness of the screen.

It seems to be a known issue in the Linux Mint forums and solved in multiple  stages by the Easy Tips Project.

I followed the instructions on Easy Tips section 5.2 in Gandalf's admin account, then re-booted, then logged in using the user account, and the brightness adjustment function worked correctly.

Easy Tips asks the user to discover the relevant property of the machine, then creates a file that contains a script of parameters that other programs in Linux Mint understand.

This method worked for Gandalf, because Gandalf has an integrated Intel chipset.

Useful commands at the Terminal ALT+T (or the Mint) menu gets to the …

Keepass and KeepassX

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 password manager, Keepass.
Environment & required functionality A number of encrypted password vaults synchronise between three machines:

The Linux Mint Xfce laptop "Gandalf";The Windows 10 laptop "Legolas";Another Windows 10 machine, name withheld to protect the guilty.
The synchronisation agent is Google Drive in Windows 10, and grive2 in Linux Mint.
Alternatives My original decision to use Keepass was in 2016 and was based on:

Keepass is open-source;Keepass is locally stored, not stored in the cloud;Keepass does not automatically plug into the browser (a plugin permits this if ever necessary);higher security standards at the office, worth deploying at home;portability of the password vault via Google Drive, encrypted such that Google would not be able to slurp data from an otherwise-unencrypted vault.overall …