3

I have the well-built legend in the QGIS print composer, as my project includes more than 20 layers. Because the legend in front layout doesn't match our legend patterns produced in Excel I am wondering about the option of exporting these legend items to MS Excel.

enter image description here

Is there any reasonable and quite quick way of exporting the print layout legend items to Excel?

4
  • Why excel? Would a PDF file suffice?
    – Erik
    Commented Jan 27, 2022 at 12:34
  • No, because it needs additional alteration in Excel.
    – Geographos
    Commented Jan 27, 2022 at 12:44
  • Are those legend items in excel actually lines and polygons or are they pictures/images?
    – Binx
    Commented Jan 27, 2022 at 15:39
  • 1
    My initial thought would be to save your layers as a "Save Layer Style" (.qml or .sld) and then perhaps extract from that.
    – Binx
    Commented Jan 27, 2022 at 15:45

1 Answer 1

8

Interesting question. I couldn't resist having a play around with this. I have hacked together a Python script (cobbled from different sources) which should take care of this for you.

Note: you will need to install one third party Python module: XlsxWriter. The best way to do this may vary depending on your OS and your QGIS version and installation. I installed QGIS on Windows with the new OSGeo4W installer, but with some tweaking this should work for any Windows install.

Option 1: I use a batch file which looks like this:

@echo off
rem Root OSGEO4W home dir to the same directory this script exists in
SET OSGEO4W_ROOT=C:\OSGeo4W
call "%OSGEO4W_ROOT%"\bin\python-qgis.bat"

rem List available o4w programs
rem but only if osgeo4w called without parameters
@echo on
@if [%1]==[] (echo run o-help for a list of available commands & cd /d "%~dp0" & cmd.exe /k) else (cmd /c "%*")

You can copy this into a text editor and edit if necessary. The line SET OSGEO4W_ROOT= should point to the directory containing the bin folder where your QGIS application is located (in my case it is C:\OSGeo4W. Next, this batch file calls another batch file (the one QGIS uses to set the pyqgis environment) so check that there is a python-qgis.bat file located in the bin folder. Depending on your version it may be python-qgis-ltr.bat so edit the batch file above accordingly. Save this batch file somewhere (give it a name like OSGeo4W_pyqgis.bat) and double click to run. The shell will open up to a Python prompt. type quit() and hit enter. Now type pip install XlsxWriter and hit enter. That is how I installed XlsxWriter and it worked for me.

Option 2: Alternatively, you could try running the following lines inside the QGIS Python console (this may indeed be the most straight forward):

import pip

pip.main(['install','XlsxWriter'])

You can check this thread and related ones for more info but again, depending on your installation, some discussions may be out of date because with the new OSGeo4W installer, there is no longer a py3_env.bat.

Assuming you have managed to install XlsxWriter in the Python used by QGIS, you can now paste the following script into an editor in the Python console, change the export path and layout name, and click run.

import xlsxwriter
from io import BytesIO

EXPORT_PATH = 'C:\\Users\\Path\\To\\Output\\legend.xlsx'# Path to save your spreadsheet
LAYOUT_NAME = 'Layout Name'# Name of your layout

workbook = xlsxwriter.Workbook(EXPORT_PATH)
worksheet = workbook.add_worksheet()

def convert_icon(icon, size=None, fmt='PNG'):
    '''
    This function modified from:
    https://stackoverflow.com/questions/40789856/python-convert-qt-qicon-into-jpeg
    Credit: stackoverflow member ekhumoro
    '''
    if isinstance(icon, QIcon):
        if size is None:
            size = icon.availableSizes()[0]
        pixmap = icon.pixmap(size)
        
    elif isinstance(icon, QPixmap):
        pixmap = icon
    array = QtCore.QByteArray()
    buffer = QtCore.QBuffer(array)
    buffer.open(QtCore.QIODevice.WriteOnly)
    pixmap.save(buffer, fmt)
    buffer.close()
    return array.data()

project = QgsProject.instance()
mgr = project.layoutManager()
layout = mgr.layoutByName(LAYOUT_NAME)
legend = [i for i in layout.items() if isinstance(i, QgsLayoutItemLegend)][0]
model = legend.model()
sheet_row = 1
for i in range(model.columnCount()):
    for j, l in enumerate(model.rootGroup().findLayers()):
        index = model.index(j, i)
        legend_nodes = model.layerLegendNodes(l)
        if len(legend_nodes) == 1:
            icon = model.data(index, 1)
            lyr_name = model.data(index, 2)
            if icon.availableSizes():
                img_data = convert_icon(icon)
                io_data = BytesIO(img_data)# BytesIO object
                icon_cell = f'A{sheet_row}'
                worksheet.insert_image(icon_cell, 'image.png', {'image_data': io_data, 'x_offset': 15, 'y_offset': 2})
                lyr_name_cell = f'B{sheet_row}'
                sheet_row+=1
                worksheet.write(lyr_name_cell, str(lyr_name))
                worksheet.set_column('B:B', 30)
            sheet_row+=1
        if len(legend_nodes) > 1:
            subhdg = model.data(index, 2)
            subhdg_cell = f'A{sheet_row}'
            sheet_row+=1
            worksheet.write(subhdg_cell, str(subhdg))
            for n in legend_nodes:
                node_index = model.legendNode2index(n)
                icon = model.data(node_index, 1)
                cat_name = model.data(node_index, 2)
                img_data = convert_icon(icon)
                io_data = BytesIO(img_data)
                icon_cell = f'A{sheet_row}'
                cat_name_cell = f'B{sheet_row}'
                sheet_row+=1
                worksheet.insert_image(icon_cell, 'image.png', {'image_data': io_data, 'x_offset': 15, 'y_offset': 2})
                worksheet.write(cat_name_cell, str(cat_name))
                worksheet.set_column('B:B', 30)
            sheet_row+=1
            
workbook.close()

See below for example results with a test layout legend. My legend in the layout looks like this:

enter image description here

The resulting spreadsheet looks like this:

enter image description here

References:

Python: convert QT QIcon into jpeg?

https://xlsxwriter.readthedocs.io/example_images.html

https://xlsxwriter.readthedocs.io/example_images_bytesio.html

xlsxwriter: how to load a BytesIO png files created with matplotlib.pyplot

3
  • pip is not recognized in OsGeo4W Shell, is there a different way of importing this library?
    – Geographos
    Commented Jan 31, 2022 at 14:13
  • @MKR, I have edited my answer with more details about installing additional Python modules for QGIS. I don't know anything about your os, qgis version or installation method so maybe you should try option 2 first
    – Ben W
    Commented Jan 31, 2022 at 23:46
  • thx for this amazing script! very well written and very easy to use!
    – AndreasK
    Commented May 23, 2023 at 15:12

Your Answer

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

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