4

Opening layers directly from XLS spreadsheets is a very useful (if not well documented) feature of QGIS, but I can't seem to be able to properly import cells where the contents of the cell is the result of a formula. The events are:

  1. Set up an XLS spreadsheet where one column is the calculated result of others (e.g the sum of the other columns), the 'sum' column
  2. Import spreadsheet as a table into Qgis by using 'add layer' and selecting the XLS file and clicking open.
  3. The table loads up with all the correct values, including the calculated values in the 'sum' column
  4. Save the table
  5. Return to the XLS spreadsheet and your 'sum' column no longer has any formulas, it's just the integer values.

Is there anything I can do in the importing or saving of edits that will preserve the column as a formula, not just the results?

Improve this question
5
  • I don't think this is possible for QGIS to store more than one value for each 'cell'.
    – Joseph
    Commented Apr 20, 2015 at 9:22
  • Are you looking for a way to edit an XLS file using QGIS? In that case I belive that you have to follow up suggestion by @AndreJ and combine results back to the original XLS (the one with the formula) using excel INDEX and MATCH funcs, based on ID field
    – dof1985
    Commented Apr 20, 2015 at 9:51
  • Spreadsheets aren't databases, and this is one of the key differences. Spreadsheet cells can contain formulas, while database fields are for the most part static entities. For them to 'self-update' requires things like triggers, which aren't actually part of the field value. Why are you saving the table? Are you trying to use the spreadsheet as the attribute table for a layer? Trying to use a join to pull data from the attribute table into the spreadsheet (which is normally the opposite direction people want to go)?
    – Chris W
    Commented Apr 20, 2015 at 21:27
  • dof1985 - Yes I was looking for a way to do that, but AndreJ's suggestion would not work because of the need to use the area of a polygon as one of the values. This means I would have to use virtual fields to keep it up to date and these can't be transferred back to the spreadsheet.
    – user39790
    Commented Apr 21, 2015 at 10:39
  • Chris W - Yes, I'm trying to use the spreadsheet as an attribute table, normal attribute tables can't do any data analysis on dynamic data.
    – user39790
    Commented Apr 21, 2015 at 10:46

2 Answers 2

Reset to default
1

It might help to leave out step 4, or make a copy of the XLS table and add that to QGIS instead of the original file.

You can still make changes in Excel, and visualize them in QGIS.

If you aim to change the data in QGIS, you might need an intermediate Excel file, and connect it to your file with formulas as external cell reference.

Improve this answer
1
  • I was also thinking about cell references, but I didn't mention it because I figured like a formula any cell reference would be lost on modification/save in QGIS. If you're saving to a static spreadsheet and then referencing that, it makes sense. But if you're trying to pull the dynamic results into QGIS and modify even other cells, there's no way around a join triangle. Anything changed in QGIS must be saved in a static spreadsheet linked to both QGIS and Excel. Then the dynamic spreadsheet can be separately joined to QGIS to see the updated results, but it can't be modified.
    – Chris W
    Commented Apr 21, 2015 at 20:59
0

Dealing with excel (CSV) and qgis is fairly easy with the use of MMQGIS

here

you can export import CSV files among other things.

Hope this helps

Improve this answer
1
  • 1
    In much the same way spreadsheets aren't databases, text files (ie comma separated value files) aren't spreadsheets. csv's don't support self executing formulas. You could save out the formula as a text string, but any automatic solving or references/links to other cells is lost.
    – Chris W
    Commented Apr 20, 2015 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.