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.