xlwings

https://github.com/xlwings/xlwings
Admin
xlwings is a Python library that makes it easy to call Python from Excel and vice versa, supporting...
Tokens:391,855
Snippets:3,384
Trust Score:8.8
License:BSD-3-Clause
Update:4 weeks ago
Show doc for...
Tokens:
Raw
### Example: get file converters

Source: https://github.com/xlwings/xlwings/blob/main/resources/mac/Excel2004AppleScriptRef.pdf

Example that returns a list of file converters.

```applescript
get file converters
```

--------------------------------

### Example configuration for end-user Python interpreter paths

Source: https://github.com/xlwings/xlwings/blob/main/docs/customaddin.md

Shows how to set interpreter paths using environment variables for end users with Anaconda installed in the default location. Use this in the myaddin.conf sheet when deploying manually.

```text
Conda Path: %USERPROFILE%\anaconda3
Conda Env: base
Interpreter_Mac: $HOME/opt/anaconda3/bin/python
```

--------------------------------

### Install documentation dependencies

Source: https://github.com/xlwings/xlwings/blob/main/DEVELOPER_GUIDE.md

Install the documentation requirements and the sphinx-autobuild package.

```bash
pip install -r docs/requirements.txt
pip install sphinx-autobuild
```

--------------------------------

### Install add-in with xlwings addin install

Source: https://github.com/xlwings/xlwings/blob/main/docs/customaddin.md

Use this command to permanently install your custom add-in. It copies the add-in to the XLSTART folder, from which Excel opens all files at startup. After installation, you must adjust the PYTHONPATH so Excel can find your Python code.

```bash
$ xlwings addin install --file C:\path\to\your\myproject.xlam
```

--------------------------------

### Get pivot table data example

Source: https://github.com/xlwings/xlwings/blob/main/resources/mac/Excel2004AppleScriptRef.pdf

Shows the sum of revenues for apples in January (Data field = Revenue, Product = Apples, Month = January).

```applescript
display dialog (get pivot table data (pivot table 1 of active sheet) ¬ name "'Sum of Revenue' Apples January") as string
```

--------------------------------

### Range autofit examples

Source: https://github.com/xlwings/xlwings/blob/main/docs/whatsnew.md

Examples of using the autofit method on different ranges.

```python
# Autofit column A
Range('A:A').autofit()
# Autofit row 1
Range('1:1').autofit()
# Autofit columns and rows, taking into account Range('A1:E4')
Range('A1:E4').autofit()
# AutoFit rows, taking into account Range('A1:E4')
Range('A1:E4').autofit('rows')
```

--------------------------------

### Install an add-in

Source: https://github.com/xlwings/xlwings/blob/main/resources/mac/Excel2004AppleScriptRef.pdf

Installs the Analysis Toolpak add-in by setting the installed property of the add in object to true. The add-in title must match the spelling in the Add-Ins dialog box, but capitalization is not required.

```applescript
set installed of add in "analysis toolpak" to true
```

--------------------------------

### Style Object Usage Examples

Source: https://github.com/xlwings/xlwings/blob/main/resources/mac/Excel2004AppleScriptRef.pdf

AppleScript examples for applying and modifying styles in Excel 2004.

```APIDOC
## [SET] style object of range "A1:A10" of worksheet "Sheet1"

### Description
Applies a style to a range of cells. This example sets the Percent style to cells A1:A10 on Sheet1.

### Method
SET (AppleScript property assignment)

### Endpoint
`style object of range "A1:A10" of worksheet "Sheet1"`

### Parameters
#### Target
- **range** (string) - Required - The range to which the style is applied. Example: "A1:A10"
- **worksheet** (string) - Required - The worksheet containing the range. Example: "Sheet1"

#### Value
- **style** (style object) - Required - The style to apply. Example: `style "Percent" of active workbook`

### Request Example
```applescript
set style object of range "A1:A10" of worksheet "Sheet1" to ¬ style "Percent" of active workbook
```

### Response
No return value.
```

