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

Post a Comment

Popular posts from this blog

Scanning & OCRring to PDF: Simple Scan, gimagereader and gscan2pdf v NAPS2 for Windows

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 scanning paper documents to PDF and digitising the scanned text via optical character recognition. Environment & required functionality The scan-and-OCR function needs to run on the following machines: The Linux Mint Xfce 18.3 laptop " Gandalf "; A Linux Mint Xfce 18.3 virtual machine " Gimli "; The Windows 10 laptop " Legolas ". In any modern office - whether at home or at work - some transactional documents and documents from public authorities still arrive by snail-mail. This requires the ability to scan all documents, optionally with the digitisation of scanned text (typically via optical character recognition). The hardware is an old HP OfficeJet Pro 276dw, connected to the LAN instead of directly to a workstation. Alternatives There are two strategies: To use the software pr...
Post a Comment
Read more

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 th...
Post a Comment
Read more

FreeFileSync: data synchronisation over the local area network

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 data synchronisation over the local area network ( now that, at last, a useful network connection has been established !) Environment & required functionality The network should be a local network - to share files and folders - between the following machines: The Linux Mint Xfce 18.3 laptop " Gandalf "; The Windows 10 laptop " Legolas ". The network is a local area network from a domestic router, the usual 192.168.x.x thing. Alternatives Peer-to-peer networking (file sharing, simple two-way server-and-client relationship) is the most efficient way for bulk data transfers, so if the network doesn’t work, then the test has failed. One alternative is already successfully tested and in use: Google Drive (via Backup & Sync for Windows and grive2 for Linux Mint) .  However, this is limited to th...
Post a Comment
Read more