Excel

Excel

Excel file format.

Based on Spark Excel file format.

Supports reading/writing files with .xlsx (read/write) and .xls (read only) extensions.

Version compatibility

• Spark versions: 3.2.x - 4.0.x

  Warning

  Not all combinations of Spark version and package version are supported. See Maven index and official documentation.

• Java versions: 8 -- 22

See documentation from link above.

Added in 0.9.4

Examples:

Note

You can pass any option mentioned in official documentation. Option names should be in camelCase!

The set of supported options depends on spark-excel package version.

Reading filesWriting files

from pyspark.sql import SparkSession
from onetl.file.format import Excel

# Create Spark session with Excel package loaded
maven_packages = Excel.get_packages(
    package_version="0.31.2",
    spark_version="3.5.8",
)
spark = (
    SparkSession.builder.appName("spark-app-name")
    .config("spark.jars.packages", ",".join(maven_packages))
    .getOrCreate()
)

excel = Excel(header=True, inferSchema=True)

# Create Spark session with Excel package loaded
spark = ...

from onetl.file.format import XML

excel = Excel(header=True, dataAddress="'Sheet1'!A1")

Parameters:

• header

  (bool, default: False ) –

  If True, the first row in file is conditioned as a header. Default False.

• dataAddress

  (str | None, default: None ) –

  Cell address used as starting point. For example: 'A1' or 'Sheet1'!A1

• timestampFormat

  (str | None, default: None ) –

  Format string used for parsing or serializing timestamp values. Default yyyy-mm-dd hh:mm:ss[.fffffffff].

• dateFormat

  (str | None, default: None ) –

  Format string used for parsing or serializing date values. Default yyyy-MM-dd.

• treatEmptyValuesAsNulls

  (bool | None, default: None ) –

  If True, empty cells are parsed as null values. If False, empty cells are parsed as empty strings. Default True.

  Note

  Used only for reading files.

• setErrorCellsToFallbackValues

  (bool | None, default: None ) –

  If True, cells containing #N/A value are replaced with default value for column type, e.g. 0 for IntegerType(). If False, #N/A values are replaced with null. Default False.

  Note

  Used only for reading files.

• usePlainNumberFormat

  (bool | None, default: None ) –

  If True, read or write numeric values with plain format, without using scientific notation or rounding. Default False.

• inferSchema

  (bool | None, default: None ) –

  If True, infer DataFrame schema based on cell content. If False and no explicit DataFrame schema is passed, all columns are StringType().

  Note

  Used only for reading files.

• workbookPassword

  (SecretStr | None, default: None ) –

  If Excel file is encrypted, provide password to open it.

  Note

  Used only for reading files. Cannot be used to write files.

• maxRowsInMemory

  (int | None, default: None ) –

  If set, use streaming reader and fetch only specified number of rows per iteration. This reduces memory usage for large files. Default None, which means reading the entire file content to memory.

  Warning

  Can be used only with .xlsx files, but fails on .xls.

  Note

  Used only for reading files.

• maxByteArraySize

  (ByteSize | None, default: None ) –

  If set, overrides memory limit (in bytes) of byte array size used for reading rows from input file. Default 0, which means using default limit.

  See IOUtils.setByteArrayMaxOverride documentation.

  Note

  Used only for reading files.

• tempFileThreshold

  (ByteSize | None, default: None ) –

  If value is greater than 0, large zip entries will be written to temporary files after reaching this threshold. If value is 0, all zip entries will be written to temporary files. If value is -1, no temp files will be created, which may cause errors if zip entry is larger than 2GiB.

  Note

  Used only for reading files.

• excerptSize

  (int | None, default: None ) –

  If inferSchema=True, set number of rows to infer schema from. Default 10.

  Note

  Used only for reading files.

get_packages(package_version, spark_version, scala_version=None)

Get package names to be downloaded by Spark.

Warning

Not all combinations of Spark version and package version are supported. See Maven index and official documentation.

Added in 0.9.4

Changed in 0.14.0

Maven package com.crealytics:spark-excel was renamed to dev.mauch:spark-excel.

Parameters:

• package_version

  (str) –

  Package version in format major.minor.patch.

  Changed in 0.14.0

  This parameter is now mandatory.

• spark_version

  (str) –

  Spark version in format major.minor.patch.

• scala_version

  (str | None, default: None ) –

  Scala version in format major.minor.

  If None, spark_version is used to determine Scala version.

Examples:

from onetl.file.format import Excel

Excel.get_packages(
    package_version="0.31.2",
    spark_version="3.5.8",
)
Excel.get_packages(
    package_version="0.31.2",
    spark_version="3.5.8",
    scala_version="2.12",
)