```APIDOC
## [SET] bold of font object of style "Normal" of active workbook

### Description
Changes a property of a style. This example sets the bold property of the Normal style to true, affecting all cells formatted with that style.

### Method
SET (AppleScript property assignment)

### Endpoint
`bold of font object of style "Normal" of active workbook`

### Parameters
#### Target
- **style** (string) - Required - The name of the style. Example: "Normal"
- **font object** (object) - Required - The font object of the style.
- **bold** (boolean) - Required - The value to set for the bold property. Example: `true`

### Request Example
```applescript
set bold of font object of style "Normal" of active workbook to true
```

### Response
No return value.
```

--------------------------------

### Connect to a specific Excel installation on Mac

Source: https://github.com/xlwings/xlwings/blob/main/docs/whatsnew.md

Shows how to use the app_target keyword argument to connect to a different Excel installation on Mac.

```python
Workbook(app_target='/Applications/Microsoft Office 2011/Microsoft Excel')
```

--------------------------------

### xlwings quickstart command

Source: https://github.com/xlwings/xlwings/blob/main/docs/vba.md

Creates a new project with the xlwings add-in and a sample VBA module. Run from the command line client.

```text
$ xlwings quickstart myproject
```

--------------------------------

### Sheet class examples

Source: https://github.com/xlwings/xlwings/blob/main/docs/whatsnew.md

Examples of using the new Sheet class: accessing sheet name, clearing contents, and getting active sheet.

```pycon
>>> Sheet(1).name
'Sheet1'
>>> Sheet('Sheet1').clear_contents()
>>> Sheet.active()
<Sheet 'Sheet1' of Workbook 'Book1'>
```

--------------------------------

### Run xlwings quickstart with --addin --ribbon

Source: https://github.com/xlwings/xlwings/blob/main/docs/customaddin.md

Creates a new custom add-in project with a ribbon. Omit the --ribbon flag to create an add-in without a ribbon.

```bash
$ xlwings quickstart myproject --addin --ribbon
```

--------------------------------

### Start Function Wizard for cell A1

Source: https://github.com/xlwings/xlwings/blob/main/resources/mac/Excel2004AppleScriptRef.pdf

Starts the Function Wizard for the upper-left cell of the range. The example targets cell A1 on Sheet1.

```applescript
function wizard cell "A1" of sheet "Sheet1"
```

--------------------------------

### Setup sample DataFrame

Source: https://github.com/xlwings/xlwings/blob/main/docs/converters.md

Creates a workbook and a sample DataFrame with NaN values for testing converters.

```python
# Fire up a Workbook and create a sample DataFrame
sheet = xw.Book().sheets[0]
df = pd.DataFrame([[1.,10.],[2.,np.nan], [3., 30.]])
```

--------------------------------

### get XML value: retrieve range value as XML

Source: https://github.com/xlwings/xlwings/blob/main/resources/mac/Excel2004AppleScriptRef.pdf

Returns the value of the specified range as XML. Example gets the value of A1:C3.

```applescript
get XML value range "A1:C3"
```

--------------------------------

### Get save as filename example

Source: https://github.com/xlwings/xlwings/blob/main/resources/mac/Excel2004AppleScriptRef.pdf

Displays the Save As dialog box with the file filter set to text files. If the user chooses a file name, the example displays that file name in a message box.

```applescript
set fileSaveName to get save as filename file filter "TEXT"
if fileSaveName is not "" then
	display dialog "Save as " & fileSaveName
end if
```

--------------------------------

### Get list item example

Source: https://github.com/xlwings/xlwings/blob/main/resources/mac/Excel2004AppleScriptRef.pdf

Returns the string contained in list item 1 of listbox 1 on the active sheet.

```applescript
get list item listbox 1 of active sheet entry index 1
```

--------------------------------

### xlwings quickstart project structure

Source: https://github.com/xlwings/xlwings/blob/main/docs/whatsnew.md

Shows the folder structure generated by `xlwings quickstart myproject`, including the Excel file and Python module.

```text
myproject
  |--myproject.xlsm
  |--myproject.py
```

--------------------------------

### Build docs without autobuild

Source: https://github.com/xlwings/xlwings/blob/main/DEVELOPER_GUIDE.md

