Func#

A Func is a template that dynamically returns an element of the appropriate shape/dimensions while applying an Excel formula to its cell(s). It should be used only when calling builtin Excel functions in your formula.

As standard across excelbird, a Func is designed for use without specifying any cell coordinates.

Func Syntax#

To build a Func, provide the exact formula string, with Python objects placed wherever the cell references should appear.

For instance, here’s how it should look if you were not referencing any cells.

from excelbird import Func, Expr, Cell, Col, Frame, Stack

simple_func = Func("SUM(5, 4, MIN(6, 7))")

To reference other layout elements in your formula, there are two options:

Referencing Elements - Option 1#

Break the string, and insert variables directly. Be warned, this option can be clumsy, tedious, and cause mistakes.

Let’s rewrite the above example, but instead of hardcoding 5 and 4, reference Cells containing them.

five = Cell(5)
four = Cell(4)
another_func = Func("SUM(", five, ", ", four, ", MIN(6, 7))")

Notice the comma we had to insert manually between five and four

Note

The above technique can be clumsy and prone to user-error. Its only benefit is improved speed, and that it doesn’t require any knowledge of object ids, headers, and Expr.

Referencing Elements - Option 2#

Templated Expr. Please read the intro to Expr if you aren’t familiar with them yet.

better_func = Func(f"SUM({{cell_five}}, {{cell_four}}, MIN(6, 7))")
five = Cell(5, id="cell_five")  # ^ Exprs can reference stuff that doesn't exist yet.
four = Cell(4, id="cell_four")

Why did we use braces {{...}} instead of square bracktets [...] as we do inside an Expr?

In an Expr, square brackets enclose an id or header. In a Func, curly braces enclose an Expr

To illustrate this, let’s make our Expr execute some python code. We’ll multiply cells five and four together, instead of listing them. For reference, in option 1 this would look like Func("SUM(", five * four, ", MIN(6, 7))")

Func(f"SUM({{[cell_five] * [cell_four]}}, MIN(6, 7))")

Which is equivalent to

Func("SUM(", Expr("[cell_five] * [cell_four]"), ", MIN(6, 7))")

Why are the braces necessary? Why can’t I just use square brackets alone?

Because in an Expr, the entire string is treated as valid Python code and is executed. This allows for Expr("[row_header].range() + [col_header].loc[3:]"). If this were placed inside a Func, it couldn’t be determined whether the .range, .loc, or + should be executed in Python, or placed as exact strings in the Excel formula. Excelbird could guess, but if it guessed incorrectly, you’d have a corrupted file.

Note

Double braces are strongly recommended, but any number of curly braces are allowed. i.e. {{{stuff}}}, {{stuff}}, or {stuff} are each valid. This is a design feature to ensure your code will look the same regardless of whether the string is an f-string.

Additional Arguments - res_type#

res_type can be used to specify the type of the returned element. This is not necessary inside structured parent containers like Frame or Row which decide automatically. Inside unstructured containers like Stack or Sheet it must be specified manually

For instance, in the following example, the parent Frame knows that res_type must be Col

Frame(
    Func(f"MAX({{second}}, {{third}})", header="first"),
    Col(1, 2, 3, header="second"),
    Col(2, 7, 1, header="third"),
)

If those elements were placed in a Stack, we have to decide: Should it return a Col with the max of each pair in the other vectors? Or should it return a Cell with the single largest value in either vector? Try both:

Stack(
    Col(1, 2, 3, header="first"),
    Col(2, 7, 1, header="second"),
    Func(f"MAX({{first}}, {{second}})", header="third", res_type=Col),
    Func(f"MAX({{first}}, {{second}})", res_type=Cell),
)

Here is an impractical example that demonstrates a variety of syntax at the same time.

my_cell = Cell(69)
Func("SUM(", {"foo"}, ", ", my_cell, None, ", ", Expr("bar"), f", SUM({{[one] * [two]}}, {{some_elem}}) + ", 5, ")")

will build a formula that’s arranged like:

=SUM(<Expr>, 99, <Cell>, SUM(<Expr>, <Expr>) + 5)

where the second Expr is the result of Expr("[one] * [two]") and the third Expr is the result of Expr("some_elem"). Notice the value of None was ignored, which is a feature shared by all excelbird layout elements to allow for conditional placement of elements.