Scripts

Scripts are the equivalent to a Sub in VBA or an Office Script. They run at the click of a button and have access to the Excel object model, i.e., they can insert a new sheet, format an Excel range as a table, set the color of a cell, etc.

Basic syntax

A script is a Python function that:

• has the @script decorator

• has a function argument with the xlwings.Book type hint

Here is how this looks:

import xlwings as xw
from xlwings import script

@script
def hello_world(book: xw.Book):
    sheet = book.sheets[0]
    sheet["A1"].value = "Hello xlwings!"

The book argument represents the active workbook and can be called differently if you like. E.g., if you want to call the argument wb instead of book, you would write wb: xw.Book instead of book: xw.Book.

You can configure scripts, see Script Configuration below. If your scripts are slow, see Performance.

Script arguments

Scripts can accept additional arguments after the book argument. Add a type hint to each one, and App Mode renders a matching form element above the script’s button:

import datetime as dt
from typing import Literal

import xlwings as xw
from xlwings import script

@script(name="Create Report")
def create_report(
    book: xw.Book,
    title: str,
    start: dt.date,
    rows: int = 10,
    tax_rate: float = 0.081,
    currency: Literal["USD", "EUR", "CHF"] = "USD",
    include_totals: bool = False,
):
    """# Monthly Report"""
    sheet = book.sheets[0]
    sheet["A1"].value = title
    sheet["B1"].value = start  # already a datetime.date

    amounts = [[i, i * (1 + tax_rate)] for i in range(1, rows + 1)]
    sheet["A3"].value = amounts
    if include_totals:
        sheet[f"A{3 + rows}"].value = ["Total", sum(row[1] for row in amounts)]
    sheet[f"B3:B{3 + rows}"].number_format = f'#,##0.00 "{currency}"'

In App Mode, this looks like this:

The type hint determines which form element is used:

Type hint	Form element	Value your script receives
str	Text box	str
int	Number box	int
float	Number box	float
bool	Checkbox	bool
Literal["a", "b"]	Dropdown	The selected value
datetime.date	Date picker	datetime.date
datetime.datetime	Date and time picker	datetime.datetime
Anything else / none	Text box	str

Note

Date conversion requires xlwings >= 0.36.11, make sure to update requirements.txt if you are using an older version. On older versions, datetime.date and datetime.datetime arguments arrive as ISO-formatted strings, which you can convert with dt.date.fromisoformat() or dt.datetime.fromisoformat().

Labels and help text

By default, the form labels each field with the parameter name. To show something friendlier, wrap the type hint in typing.Annotated with a label and an optional description:

from typing import Annotated

@script(name="Create Report")
def create_report(
    book: xw.Book,
    n_rows: Annotated[int, {"label": "Number of rows"}] = 10,
    tax_rate: Annotated[float, {"label": "Tax rate", "description": "As a decimal, e.g. 0.081"}] = 0.081,
):
    ...

Here’s how tax_rate ends up looking:

The label replaces the parameter name in the form and in any validation message, and the description is shown as muted help text between the label and the field, so it’s read before the value is entered. Everything else — which widget is used, defaults, whether the argument is required — still comes from the type hint and the signature, so you can add a label to any argument without changing its behavior.

Scripts with arguments are only runnable in App Mode except if they all have a default argument.

Button Label

By default, the button shows the name of the function. To change this, provide a name argument in the @script decorator:

import xlwings as xw
from xlwings import script

@script(name="Run Hello World")
def hello_world(book: xw.Book):
    sheet = book.sheets[0]
    sheet["A1"].value = "Hello xlwings!"

Running a script from the add-in

To run a script, click the play button in the editor gutter next to its function, click the run button, or press F5:

To select a different script to run, select it via dropdown:

Whenever you add a new script or change the name of an existing script, the button and dropdown will update automatically.

The dropdown lists scripts from main.py first, followed by other workbook modules alphabetically and then personal modules alphabetically. Within each workbook module, scripts retain their source-code order.

Running a script via sheet button

Office.js (the add-in technology that xlwings Lite is programmed in) doesn’t offer a native way to connect a button on a sheet to a script. Therefore, xlwings Lite supports a solution via shapes and hyperlinks. While this is a workaround, it offers a user experience that is on par with the official buttons that Office Scripts offers.

Warning

If your script depends on the selected cells, this solution currently doesn’t work as clicking the button will change the selected cell. Run your script from the task pane instead.

1. On the Excel ribbon, go to Insert > Shapes and select e.g., a rounded rectangle. Then draw the shape on the sheet. Adjust colors and text to your liking.

2. Select the shape. In the name box (on the left of the formula bar), give it a specific name, then hit Enter:

   

   For the rest of this example we’re going to use xlwings_button.

3. Right-click on the shape and select Link (Windows) or Hyperlink... (macOS). On the tab Place in This Document (Windows) or This Document (macOS), where it says Type the cell reference, write the name of a cell that is hidden behind the shape, e.g., B4. Confirm by clicking on OK.

4. Provide the following arguments in the script decorator (show_taskpane is optional and opens the task pane if it closed):

   @script(button="[xlwings_button]Sheet1!B4", show_taskpane=True)
   def hello_world(book: xw.Book):
       ...
   

How does it work? When the xlwings Lite saves the Python code, it registers an event handler that runs the script when the referenced cell is selected. This happens when you click the button as we have set up a hyperlink. Immediately after that cell has been selected, it selects the cell below it to be ready for the next call.

Note

When you open a workbook with a sheet button, you currently need to first open xlwings Lite to activate the buttons. In a future release, you will be able to set the add-in to start automatically so this won’t be required anymore.