Change to the docs directory and build the HTML documentation with make.

```bash
cd docs
make html
```

--------------------------------

### Get FileMaker criteria example

Source: https://github.com/xlwings/xlwings/blob/main/resources/mac/Excel2004AppleScriptRef.pdf

Returns the values of arguments used in the second set of criteria from an existing Excel query table created against a FileMaker database.

```applescript
set QT to query table 1 of active sheet
set criteriaRecord to get FileMaker criteria QT criteria index 2
set varfieldName to «class 5324» of criteriaRecord
set varClauseText to «class 5325» of criteriaRecord
set varCondition to «class 5326» of criteriaRecord
set varOperator to operator of criteriaRecord
```

--------------------------------

### Using App as a context manager to avoid zombie processes

Source: https://github.com/xlwings/xlwings/blob/main/docs/whatsnew.md

Demonstrates using `xw.App` as a context manager to ensure no zombie processes remain on Windows, even with hidden instances or code failures.

```python
with xw.App(visible=True) as app:
    print(app.books)
```

--------------------------------

### Get workbook for add-in

Source: https://github.com/xlwings/xlwings/blob/main/resources/mac/Excel2004AppleScriptRef.pdf

Returns a reference to the workbook corresponding to a loaded add-in by using the add-in filename (not title). The example sets the wb variable to the workbook for Myaddin.xla.

```applescript
set wb to workbook "Myaddin.xla"
```

--------------------------------

### Object Hierarchy Navigation with myrange.sheet.book.app

Source: https://github.com/xlwings/xlwings/blob/main/docs/syntax_overview.md

Shows how to get from a range object back up the object hierarchy to the Excel app. The example is in a Python console and shows the returned Excel App object.

```pycon
>>> myrange = xw.apps[10559].books[0].sheets[0].range('A1')
>>> myrange.sheet.book.app
<Excel App 10559>
```

--------------------------------

### Get app from book: wb.app vs xw.Application(wb)

Source: https://github.com/xlwings/xlwings/blob/main/docs/whatsnew.md

In v0.9.0, get the app from a book with `app = wb.app`. In v0.7.2, use `app = xw.Application(wb)`.

```python
app = wb.app
```

```python
app = xw.Application(wb)
```

--------------------------------

### Range.row, Range.column, Range.last_cell usage

Source: https://github.com/xlwings/xlwings/blob/main/docs/whatsnew.md

Example from the 'New methods' section showing how to use Range.row, Range.column, and Range.last_cell on a table range. The output demonstrates that the table starts at (1,1) and ends at (4,5).

```pycon
>>> rng = Range('A1').table
>>> rng.row, rng.column
(1, 1)
>>> rng.last_cell.row, rng.last_cell.column
(4, 5)
```

--------------------------------

### Connect to a workbook with xw.Book()

Source: https://github.com/xlwings/xlwings/blob/main/docs/quickstart.md

Establish a connection with a workbook using xw.Book(). The first call opens a new workbook, the second connects to a file that is open or in the current working directory, and the third uses a raw string on Windows to escape backslashes.

```pycon
>>> import xlwings as xw
>>> wb = xw.Book()  # this will open a new workbook
>>> wb = xw.Book('FileName.xlsx')  # connect to a file that is open or in the current working directory
>>> wb = xw.Book(r'C:\path\to\file.xlsx')  # on Windows: use raw strings to escape backslashes
```

--------------------------------

### expand option: dynamic range expansion

Source: https://github.com/xlwings/xlwings/blob/main/docs/converters.md

Shows how the expand option works like the Range properties table, vertical, and horizontal, but is only evaluated when getting values. The example compares the expand() method with options(expand='table') and shows that the latter updates when new data is added.

```python
>>> import xlwings as xw
>>> sheet = xw.Book().sheets[0]
>>> sheet['A1'].value = [[1,2], [3,4]]
>>> range1 = sheet['A1'].expand()
>>> range2 = sheet['A1'].options(expand='table')
>>> range1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> range2.value
[[1.0, 2.0], [3.0, 4.0]]
>>> sheet['A3'].value = [5, 6]
>>> range1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> range2.value
[[1.0, 2.0], [3.0, 4.0], [5.0, 6.0]]
```

