Writing Your First Book#

An HTML Layout#

<html>
    <body>
        <div>
            stuff
        </div>
        <div>
            more stuff
        </div>
    </body>
</html>

Excelbird Layout#

Book(  # no need to create a variable for it.
    Sheet(
        Frame(  # Columnar dataframe
            Col(
                Cell(1),
                Cell(2),
            ),
        ),
        Stack( # Unstructured horizontal container
            Cell(1),
            Row(1, 2, 3),
            Col(5, 6, 7),
            VFrame([[10,20], [30, 40]]),
        )
    )
).write(path)

Writing your first `Book`#

[1]:

import excelbird as xb
PATH = "test.xlsx"

A layout is a nested arrangement of elements. Each element will arrange its children in a different way. This way, you can re-arrange elements by simply re-arranging your code, without editing it.

Every layout is built inside a Book. There’s no need to assign it to a variable, since there’s nothing you can do to it except call .write().

Book doesn’t care what you put inside it. It will just put each argument into a separate sheet

[3]:

xb.Book(
    xb.Cell(1),  # sheet1
    xb.Cell(2),   # sheet2
).write(PATH)

Book 'test.xlsx' saved

fda28f27f76a4df1aab358c44c301d56

A Sheet is a container that arranges its elements vertically.

[ ]:

xb.Book(
    xb.Sheet(
        xb.Cell(1),
        xb.Cell(2),
    )
).write(PATH)

ef22027a36c14b08b2a809d809aafbbf

A Col is also a vertical container, but it’s structured, and contains Cells only

[14]:

xb.Col(1, 2, 3)

[14]:


Cell(1)
Cell(2)
Cell(3)

[15]:

xb.Row(1, 2, 3)

[15]:


Cell(1)	Cell(2)	Cell(3)

[ ]:

# 'import *' should usually be avoided,
# but it will make these demos easier to read
from excelbird import *

Book(
    Sheet(
        Col(Cell(1), Cell(2)),
        Col(1, 2),  # Any value will be converted to Cell
    ),
).write(PATH)

2caa7faf7bef46ecb0c852d1b50d5408

That’s not quite right. We need a container that’s horizontally arranged - Frame

[ ]:

Book(
    Sheet(
        Frame(
            Col(Cell(1), Cell(2)),
            Col(1, 2)  # Any value will be converted to Cell
        ),
    ),
).write(PATH)

db25e763d7884f38ab1a55160f7156b3

Auto-open#

Excelbird will auto-open your workbook for you if auto_open=True is set on the Book. It will even manage your Excel session by auto-closing the file first, if it’s already open. This feature is highly recommended for development - I always use it.

For this feature to work, the Excel app must already be running. It doesn’t matter if any workbooks are open or not, as long as the application is running.

Warning

Issues will likely be encountered when managing workbooks stored in OneDrive. In most cases the file will auto-open successfully, but throw an error when trying to auto-close on the next run. To avoid issues, write your files to Desktop, or somewhere else outside OneDrive.

Here’s an example to demonstrate syntax. We’ll omit this option on future examples just to simplify the code, but you should get in the habit of using it

[ ]:

Book(
    ...,
    auto_open=True,
).write(PATH)

Sibling Types#

All containers (besides Book and Sheet) have a sibling type: a functionally identical type who arranges its children along the opposite axis.

Note

You may be wondering why siblings must be separate classes, rather than just options specified by keyword argument. The answer is readability. Unlike HTML where an element’s features are specified in the opening tag, Python keyword arguments must go at the bottom, after the children. We enjoy having flow direction declared at the top of an element.

Col’s sibling type is Row, and Frame’s sibling type is VFrame. A VFrame contains Rows.

[ ]:

Book(
    VFrame(
        Row(1, 2, 3),
        Row(1, 2, 3),
    ),
).write(PATH)

5f93c11491204cffab03b0d662d595bf

Transpose#

Call .transpose() on any element who has a sibling type to toggle between types

[ ]:

Book(
    VFrame(
        Row(1, 2, 3),
        Row(1, 2, 3),
    ).transpose(),  # <-- switch to Frame/Col
).write(PATH)

d080340af3434da89b1e6d4eb3cea5f0

Stacks#

A Stack is an unstructured container that can hold any child type, and can nest other Stacks inside of itself.

Stack arranges its children horizontally, and has a sibling-type VStack.

Note

If you’ve been waiting to find out how to place an element anywhere on a page, nesting Stacks is one of the tools needed to do so. You’ll also need Gap to apply spacing, but we’ll get to that later.

Remember how Sheet arranges its elements vertically? This is exactly how a VStack will behave. Here’s a quick example of Sheet: We’ll place a Frame on top of a Row

