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.