Reading Excel file using PyQGIS

Question

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 converted my file to the .csv format, then there would have been no problem with the reading of the items in PyQGIS, but I would like to know if there is a direct way to avoid this passage.

Answer 1

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:

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']

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

Answer 2

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.

Answer 3

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())

Answer 4

As it says in the xlrd documentation:

This library will no longer read anything other than .xls files.

Hence working with the xlrd Python package while reading Excel files can lead to the following xlrd.biffh.XLRDError exception:

Traceback (most recent call last):
  File "C:\OSGeo4W\apps\Python312\Lib\code.py", line 90, in runcode
    exec(code, self.locals)
  File "<input>", line 1, in <module>
  File "<string>", line 11, in <module>
  File "C:\OSGeo4W\apps\Python312\Lib\site-packages\xlrd\__init__.py", line 170, in open_workbook
    raise XLRDError(FILE_FORMAT_DESCRIPTIONS[file_format]+'; not supported')
xlrd.biffh.XLRDError: Excel xlsx file; not supported

See this thread for more details: xlrd.biffh.XLRDError: Excel xlsx file; not supported [duplicate].

Therefore, it is suggested when working with .xlsx Excel file extensions to utilize the openpyxl Python package, as demonstrated here: Drawing routes (on road shapefile) between X,Y coordinates in QGIS.