4

I am a bit new in python now working in qgis to automates some task. I have an excel pre-written template file where I have to add different data in different columns.

outpufFile = open('d:/template.xlsx','w')
for layer in QgsMapLayerRegistry.instance().mapLayers().values():
    print layer.name() +","+ str(layer.featureCount())
    line = layer.name()
    unicode_line = line.encode('utf-8')
    outpufFile.write(unicode_line)
outpufFile.close() 

I get this so far but it corrupting my template while I also don't know how to access specific column or row of my file In order to write at exactly column and row to full my template.

  • 1
    Excel fomat is not a simple text format, you need to use a Python module for that : Python Package Index (Pypi): Excel – gene Sep 29 '16 at 15:21
  • @gene how do i install plugin? – Muhammad Faizan Khan Sep 30 '16 at 13:12
  • They are not plugins but simple Python modules/libraries – gene Sep 30 '16 at 13:32
3

As say in my comment, I will use specific Python modules:

1) openpyxl (pure Python module, easy to install in the Windows, Linux and Mac OS X QGIS distributions)

A simple template

enter image description here

import openpyxl
wb = openpyxl.load_workbook('template.xlsx')
# # grab the active worksheet but you can choose others
ws = wb.active
for index, layer in enumerate(QgsMapLayerRegistry.instance().mapLayers().values()):
    ws.cell(row=index+2, column=1).value = layer.name()
    ws.cell(row=index+2, column=2).value = layer.featureCount()

# as template.xlsx is a template, we create another file
wb.save('result.xlsx') 

Result

enter image description here

2) you can also the more general Pandas (present in the Windows distribution of QGIS, easy to install in the Linux and Mac OS X distributions)

import pandas as pd
# convert Excel file to Pandas Dataframe
templ= pd.read_excel('template.xlst', sheetname='Sheet1')
print templ.head()
 Empty DataFrame
 Columns: [name, count]
 Index: []
# populate the DataFrame
counts = [layer.featureCount() for layer in   QgsMapLayerRegistry.instance().mapLayers().values()]
names = [layer.name() for layer in  QgsMapLayerRegistry.instance().mapLayers().values()]
templ['name'] = names
templ['count'] = counts
print  templ.head()
        name     count
0       compa1    100
1       compa2    300
2       compa3    500
3       compa4     10
# convert Pandas Dataframe to Excel 
templ.to_excel('result2.xlsx', sheet_name='Sheet1')

Result

enter image description here

0

Althoug @Gene answer is enough but for future users I want to share some details about the answer(as I face much difficulty).

I used openpyxl Package for excel template writing but before using this package you have require: et_xmlfile and jdcal packages also (it is requirement).

Then I extract all three(openpyxl,jdcal,et_xmlfile ) in my D drive(You can do it in any drive). You have required to append it first in python. Then i used this code

    #For Writing in Excel File
    import sys
    #For openpyxl-2.4.0 Python Package you should need to have et_xmlfile and jdcal package

    sys.path.append("D:\et_xmlfile-1.0.1")
    sys.path.append("D:\jdcal-1.3")
    sys.path.append('D:\openpyxl-2.4.0')

    import openpyxl
    from openpyxl import Workbook

    wb = openpyxl.load_workbook('D:\template.xlsx') #loading template

    # grab the active worksheet
    ws = wb.active
    ws['C1'] = "This will save in C1"
    #save in d drive with result name
    wb.save('D:\Result.xlsx')
  • Just use pip install openpyxl – gene Oct 3 '16 at 16:29
  • this didn't work for me. i tried it – Muhammad Faizan Khan Oct 4 '16 at 4:25

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.