Warning: When setting a sheet name to longer than 31 characters (Excel's limit), the sheet name is cut short. This will causes errors if the recipe is run more than once without deleting prior generated sheets.
The Excel connector allows users to define which sheet and where to write the output, write to multiple sheets, write to sheets dynamically or return data at different stages of the recipe. More configuration options will be added over time.
read:
- excel.selected_data: {}
excel.selected_data reads the selection supplied by WranglesXL for the current batch. It supports the shared read filters, including columns, not_columns, and where. See Read for joins and batching.
| Parameter | Required | Data Type | Notes |
|---|---|---|---|
| columns | str, list | Include selected columns. | |
| not_columns | str, list | Exclude columns. Absent excluded names are ignored. | |
| where | str | SQL WHERE filter applied to the selected data. | |
| where_params | list, object | Parameters used by the WHERE expression. |
write:
- excel.columns:
columns: [Description, Category]
Insert new or changed output columns alongside the selected rows. Unchanged input columns are omitted at the XL write boundary. If all requested columns are unchanged, nothing is inserted. The output must retain the selected rows' alignment and row count; use excel.sheet for row-expanding or row-reducing results.
| Parameter | Required | Data Type | Notes |
|---|---|---|---|
| columns | str, list | Columns to include in this write. | |
| not_columns | str, list | Columns to exclude; absent names are ignored. | |
| if | str | Condition controlling this write. |
Each write uses its own column selection. Columns first appearing in later batches are added in first-seen order and values are aligned by name. If an input column first changes in a later batch, earlier output cells are filled from the original selected values.
These writers require matching WranglesXL and recipe-runtime support. Installing the Python package alone does not update the deployed Excel service.
write:
- excel.sheet:
name: NewSheet
cell: C3
write:
- excel.sheet:
name: Results
action: overwrite
formatting:
columns:
Price:
num_format: '$0.00'
align: right
Approved:
checkbox: true
Description:
text_wrap: true
bold: false
align accepts general, left, center, or right. num_format is a nonempty Excel format string. bold, checkbox, and text_wrap are booleans. Unsupported formatting groups/options are rejected. These options belong to excel.sheet; the file connector has a separate formatting contract.
Sheet mode writes all requested columns, including unchanged input columns. Later batches append to the same resolved sheet/range after the initial overwrite. Nested recipe wrangles suppress Excel writes; use write: dataframe inside the nested recipe and write from its parent.
The Excel connector can be utilized in a custom function but please remember that it was built only to function within WranglesXL and will function properly otherwise.
from wrangles.connectors import excel
excel.write(df=my_dataframe, name='NewSheet', cell = 'C3')
| Parameter | Required | Data Type | Notes |
|---|---|---|---|
| name | str | Name of the sheet to write to. If omitted, will default to the name of the recipe. | |
| columns | list | Subset of the columns to be written. If not provided, all columns will be output. | |
| not_columns | list | Subset of the columns to be left out. | |
| cell | str | The top left cell to write the data from. Default A1. | |
| action | str | Action (append, increment, or overwrite) to take when writing the data if the sheet already exists. Default append. Append: add to the existing sheet. Increment: add a new sheet with an incrementing number. Overwrite: replace existing sheet. | |
| freezepanes | bool | If true, will freeze the first row. Default false. | |
| as_table | bool | If true, will write the data as an Excel table. Default true. | |
| order_by | str | Uses SQL syntax to sort the output. | |
| formatting | object | Column-specific formatting for excel.sheet. Use formatting.columns, keyed by output column name. Supported options: align, num_format, bold, checkbox, and text_wrap. |
|
| if | str | A condition that will determine whether the action runs or not as a whole. |
Note: The Excel connector is only to be used in recipes within WranglesXL and will not function properly otherwise.