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 the first test of LibreCalc for its compatibility, functionality and methodology relative to MS Excel 2016.
The synchronisation agent is Google Drive in Windows 10, and grive2 in Linux Mint.
Thus, OpenOffice is disregarded for now. As LibreOffice came with Linux Mint Xfce 18.3, might as well test LibreOffice.
There are loads of alternatives! Could spent an entire lifetime testing software instead of getting some work done.
The files sit in Google Drive and sync to Legolas via Google Backup & Sync, and to Gandalf via Grive2.
Crucially, one test file contains links to other Excel workbooks. This is crucial functionality in my data system.
The error most likely arises because Excel’s format xlsx contains vastly more rows and columns than its rivals (as far as I am aware).
As first impressions go, this is piss-poor: first rule of any communication is that the communication is relevant. Calc should have first established whether data was to be lost before bullshitting the user. A false error message is garbage, and it forces the user to waste time reading the screen and dismiss the dialogue box.
For workbook “Unposted cashbook”, Calc then asks another question of the user, “This file contains links to other files. Should they be updated?” In Excel, this question was disabled on grounds of irrelevance: as a user, I’ve got more important things to do than to answer questions that objectively don’t need to be asked. The correct answer to this question is therefore “piss off”, but Calc doesn’t offer that, so it should get “no”.
For the purpose of testing, however, let’s click on “yes”. And an immediate error arises! That three pointless dialogue boxes before one has even started! The error is “The following external files could not be loaded…”
Crucially, the dialogue box reveals the filepath of the file it could not find. Calc then demanded one dismissal for each of the files that it couldn’t link to. That’s 9 in “Unposted cashbook”.
So, that’s 11 dismissals needlessly demanded of the user before the file becomes readable. Not a great start.
When the file is at last readable, it looks pretty good. All of the data are present. All of the formatting is comparable, nearly identical. The main difference are the on-screen textmetrics. Calc presents tighter/narrower textmetrics than they are in either Excel or Google Sheets. Calc thus has a point in its favour, designing textmetrics to work for adults (Excel and Google are both parasitised by out-of-work graphics designers obsessing with large fonts and huge amounts of white space so that us desktop users can all be crippled to the same low level of dumb-arsed smartphone users).
But Calc has its limitations.
A power user would also typically use bookmarks to get around a workbook quickly, especially to get to fields that controlled regularly-used auto-filters. In Excel, F5 still produces a dialogue box from which accessing a bookmark/named range is fairly swift and even still with the keyboard.
I guess this is an oversight by Excel’s developers, whose ideological crusade against the keyboard user failed to spot this dialogue box.
In Calc, F5 also produces an equivalent dialogue box. But Calc’s developers have seriously bought into the ideological crusade against keyboard users by designing a box which contains elements that a keyboard user cannot access.
In Calc, paste special has the same broad functionality, but with slightly different keyboard access, and the keyboard access is less efficient. ALT+E+S then ALT+x to select an option then space to toggle the selected option. This is too inefficient for corporate/power use.
However, this finding is based solely on the dialogue box. Further testing would determine whether Calc, like Excel, creates data problems by the formatting of data. If Calc isn’t so confused as Excel, then the frequency of paste-special-values and paste-special-formulae in Calc should be fewer than in Excel.
Great news: in both Excel and Calc, access to a set auto-filter is CTR-↓ (it’s ALT+CTRL+R in Google Sheets). The majority of the resulting auto-filter dialogue box is very similar, but… in Calc, some of it is keyboard inaccessible. The screenshots are Excel first, Calc second.
However, Calc makes up for this with a more powerful custom filter functionality, as shown by the respective dialogue boxes. The screenshots are Excel first, Calc second.
Between Calc and Excel, there is broadly similar functionality. But there are three severe limitations in Gandalf’s environment.
For this file - synced between two environments by Google Backup & Sync/Grive2 - the correct absolute filepaths are:
In both environments, the common root (for all but one linked file) is folder "Ye2019", so absolute filepaths shouldn't be necessary.
After some edits to the test file, saved in formats XSLX and ODS from Calc, then grived to Google Drive. Thus ended the session on Gandalf. Legolas now had to make sense of the edits.
It turns out that the “problem” was that the cache of data used behind the two pivot tables in “dbase”. Which Excel solved immediately. So obviously it wasn’t a problem at all. Like Calc, Excel generated a false error message that sought to waste the user’s time in pointless confirmations/mouse-clicks/reading-bollocks on the screen.
All of the data are present. None are corrupted. All of the formatting is comparable, although the pivot tables have adopted the formatting that Calc saved onto the pivot tables. The formatting is thus different from that in the original XSLX file.
Opening the ODS file in Excel and in Apache OpenOffice (also present on Legolas) found similarly that the data was present and uncorrupted. The sole issue was that dates appear as integers and not human-readable dates.
The cross-platform user would be constantly relinking workbooks to cope with today's platform, then relinking them again tomorrow on tomorrow's platform, etc ad nauseum.
Therefore, this test finds that the timecost to the user to work on linked spreadsheets cross-platform is very high. By contrast, the timecost required to rule out a cross-platform environment is probably quite low for the typical Windows user.
Either way, it requires a huge amount of timecost to the Windows user to configure Linux Mint appropriately, and/or to find workarounds for functionality in Calc (or Excel-on-Wine) missing from Excel-on-Windows.
To use Wine, Mono, Crossover etc substantially compromises the scope of this project, which is to migrate from Windows (not use bits of Windows in a more awkward way, and risk the Linux Mint environment in the process: might as well stick to using Windows and ignore Linux Mint).
There is no solution to the issue of absolute filepaths of linked files between platforms.
In all probability:
Tests completed 28Apr2018.
This stage relates to the first test of LibreCalc for its compatibility, functionality and methodology relative to MS Excel 2016.
Environment & required functionality
Excel and Calc both need to be used on the following machines:- The Linux Mint Xfce 18.3 laptop "Gandalf";
- The Windows 10 laptop "Legolas";
- Potentially 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
The salient alternative to LibreOffice is Apache OpenOffice. At first glance, these two offerings seem too similar to eachother.Thus, OpenOffice is disregarded for now. As LibreOffice came with Linux Mint Xfce 18.3, might as well test LibreOffice.
There are loads of alternatives! Could spent an entire lifetime testing software instead of getting some work done.
Software selection
LibreOffice came with Linux Mint Xfce 18.3.Installation experience
LibreOffice came with Linux Mint Xfce 18.3, but the version was an old version, so it provided an ideal opportunity to play with the update process.User experience
Within the first few minutes, it became apparent that the learning curve is going to be steep, and that there are useage compatibility issues between Calc and Excel.Test files
Two Excel files to test are:- “Unposted cashbook 2019.xlsx”. This is one workbook containing eight worksheets, functions and formatting. There are no pivot tables or other intermediate functionality. The maximum dimension throughout the entire workbook is A1:V468. It links to 9 other workbooks.
- ”dbase- chargeable food base 2019.xlsx”. This is one workbook containing four worksheets, of which two sheets contain pivot tables from the same source data as the third sheet, which in turn looks up data from the fourth sheet. The maximum dimension is A1:O1481.
The files sit in Google Drive and sync to Legolas via Google Backup & Sync, and to Gandalf via Grive2.
Crucially, one test file contains links to other Excel workbooks. This is crucial functionality in my data system.
First look and feel
Immediately, Calc feels refreshingly like Excel 2003, with menus. Menus are great: they contain plain English words that are findable, readable and pronounceable. You can shout them over the office more efficiently to the lazy fool who doesn’t do their job properly than trying to describe the idiotic icons that Excel 2007 (and later) use on its poxy ribbon.Opening the file
On either workbook, Calc reports a warning, “The data could not be loaded completely because the maximum number of columns was exceeded.”The error most likely arises because Excel’s format xlsx contains vastly more rows and columns than its rivals (as far as I am aware).
As first impressions go, this is piss-poor: first rule of any communication is that the communication is relevant. Calc should have first established whether data was to be lost before bullshitting the user. A false error message is garbage, and it forces the user to waste time reading the screen and dismiss the dialogue box.
For workbook “Unposted cashbook”, Calc then asks another question of the user, “This file contains links to other files. Should they be updated?” In Excel, this question was disabled on grounds of irrelevance: as a user, I’ve got more important things to do than to answer questions that objectively don’t need to be asked. The correct answer to this question is therefore “piss off”, but Calc doesn’t offer that, so it should get “no”.
For the purpose of testing, however, let’s click on “yes”. And an immediate error arises! That three pointless dialogue boxes before one has even started! The error is “The following external files could not be loaded…”
Crucially, the dialogue box reveals the filepath of the file it could not find. Calc then demanded one dismissal for each of the files that it couldn’t link to. That’s 9 in “Unposted cashbook”.
So, that’s 11 dismissals needlessly demanded of the user before the file becomes readable. Not a great start.
When the file is at last readable, it looks pretty good. All of the data are present. All of the formatting is comparable, nearly identical. The main difference are the on-screen textmetrics. Calc presents tighter/narrower textmetrics than they are in either Excel or Google Sheets. Calc thus has a point in its favour, designing textmetrics to work for adults (Excel and Google are both parasitised by out-of-work graphics designers obsessing with large fonts and huge amounts of white space so that us desktop users can all be crippled to the same low level of dumb-arsed smartphone users).
Editing the file
For a power user, the keyboard is way more important than the poxy mouse. The mouse is for children. Calc honours the basic keyboard shortcuts, CTRL-O, CTRL-W, CTRL-S, CTRL-P, CTRL-Q etc. Calc also honours the keyboard access of ALT+menu. As Calc’s menus are based on Excel 2003, there is rapid keyboard access to the most commonly used functions of Calc/Excel 2003 - an advantage which Excel exterminated in Excel 2007 by hiding the most commonly used functions behind more than double the number of keystrokes and often one further unnecessary dumbed-down dialogue box, which isn’t keyboard-accessible, e.g. try sorting data in Excel 2007+ with more than one sort key.But Calc has its limitations.
A power user would also typically use bookmarks to get around a workbook quickly, especially to get to fields that controlled regularly-used auto-filters. In Excel, F5 still produces a dialogue box from which accessing a bookmark/named range is fairly swift and even still with the keyboard.
I guess this is an oversight by Excel’s developers, whose ideological crusade against the keyboard user failed to spot this dialogue box.
In Calc, F5 also produces an equivalent dialogue box. But Calc’s developers have seriously bought into the ideological crusade against keyboard users by designing a box which contains elements that a keyboard user cannot access.
Paste special
Of crucial importance to a power user of Excel is the use of paste-special-values and paste-special-formulae. So many, many problems arise in Excel because of formatting, where the developers have made everything so excessively complex (typical Microsoft behaviour). In some cases, formatting changes the way that Excel handles the underlying data, resulting in calculation errors. A competent power user would therefore paste-special everything. In Excel 2007+, the keyboard command ALT+E+S+V and ALT+E+F should be the most common keyboard combination of an Excel power user. These are the keys that should look the most worn in any accounts department in the world.In Calc, paste special has the same broad functionality, but with slightly different keyboard access, and the keyboard access is less efficient. ALT+E+S then ALT+x to select an option then space to toggle the selected option. This is too inefficient for corporate/power use.
However, this finding is based solely on the dialogue box. Further testing would determine whether Calc, like Excel, creates data problems by the formatting of data. If Calc isn’t so confused as Excel, then the frequency of paste-special-values and paste-special-formulae in Calc should be fewer than in Excel.
Auto-filters
Auto-filters are crucial functionality for power users of any spreadsheet package, or any data technician who knows what they are doing. Fools who play at work - thinking that Excel is a mere typing exercise like Word - won’t understand this bit.Great news: in both Excel and Calc, access to a set auto-filter is CTR-↓ (it’s ALT+CTRL+R in Google Sheets). The majority of the resulting auto-filter dialogue box is very similar, but… in Calc, some of it is keyboard inaccessible. The screenshots are Excel first, Calc second.
However, Calc makes up for this with a more powerful custom filter functionality, as shown by the respective dialogue boxes. The screenshots are Excel first, Calc second.
Edit links
In any environment of linked workbooks, the ability to change links quickly and unilaterally is essential.Between Calc and Excel, there is broadly similar functionality. But there are three severe limitations in Gandalf’s environment.
- On Gandalf, Calc cripples the keyboard user by actively preventing the keyboard user from opening a linked file. Instead, the keyboard user must use the poxy, bloody mouse to click on a hyperlink. What child designed that is an open question. Compare the two screenshots below (Excel first, Calc second):
- Between Linux and Windows, the file system is completely different. Excel 2007+ stores relative filepath links in only very limited circumstances; it seems that Calc is going to read an absolute file link for Windows that cannot possibly work in a Linux user environment (even though Calc looks like it has an option for the user to store relative filepaths). The same crippling behaviour is seen by Microsoft with Excel and OneDrive.
For this file - synced between two environments by Google Backup & Sync/Grive2 - the correct absolute filepaths are:
- in Windows: c:\users\<username>\Google Drive\PRIV\Accounts\Ye2019\<filename>.xlsx
- in Linux: /dev/sda1/home/<user>/grive/PRIV/Accounts/Ye2019/<filename>.xlsx
In both environments, the common root (for all but one linked file) is folder "Ye2019", so absolute filepaths shouldn't be necessary.
After some edits to the test file, saved in formats XSLX and ODS from Calc, then grived to Google Drive. Thus ended the session on Gandalf. Legolas now had to make sense of the edits.
Legolas’s side of the story
On Legolas, immediately on opening “dbase” as the XLSX saved by Calc (but not “Unposted cashbook”), Excel kicks off with an error message, “We found some problems with the content in xxx.xlsx. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click yes.”It turns out that the “problem” was that the cache of data used behind the two pivot tables in “dbase”. Which Excel solved immediately. So obviously it wasn’t a problem at all. Like Calc, Excel generated a false error message that sought to waste the user’s time in pointless confirmations/mouse-clicks/reading-bollocks on the screen.
All of the data are present. None are corrupted. All of the formatting is comparable, although the pivot tables have adopted the formatting that Calc saved onto the pivot tables. The formatting is thus different from that in the original XSLX file.
Opening the ODS file in Excel and in Apache OpenOffice (also present on Legolas) found similarly that the data was present and uncorrupted. The sole issue was that dates appear as integers and not human-readable dates.
Conclusions
Summary of findings
For unlinked/standalone files, Google Backup & Sync and Grive2 enable a user to work with data cross-platform, with limited annoyances regarding the formatting of spreadsheets and false error messages arising from poor program design.
For linked files, Google Backup & Sync and Grive2 are undermined by the choice of both Excel and Calc to use absolute filepath references. The cross-platform user would need constantly to re-link these files for use on each platform, which would consume more timecost than would be worth spending on using two environments even as part of a phased migration from Windows to Linux Mint.
Is the project still viable as a result of these findings?
The project is still viable. But it has just got somewhat harder, because it prohibits the user from using two platforms as part of any transition period.What are the implications of this test on the project?
Using the data across the platforms is more challenging than objectively it should be, because the apps involved make linked spreadsheets unnecessarily harder to use/to manage.The cross-platform user would be constantly relinking workbooks to cope with today's platform, then relinking them again tomorrow on tomorrow's platform, etc ad nauseum.
Therefore, this test finds that the timecost to the user to work on linked spreadsheets cross-platform is very high. By contrast, the timecost required to rule out a cross-platform environment is probably quite low for the typical Windows user.
What does this test say about how the Windows user could migrate to Linux Mint?
The implication of this finding for this project is that migration needs to choose a strategy early in the migration process:- choice 1: to abandon Excel, use Calc and migrate to Linux Mint as a "big bang";
- choice 2: to use Excel in Linux Mint, using Wine, Mono, Crossover etc and hope that each translation layer allows Excel to perform its full range of functionality.
Either way, it requires a huge amount of timecost to the Windows user to configure Linux Mint appropriately, and/or to find workarounds for functionality in Calc (or Excel-on-Wine) missing from Excel-on-Windows.
To use Wine, Mono, Crossover etc substantially compromises the scope of this project, which is to migrate from Windows (not use bits of Windows in a more awkward way, and risk the Linux Mint environment in the process: might as well stick to using Windows and ignore Linux Mint).
There is no solution to the issue of absolute filepaths of linked files between platforms.
In all probability:
- most Windows users will not want to spend their limited time finding out how apps available for Linux Mint cannot achieve what apps can achieve on Windows; and
- most Windows users would have long since given up at the sight of linked workbooks' filepaths being incompatible between platforms.
Alternative future strategies for testing
- on Gandalf, find a replacement for LibreOffice that does everything LibreOffice does, except to handle linked spreadsheet filepaths appropriately in a cross-platform environment;
- on Gandalf, find a version of Excel that will work fully on Wine or Mono;
- in both Excel and Calc, to find a way of forcing only relative filepaths being stored in linked files.
Tests completed 28Apr2018.
Comments
Post a Comment