[ ]:

Book(
    Sheet(
        Frame(
            Col(1, 2, 3),
            Col(1, 2, 3)
        ),
        Row(10, 20, 30),
    ),
).write(PATH)

bb45f26dcae14937bb2147fc0300e89a

To wrap these elements horizontally instead, use a Stack.

[ ]:

Book(
    Sheet(
        Stack(
            Frame(
                Col(1, 2, 3),
                Col(1, 2, 3)
            ),
            Row(10, 20, 30),
        ),
    ),
).write(PATH)

a88f54dddb484543b5613033682e2b37

Nested Stacks#

We can nest Stacks inside of one another. To place a Col right below the Row above, we’d nest them in a VStack.

[ ]:

Book(
    Sheet(
        Stack(
            Frame(
                Col(1, 2, 3),
                Col(1, 2, 3)
            ),
            VStack(
                Row(10, 20, 30),
                Col(100, 200, 300),
            ),
        ),
    ),
).write(PATH)

cac026eb9cd04b2ea2f1ac7d10fbed5e

Series Headers#

Col and Row have a unique attribute: header. The full utility of headers will become apparent later on, but for now, here’s how they work:

A header is a string applied via keyword argument to Col or Row: Col(1, 2, header="My Column")
It will be ignored by cell references created by expressions or functions (more on that later)
Its styling is independent from the other children, and is set by passing arguments to the header_style attribute
It will be inserted automatically if a pandas Series is given as input data. (Pandas DataFrame holds an array of pandas Series. Each pd.Series has a name attribute, which are used as the DataFrame’s column names)
Just like the id attribute, headers are unique identifiers that can be referenced globally by expressions (more on that later)

[16]:

Col(1, 2, 3, header="My Column")

[16]:

My Column
Cell(1)
Cell(2)
Cell(3)

[17]:

Row(1, 2, 3, header="My Row")

[17]:


My Row	Cell(1)	Cell(2)	Cell(3)

[19]:

import pandas as pd
df = pd.DataFrame([[1, 3], [2, 4]], columns=['First', 'Second'])

pandas_series = df['First']

Col(pandas_series)

[19]:

First
Cell(1)
Cell(2)

[ ]:

theme = xb.colors.theme
Book(
    Col(
        1,
        2,
        3,
        header="Column",
        fill_color=theme.green1,
        header_style=dict(bold=True),
    ),
    auto_open=True,
    zoom=350,
).write(PATH)

b45fcd631e0a423aad9d3aed01cd8133

Frames#

As expected, we can specify header_style at the Frame level to style all our columns

[ ]:

Book(
    Frame(
        Col(1, 2, 3, header="First"),
        Col(4, 5, 6, header="Second"),
        fill_color=theme.green2,
        header_style=dict(
            fill_color=theme.green1,
            bold=True,
            auto_shade_font=True,  # See styling tutorial
        ),
    ),
).write(PATH)

662efe6a756745be9eb7621b0bda86ba

Pandas Integration#

When given to Frame/VFrame, pandas Series will be interpreted as the respective child type (Col/Row)

[36]:

df = pd.DataFrame(
    zip([1,2,3,4], ['one', 'two', 'three', 'four']),
    columns=['Number', 'Word']
)
fr = Frame(
    df['Number'],
    df['Word'],
)
fr

[36]:

Number	Word
Cell(1)	Cell(one)
Cell(2)	Cell(two)
Cell(3)	Cell(three)
Cell(4)	Cell(four)

In the same way that we could give a single pandas Series to Col/Row, we can also give a DataFrame to Frame/VFrame

[37]:

fr = Frame(df)
fr

[37]:

Number	Word
Cell(1)	Cell(one)
Cell(2)	Cell(two)
Cell(3)	Cell(three)
Cell(4)	Cell(four)

Automatic Iterable Unpacking#

Why can Frame accept both a 2-dimensional DataFrame or 1-dimensional Series as arguments, and give the same result?

Frame/VFrame and Col/Row will look at their child arguments and attempt to identify iterables that have higher dimensions than is desired, and effectively explode those arguments inplace. Here’s a couple examples:

[38]:

Col(
    1,
    [2, 3],
    4
)

[38]:


Cell(1)
Cell(2)
Cell(3)
Cell(4)

[45]:

fr = Frame(
    # 1-dimensional
    pd.Series([1, 2, 3], name="One"),
    # 2-dimensional
    pd.DataFrame(zip([10,20,30], [100,200,300]), columns=["Two", "Three"]),
    # 1-dimensional
    Col(1000, 2000, 3000, header="Four"),
    # 2-dimensional
    Frame([100, 200, 300], [10, 20, 30], [1, 2, 3]),
)
fr

