Async API

For scripts and notebooks, xlwings Lite lets you use the same syntax as the classic, locally installed xlwings to interact with the Excel object model (sync API). This often works fine, but because xlwings Lite runs in a browser, it talks to Excel differently, which comes with a few limitations. The async API is designed to work around them:

  1. Writes are queued: Instead of being applied immediately, writes are collected and applied to Excel (in order) at the end of a notebook cell or when a script finishes.

    → await book.flush() applies pending writes immediately.

  2. The whole workbook is loaded: The sync API (myrange.value) has to load all values of the workbook up front. This can be slow or even run out of memory with large workbooks.

    → The async API transfers only the values you request.

  3. State can be stale: Because everything is loaded up front, anything you read from the workbook reflects its state at the moment you got the book object, not at the moment you access the property.

    → Use the async API, or refresh the sync API’s data with book.load().

Getting started with the Async API

This section introduces the async API by comparing it with the sync API. The main goal of the async API is to read cell values on demand. Other parts of the Excel object model (such as the sheets or tables collections) aren’t covered right now, since load() handles them well enough.

The sync API is the traditional, property-based API used by classic xlwings. You read and write values through properties, and get the book differently depending on whether you’re in a notebook (xw.books.active) or a script (xw.Book annotation):

# %%
import xlwings as xw

book = xw.books.active
myrange = book.sheets[0]["A1"]
myrange.value

import xlwings as xw
from xlwings import script

@script
def my_script(book: xw.Book):
    myrange = book.sheets[0]["A1"]
    myrange.value

The async API uses coroutines that you await instead of properties. To get the book object, use await xw.books.get_active() in a notebook or xw.BookAsync in a script:

Note

  • Notebooks let you await at the top level without requiring an async def.

  • Scripts must use await inside of an async def.

# %%
import xlwings as xw

book = await xw.books.get_active()
myrange = book.sheets[0]["A1"]
await myrange.get_value()

import xlwings as xw
from xlwings import script

@script
async def my_script(book: xw.BookAsync):
    myrange = book.sheets[0]["A1"]
    await myrange.get_value()

The following example goes into more detail to show the benefits of the async API:

Sync API

# %%
import xlwings as xw

book = xw.books.active  # Fetches all values from all sheets up front
sheet = book.sheets[0]
sheet["A1"].value = "xlwings"  # Written to Excel at the end of the cell

# %%
book.sheets[0]["A1"].value  # Reads the snapshot from when xw.books.active ran

# Output
# [1]
# (no output)
# [2]
# (no output)

Async API

# %%
import xlwings as xw

book = await xw.books.get_active()  # Doesn't fetch any values
sheet = book.sheets[0]
sheet["A1"].value = "xlwings"  # Written to Excel at the end of the cell

# %%
await sheet["A1"].get_value()  # Reads the current value from Excel

# Output
# [1]
# (no output)
# [2]
# 'xlwings'

Sync API

import xlwings as xw
from xlwings import script

@script
def my_script(book: xw.Book):  # xw.Book fetches all values from all sheets up front
    sheet = book.sheets[0]
    sheet["A1"].value = "xlwings"
    print(book.sheets[0]["A1"].value)  # Values reflect state when entering the function
    # prints None

Async API

import xlwings as xw
from xlwings import script

@script
async def my_script(book: xw.BookAsync):  # xw.BookAsync doesn't fetch any values
    sheet = book.sheets[0]
    sheet["A1"].value = "xlwings"  # This write is queued
    await book.flush()  # Applies the queued write to Excel
    print(await sheet["A1"].get_value())  # Reads the current value from Excel
    # prints 'xlwings'

Note that get_value() respects the same converters and options as the sync .value, so you can, for example, read a range directly into a pandas DataFrame:

import pandas as pd

df = await (
    book.sheets["Sheet1"]["A1:C10"]
    .options(pd.DataFrame, index=False)
    .get_value()
)

load(): Controlling the read process

If you need the current workbook state for an object that isn’t covered by the async API yet (see Async API Coverage), or if you’d rather use the sync API throughout, refresh the data with book.load() or mysheet.load() like so:

Note

Notebooks call load() automatically before each cell, so the workbook state is current whenever a cell runs. Cell values are the exception—read them on demand via await myrange.get_value().

% ##
book = await xw.books.get_active()
sheet1 = book.sheets[0]
await book.load()  # or: await sheet1.load()
sheet1.tables

import xlwings as xw
from xlwings import script

@script
async def my_script(book: xw.BookAsync):
    sheet1 = book.sheets[0]
    await book.load()  # or: await sheet1.load()
    sheet1.tables

Two things to keep in mind:

  • On a sync book (xw.books.active or xw.Book), load() loads everything, including values. On an async book (await xw.books.get_active() or xw.BookAsync), it loads everything except values, which you keep reading on demand via await myrange.get_value().

  • Calling load() on a sheet object rather than the book loads only that sheet instead of the whole book.

flush(): Controlling the write process

When writing to the workbook, xlwings Lite queues every action and applies them in order at the end of a notebook cell or when the script finishes:

# %%
book = await xw.books.get_active()
book.sheets[0]["A1"].value = "Hello xlwings!"

import xlwings as xw
from xlwings import script

@script
async def my_script(book: xw.BookAsync):
    book.sheets[0]["A1"].value = "Hello xlwings!"

If you need to flush queued writes to Excel before the automatic flush happens (for example, to write a value and then read it back), call await book.flush():

# %%
book = await xw.books.get_active()
book.sheets[0]["A1"].value = "Hello xlwings!"
await book.flush()
await book.sheets[0]["A1"].get_value()

# Output
# 'Hello xlwings!'

import xlwings as xw
from xlwings import script

@script
async def my_script(book: xw.BookAsync):
    book.sheets[0]["A1"].value = "Hello xlwings!"
    await book.flush()
    print(await book.sheets[0]["A1"].get_value())
    # prints 'Hello xlwings!'

You also need flush() if you want print output to show up in the Output pane immediately rather than all at once at the end:

# %%
import time

book = await xw.books.get_active()

for i in range(5):
    print(i)
    await book.flush()
    time.sleep(1)

import time

import xlwings as xw
from xlwings import script

@script
async def my_script(book: xw.BookAsync):
    for i in range(5):
        print(i)
        await book.flush()
        time.sleep(1)

Another common task is to export a range as PNG and then read it back (e.g., to use it in a PowerPoint presentation via the python-pptx package). Since exporting the range to PNG is a queued action, you need to flush() before the file is available on disk:

# %%
import os
import xlwings as xw

# %%
book = await xw.books.get_active()
sheet = book.sheets[0]
sheet["A1:D10"].to_png("/data/range.png")
await book.flush()
print(os.listdir("/data"))

import os

import xlwings as xw
from xlwings import script

@script
async def my_script(book: xw.BookAsync):
    sheet = book.sheets[0]
    sheet["A1:D10"].to_png("/data/range.png")
    await book.flush()
    print(os.listdir("/data"))

Async API Coverage

The async API currently covers the following:

  • book = await xw.books.get_active()

  • await book.sheets.get_active()

  • await book.get_selection()

  • await myrange.get_value()

For everything else, use book.load() or mysheet.load() in combination with the sync API. For example, the following two approaches are equivalent (load() is slightly less efficient, as it fetches the whole book instead of just the selection):

# Async API
await book.get_selection()

# Sync API with load()
await book.load()
book.selection

Note

Notebooks call load() automatically before each cell, so the workbook state is current whenever a cell runs. Cell values are the exception—read them on demand via await myrange.get_value().