There seem to now be many methods for working with an Excel spreadsheet in QGIS. Some allow read-only, and some require one Excel format or another. Some also have historically been unreliable (e.g. where trivial changes to the Excel sheet break the QGIS project).
What are the main different methods for working DIRECTLY with Excel at QGIS version 2.18?
Clearly there are more convoluted methods - Excel data can be exported and then imported to many other formats (e.g. csv), but I'm asking about more direct methods of making a direct link with the original Excel file (so changes are seen in QGIS). Often other people wish to maintain live data in Excel - and GIS professionals need (if being efficient) to maintain live links to that live data.
I (think I) know of the following (but have questions about each). I suspect that the best answer will simply list the basic facts about each of the various methods - these and others.
- Save using older .xls format. Bring into QGIS using a virtual xls table in a Spatialite database. This I think results in a read only table but it is possible to define that the first row has column headings.
- Save using .xlsx format. Drag into QGIS or open using 'add vector layer'. No easy way to define if top row is column labels.
- Use the 'Spreadsheet Layers' plugin. It is possible to define a header row, and x/y data. The plugin works by creating a VRT file which sits in parallel to the original file. Presumably this VRT file could be moved (if the file reference in this VRT file continues to point correctly at the original file). The VRT file records details about the spreadsheet (data types etc).
In all cases the header row data (i.e. field names) in Excel needs to be handled carefully - odd characters (etc) can break things.
I realise that this question doesn't ask for a simple 'how to' answer, but working with Excel spreadsheets is trivial in some software so I believe that some clear comments about the facts about the various methods are justified. The information above is incomplete.