--------------------------------

### Create a report with app.render_template (report.py)

Source: https://github.com/xlwings/xlwings/blob/main/docs/pro/reports/reports.md

Creates a report by rendering a template Excel file with data, then exports to PDF. Uses a hidden Excel instance when visible=False. The template file 'mytemplate.xlsx' must be in the same directory as the script.

```python
# report.py
from pathlib import Path

import pandas as pd
import xlwings as xw

# We'll place this file in the same directory as the Excel template
this_dir = Path(__file__).resolve().parent

data = dict(
    title='MyTitle',
    df=pd.DataFrame(data={'one': [1, 2], 'two': [3, 4]})
)

# Change visible=False to run this in a hidden Excel instance
with xw.App(visible=True) as app:
    book = app.render_template(this_dir / 'mytemplate.xlsx',
                               this_dir / 'myreport.xlsx',
                               **data)
    book.to_pdf(this_dir / 'myreport.pdf')
```

--------------------------------

### Run a unittest

Source: https://github.com/xlwings/xlwings/blob/main/DEVELOPER_GUIDE.md

Run a single unittest module (e.g., test_range) using the unittest framework. The full test suite is currently broken, so running single modules is recommended.

```bash
python -m unittest tests.test_range
```

--------------------------------

### hello.py world() function

Source: https://github.com/xlwings/xlwings/blob/main/docs/vba.md

Python module called by the VBA RunPython example. Uses xw.Book.caller() to get the calling workbook and writes to cell A1. Place xw.Book.caller() inside the function, not as a global variable, to avoid zombie processes when Use UDF Server is True.

```python
# hello.py
import numpy as np
import xlwings as xw

def world():
    wb = xw.Book.caller()
    wb.sheets[0]['A1'].value = 'Hello World!'
```

--------------------------------

### Read 1d ranges as flat lists or 1d arrays with atleast_2d

Source: https://github.com/xlwings/xlwings/blob/main/docs/whatsnew.md

In v0.1.1, 1d Ranges in Excel (rows or columns) are read as flat lists or 1d arrays. Use the atleast_2d keyword to get 2d arrays. This example shows various ways to read vertical, horizontal, and table ranges.

```pycon
>>> Range('A1').vertical.value
[1.0, 2.0, 3.0, 4.0]
>>> Range('A1', atleast_2d=True).vertical.value
[[1.0], [2.0], [3.0], [4.0]]
>>> Range('C1').horizontal.value
[1.0, 2.0, 3.0, 4.0]
>>> Range('C1', atleast_2d=True).horizontal.value
[[1.0, 2.0, 3.0, 4.0]]
>>> Range('A1', asarray=True).table.value
array([ 1.,  2.,  3.,  4.])
>>> Range('A1', asarray=True, atleast_2d=True).table.value
array([[ 1.],
       [ 2.],
       [ 3.],
       [ 4.]])
```

--------------------------------

### raw converter example

Source: https://github.com/xlwings/xlwings/blob/main/docs/converters.md

Shows the difference between default value access and the 'raw' converter, which returns values unchanged from the underlying libraries (pywin32 on Windows, appscript on Mac).

```pycon
>>> sheet['A1:B2'].value
[[1.0, 'text'], [datetime.datetime(2016, 2, 1, 0, 0), None]]

>>> sheet['A1:B2'].options('raw').value  # or sheet['A1:B2'].raw_value
((1.0, 'text'), (pywintypes.datetime(2016, 2, 1, 0, 0, tzinfo=TimeZoneInfo('GMT Standard Time', True)), None))
```

--------------------------------

### Install xlwings from conda-forge

Source: https://github.com/xlwings/xlwings/blob/main/docs/installation.md

Installs xlwings from the conda-forge channel. Replace 'install' with 'upgrade' if xlwings is already installed.

```text
conda install -c conda-forge xlwings
```

--------------------------------

### UDF example with DataFrameDropna

Source: https://github.com/xlwings/xlwings/blob/main/docs/converters.md

Shows how to use the custom converter with @arg and @ret decorators in a UDF.

