# 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:

```python
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](app-mode.md#form-fields) above the script’s button:

```python
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:

![image](images/app_mode_form_fields.png)

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`:

```python
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:

![image](images/app_mode_form_fields_extended.png)

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:

```python
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`:

![image](images/script_button.png)

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

![image](images/script_button_dropdown.png)

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:
   ![image](images/namebox.png)

   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):
   ```python
   @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.:

```python
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:

```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](async-api.md). 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](https://docs.xlwings.org/en/latest/api/index.html) (see also [Limitations]())

It’s also worth looking at the following tutorials:

- [Syntax Overview](https://docs.xlwings.org/en/latest/syntax_overview.html)
- [Data Structures](https://docs.xlwings.org/en/latest/datastructures.html)
- [Matplotlib](https://docs.xlwings.org/en/latest/matplotlib.html)
- [Converters](https://docs.xlwings.org/en/latest/converters.html)

## 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:
  ```text
  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
  ```