7

I have two memory layers that I would like to write their attribute tables to the same Excel file but in different sheets. So far, I've managed to write the attribute tables to separate files using QgsVectorFileWriter.writeAsVectorFormat.

layer1 = QgsProject.instance().mapLayersByName("memory_layer1")[0]
layer2 = QgsProject.instance().mapLayersByName("memory_layer2")[0]

# export to separate file
QgsVectorFileWriter.writeAsVectorFormat(layer1, 'path/file1.xlsx', 
    "utf-8", layer1.crs(), "XLSX", layerOptions = ['GEOMETRY=AS_XYZ'])

QgsVectorFileWriter.writeAsVectorFormat(layer2, 'path/file2.xlsx', 
    "utf-8", layer2.crs(), "XLSX", layerOptions = ['GEOMETRY=AS_XYZ'])

2 Answers 2

12

On QGIS 3.10.3 or above

You can do that in this way:

# Prepare options for layer1
options = QgsVectorFileWriter.SaveVectorOptions()
options.driverName = "XLSX"
options.fileEncoding = "utf-8"
options.layerOptions = ["GEOMETRY=AS_XYZ"]
options.layerName = 'layer1'

# Export layer1
result, error_str = QgsVectorFileWriter.writeAsVectorFormatV2(
    layer1,
    '/tmp/my_file.xlsx',
    QgsProject.instance().transformContext(),
    options
)
print(result)

# Prepare options for layer2
options.actionOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer
options.layerName = 'layer2'

# Export layer2
result, error_str = QgsVectorFileWriter.writeAsVectorFormatV2(
    layer2,
    '/tmp/my_file.xlsx',
    QgsProject.instance().transformContext(),
    options
)
print(result)

The key is to open the file in update mode when exporting the second layer, i.e., to set options.actionOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer. See the full list of options in the docs


On QGIS 3.10.2 or below

You can use QgsVectorFileWriter.writeAsVectorFormat() in this way:

# Export layer
result, error_str = QgsVectorFileWriter.writeAsVectorFormat(
    layer,
    '/tmp/my_file.xlsx',
    options  # See above how to set the options object
)
3
  • 'QgsVectorFileWriter.writeAsVectorFormatV2' is present since QGIS 3.0. This shouldn't be a problem as I'm using QGIS 3.8. However, I get an AttributeError, saying "type object 'QgsVectorFileWriter' has no attribute 'writeAsVectorFormatV2' ". I've tried a few imports into PyQGIS console, such as 'import qgis', 'from qgis.core import *', 'from qgis.core import QgsVectorFileWriter' and 'from qgis.core import QgsVectorFileWriter.writeAsVectorFormatV2' (didn't like the syntax for this one), but this didn't solve the problem. Jan 13, 2021 at 12:31
  • 1
    You need at least QGIS v3.10.3 to use QgsVectorFileWriter.writeAsVectorFormatV2. The other methods are deprecated. Jan 13, 2021 at 13:21
  • What is the difference between using QgsProject.instance().transformContext() and QgsCoordinateTransformContext() in QgsVectorFileWriter.writeAsVectorFormatV3? If I want to export the new layer in the same CRS from input layer, is it ok to use only QgsCoordinateTransformContext()? Aug 8, 2023 at 20:46
1

Some advanced format creation options can usually be found in OGR format specification: https://gdal.org/drivers/vector/xlsx.html

There are no options suitable for your need but you can easily merge the files afterwards using pandas like this:

import pandas as pd

file1='path/file1.xls'
file2='path/file2.xls'
df1 = pd.read_excel(file1, sheet_name='Sheet1')
df2 = pd.read_excel(file2, sheet_name='Sheet1')

outPath = 'merged.xls'
writer = pd.ExcelWriter(outPath, engine = 'xlsxwriter')
df1.to_excel(writer, sheet_name = 'Sheet_from_1')
df2.to_excel(writer, sheet_name = 'Sheet_from_2')
writer.save()

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.