```python
@xw.func
@arg('x', DataFrameDropna, dropna=True)
@ret(DataFrameDropna, dropna=True)
def myfunction(x):
    # ...
    return x
```

--------------------------------

### Get registered functions example

Source: https://github.com/xlwings/xlwings/blob/main/resources/mac/Excel2004AppleScriptRef.pdf

Creates a list of registered functions, placing one registered function in each row on Sheet1. Column A contains the full path and file name of the DLL or code resource, column B contains the function name, and column C contains the argument data type code.

```applescript
set theList to (get registered functions)
if theList is not "" then
	display dialog "No registered functions"
else
	repeat with i from 1 to (get count of theList)
		repeat with j from 1 to 3
			set formula of cell i of column j of worksheet "Sheet1" to ¬
				(item j of item 1 of theList)
		end repeat
	end repeat
end if
```

--------------------------------

### Example project structure for xlwings release

Source: https://github.com/xlwings/xlwings/blob/main/docs/pro/release.md

Shows the required file layout: the workbook in .xlsm format next to Python code and a package directory.

```text
myworkbook.xlsm
mymodule_one.py
mypackage/
  mymodule_two.py
...
```

--------------------------------

### Range Commands

Source: https://github.com/xlwings/xlwings/blob/main/resources/mac/Excel2004AppleScriptRef.pdf

Commands for the range object in Excel 2004 AppleScript: get end, get offset, get resize, get XML value, goal seek, group, and insert indent.

```APIDOC
## get end

### Description
Returns a **range** object that represents the cell at the end of the region containing the source range. Equivalent to pressing END+UP ARROW, END+DOWN ARROW, END+LEFT ARROW, or END+RIGHT ARROW.

### Syntax
**get end** range Required. An expression that returns a **range** object.

**direction** Required. The direction to move. Can be one of the following constants: toward the top, toward the bottom, toward the left, or toward the right.

### Parameters
- **range** (range) - Required - An expression that returns a **range** object.
- **direction** (constant) - Required - The direction to move: toward the top, toward the bottom, toward the left, or toward the right.

### Example
```applescript
set theEnd to get end range "C2" direction toward the right
```
```

```APIDOC
## get offset

### Description
Returns a **range** object that represents a range that's offset from the specified range.

### Syntax
**get offset** range Required. An expression that returns a **range** object.

[**row offset** integer] Optional. The number of rows — positive, negative, or zero — by which the range is to be offset. The default value is 0.

[**column offset** integer] Optional. The number of columns — positive, negative, or zero — by which the range is to be offset. The default value is 0.

### Parameters
- **range** (range) - Required - An expression that returns a **range** object.
- **row offset** (integer) - Optional - The number of rows — positive, negative, or zero — by which the range is to be offset. Default is 0.
- **column offset** (integer) - Optional - The number of columns — positive, negative, or zero — by which the range is to be offset. Default is 0.

### Example
```applescript
get offset range "A3:E5" row offset 3 column offset 4
```
```

```APIDOC
## get resize

### Description
Resizes the specified range. Returns a **range** object that represents the resized range.

### Syntax
**get resize** range Required. An expression that returns a **range** object.

[**row size** integer] Optional. The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same.

[**column size** integer] Optional. The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same.

### Parameters
- **range** (range) - Required - An expression that returns a **range** object.
- **row size** (integer) - Optional - The number of rows in the new range. If omitted, the number of rows remains the same.
- **column size** (integer) - Optional - The number of columns in the new range. If omitted, the number of columns remains the same.

### Example
```applescript
get resize range "A3:E5" row size 7 column size 4
```
```

```APIDOC
## get XML value

### Description
Returns the value of the specified range as XML.

### Syntax
**get XML value** range Required. An expression that returns a **range** object.

### Parameters
- **range** (range) - Required - An expression that returns a **range** object.

### Example
```applescript
get XML value range "A1:C3"
```
```

