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:

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

• mgri: 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! =)

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.

• mgri: 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...
• Spacedman: 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.

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

• mgri: 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.
• weiji14: 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).

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.

Answer 5

Try the following example from QGIS python console:

from qgis.utils import iface
from PyQt5.QtWidgets import QFileDialog, QTextBrowser, QVBoxLayout, QWidget, QDockWidget, QMessageBox
from qgis.PyQt.QtCore import QFile, QTextStream, QVariant
from qgis.core import QgsVectorLayer,QgsField,QgsFields,QgsFeature,QgsProject,QgsVectorFileWriter
xlsfile, _ = QFileDialog.getOpenFileName(
 widget,
 "Select Excel xlsx",
 "",
 "Excel file (*.xlsx)"
)
if xlsfile:
 #print(xlsfile)
 ExcelLayerS=iface.addVectorLayer(xlsfile, os.path.basename(xlsfile).split(".")[0], "ogr")
 if not ExcelLayerS.isValid():
 print("Layer failed to load!")
 raise Exception('Layer is not valid')
 else:
 
 QMessageBox.information(widget, "Done", "Excel reading finished.")
 QgsProject.instance().addMapLayer(ExcelLayerS)
 widget = QWidget()
 layout = QVBoxLayout()
else:
 QMessageBox.information(widget, "No file selected", "No file selected.")