2

I have an Excel file (with .xlsx extension) containing some non-spatial data that I want to use in combination with some other spatial data.

I generally know how to load an external source (Loading Layers from the PyQGIS Cookbook), but I haven't found any reference for files with .xlsx extension, so I tried to import it in this way:

uri = 'path_to_the_file/test.xlsx'
layer = QgsVectorLayer(uri, 'test', 'ogr')

but when I try to read something, for example all the elements from the first column:

for item in layer:
    print item[0]

I get this error:

'QgsVectorLayer' object is not iterable

What I'm doing wrong? If I previously convert my file to the .csv format, then there is not problem with the reading of the items in PyQGIS, but I would like to know if there is a direct way in order to avoid this passage.

2

Following your example what you want to do is to iterate over all FEATURES of the layer, not over the layer object itself. That's why your Code fails.

Given this very simple Excel sheet:

Contents of Excel sheet

Make a layer from the xlsx file as you already did:

uri = 'path_to_the_file/test.xlsx'
layer = QgsVectorLayer(uri, 'test', 'ogr')

To learn how the columns can be referenced by Name type:

layer.fields()[0].name()
# u'Field1'

Now, when you want to iterate over all features in this layer, that are all rows of the underlying Excel sheet, use getFeatures():

for feat in layer.getFeatures():
    print feat['Field1']

result of print command

Instead field names you may reference fields by their index. In my example feat[0] gives the same result as feat['Field1'].

Version Information: Excel 2016, QGIS 2.14.3

  • Oh my God, this was a terrible oversight! I certainly know how to iterate over the features of a layer and how to get their values... I focused too much on complicated methods for solving the issue, but it was all so simple! Anyway, thanks! =) – mgri Dec 23 '16 at 16:03
1

You might need an extra Python package for reading Excel spreadsheets. I've used xlrd in the past: http://www.python-excel.org/ but there might be better others now.

I don't think what you tried -- reading an Excel file as a QgsVectorLayer -- can work - and you should check layer.isValid() before doing anything with it anyway. You'll need to use the xlrd package to read into normal python structures.

You might also get some joy with the XYtools plugin: http://plugins.qgis.org/plugins/xytools/ which wraps the xlrd python package.

  • Thanks for the suggestion of the xlrd package, I never heard about it. I know the XYtool plugin, but I was looking for a PyQGIS solution... – mgri Dec 23 '16 at 11:19
  • I was thinking more that you might be able to use some of the XYtool python code for examples of how to read Excel files in your own python code. – Spacedman Dec 23 '16 at 11:23
1

Just some sample code for you to read the data. You'll have to 'pip install xlrd' first. See https://pypi.python.org/pypi/xlrd.

import xlrd
uri = 'path_to_the_file/test.xlsx'
workbook = xlrd.open_workbook(uri)
wrksht1 = workbook.sheet_by_index(0)  #assuming you mean the first sheet in Excel

offset = 0   #set to 1 if there's 1 header line
rows = []
for i, row in enumerate(range(wrksht1.nrows)):
    if i <= offset: continue
    r = []
    try:
        r.append(wrksht1.cell_value(i, 0))   #First column
        r.append(wrksht1.cell_value(i, 1))   #Second column
        r.append(wrksht1.cell_value(i, 2))   #Third column
        rows.append(r)
    except ValueError:
        pass

xlsxDict = {}  #Dictionary to store your excel data
for aRow in rows:
    try:
        xlsxDict[aRow[0]]=[aRow[1], aRow[2]]  #first column is key, 2nd/3rd columns are the values
    except:
        raise  
print len(xlsxDict.keys())
  • Thanks for the answer, but it doesn't work by now. I installed the xlrd library from the OSGeo4W installer, but I get this error: Unsupported format, or corrupt file: Expected BOF record; found 'PK\x03\x04\x14\x00\x06\x00'. I think it is related to the version of the installed library (from the installer, I get v0.7.1), and it seems confirmed here. Do you have the newest version of the library? However, if I use a file with .xls extension, it works. – mgri Dec 23 '16 at 11:28
  • I have version xlrd-0.9.2-py2.7 installed, manually using pip. You're right that OSGeo4W currently has a lower version (0.7.5). – weiji14 Jan 3 '17 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.