```APIDOC
## goal seek

### Description
Calculates the values necessary to achieve a specific goal. If the goal is an amount returned by a formula, this calculates a value that, when supplied to your formula, causes the formula to return the number you want. Returns **true** if the goal seek is successful.

### Syntax
**goal seek** range Required. An expression that returns a **range** object. Must be a single cell.

**goal** real Required. The value you want returned in this cell.

**changing cell** range Required. Specifies which cell should be changed to achieve the target value.

### Parameters
- **range** (range) - Required - An expression that returns a **range** object. Must be a single cell.
- **goal** (real) - Required - The value you want returned in this cell.
- **changing cell** (range) - Required - Specifies which cell should be changed to achieve the target value.

### Example
```applescript
goal seek (range "Polynomial" of worksheet "Sheet1") goal 15 changing cell (range "X" of worksheet "Sheet1")
```
```

```APIDOC
## group

### Description
When the **range** object represents a single cell in the pivot field's data range, the **group** command performs numeric or date-based grouping in a pivot field.

### Syntax
**group** range Required. An expression that returns a **range** object. The **range** object must be a single cell in the pivot field's data range. If you attempt to apply this command to more than one cell, it will fail (without displaying an error message).

[**start** integer] Optional. The first value to be grouped. If this argument is omitted or **true**, the first value in the field is used.

[**end** integer] Optional. The last value to be grouped. If this argument is omitted or **true**, the last value in the field is used.

[**by** integer] Optional. If the field is numeric, this argument specifies the size of each group. If the field is a date, this argument specifies the number of days in each group if element 4 in the **periods** list is **true** and all the other elements are **false**. Otherwise, this argument is ignored. If this argument is omitted, Excel automatically chooses a default group size.

[**periods** list] Optional. A list of Boolean values that specify the period for the group, as shown in the following table.

|Element|Period|
|---|---|
|1|Seconds|
|2|Minutes|
|3|Hours|
|4|Days|
|5|Months|
|6|Quarters|
|7|Years|

If an element in the list is **true**, a group is created for the corresponding time; if the element is **false**, no group is created. If the field isn't a date field, this argument is ignored.

### Parameters
- **range** (range) - Required - An expression that returns a **range** object. Must be a single cell in the pivot field's data range.
- **start** (integer) - Optional - The first value to be grouped. If omitted or **true**, the first value in the field is used.
- **end** (integer) - Optional - The last value to be grouped. If omitted or **true**, the last value in the field is used.
- **by** (integer) - Optional - If the field is numeric, specifies the size of each group. If the field is a date, specifies the number of days in each group if element 4 in the **periods** list is **true** and all other elements are **false**. Otherwise, ignored. If omitted, Excel automatically chooses a default group size.
- **periods** (list) - Optional - A list of Boolean values that specify the period for the group. If an element is **true**, a group is created for the corresponding time; if **false**, no group is created. If the field isn't a date field, this argument is ignored.

### Example
```applescript
set pvtTable to pivot table of range "A3" of worksheet "Sheet1"
set groupRange to data range of pivot field "ORDER_DATE" of pvtTable
group (cell 1 of groupRange) by 10 periods {false, false, false, true, false, false, false}
```
```

```APIDOC
## insert indent

### Description
Adds an indent to the specified range.

### Syntax
**insert indent** range Required. An expression that returns a **range** object.

### Parameters
- **range** (range) - Required - An expression that returns a **range** object.

### Example
```applescript
insert indent range "A1:B2"
```
```

--------------------------------

### help

Source: https://github.com/xlwings/xlwings/blob/main/resources/mac/Excel2004AppleScriptRef.pdf

Displays the Help window.

```APIDOC
## help

### Description
Displays the Help window.

### Syntax
`help` [help file Unicode text] Optional. The name of the online Help file to display. If not specified, Excel Help is displayed. [help context id integer] Optional. Specifies the context ID number for the Help topic to display. If not specified, the default topic for the specified Help file is displayed.

### Parameters
- **help file** (Unicode text) - Optional - The name of the online Help file to display.
- **help context id** (integer) - Optional - The context ID number for the Help topic to display.

### Remarks
The **help file** argument must specify an Office Help file. Otherwise, a "file could not be found" message appears.

### Example
```applescript
help help file "Excel Help" help context id 1027
```
```
Tokens:
Raw