[45]:

One	Two	Three	Four
Cell(1)	Cell(10)	Cell(100)	Cell(1000)	Cell(100)	Cell(10)	Cell(1)
Cell(2)	Cell(20)	Cell(200)	Cell(2000)	Cell(200)	Cell(20)	Cell(2)
Cell(3)	Cell(30)	Cell(300)	Cell(3000)	Cell(300)	Cell(30)	Cell(3)

[46]:

fr.transpose()  # VFrame/Row

[46]:


One	Cell(1)	Cell(2)	Cell(3)
Two	Cell(10)	Cell(20)	Cell(30)
Three	Cell(100)	Cell(200)	Cell(300)
Four	Cell(1000)	Cell(2000)	Cell(3000)
	Cell(100)	Cell(200)	Cell(300)
	Cell(10)	Cell(20)	Cell(30)
	Cell(1)	Cell(2)	Cell(3)

We gave Frame 4 arguments, but the second and fourth were 2-dimensional, so they were unpacked inplace.

You should not get in the habit of relying on this feature for unconventional use-cases (don’t pass a Frame to another Frame). The feature exists to make working with pandas DataFrames and Series easier.

Subscripting/Slicing#

Note

Subscripting refers to accessing elements in an iterable using square brackets, like my_list[5]. Slicing refers to selecting multiple elements at a time using colons: my_list[5:8] will return a new list with elements 5, 6, and 7. Slices can also take a 3rd argument, ‘step’: my_list[::2] means “select every other element, starting from 0”.

You can select an element from a Frame/VFrame by its header

[71]:

fr = Frame(
    Col(1,2,3, header="One"),
    Col(10,20,30, header="Two"),
    Col(100,200,300, header="Three"),
    Col(1000,2000,3000, header="Four"),
    Col(100,200,300, header="Five"),
)
fr['Two']

[71]:

Two
Cell(10)
Cell(20)
Cell(30)

Slice by index

[72]:

fr[1:3]

[72]:

Two	Three
Cell(10)	Cell(100)
Cell(20)	Cell(200)
Cell(30)	Cell(300)

When slicing by header, the same rules apply: you will slice up to (exclusive of) the second part of the slice

[73]:

fr['Two':'Four']

[73]:

Two	Three
Cell(10)	Cell(100)
Cell(20)	Cell(200)
Cell(30)	Cell(300)

Part 3 of the slice (‘step’) can be used like normal, but it must be an integer

[67]:

fr[::2]

[67]:

One	Three	Five
Cell(1)	Cell(100)	Cell(100)
Cell(2)	Cell(200)	Cell(200)
Cell(3)	Cell(300)	Cell(300)

[68]:

fr['Two'::2]

[68]:

Two	Four
Cell(10)	Cell(1000)
Cell(20)	Cell(2000)
Cell(30)	Cell(3000)

Filter/Reorder Elements#

Passing a list of headers (double brackets), will return a new Frame with the given elements, in the desired order

[84]:

fr = fr[['Three', 'One', 'Two']]
fr

[84]:

Three	One	Two
Cell(100)	Cell(1)	Cell(10)
Cell(200)	Cell(2)	Cell(20)
Cell(300)	Cell(3)	Cell(30)

[85]:

fr = fr[['One', 'Two', 'Three']]
fr

[85]:

One	Two	Three
Cell(1)	Cell(10)	Cell(100)
Cell(2)	Cell(20)	Cell(200)
Cell(3)	Cell(30)	Cell(300)

Assignment#

We can also use subscripting to append new elements to a Frame/VFrame while giving them a header at the same time

[89]:

fr['Four'] = Col(10, 20, 30)
fr

[89]:

One	Two	Three	Four
Cell(1)	Cell(10)	Cell(100)	Cell(10)
Cell(2)	Cell(20)	Cell(200)	Cell(20)
Cell(3)	Cell(30)	Cell(300)	Cell(30)

Use the same syntax to modify an existing element

[90]:

fr['One'] = Col(1, 2, 3, 4, 5)
fr

[90]:

One	Two	Three	Four
Cell(1)	Cell(10)	Cell(100)	Cell(10)
Cell(2)	Cell(20)	Cell(200)	Cell(20)
Cell(3)	Cell(30)	Cell(300)	Cell(30)
Cell(4)
Cell(5)

Writing Your First Book#

An HTML Layout#

Excelbird Layout#

Writing your first Book#

Auto-open#

Sibling Types#

Transpose#

Stacks#

Nested Stacks#

Series Headers#

Frames#

Pandas Integration#

Automatic Iterable Unpacking#

Subscripting/Slicing#

Filter/Reorder Elements#

Assignment#

Writing your first `Book`#