QGIS – Exporting Legend Items to Excel from Print Layout

excelprint-composerqgis

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.

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

Best Answer

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: