Excel API

Package fabrictools.excel — fonctions nommées comme en Excel.

Excel formula-style API for Spark DataFrames (Excel.XLookup, Excel.SumIf, …).

Use with fabrictools.read_lakehouse() and prepared tables from Power Query scripts instead of Excel structured tables and worksheet formulas.

Namespaces exported:

  • Excel — Excel.XLookup, Excel.SumIf, Excel.SumIfs, Excel.If, Excel.Round, Excel.TextJoin

Example

>>> from fabrictools import read_lakehouse, Excel
>>> projects = read_lakehouse("Lakehouse", "dbo/backlog_projects")
>>> df = Excel.XLookup(projects, "RAO CODE", customer_projects, "RAO CODE", "Date")
class fabrictools.excel.Excel[source]

Bases: object

Namespace for Excel worksheet functions on Spark DataFrames.

Names match Excel EN function names (XLookup = XLOOKUP / RECHERCHEX).

static If(condition: pyspark.sql.Column, value_if_true: pyspark.sql.Column | str | int | float | bool | None, value_if_false: pyspark.sql.Column | str | int | float | bool | None) → pyspark.sql.Column

Conditional expression (Excel IF / SI).

Parameters:

  • condition (Column) – Boolean Spark column expression.

  • value_if_true (Column | str | int | float | bool | None) – Value when condition is true.

  • value_if_false (Column | str | int | float | bool | None) – Value when condition is false.

Returns:

Conditional column expression.

Return type:

Column

Example

>>> from fabrictools import Excel
>>> from pyspark.sql import functions as F
>>> Excel.If(F.col("amount") > 0, "OPEN", "CLOSED")
static Round(expr: pyspark.sql.Column | str, num_digits: int = 0) → pyspark.sql.Column

Round a numeric column (Excel ROUND / ARRONDI).

Parameters:

  • expr (Column | str) – Numeric column or expression.

  • num_digits (int) – Number of decimal places.

Returns:

Rounded column expression.

Return type:

Column

Example

>>> from fabrictools import Excel
>>> Excel.Round(F.col("TO still to recognize"), 0)
static SumIf(left: pyspark.sql.DataFrame, criteria_col: str, right: pyspark.sql.DataFrame, match_col: str, sum_col: str, *, output_name: str | None = None) → pyspark.sql.DataFrame

Conditional sum keyed on one column (Excel SUMIF / SOMME.SI).

Pre-aggregates right with groupBy(match_col).sum(sum_col) then left-joins to left[criteria_col]. This matches Excel row-wise SUMIF semantics without row loops.

Parameters:

  • left (DataFrame) – Driving dataframe.

  • criteria_col (str) – Column on left used as the criteria value.

  • right (DataFrame) – Source table to aggregate.

  • match_col (str) – Column on right compared to criteria_col.

  • sum_col (str) – Column on right to sum.

  • output_name (str | None) – Name of the added column (defaults to sum_col).

Returns:

left with the conditional sum column appended.

Return type:

DataFrame

Raises:

ValueError – If a required column does not resolve.

Example

>>> from fabrictools import Excel
>>> df = Excel.SumIf(df, "RAO CODE", invoicing, "Project Number", "Total Invoice Amount",
...                  output_name="Total Invoicing")
static SumIfs(left: pyspark.sql.DataFrame, sum_col: str, right: pyspark.sql.DataFrame, criteria: Mapping[str, str | int | float | bool | None], *, output_name: str | None = None) → pyspark.sql.DataFrame

Multi-criteria conditional sum (Excel SUMIFS / SOMME.SI.ENS).

Each entry in criteria maps a right column to either:

  • a left column name (joined after aggregation), or

  • a literal value (filters right before aggregation).

Parameters:

  • left (DataFrame) – Driving dataframe.

  • sum_col (str) – Column on right to sum.

  • right (DataFrame) – Source table to aggregate.

  • criteria (collections.abc.Mapping[str, str | int | float | bool | None]) – {right_col: left_col_name | literal} mapping.

  • output_name (str | None) – Name of the added column (defaults to sum_col).

Returns:

left with the conditional sum column appended.

Return type:

DataFrame

Raises:

ValueError – If sum_col or join criteria do not resolve.

Example

>>> from fabrictools import Excel
>>> df = Excel.SumIfs(df, "Turnover", turnover_follow,
...     {"Project No.": "RAO CODE", "Year": 2022},
...     output_name="Turnover Follow 2022")
static TextJoin(delimiter: str, ignore_empty: bool, *parts: pyspark.sql.Column | str) → pyspark.sql.Column

Join text parts with a delimiter (Excel TEXTJOIN / JOINDRE.TEXTE).

Parameters:

  • delimiter (str) – Separator between non-empty parts.

  • ignore_empty (bool) – When True, skip null and blank strings (Excel TRUE).

  • parts (Column | str) – Column expressions or string literals to concatenate.

Returns:

Concatenated column expression.

Return type:

Column

Example

>>> from fabrictools import Excel
>>> Excel.TextJoin(" | ", True, F.lit("a"), F.lit(""))
static XLookup(left: pyspark.sql.DataFrame, lookup_col: str, right: pyspark.sql.DataFrame, match_col: str, return_col: str, *, default: Any = None, output_name: str | None = None) → pyspark.sql.DataFrame

Lookup a value from right and add it to left (Excel XLOOKUP / RECHERCHEX).

Performs a left join on lookup_col (left) and match_col (right), keeping the first match when right contains duplicate keys (Excel behaviour).

Parameters:

  • left (DataFrame) – Driving dataframe (equivalent to the row with [@[lookup_col]]).

  • lookup_col (str) – Column on left holding the lookup key.

  • right (DataFrame) – Lookup table.

  • match_col (str) – Column on right to match against.

  • return_col (str) – Column on right whose value is returned.

  • default (Any) – Value used when no match is found (None keeps null).

  • output_name (str | None) – Name of the added column (defaults to return_col).

Returns:

left with the looked-up column appended.

Return type:

DataFrame

Raises:

ValueError – If a required column does not resolve.

Example

>>> from fabrictools import Excel
>>> df = Excel.XLookup(projects, "RAO CODE", customer_projects, "RAO CODE", "Date")