Sheet button troubleshooting

• Make sure that you haven’t initially selected the cell that is referenced under button.

• Make sure the button reference in the script decorator is correct, especially with regard to the button name: button=[button_name]Sheet1!A1.

• Restart xlwings Lite to make sure that event handlers are properly registered.

Note

Excel on the web doesn’t allow you to add a hyperlink to a shape. However, workbooks that were set up on the desktop version of Excel also work with Excel on the web.

Script Configuration

To configure scripts, you can provide the decorator with arguments, e.g.:

import xlwings as xw
from xlwings import script

@script(include=["Sheet1", "Sheet2"])
def hello_world(book: xw.Book):
    sheet = book.sheets[0]
    sheet["A1"].value = "Hello xlwings!"

Here are the settings that you can provide:

• exclude (optional): By default, xlwings sends over the content of the whole workbook to Python. If you have sheets with big amounts of data, this can make the calls slow or timeout. If your code doesn’t need the content of certain sheets, the exclude option will block the sheet’s content (e.g., values, pictures, etc.) from being sent to Python. Currently, you can only exclude entire sheets like so: exclude=["Sheet1", "Sheet2"].

• include (optional): It’s the counterpart to exclude and allows you to submit the names of a few seleceted sheets whose content (e.g., values, pictures, etc.) you want to send to Python. Currently, you can only include entire sheets like so: include=["Sheet1", "Sheet2"].

• button (optional): If you want to use a sheet button, you need to provide the reference for the button and its linked cell, e.g., button=[mybutton]Sheet1!A1.

• show_taskpane (optional): Use this in connection with button. If show_taskpane=True, the task pane will automatically show up when the user clicks on a sheet button.

Performance

By default, xlwings Lite transfers the content of the whole workbook to Python up front. For small workbooks, you won’t notice this. But if you have sheets with large amounts of data, this transfer can make your scripts slow or even cause them to time out.

There are two ways to deal with this:

1. Limit the data with include/exclude

If your script only needs a few sheets, use the include or exclude arguments in the @script decorator to restrict which sheets are sent to Python:

import xlwings as xw
from xlwings import script

# Only send Sheet1 and Sheet2 to Python
@script(include=["Sheet1", "Sheet2"])
def include_sample(book: xw.Book):
    sheet = book.sheets[0]
    sheet["A1"].value = "Hello xlwings!"

# Send everything except the data-heavy sheets
@script(exclude=["BigData1", "BigData2"])
def exlude_sample(book: xw.Book):
    sheet = book.sheets[0]
    sheet["A1"].value = "Hello xlwings!"

Note that include and exclude operate on entire sheets: an excluded sheet’s content isn’t available to your script at all. If you need finer-grained control, use the async API described next.

2. Read on demand with the async API

include/exclude still transfer the full content of the sheets they let through. For an even more efficient approach that transfers only the exact values you request—working the same way as classic, locally installed xlwings—use the Async API. Instead of loading the whole workbook up front, it reads and writes cell values on demand, which avoids the up-front transfer entirely.

Excel object model

To learn about the Excel object model, have a look at the following docs from xlwings:

• API reference (see also Limitations)

It’s also worth looking at the following tutorials:

• Syntax Overview

• Data Structures

• Matplotlib

• Converters

Limitations

• Script arguments must be JSON-serializable, and keyword-only arguments (after a *) as well as **kwargs aren’t supported, see Script arguments.

• xlwings Lite doesn’t support the api property that classic xlwings offers to workaround missing features.

• At the moment, xlwings Lite doesn’t cover yet 100% of the xlwings API. The following attributes are currently missing:

  xlwings.App
  
      - cut_copy_mode
      - quit()
      - display_alerts
      - startup_path
      - calculate()
      - status_bar
      - path
      - version
      - screen_updating
      - interactive
      - enable_events
      - calculation
  
  xlwings.Book
  
      - to_pdf()
      - save()
  
  xlwings.Characters
  
      - font
      - text
  
  xlwings.Chart
  
      - set_source_data()
      - to_pdf()
      - parent
      - delete()
      - top
      - width
      - height
      - name
      - to_png()
      - left
      - chart_type
  
  xlwings.Charts
  
      - add()
  
  xlwings.Font (setting the following properties is supported, only getting them isn't!)
  
      - size
      - italic
      - color
      - name
      - bold
  
  xlwings.Note
  
      - delete()
      - text
  
  xlwings.PageSetup
  
      - print_area
  
  xlwings.Picture
  
      - top
      - left
      - lock_aspect_ratio
  
  xlwings.Range
  
      - hyperlink
      - formula
      - font
      - width
      - formula2
      - characters
      - to_png()
      - columns
      - height
      - formula_array
      - paste()
      - rows
      - note
      - merge_cells
      - row_height
      - get_address()
      - merge()
      - to_pdf()
      - autofill()
      - top
      - wrap_text
      - merge_area
      - column_width
      - copy_picture()
      - table
      - unmerge()
      - current_region
      - left
  
  xlwings.Shape
  
      - parent
      - delete()
      - font
      - top
      - scale_height()
      - activate()
      - width
      - index
      - text
      - height
      - characters
      - name
      - type
      - scale_width()
      - left
  
  xlwings.Sheet
  
      - page_setup
      - used_range
      - shapes
      - charts
      - autofit()
      - copy()
      - to_html()
      - select()
      - visible
  
  xlwings.Table
  
      - display_name
      - show_table_style_last_column
      - show_table_style_column_stripes
      - insert_row_range
      - show_table_style_first_column
      - show_table_style_row_stripes