xlwings - Make Excel Fly!

xlwings is a BSD-licensed Python library that makes it easy to call Python from Excel and vice versa:

  • Scripting: Automate/interact with Excel from Python using a syntax close to VBA.
  • Macros: Replace VBA macros with clean and powerful Python code.
  • UDFs: Write User Defined Functions (UDFs) in Python (Windows only).
  • REST API: Expose your Excel workbooks via REST API.

Numpy arrays and Pandas Series/DataFrames are fully supported. xlwings-powered workbooks are easy to distribute and work on Windows and Mac.

Video course

Those who prefer a didactically structured video course over this documentation should have a look at our video course:

https://training.zoomeranalytics.com/p/xlwings

It’s also a great way to support the ongoing development of xlwings :)

What’s New

v0.15.7 (May 5, 2019)

  • [Bug Fix] Conda Base and Conda Env weren’t stored correctly in the config file from the ribbon (GH1090).
  • [Bug Fix] UDFs now work correctly with Conda Base and Conda Env. Note, however, that currently there is no way to hide the command prompt in that configuration (GH1090).
  • [Enhancement] Restart UDF Server now actually does what it says: it stops and restarts the server. Previously it was only stopping the server and only when the first call to Python was made, it was started again (GH1096).

v0.15.6 (Apr 29, 2019)

  • [Feature] New default converter for OrderedDict (GH1068).
  • [Enhancement] Import Functions now restarts the UDF server to guarantee a clean state after importing. (GH1092)
  • [Enhancement] The ribbon now shows tooltips on Windows (GH1093)
  • [Bug Fix] RunPython now properly supports conda environments on Windows (they started to require proper activation with packages like numpy etc). Conda >=4.6. required. A fix for UDFs is still pending (GH954).

Breaking Change:

  • [Bug Fix] RunFronzenPython now accepts spaces in the path of the executable, but in turn requires to be called with command line arguments as a separate VBA argument. Example: RunFrozenPython "C:\path\to\frozen_executable.exe", "arg1 arg2" (GH1063).

v0.15.5 (Mar 25, 2019)

  • [Enhancement] wb.macro() now accepts xlwings objects as arguments such as range, sheet etc. when the VBA macro expects the corresponding Excel object (e.g. Range, Worksheet etc.) (GH784 and GH1084)

Breaking Change:

  • Cells that contain a cell error such as #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE! return now None as value in Python. Previously they were returned as constant on Windows (e.g. -2146826246) or k.missing_value on Mac.

v0.15.4 (Mar 17, 2019)

  • [Win] BugFix: The ribbon was not showing up in Excel 2007. (GH1039)
  • Enhancement: Allow to install xlwings on Linux even though it’s not a supported platform: export INSTALL_ON_LINUX=1; pip install xlwings (GH1052)

v0.15.3 (Feb 23, 2019)

Bug Fix release:

  • [Mac] RunPython was broken by the previous release. If you install via conda, make sure to run xlwings runpython install again! (GH1035)
  • [Win] Sometimes, the ribbon was throwing errors (GH1041)

v0.15.2 (Feb 3, 2019)

Better support and docs for deployment, see Deployment:

  • You can now package your python modules into a zip file for easier distribution (GH1016).
  • RunFrozenPython now allows to includes arguments, e.g. RunFrozenPython "C:\path\to\my.exe arg1 arg2" (GH588).

Breaking changes:

  • Accessing a not existing PID in the apps collection raises now a KeyError instead of an Exception (GH1002).

v0.15.1 (Nov 29, 2018)

Bug Fix release:

  • [Win] Calling Subs or UDFs from VBA was causing an error (GH998).

v0.15.0 (Nov 20, 2018)

Dynamic Array Refactor

While we’re all waiting for the new native dynamic arrays, it’s still going to take another while until the majority can use them (they are not yet part of Office 2019).

In the meantime, this refactor improves the current xlwings dynamic arrays in the following way:

  • Use of native (“legacy”) array formulas instead of having a normal formula in the top left cell and writing around it
  • It’s up to 2x faster
  • There’s no empty row/col required outside of the dynamic array anymore
  • It continues to overwrite existing cells (no change there)
  • There’s a small breaking change in the unlikely case that you were assigning values with the expand option: myrange.options(expand='table').value = [['b'] * 3] * 3. This was previously clearing contiguous cells to the right and bottom (or one of them depending on the option), now you have to do that explicitly.

Bug Fixes:

  • Importing multiple UDF modules has been fixed (GH991).

v0.14.1 (Nov 9, 2018)

This is a bug fix release:

  • [Win] Fixed an issue when the new async_mode was used together with numpy arrays (GH984)
  • [Mac] Fixed an issue with multiple arguments in RunPython (GH905)
  • [Mac] Fixed an issue with the config file (GH982)

v0.14.0 (Nov 5, 2018)

Features:

This release adds support for asynchronous functions (like all UDF related functionality, this is only available on Windows). Making a function asynchronous is as easy as:

import xlwings as xw
import time

@xw.func(async_mode='threading')
def myfunction(a):
    time.sleep(5)  # long running tasks
    return a

See Asynchronous UDFs for the full docs.

Bug Fixes:

v0.13.0 (Oct 22, 2018)

Features:

This release adds a REST API server to xlwings, allowing you to easily expose your workbook over the internet, see REST API for all the details!

Enhancements:

  • Dynamic arrays are now more robust. Before, they often didn’t manage to write everything when there was a lot going on in the workbook (GH880)
  • Jagged arrays (lists of lists where not all rows are of equal length) now raise an error (GH942)
  • xlwings can now be used with threading, see the docs: Threading and Multiprocessing (GH759).
  • [Win] xlwings now enforces pywin32 224 when installing xlwings on Python 3.7 (GH959)
  • New xlwings.Sheet.used_range property (GH112)

Bug Fixes:

  • The current directory is now inserted in front of everything else on the PYTHONPATH (GH958)
  • The standalone files had an issue in the VBA module (GH960)

Breaking changes:

  • Members of the xw.apps collection are now accessed by key (=PID) instead of index, e.g.: xw.apps[12345] instead of xw.apps[0]. The apps collection also has a new xw.apps.keys() method. (GH951)

v0.12.1 (Oct 7, 2018)

[Py27] Bug Fix for a Python 2.7 glitch.

v0.12.0 (Oct 7, 2018)

Features:

This release adds support to call Python functions from VBA in all Office apps (e.g. Access, Outlook etc.), not just Excel. As this uses UDFs, it is only available on Windows. See the docs: xlwings with other Office Apps.

Breaking changes:

Previously, Python functions were always returning 2d arrays when called from VBA, no matter whether it was actually a 2d array or not. Now you get the proper dimensionality which makes it easier if the return value is e.g. a string or scalar as you don’t have to unpack it anymore.

Consider the following example using the VBA Editor’s Immediate Window after importing UDFs from a project created using by xlwings quickstart:

Old behaviour

?TypeName(hello("xlwings"))
Variant()
?hello("xlwings")(0,0)
hello xlwings

New behaviour

?TypeName(hello("xlwings"))
String
?hello("xlwings")
hello xlwings

Bug Fixes:

v0.11.8 (May 13, 2018)

  • [Win] pywin32 is now automatically installed when using pip (GH827)
  • xlwings.bas has been readded to the python package. This facilitates e.g. the use of xlwings within other addins (GH857)

v0.11.7 (Feb 5, 2018)

  • [Win] This release fixes a bug introduced with v0.11.6 that would’t allow to open workbooks by name (GH804)

v0.11.6 (Jan 27, 2018)

Bug Fixes:

  • [Win] When constantly writing to a spreadsheet, xlwings now correctly resumes after clicking into cells, previously it was crashing. (GH587)
  • Options are now correctly applied when writing to a sheet (GH798)

v0.11.5 (Jan 7, 2018)

This is mostly a bug fix release:

  • Config files can now additionally be saved in the directory of the workbooks, overriding the global Ribbon config, see Global Config: Ribbon/Config File (GH772)
  • Reading Pandas DataFrames with a simple index was creating a MultiIndex with Pandas > 0.20 (GH786)
  • [Win] The xlwings dlls are now properly versioned, allowing to use pre 0.11 releases in parallel with >0.11 releases (GH743)
  • [Mac] Sheet.names.add() was always adding the names on workbook level (GH771)
  • [Mac] UDF decorators now don’t cause errors on Mac anymore (GH780)

v0.11.4 (Jul 23, 2017)

This release brings further improvements with regards to the add-in:

  • The add-in now shows the version on the ribbon. This makes it easy to check if you are using the correct version (GH724):

    _images/addin_version.png

  • [Mac] On Mac Excel 2016, the ribbon now only shows the available functionality (GH723):

    _images/mac_ribbon.png

  • [Mac] Mac Excel 2011 is now supported again with the new add-in. However, since Excel 2011 doesn’t support the ribbon, the config file has be created/edited manually, see Global Config: Ribbon/Config File (GH714).

Also, some new docs:

v0.11.3 (Jul 14, 2017)

  • Bug Fix: When using the xlwings.conf sheet, there was a subscript out of range error (GH708)
  • Enhancement: The add-in is now password protected (pw: xlwings) to declutter the VBA editor (GH710)

You need to update your xlwings add-in to get the fixes!

v0.11.2 (Jul 6, 2017)

  • Bug Fix: The sql extension was sometimes not correctly assigning the table aliases (GH699)
  • Bug Fix: Permission errors during pip installation should be resolved now (GH693)

v0.11.1 (Jul 5, 2017)

v0.11.0 (Jul 2, 2017)

Big news! This release adds a full blown add-in! We also throw in a great In-Excel SQL Extension and a few bug fixes:

Add-in

_images/ribbon.png

A few highlights:

  • Settings don’t have to be manipulated in VBA code anymore, but can be either set globally via Ribbon/config file or for the workbook via a special worksheet
  • UDF server can be restarted directly from the add-in
  • You can still use a VBA module instead of the add-in, but the recommended way is the add-in
  • Get all the details here: Add-in

In-Excel SQL Extension

The add-in can be extended with own code. We throw in an sql function, that allows you to perform SQL queries on data in your spreadsheets. It’s pretty awesome, get the details here: Extensions.

Bug Fixes

  • [Win]: Running Debug > Compile is not throwing errors anymore (GH678)
  • Pandas deprecation warnings have been fixed (GH675 and GH664)
  • [Mac]: Errors are again shown correctly in a pop up (GH660)
  • [Mac]: Like Windows, Mac now also only shows errors in a popup. Before it was including stdout, too (GH666)

Breaking Changes

  • RunFrozenPython now requires the full path to the executable.
  • The xlwings CLI xlwings template functionality has been removed. Use quickstart instead.

v0.10.4 (Feb 19, 2017)

  • [Win] Bug Fix: v0.10.3 introduced a bug that imported UDFs by default with volatile=True, this has now been fixed. You will need to reimport your functions after upgrading the xlwings package.

v0.10.3 (Jan 28, 2017)

This release adds new features to User Defined Functions (UDFs):

  • categories
  • volatile option
  • suppress calculation in function wizard

Syntax:

import xlwings as xw
@xw.func(category="xlwings", volatile=False, call_in_wizard=True)
def myfunction():
    return ...

For details, check out the (also new) and comprehensive API docs about the decorators: UDF decorators

v0.10.2 (Dec 31, 2016)

  • [Win] Python 3.6 is now supported (GH592)

v0.10.1 (Dec 5, 2016)

  • Writing a Pandas Series with a MultiIndex header was not writing out the header (GH572)
  • [Win] Docstrings for UDF arguments are now working (GH367)
  • [Mac] Range.clear_contents() has been fixed (it was doing clear() instead) (GH576)
  • xw.Book(...) and xw.books.open(...) raise now the same error in case the file doesn’t exist (GH540)

v0.10.0 (Sep 20, 2016)

Dynamic Array Formulas

This release adds an often requested & powerful new feature to User Defined Functions (UDFs): Dynamic expansion for array formulas. While Excel offers array formulas, you need to specify their dimensions up front by selecting the result array first, then entering the formula and finally hitting Ctrl-Shift-Enter. While this makes sense from a data integrity point of view, in practice, it often turns out to be a cumbersome limitation, especially when working with dynamic arrays such as time series data.

This is a simple example that demonstrates the syntax and effect of UDF expansion:

import numpy as np

@xw.func
@xw.ret(expand='table')
def dynamic_array(r, c):
    return np.random.randn(int(r), int(c))
_images/dynamic_array1.png
_images/dynamic_array2.png

Note: Expanding array formulas will overwrite cells without prompting and leave an empty border around them, i.e. they will clear the row to the bottom and the column to the right of the array.

Bug Fixes

  • The int converter works now always as you would expect (e.g.: xw.Range('A1').options(numbers=int).value). Before, it could happen that the number was off by 1 due to floating point issues (GH554).

v0.9.3 (Aug 22, 2016)

  • [Win] App.visible wasn’t behaving correctly (GH551).
  • [Mac] Added support for the new 64bit version of Excel 2016 on Mac (GH549).
  • Unicode book names are again supported (GH546).
  • xlwings.Book.save() now supports relative paths. Also, when saving an existing book under a new name without specifying the full path, it’ll be saved in Python’s current working directory instead of in Excel’s default directory (GH185).

v0.9.2 (Aug 8, 2016)

Another round of bug fixes:

  • [Mac]: Sometimes, a column was referenced instead of a named range (GH545)
  • [Mac]: Python 2.7 was raising a LookupError: unknown encoding: mbcs (GH544)
  • Fixed docs regarding set_mock_caller (GH543)

v0.9.1 (Aug 5, 2016)

This is a bug fix release: As to be expected after a rewrite, there were some rough edges that have now been taken care of:

  • [Win] Opening a file via xw.Book() was causing an additional Book1 to be opened in case Excel was not running yet (GH531)
  • [Win] Some users were getting an ImportError (GH533)
  • [PY 2.7] RunPython was broken with Python 2.7 (GH537)
  • Some corrections in the docs (GH538 and GH536)

v0.9.0 (Aug 2, 2016)

Exciting times! v0.9.0 is a complete rewrite of xlwings with loads of syntax changes (hence the version jump). But more importantly, this release adds a ton of new features and bug fixes that would have otherwise been impossible. Some of the highlights are listed below, but make sure to check out the full migration guide for the syntax changes in details. Note, however, that the syntax for user defined functions (UDFs) did not change. At this point, the API is fairly stable and we’re expecting only smaller changes on our way towards a stable v1.0 release.

  • Active book instead of current book: xw.Range('A1') goes against the active sheet of the active book like you’re used to from VBA. Instantiating an explicit connection to a Book is not necessary anymore:

    >>> import xlwings as xw
>>> xw.Range('A1').value = 11
>>> xw.Range('A1').value
11.0

  • Excel Instances: Full support of multiple Excel instances (even on Mac!)

    >>> app1 = xw.App()
>>> app2 = xw.App()
>>> xw.apps
Apps([<Excel App 1668>, <Excel App 1644>])

  • New powerful object model based on collections and close to Excel’s original, allowing to fully qualify objects: xw.apps[0].books['MyBook.xlsx'].sheets[0].range('A1:B2').value

    It supports both Python indexing (square brackets) and Excel indexing (round brackets):

    xw.books[0].sheets[0] is the same as xw.books(1).sheets(1)

    It also supports indexing and slicing of range objects:

    >>> rng = xw.Range('A1:E10')
>>> rng[1]
<Range [Workbook1]Sheet1!$B$1>
>>> rng[:2, :2]
<Range [Workbook1]Sheet1!$A$1:$B$2>

    For more details, see Syntax Overview.

  • UDFs can now also be imported from packages, not just modules (GH437)

  • Named Ranges: Introduction of full object model and proper support for sheet and workbook scope (GH256)

  • Excel doesn’t become the active window anymore so the focus stays on your Python environment (GH414)

  • When writing to ranges while Excel is busy, xlwings is now retrying until Excel is idle again (GH468)

  • xlwings.view() has been enhanced to accept an optional sheet object (GH469)

  • Objects like books, sheets etc. can now be compared (e.g. wb1 == wb2) and are properly hashable

  • Note that support for Python 2.6 has been dropped

Some of the new methods/properties worth mentioning are:

Bug Fixes

  • See here for details about which bugs have been fixed.

v0.7.2 (May 18, 2016)

Bug Fixes

  • [Win] UDFs returning Pandas DataFrames/Series containing nan were failing (GH446).
  • [Win] RunFrozenPython was not finding the executable (GH452).
  • The xlwings VBA module was not finding the Python interpreter if PYTHON_WIN or PYTHON_MAC contained spaces (GH461).

v0.7.1 (April 3, 2016)

Enhancements

  • [Win]: User Defined Functions (UDFs) support now optional/default arguments (GH363)

  • [Win]: User Defined Functions (UDFs) support now multiple source files, see also under API changes below. For example (VBA settings): UDF_MODULES="common;myproject"

  • VBA Subs & Functions are now callable from Python:

    As an example, this VBA function:

    Function MySum(x, y)
    MySum = x + y
End Function

    can be accessed like this:

    >>> import xlwings as xw
>>> wb = xw.Workbook.active()
>>> my_sum = wb.macro('MySum')
>>> my_sum(1, 2)
3.0

  • New xw.view method: This opens a new workbook and displays an object on its first sheet. E.g.:

    >>> import xlwings as xw
>>> import pandas as pd
>>> import numpy as np
>>> df = pd.DataFrame(np.random.rand(10, 4), columns=['a', 'b', 'c', 'd'])
>>> xw.view(df)

  • New docs about Matplotlib and Custom Converter

  • New method: xlwings.Range.formula_array() (GH411)

API changes

  • VBA settings: PYTHON_WIN and PYTHON_MAC must now include the interpreter if you are not using the default (PYTHON_WIN = "") (GH289). E.g.:

    PYTHON_WIN: "C:\Python35\pythonw.exe"
PYTHON_MAC: "/usr/local/bin/python3.5"

  • [Win]: VBA settings: UDF_PATH has been replaced with UDF_MODULES. The default behaviour doesn’t change though (i.e. if UDF_MODULES = "", then a Python source file with the same name as the Excel file, but with .py ending will be imported from the same directory as the Excel file).

    New:

    UDF_MODULES: "mymodule"
PYTHONPATH: "C:\path\to"

    Old:

    UDF_PATH: "C:\path\to\mymodule.py"

Bug Fixes

  • Numpy scalars issues were resolved (GH415)
  • [Win]: xlwings was failing with freezers like cx_Freeze (GH413)
  • [Win]: UDFs were failing if they were returning None or np.nan (GH390)
  • Multiindex Pandas Series have been fixed (GH383)
  • [Mac]: xlwings runpython install was failing (GH424)

v0.7.0 (March 4, 2016)

This version marks an important first step on our path towards a stable release. It introduces converters, a new and powerful concept that brings a consistent experience for how Excel Ranges and their values are treated both when reading and writing but also across xlwings.Range objects and User Defined Functions (UDFs).

As a result, a few highlights of this release include:

  • Pandas DataFrames and Series are now supported for reading and writing, both via Range object and UDFs
  • New Range converter options: transpose, dates, numbers, empty, expand
  • New dictionary converter
  • New UDF debug server
  • No more pyc files when using RunPython

Converters are accessed via the new options method when dealing with xlwings.Range objects or via the @xw.arg and @xw.ret decorators when using UDFs. As an introductory sample, let’s look at how to read and write Pandas DataFrames:

_images/df_converter.png

Range object:

>>> import xlwings as xw
>>> import pandas as pd
>>> wb = xw.Workbook()
>>> df = xw.Range('A1:D5').options(pd.DataFrame, header=2).value
>>> df
    a     b
    c  d  e
ix
10  1  2  3
20  4  5  6
30  7  8  9

# Writing back using the defaults:
>>> Range('A1').value = df

# Writing back and changing some of the options, e.g. getting rid of the index:
>>> Range('B7').options(index=False).value = df

UDFs:

This is the same sample as above (starting in Range('A13') on screenshot). If you wanted to return a DataFrame with the defaults, the @xw.ret decorator can be left away.

@xw.func
@xw.arg('x', pd.DataFrame, header=2)
@xw.ret(index=False)
def myfunction(x):
   # x is a DataFrame, do something with it
   return x

Enhancements

  • Dictionary (dict) converter:

    _images/dict_converter.png 
    >>> Range('A1:B2').options(dict).value
{'a': 1.0, 'b': 2.0}
>>> Range('A4:B5').options(dict, transpose=True).value
{'a': 1.0, 'b': 2.0}

  • transpose option: This works in both directions and finally allows us to e.g. write a list in column orientation to Excel (GH11):

    Range('A1').options(transpose=True).value = [1, 2, 3]

  • dates option: This allows us to read Excel date-formatted cells in specific formats:

    >>> import datetime as dt
>>> Range('A1').value
datetime.datetime(2015, 1, 13, 0, 0)
>>> Range('A1').options(dates=dt.date).value
datetime.date(2015, 1, 13)

  • empty option: This allows us to override the default behavior for empty cells:

    >>> Range('A1:B1').value
[None, None]
>>> Range('A1:B1').options(empty='NA')
['NA', 'NA']

  • numbers option: This transforms all numbers into the indicated type.

    >>> xw.Range('A1').value = 1
>>> type(xw.Range('A1').value)  # Excel stores all numbers interally as floats
float
>>> type(xw.Range('A1').options(numbers=int).value)
int

  • expand option: This works the same as the Range properties table, vertical and horizontal but is only evaluated when getting the values of a Range:

    >>> import xlwings as xw
>>> wb = xw.Workbook()
>>> xw.Range('A1').value = [[1,2], [3,4]]
>>> rng1 = xw.Range('A1').table
>>> rng2 = xw.Range('A1').options(expand='table')
>>> rng1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> rng2.value
[[1.0, 2.0], [3.0, 4.0]]
>>> xw.Range('A3').value = [5, 6]
>>> rng1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> rng2.value
[[1.0, 2.0], [3.0, 4.0], [5.0, 6.0]]

All these options work the same with decorators for UDFs, e.g. for transpose:

@xw.arg('x', transpose=True)
@xw.ret(transpose=True)
def myfunction(x):
    # x will be returned unchanged as transposed both when reading and writing
    return x

Note: These options (dates, empty, numbers) currently apply to the whole Range and can’t be selectively applied to e.g. only certain columns.

  • UDF debug server

    The new UDF debug server allows you to easily debug UDFs: just set UDF_DEBUG_SERVER = True in the VBA Settings, at the top of the xlwings VBA module (make sure to update it to the latest version!). Then add the following lines to your Python source file and run it:

    if __name__ == '__main__':
    xw.serve()

    When you recalculate the Sheet, the code will stop at breakpoints or print any statements that you may have. For details, see: Debugging.

  • pyc files: The creation of pyc files has been disabled when using RunPython, leaving your directory in an uncluttered state when having the Python source file next to the Excel workbook (GH326).

API changes

  • UDF decorator changes (it is assumed that xlwings is imported as xw and numpy as np):

    New Old
    @xw.func @xw.xlfunc
    @xw.arg @xw.xlarg
    @xw.ret @xw.xlret
    @xw.sub @xw.xlsub

    Pay attention to the following subtle change:

    New Old
    @xw.arg("x", np.array) @xw.xlarg("x", "nparray")

  • Samples of how the new options method replaces the old Range keyword arguments:

    New Old
    Range('A1:A2').options(ndim=2) Range('A1:A2', atleast_2d=True)
    Range('A1:B2').options(np.array) Range('A1:B2', asarray=True)
    Range('A1').options(index=False, header=False).value = df Range('A1', index=False, header=False).value = df

  • Upon writing, Pandas Series are now shown by default with their name and index name, if they exist. This can be changed using the same options as for DataFrames (GH276):

    import pandas as pd

# unchanged behaviour
Range('A1').value = pd.Series([1,2,3])

# Changed behaviour: This will print a header row in Excel
s = pd.Series([1,2,3], name='myseries', index=pd.Index([0,1,2], name='myindex'))
Range('A1').value = s

# Control this behaviour like so (as with DataFrames):
Range('A1').options(header=False, index=True).value = s

  • NumPy scalar values

    Previously, NumPy scalar values were returned as np.atleast_1d. To keep the same behaviour, this now has to be set explicitly using ndim=1. Otherwise they’re returned as numpy scalar values.

    New Old
    Range('A1').options(np.array, ndim=1).value Range('A1', asarray=True).value

Bug Fixes

A few bugfixes were made: GH352, GH359.

v0.6.4 (January 6, 2016)

API changes

None

Enhancements

  • Quickstart: It’s now easier than ever to start a new xlwings project, simply use the commmand line client (GH306):

    xlwings quickstart myproject will produce a folder with the following files, ready to be used (see Command Line Client):

    myproject
  |--myproject.xlsm
  |--myproject.py

  • New documentation about how to use xlwings with other languages like R and Julia, see xlwings with R and Julia.

Bug Fixes

  • [Win]: Importing UDFs with the add-in was throwing an error if the filename was including characters like spaces or dashes (GH331). To fix this, close Excel completely and run xlwings addin update.
  • [Win]: Workbook.caller() is now also accessible within functions that are decorated with @xlfunc. Previously, it was only available with functions that used the @xlsub decorator (GH316).
  • Writing a Pandas DataFrame failed in case the index was named the same as a column (GH334).

v0.6.3 (December 18, 2015)

Bug Fixes

  • [Mac]: This fixes a bug introduced in v0.6.2: When using RunPython from VBA, errors were not shown in a pop-up window (GH330).

v0.6.2 (December 15, 2015)

API changes

  • LOG_FILE: So far, the log file has been placed next to the Excel file per default (VBA settings). This has been changed as it was causing issues for files on SharePoint/OneDrive and Mac Excel 2016: The place where LOG_FILE = "" refers to depends on the OS and the Excel version, see Log File default locations.

Enhancements

  • [Mac]: This version adds support for the VBA module on Mac Excel 2016 (i.e. the RunPython command) and is now feature equivalent with Mac Excel 2011 (GH206).

Bug Fixes

  • [Win]: On certain systems, the xlwings dlls weren’t found (GH323).

v0.6.1 (December 4, 2015)

Bug Fixes

  • [Python 3]: The command line client has been fixed (GH319).
  • [Mac]: It now works correctly with psutil>=3.0.0 (GH315).

v0.6.0 (November 30, 2015)

API changes

None

Enhancements

  • User Defined Functions (UDFs) - currently Windows only

    The ExcelPython project has been fully merged into xlwings. This means that on Windows, UDF’s are now supported via decorator syntax. A simple example:

    from xlwings import xlfunc

@xlfunc
def double_sum(x, y):
    """Returns twice the sum of the two arguments"""
    return 2 * (x + y)

    For array formulas with or without NumPy, see the docs: VBA: User Defined Functions (UDFs)

  • Command Line Client

    The new xlwings command line client makes it easy to work with the xlwings template and the developer add-in (the add-in is currently Windows-only). E.g. to create a new Excel spreadsheet from the template, run:

    xlwings template open

    For all commands, see the docs: Command Line Client

  • Other enhancements:

v0.5.0 (November 10, 2015)

API changes

None

Enhancements

This version adds support for Matplotlib! Matplotlib figures can be shown in Excel as pictures in just 2 lines of code:

_images/matplotlib.png
  1. Get a matplotlib figure object:

  • via PyPlot interface:

    import matplotlib.pyplot as plt
fig = plt.figure()
plt.plot([1, 2, 3, 4, 5])

  • via object oriented interface:

    from matplotlib.figure import Figure
fig = Figure(figsize=(8, 6))
ax = fig.add_subplot(111)
ax.plot([1, 2, 3, 4, 5])

  • via Pandas:

    import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.rand(10, 4), columns=['a', 'b', 'c', 'd'])
ax = df.plot(kind='bar')
fig = ax.get_figure()

  1. Show it in Excel as picture:

    plot = Plot(fig)
plot.show('Plot1')

See the full API: xlwings.Plot(). There’s also a new example available both on GitHub and as download on the homepage.

Other enhancements:

  • New xlwings.Shape() class
  • New xlwings.Picture() class
  • The PYTHONPATH in the VBA settings now accepts multiple directories, separated by ; (GH258)
  • An explicit exception is raised when Range is called with 0-based indices (GH106)

Bug Fixes

  • Sheet.add was not always acting on the correct workbook (GH287)
  • Iteration over a Range only worked the first time (GH272)
  • [Win]: Sometimes, an error was raised when Excel was not running (GH269)
  • [Win]: Non-default Python interpreters (as specified in the VBA settings under PYTHON_WIN) were not found if the path contained a space (GH257)

v0.4.1 (September 27, 2015)

API changes

None

Enhancements

This release makes it easier than ever to connect to Excel from Python! In addition to the existing ways, you can now connect to the active Workbook (on Windows across all instances) and if the Workbook is already open, it’s good enough to refer to it by name (instead of having to use the full path). Accordingly, this is how you make a connection to… (GH30 and GH226):

  • a new workbook: wb = Workbook()
  • the active workbook [New!]: wb = Workbook.active()
  • an unsaved workbook: wb = Workbook('Book1')
  • a saved (open) workbook by name (incl. xlsx etc.) [New!]: wb = Workbook('MyWorkbook.xlsx')
  • a saved (open or closed) workbook by path: wb = Workbook(r'C:\\path\\to\\file.xlsx')

Also, there are some new docs:

Bug Fixes

  • The Excel template was updated to the latest VBA code (GH234).
  • Connections to files that are saved on OneDrive/SharePoint are now working correctly (GH215).
  • Various issues with timezone-aware objects were fixed (GH195).
  • [Mac]: A certain range of integers were not written to Excel (GH227).

v0.4.0 (September 13, 2015)

API changes

None

Enhancements

The most important update with this release was made on Windows: The methodology used to make a connection to Workbooks has been completely replaced. This finally allows xlwings to reliably connect to multiple instances of Excel even if the Workbooks are opened from untrusted locations (network drives or files downloaded from the internet). This gets rid of the dreaded Filename is already open... error message that was sometimes shown in this context. It also allows the VBA hooks (RunPython) to work correctly if the very same file is opened in various instances of Excel.

Note that you will need to update the VBA module and that apart from pywin32 there is now a new dependency for the Windows version: comtypes. It should be installed automatically though when installing/upgrading xlwings with pip.

Other updates:

Bug Fixes

  • The VBA module was not accepting lower case drive letters (GH205).
  • Fixed an error when adding a new Sheet that was already existing (GH211).

v0.3.6 (July 14, 2015)

API changes

Application as attribute of a Workbook has been removed (wb is a Workbook object):

Correct Syntax (as before) Removed
Application(wkb=wb) wb.application

Enhancements

Excel 2016 for Mac Support (GH170)

Excel 2016 for Mac is finally supported (Python side). The VBA hooks (RunPython) are currently not yet supported. In more details:

  • This release allows Excel 2011 and Excel 2016 to be installed in parallel.

  • Workbook() will open the default Excel installation (usually Excel 2016).

  • The new keyword argument app_target allows to connect to a different Excel installation, e.g.:

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

    Note that app_target is only available on Mac. On Windows, if you want to change the version of Excel that xlwings talks to, go to Control Panel > Programs and Features and Repair the Office version that you want as default.

  • The RunPython calls in VBA are not yet available through Excel 2016 but Excel 2011 doesn’t get confused anymore if Excel 2016 is installed on the same system - make sure to update your VBA module!

Other enhancements

  • New method: xlwings.Application.calculate() (GH207)

Bug Fixes

  • [Win]: When using the OPTIMIZED_CONNECTION on Windows, Excel left an orphaned process running after closing (GH193).

Various improvements regarding unicode file path handling, including:

  • [Mac]: Excel 2011 for Mac now supports unicode characters in the filename when called via VBA’s RunPython (but not in the path - this is a limitation of Excel 2011 that will be resolved in Excel 2016) (GH154).
  • [Win]: Excel on Windows now handles unicode file paths correctly with untrusted documents. (GH154).

v0.3.5 (April 26, 2015)

API changes

Sheet.autofit() and Range.autofit(): The integer argument for the axis has been removed (GH186). Use string arguments rows or r for autofitting rows and columns or c for autofitting columns (as before).

Enhancements

New methods:

Example:

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

Bug Fixes

  • The unicode bug on Windows/Python3 has been fixed (GH161)

v0.3.4 (March 9, 2015)

Bug Fixes

  • The installation error on Windows has been fixed (GH160)

v0.3.3 (March 8, 2015)

API changes

None

Enhancements

  • New class Application with quit method and properties screen_updating und calculation (GH101, GH158, GH159). It can be conveniently accessed from within a Workbook (on Windows, Application is instance dependent). A few examples:

    >>> from xlwings import Workbook, Calculation
>>> wb = Workbook()
>>> wb.application.screen_updating = False
>>> wb.application.calculation = Calculation.xlCalculationManual
>>> wb.application.quit()

  • New headless mode: The Excel application can be hidden either during Workbook instantiation or through the application object:

    >>> wb = Workbook(app_visible=False)
>>> wb.application.visible
False
>>> wb.application.visible = True

  • Newly included Excel template which includes the xlwings VBA module and boilerplate code. This is currently accessible from an interactive interpreter session only:

    >>> from xlwings import Workbook
>>> Workbook.open_template()

Bug Fixes

  • [Win]: datetime.date objects were causing an error (GH44).
  • Depending on how it was instantiated, Workbook was sometimes missing the fullname attribute (GH76).
  • Range.hyperlink was failing if the hyperlink had been set as formula (GH132).
  • A bug introduced in v0.3.0 caused frozen versions (eg. with cx_Freeze) to fail (GH133).
  • [Mac]: Sometimes, xlwings was causing an error when quitting the Python interpreter (GH136).

v0.3.2 (January 17, 2015)

API changes

None

Enhancements

None

Bug Fixes

  • The xlwings.Workbook.save() method has been fixed to show the expected behavior (GH138): Previously, calling save() without a path argument would always create a new file in the current working directory. This is now only happening if the file hasn’t been previously saved.

v0.3.1 (January 16, 2015)

API changes

None

Enhancements

  • New method xlwings.Workbook.save() (GH110).

  • New method xlwings.Workbook.set_mock_caller() (GH129). This makes calling files from both Excel and Python much easier:

    import os
from xlwings import Workbook, Range

def my_macro():
    wb = Workbook.caller()
    Range('A1').value = 1

if __name__ == '__main__':
    # To run from Python, not needed when called from Excel.
    # Expects the Excel file next to this source file, adjust accordingly.
    path = os.path.abspath(os.path.join(os.path.dirname(__file__), 'myfile.xlsm'))
    Workbook.set_mock_caller(path)
    my_macro()

  • The simulation example on the homepage works now also on Mac.

Bug Fixes

  • [Win]: A long-standing bug that caused the Excel file to close and reopen under certain circumstances has been fixed (GH10): Depending on your security settings (Trust Center) and in connection with files downloaded from the internet or possibly in connection with some add-ins, Excel was either closing the file and reopening it or giving a “file already open” warning. This has now been fixed which means that the examples downloaded from the homepage should work right away after downloading and unzipping.

v0.3.0 (November 26, 2014)

API changes

  • To reference the calling Workbook when running code from VBA, you now have to use Workbook.caller(). This means that wb = Workbook() is now consistently creating a new Workbook, whether the code is called interactively or from VBA.

    New Old
    Workbook.caller() Workbook()

Enhancements

This version adds two exciting but still experimental features from ExcelPython (Windows only!):

  • Optimized connection: Set the OPTIMIZED_CONNECTION = True in the VBA settings. This will use a COM server that will keep the connection to Python alive between different calls and is therefore much more efficient. However, changes in the Python code are not being picked up until the pythonw.exe process is restarted by killing it manually in the Windows Task Manager. The suggested workflow is hence to set OPTIMIZED_CONNECTION = False for development and only set it to True for production - keep in mind though that this feature is still experimental!
  • User Defined Functions (UDFs): Using ExcelPython’s wrapper syntax in VBA, you can expose Python functions as UDFs, see VBA: User Defined Functions (UDFs) for details.

Note: ExcelPython’s developer add-in that autogenerates the VBA wrapper code by simply using Python decorators isn’t available through xlwings yet.

Further enhancements include:

Bug Fixes

  • [Mac]: Environment variables from .bash_profile are now available when called from VBA, e.g. by using: os.environ['USERNAME'] (GH95)

v0.2.3 (October 17, 2014)

API changes

None

Enhancements

  • New method Sheet.add() (GH71):

    >>> Sheet.add()  # Place at end with default name
>>> Sheet.add('NewSheet', before='Sheet1')  # Include name and position
>>> new_sheet = Sheet.add(after=3)
>>> new_sheet.index
4

  • New method Sheet.count():

    >>> Sheet.count()
3

  • autofit() works now also on Sheet objects, not only on Range objects (GH66):

    >>> Sheet(1).autofit()  # autofit columns and rows
>>> Sheet('Sheet1').autofit('c')  # autofit columns

  • New property number_format for Range objects (GH60):

    >>> Range('A1').number_format
'General'
>>> Range('A1:C3').number_format = '0.00%'
>>> Range('A1:C3').number_format
'0.00%'

    Works also with the Range properties table, vertical, horizontal:

    >>> Range('A1').value = [1,2,3,4,5]
>>> Range('A1').table.number_format = '0.00%'

  • New method get_address for Range objects (GH7):

    >>> Range((1,1)).get_address()
'$A$1'
>>> Range((1,1)).get_address(False, False)
'A1'
>>> Range('Sheet1', (1,1), (3,3)).get_address(True, False, include_sheetname=True)
'Sheet1!A$1:C$3'
>>> Range('Sheet1', (1,1), (3,3)).get_address(True, False, external=True)
'[Workbook1]Sheet1!A$1:C$3'

  • New method Sheet.all() returning a list with all Sheet objects:

    >>> Sheet.all()
[<Sheet 'Sheet1' of Workbook 'Book1'>, <Sheet 'Sheet2' of Workbook 'Book1'>]
>>> [i.name.lower() for i in Sheet.all()]
['sheet1', 'sheet2']
>>> [i.autofit() for i in Sheet.all()]

Bug Fixes

  • xlwings works now also with NumPy < 1.7.0. Before, doing something like Range('A1').value = 'Foo' was causing a NotImplementedError: Not implemented for this type error when NumPy < 1.7.0 was installed (GH73).
  • [Win]: The VBA module caused an error on the 64bit version of Excel (GH72).
  • [Mac]: The error pop-up wasn’t shown on Python 3 (GH85).
  • [Mac]: Autofitting bigger Ranges, e.g. Range('A:D').autofit() was causing a time out (GH74).
  • [Mac]: Sometimes, calling xlwings from Python was causing Excel to show old errors as pop-up alert (GH70).

v0.2.2 (September 23, 2014)

API changes

  • The Workbook qualification changed: It now has to be specified as keyword argument. Assume we have instantiated two Workbooks like so: wb1 = Workbook() and wb2 = Workbook(). Sheet, Range and Chart classes will default to wb2 as it was instantiated last. To target wb1, use the new wkb keyword argument:

    New Old
    Range('A1', wkb=wb1).value wb1.range('A1').value
    Chart('Chart1', wkb=wb1) wb1.chart('Chart1')

    Alternatively, simply set the current Workbook before using the Sheet, Range or Chart classes:

    wb1.set_current()
Range('A1').value

  • Through the introduction of the Sheet class (see Enhancements), a few methods moved from the Workbook to the Sheet class. Assume the current Workbook is: wb = Workbook():

    New Old
    Sheet('Sheet1').activate() wb.activate('Sheet1')
    Sheet('Sheet1').clear() wb.clear('Sheet1')
    Sheet('Sheet1').clear_contents() wb.clear_contents('Sheet1')
    Sheet.active().clear_contents() wb.clear_contents()

  • The syntax to add a new Chart has been slightly changed (it is a class method now):

    New Old
    Chart.add() Chart().add()

Enhancements

  • [Mac]: Python errors are now also shown in a Message Box. This makes the Mac version feature equivalent with the Windows version (GH57):

    _images/mac_error.png

  • New Sheet class: The new class handles everything directly related to a Sheet. See the section about api_sheet for details (GH62). A few examples:

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

  • The Range class has a new method autofit() that autofits the width/height of either columns, rows or both (GH33).

    Arguments:

    axis : string or integer, default None
    - To autofit rows, use one of the following: 'rows' or 'r'
    - To autofit columns, use one of the following: 'columns' or 'c'
    - To autofit rows and columns, provide no arguments

    Examples:

    # 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')

  • The Workbook class has the following additional methods: current() and set_current(). They determine the default Workbook for Sheet, Range or Chart. On Windows, in case there are various Excel instances, when creating new or opening existing Workbooks, they are being created in the same instance as the current Workbook.

    >>> wb1 = Workbook()
>>> wb2 = Workbook()
>>> Workbook.current()
<Workbook 'Book2'>
>>> wb1.set_current()
>>> Workbook.current()
<Workbook 'Book1'>

  • If a Sheet, Range or Chart object is instantiated without an existing Workbook object, a user-friendly error message is raised (GH58).

  • New docs about Debugging and Data Structures Tutorial.

Bug Fixes

  • The atleast_2d keyword had no effect on Ranges consisting of a single cell and was raising an error when used in combination with the asarray keyword. Both have been fixed (GH53):

    >>> Range('A1').value = 1
>>> Range('A1', atleast_2d=True).value
[[1.0]]
>>> Range('A1', atleast_2d=True, asarray=True).value
array([[1.]])

  • [Mac]: After creating two new unsaved Workbooks with Workbook(), any Sheet, Range or Chart object would always just access the latest one, even if the Workbook had been specified (GH63).

  • [Mac]: When xlwings was imported without ever instantiating a Workbook object, Excel would start upon quitting the Python interpreter (GH51).

  • [Mac]: When installing xlwings, it now requires psutil to be at least version 2.0.0 (GH48).

v0.2.1 (August 7, 2014)

API changes

None

Enhancements

  • All VBA user settings have been reorganized into a section at the top of the VBA xlwings module:

    PYTHON_WIN = ""
PYTHON_MAC = GetMacDir("Home") & "/anaconda/bin"
PYTHON_FROZEN = ThisWorkbook.Path & "\build\exe.win32-2.7"
PYTHONPATH = ThisWorkbook.Path
LOG_FILE = ThisWorkbook.Path & "\xlwings_log.txt"

  • Calling Python from within Excel VBA is now also supported on Mac, i.e. Python functions can be called like this: RunPython("import bar; bar.foo()"). Running frozen executables (RunFrozenPython) isn’t available yet on Mac though.

Note that there is a slight difference in the way that this functionality behaves on Windows and Mac:

  • Windows: After calling the Macro (e.g. by pressing a button), Excel waits until Python is done. In case there’s an error in the Python code, a pop-up message is being shown with the traceback.
  • Mac: After calling the Macro, the call returns instantly but Excel’s Status Bar turns into “Running…” during the duration of the Python call. Python errors are currently not shown as a pop-up, but need to be checked in the log file. I.e. if the Status Bar returns to its default (“Ready”) but nothing has happened, check out the log file for the Python traceback.

Bug Fixes

None

Special thanks go to Georgi Petrov for helping with this release.

v0.2.0 (July 29, 2014)

API changes

None

Enhancements

  • Cross-platform: xlwings is now additionally supporting Microsoft Excel for Mac. The only functionality that is not yet available is the possibility to call the Python code from within Excel via VBA macros.

  • The clear and clear_contents methods of the Workbook object now default to the active sheet (GH5):

    wb = Workbook()
wb.clear_contents()  # Clears contents of the entire active sheet

Bug Fixes

  • DataFrames with MultiHeaders were sometimes getting truncated (GH41).

v0.1.1 (June 27, 2014)

API Changes

  • If asarray=True, NumPy arrays are now always at least 1d arrays, even in the case of a single cell (GH14):

    >>> Range('A1', asarray=True).value
array([34.])

  • Similar to NumPy’s logic, 1d Ranges in Excel, i.e. rows or columns, are now being read in as flat lists or 1d arrays. If you want the same behavior as before, you can use the atleast_2d keyword (GH13).

    Note

    The table property is also delivering a 1d array/list, if the table Range is really a column or row.

    _images/1d_ranges.png 
    >>> 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.]])

  • The single file approach has been dropped. xlwings is now a traditional Python package.

Enhancements

  • xlwings is now officially suppported on Python 2.6-2.7 and 3.1-3.4

  • Support for Pandas Series has been added (GH24):

    >>> import numpy as np
>>> import pandas as pd
>>> from xlwings import Workbook, Range
>>> wb = Workbook()
>>> s = pd.Series([1.1, 3.3, 5., np.nan, 6., 8.])
>>> s
0    1.1
1    3.3
2    5.0
3    NaN
4    6.0
5    8.0
dtype: float64
>>> Range('A1').value = s
>>> Range('D1', index=False).value = s
    _images/pandas_series.png

  • Excel constants have been added under their original Excel name, but categorized under their enum (GH18), e.g.:

    # Extra long version
import xlwings as xl
xl.constants.ChartType.xlArea

# Long version
from xlwings import constants
constants.ChartType.xlArea

# Short version
from xlwings import ChartType
ChartType.xlArea

  • Slightly enhanced Chart support to control the ChartType (GH1):

    >>> from xlwings import Workbook, Range, Chart, ChartType
>>> wb = Workbook()
>>> Range('A1').value = [['one', 'two'],[10, 20]]
>>> my_chart = Chart().add(chart_type=ChartType.xlLine,
                           name='My Chart',
                           source_data=Range('A1').table)

    alternatively, the properties can also be set like this:

    >>> my_chart = Chart().add()  # Existing Charts: my_chart = Chart('My Chart')
>>> my_chart.name = 'My Chart'
>>> my_chart.chart_type = ChartType.xlLine
>>> my_chart.set_source_data(Range('A1').table)
    _images/chart_type.png

  • pytz is no longer a dependency as datetime object are now being read in from Excel as time-zone naive (Excel doesn’t know timezones). Before, datetime objects got the UTC timezone attached.

  • The Workbook class has the following additional methods: close()

  • The Range class has the following additional methods: is_cell(), is_column(), is_row(), is_table()

Bug Fixes

  • Writing None or np.nan to Excel works now (GH16 & GH15).
  • The import error on Python 3 has been fixed (GH26).
  • Python 3 now handles Pandas DataFrames with MultiIndex headers correctly (GH39).
  • Sometimes, a Pandas DataFrame was not handling nan correctly in Excel or numbers were being truncated (GH31) & (GH35).
  • Installation is now putting all files in the correct place (GH20).

v0.1.0 (March 19, 2014)

Initial release of xlwings.

Migrate to v0.11 (Add-in)

This migration guide shows you how you can start using the new xlwings add-in as opposed to the old xlwings VBA module (and the old add-in that consisted of just a single import button).

Upgrade the xlwings Python package

  1. Check where xlwings is currently installed

    >>> import xlwings
>>> xlwings.__path__

  2. If you installed xlwings with pip, for once, you should first uninstall xlwings: pip uninstall xlwings

  3. Check the directory that you got under 1): if there are any files left over, delete the xlwings folder and the remaining files manually

  4. Install the latest xlwings version: pip install xlwings

  5. Verify that you have >= 0.11 by doing

    >>> import xlwings
>>> xlwings.__version__

Install the add-in

  1. If you have the old xlwings addin installed, find the location and remove it or overwrite it with the new version (see next step). If you installed it via the xlwings command line client, you should be able to do: xlwings addin remove.
  2. Close Excel. Run xlwings addin install from a command prompt. Reopen Excel and check if the xlwings Ribbon appears. If not, copy xlwings.xlam (from your xlwings installation folder under addin\xlwings.xlam manually into the XLSTART folder. You can find the location of this folder under Options > Trust Center > Trust Center Settings… > Trusted Locations, under the description Excel default location: User StartUp. Restart Excel and you should see the add-in.

Upgrade existing workbooks

  1. Make a backup of your Excel file
  2. Open the file and go to the VBA Editor (Alt-F11)
  3. Remove the xlwings VBA module
  4. Add a reference to the xlwings addin, see Installation
  5. If you want to use workbook specific settings, add a sheet xlwings.conf, see Workbook Config: xlwings.conf Sheet

Note: To import UDFs, you need to have the reference to the xlwings add-in set!

Migrate to v0.9

The purpose of this document is to enable you a smooth experience when upgrading to xlwings v0.9.0 and above by laying out the concept and syntax changes in detail. If you want to get an overview of the new features and bug fixes, have a look at the release notes. Note that the syntax for User Defined Functions (UDFs) didn’t change.

Full qualification: Using collections

The new object model allows to specify the Excel application instance if needed:

  • old: xw.Range('Sheet1', 'A1', wkb=xw.Workbook('Book1'))
  • new: xw.apps[0].books['Book1'].sheets['Sheet1'].range('A1')

See Syntax Overview for the details of the new object model.

Connecting to Books

  • old: xw.Workbook()
  • new: xw.Book() or via xw.books if you need to control the app instance.

See Connect to a Book for the details.

Active Objects

# Active app (i.e. Excel instance)
>>> app = xw.apps.active

# Active book
>>> wb = xw.books.active  # in active app
>>> wb = app.books.active  # in specific app

# Active sheet
>>> sht = xw.sheets.active  # in active book
>>> sht = wb.sheets.active  # in specific book

# Range on active sheet
>>> xw.Range('A1')  # on active sheet of active book of active app

Round vs. Square Brackets

Round brackets follow Excel’s behavior (i.e. 1-based indexing), while square brackets use Python’s 0-based indexing/slicing.

As an example, the following all reference the same range:

xw.apps[0].books[0].sheets[0].range('A1')
xw.apps(1).books(1).sheets(1).range('A1')
xw.apps[0].books['Book1'].sheets['Sheet1'].range('A1')
xw.apps(1).books('Book1').sheets('Sheet1').range('A1')

Access the underlying Library/Engine

  • old: xw.Range('A1').xl_range and xl_sheet etc.
  • new: xw.Range('A1').api, same for all other objects

This returns a pywin32 COM object on Windows and an appscript object on Mac.

Cheat sheet

Note that sht stands for a sheet object, like e.g. (in 0.9.0 syntax): sht = xw.books['Book1'].sheets[0]

  v0.9.0 v0.7.2
Active Excel instance xw.apps.active unsupported
New Excel instance app = xw.App() unsupported
Get app from book app = wb.app app = xw.Application(wb)
Target installation (Mac) app = xw.App(spec=...) wb = xw.Workbook(app_target=...)
Hide Excel Instance app = xw.App(visible=False) wb = xw.Workbook(app_visible=False)
Selected Range app.selection wb.get_selection()
Calculation mode app.calculation = 'manual' app.calculation = xw.constants.Calculation.xlCalculationManual
All books in app app.books unsupported
     
Fully qualified book app.books['Book1'] unsupported
Active book in active app xw.books.active xw.Workbook.active()
New book in active app wb = xw.Book() wb = xw.Workbook()
New book in specific app wb = app.books.add() unsupported
All sheets in book wb.sheets xw.Sheet.all(wb)
Call a macro in an addin app.macro('MacroName') unsupported
     
First sheet of book wb wb.sheets[0] xw.Sheet(1, wkb=wb)
Active sheet wb.sheets.active xw.Sheet.active(wkb=wb) or wb.active_sheet
Add sheet wb.sheets.add() xw.Sheet.add(wkb=wb)
Sheet count wb.sheets.count or len(wb.sheets) xw.Sheet.count(wb)
     
Add chart to sheet chart = wb.sheets[0].charts.add() chart = xw.Chart.add(sheet=1, wkb=wb)
Existing chart wb.sheets['Sheet 1'].charts[0] xw.Chart('Sheet 1', 1)
Chart Type chart.chart_type = '3d_area' chart.chart_type = xw.constants.ChartType.xl3DArea
     
Add picture to sheet wb.sheets[0].pictures.add('path/to/pic') xw.Picture.add('path/to/pic', sheet=1, wkb=wb)
Existing picture wb.sheets['Sheet 1'].pictures[0] xw.Picture('Sheet 1', 1)
Matplotlib sht.pictures.add(fig, name='x', update=True) xw.Plot(fig).show('MyPlot', sheet=sht, wkb=wb)
     
Table expansion sht.range('A1').expand('table') xw.Range(sht, 'A1', wkb=wb).table
Vertical expansion sht.range('A1').expand('down') xw.Range(sht, 'A1', wkb=wb).vertical
Horizontal expansion sht.range('A1').expand('right') xw.Range(sht, 'A1', wkb=wb).horizontal
     
Set name of range sht.range('A1').name = 'name' xw.Range(sht, 'A1', wkb=wb).name = 'name'
Get name of range sht.range('A1').name.name xw.Range(sht, 'A1', wkb=wb).name
     
mock caller xw.Book('file.xlsm').set_mock_caller() xw.Workbook.set_mock_caller('file.xlsm')

Installation

The easiest way to install xlwings is via pip:

pip install xlwings

or conda:

conda install xlwings

Note that the official conda package might be few releases behind. You can, however, use the conda-forge channel (see: https://anaconda.org/conda-forge/xlwings) which should usually be up to date (but might still be a day or so behind the pip release):

conda install -c conda-forge xlwings

Note

When you are using Mac Excel 2016 and are installing xlwings with conda (or use the version that comes with Anaconda), you’ll need to run $ xlwings runpython install once to enable the RunPython calls from VBA. Alternatively, you can simply install xlwings with pip.

Dependencies

  • Windows: pywin32, comtypes

    On Windows, the dependencies are automatically being handled if xlwings is installed with conda or pip.

  • Mac: psutil, appscript

    On Mac, the dependencies are automatically being handled if xlwings is installed with conda or pip. However, with pip, the Xcode command line tools need to be available. Mac OS X 10.4 (Tiger) or later is required. The recommended Python distribution for Mac is Anaconda.

Optional Dependencies

  • NumPy
  • Pandas
  • Matplotlib
  • Pillow/PIL

These packages are not required but highly recommended as they play very nicely with xlwings.

Add-in

Please see Add-in on how to install the xlwings add-in.

Python version support

xlwings is tested on Python 2.7 and 3.3+

Quickstart

This guide assumes you have xlwings already installed. If that’s not the case, head over to Installation.

1. Scripting: Automate/interact with Excel from Python

Establish a connection to a workbook:

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

If you have the same file open in two instances of Excel, you need to fully qualify it and include the app instance. You will find your app instance key (the PID) via xw.apps.keys():

>>> xw.apps[10559].books['FileName.xlsx']

Instantiate a sheet object:

>>> sht = wb.sheets['Sheet1']

Reading/writing values to/from ranges is as easy as:

>>> sht.range('A1').value = 'Foo 1'
>>> sht.range('A1').value
'Foo 1'

There are many convenience features available, e.g. Range expanding:

>>> sht.range('A1').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
>>> sht.range('A1').expand().value
[['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]

Powerful converters handle most data types of interest, including Numpy arrays and Pandas DataFrames in both directions:

>>> import pandas as pd
>>> df = pd.DataFrame([[1,2], [3,4]], columns=['a', 'b'])
>>> sht.range('A1').value = df
>>> sht.range('A1').options(pd.DataFrame, expand='table').value
       a    b
0.0  1.0  2.0
1.0  3.0  4.0

Matplotlib figures can be shown as pictures in Excel:

>>> import matplotlib.pyplot as plt
>>> fig = plt.figure()
>>> plt.plot([1, 2, 3, 4, 5])
[<matplotlib.lines.Line2D at 0x1071706a0>]
>>> sht.pictures.add(fig, name='MyPlot', update=True)
<Picture 'MyPlot' in <Sheet [Workbook4]Sheet1>>

Shortcut for the active sheet: xw.Range

If you want to quickly talk to the active sheet in the active workbook, you don’t need instantiate a workbook and sheet object, but can simply do:

>>> import xlwings as xw
>>> xw.Range('A1').value = 'Foo'
>>> xw.Range('A1').value
'Foo'

Note: You should only use xw.Range when interacting with Excel. In scripts, you should always go via book and sheet objects as shown above.

2. Macros: Call Python from Excel

You can call Python functions from VBA using the RunPython function:

Sub HelloWorld()
    RunPython ("import hello; hello.world()")
End Sub

Per default, RunPython expects hello.py in the same directory as the Excel file. Refer to the calling Excel book by using xw.Book.caller:

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

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

To make this run, you’ll need to have the xlwings add-in installed. The easiest way to get everything set up is to use the xlwings command line client from either a command prompt on Windows or a terminal on Mac: xlwings quickstart myproject.

For details about the addin, see Add-in.

3. UDFs: User Defined Functions (Windows only)

Writing a UDF in Python is as easy as:

import xlwings as xw

@xw.func
def hello(name):
    return 'Hello {0}'.format(name)

Converters can be used with UDFs, too. Again a Pandas DataFrame example:

import xlwings as xw
import pandas as pd

@xw.func
@xw.arg('x', pd.DataFrame)
def correl2(x):
    # x arrives as DataFrame
    return x.corr()

Import this function into Excel by clicking the import button of the xlwings add-in: For further details, see VBA: User Defined Functions (UDFs).

Connect to a Book

When reading/writing data to the active sheet, you don’t need a book object:

>>> import xlwings as xw
>>> xw.Range('A1').value = 'something'

Python to Excel

The easiest way to connect to a book is offered by xw.Book: it looks for the book in all app instances and returns an error, should the same book be open in multiple instances. To connect to a book in the active app instance, use xw.books and to refer to a specific app, use:

>>> app = xw.App()  # or something like xw.apps[10559] for existing apps, get the available PIDs via xw.apps.keys()
>>> app.books['Book1']
  xw.Book xw.books
New book xw.Book() xw.books.add()
Unsaved book xw.Book('Book1') xw.books['Book1']
Book by (full)name xw.Book(r'C:/path/to/file.xlsx') xw.books.open(r'C:/path/to/file.xlsx')

Note

When specifying file paths on Windows, you should either use raw strings by putting an r in front of the string or use double back-slashes like so: C:\\path\\to\\file.xlsx.

Excel to Python (RunPython)

To reference the calling book when using RunPython in VBA, use xw.Book.caller(), see Call Python with “RunPython”. Check out the section about Debugging to see how you can call a script from both sides, Python and Excel, without the need to constantly change between xw.Book.caller() and one of the methods explained above.

User Defined Functions (UDFs)

Unlike RunPython, UDFs don’t need a call to xw.Book.caller(), see VBA: User Defined Functions (UDFs). However, it’s available (restricted to read-only though), which sometimes proofs to be useful.

Syntax Overview

The xlwings object model is very similar to the one used by VBA.

All code samples below depend on the following import:

>>> import xlwings as xw

Active Objects

# Active app (i.e. Excel instance)
>>> app = xw.apps.active

# Active book
>>> wb = xw.books.active  # in active app
>>> wb = app.books.active  # in specific app

# Active sheet
>>> sht = xw.sheets.active  # in active book
>>> sht = wb.sheets.active  # in specific book

# Range on active sheet
>>> xw.Range('A1')  # on active sheet of active book of active app

A Range can be instantiated with A1 notation, a tuple of Excel’s 1-based indices, a named range or two Range objects:

xw.Range('A1')
xw.Range('A1:C3')
xw.Range((1,1))
xw.Range((1,1), (3,3))
xw.Range('NamedRange')
xw.Range(xw.Range('A1'), xw.Range('B2'))

Full qualification

Round brackets follow Excel’s behavior (i.e. 1-based indexing), while square brackets use Python’s 0-based indexing/slicing. As an example, the following expressions all reference the same range:

xw.apps[763].books[0].sheets[0].range('A1')
xw.apps(10559).books(1).sheets(1).range('A1')
xw.apps[763].books['Book1'].sheets['Sheet1'].range('A1')
xw.apps(10559).books('Book1').sheets('Sheet1').range('A1')

Note that the apps keys are different for you as they are the process IDs (PID). You can get the list of your PIDs via xw.apps.keys().

Range indexing/slicing

Range objects support indexing and slicing, a few examples:

>>> rng = xw.Book().sheets[0].range('A1:D5')
>>> rng[0, 0]
 <Range [Workbook1]Sheet1!$A$1>
>>> rng[1]
 <Range [Workbook1]Sheet1!$B$1>
>>> rng[:, 3:]
<Range [Workbook1]Sheet1!$D$1:$D$5>
>>> rng[1:3, 1:3]
<Range [Workbook1]Sheet1!$B$2:$C$3>

Range Shortcuts

Sheet objects offer a shortcut for range objects by using index/slice notation on the sheet object. This evaluates to either sheet.range or sheet.cells depending on whether you pass a string or indices/slices:

>>> sht = xw.Book().sheets['Sheet1']
>>> sht['A1']
<Range [Book1]Sheet1!$A$1>
>>> sht['A1:B5']
<Range [Book1]Sheet1!$A$1:$B$5>
>>> sht[0, 1]
<Range [Book1]Sheet1!$B$1>
>>> sht[:10, :10]
<Range [Book1]Sheet1!$A$1:$J$10>

Object Hierarchy

The following shows an example of the object hierarchy, i.e. how to get from an app to a range object and all the way back:

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

Data Structures Tutorial

This tutorial gives you a quick introduction to the most common use cases and default behaviour of xlwings when reading and writing values. For an in-depth documentation of how to control the behavior using the options method, have a look at Converters and Options.

All code samples below depend on the following import:

>>> import xlwings as xw

Single Cells

Single cells are by default returned either as float, unicode, None or datetime objects, depending on whether the cell contains a number, a string, is empty or represents a date:

>>> import datetime as dt
>>> sht = xw.Book().sheets[0]
>>> sht.range('A1').value = 1
>>> sht.range('A1').value
1.0
>>> sht.range('A2').value = 'Hello'
>>> sht.range('A2').value
'Hello'
>>> sht.range('A3').value is None
True
>>> sht.range('A4').value = dt.datetime(2000, 1, 1)
>>> sht.range('A4').value
datetime.datetime(2000, 1, 1, 0, 0)

Lists

  • 1d lists: Ranges that represent rows or columns in Excel are returned as simple lists, which means that once they are in Python, you’ve lost the information about the orientation. If that is an issue, the next point shows you how to preserve this info:

    >>> sht = xw.Book().sheets[0]
>>> sht.range('A1').value = [[1],[2],[3],[4],[5]]  # Column orientation (nested list)
>>> sht.range('A1:A5').value
[1.0, 2.0, 3.0, 4.0, 5.0]
>>> sht.range('A1').value = [1, 2, 3, 4, 5]
>>> sht.range('A1:E1').value
[1.0, 2.0, 3.0, 4.0, 5.0]

    To force a single cell to arrive as list, use:

    >>> sht.range('A1').options(ndim=1).value
[1.0]

    Note

    To write a list in column orientation to Excel, use transpose: sht.range('A1').options(transpose=True).value = [1,2,3,4]

  • 2d lists: If the row or column orientation has to be preserved, set ndim in the Range options. This will return the Ranges as nested lists (“2d lists”):

    >>> sht.range('A1:A5').options(ndim=2).value
[[1.0], [2.0], [3.0], [4.0], [5.0]]
>>> sht.range('A1:E1').options(ndim=2).value
[[1.0, 2.0, 3.0, 4.0, 5.0]]

  • 2 dimensional Ranges are automatically returned as nested lists. When assigning (nested) lists to a Range in Excel, it’s enough to just specify the top left cell as target address. This sample also makes use of index notation to read the values back into Python:

    >>> sht.range('A10').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10, 20, 30]]
>>> sht.range((10,1),(11,3)).value
[['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]

Note

Try to minimize the number of interactions with Excel. It is always more efficient to do sht.range('A1').value = [[1,2],[3,4]] than sht.range('A1').value = [1, 2] and sht.range('A2').value = [3, 4].

Range expanding

You can get the dimensions of Excel Ranges dynamically through either the method expand or through the expand keyword in the options method. While expand gives back an expanded Range object, options are only evaluated when accessing the values of a Range. The difference is best explained with an example:

>>> sht = xw.Book().sheets[0]
>>> sht.range('A1').value = [[1,2], [3,4]]
>>> rng1 = sht.range('A1').expand('table')  # or just .expand()
>>> rng2 = sht.range('A1').options(expand='table')
>>> rng1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> rng2.value
[[1.0, 2.0], [3.0, 4.0]]
>>> sht.range('A3').value = [5, 6]
>>> rng1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> rng2.value
[[1.0, 2.0], [3.0, 4.0], [5.0, 6.0]]

'table' expands to 'down' and 'right', the other available options which can be used for column or row only expansion, respectively.

Note

Using expand() together with a named Range as top left cell gives you a flexible setup in Excel: You can move around the table and change it’s size without having to adjust your code, e.g. by using something like sht.range('NamedRange').expand().value.

NumPy arrays

NumPy arrays work similar to nested lists. However, empty cells are represented by nan instead of None. If you want to read in a Range as array, set convert=np.array in the options method:

>>> import numpy as np
>>> sht = xw.Book().sheets[0]
>>> sht.range('A1').value = np.eye(3)
>>> sht.range('A1').options(np.array, expand='table').value
array([[ 1.,  0.,  0.],
       [ 0.,  1.,  0.],
       [ 0.,  0.,  1.]])

Pandas DataFrames

>>> sht = xw.Book().sheets[0]
>>> df = pd.DataFrame([[1.1, 2.2], [3.3, None]], columns=['one', 'two'])
>>> df
   one  two
0  1.1  2.2
1  3.3  NaN
>>> sht.range('A1').value = df
>>> sht.range('A1:C3').options(pd.DataFrame).value
   one  two
0  1.1  2.2
1  3.3  NaN
# options: work for reading and writing
>>> sht.range('A5').options(index=False).value = df
>>> sht.range('A9').options(index=False, header=False).value = df

Pandas Series

>>> import pandas as pd
>>> import numpy as np
>>> sht = xw.Book().sheets[0]
>>> s = pd.Series([1.1, 3.3, 5., np.nan, 6., 8.], name='myseries')
>>> s
0    1.1
1    3.3
2    5.0
3    NaN
4    6.0
5    8.0
Name: myseries, dtype: float64
>>> sht.range('A1').value = s
>>> sht.range('A1:B7').options(pd.Series).value
0    1.1
1    3.3
2    5.0
3    NaN
4    6.0
5    8.0
Name: myseries, dtype: float64

Note

You only need to specify the top left cell when writing a list, a NumPy array or a Pandas DataFrame to Excel, e.g.: sht.range('A1').value = np.eye(10)

Add-in

_images/ribbon.png

The xlwings add-in is the preferred way to be able to use RunPython or UDFs. Note that you don’t need an add-in if you just want to manipulate Excel from Python via xlwings.

Note

The ribbon of the add-in is compatible with Excel >= 2007 on Windows and >= 2016 on Mac. You could, however, use the add-in with earlier versions but you would need to change the settings directly in the config file, see below. On Mac, all UDF related functionality is not available.

Note

The add-in is password protected with the password xlwings. For debugging or to add new extensions, you need to unprotect it.

Installation

To install the add-in, it’s easiest to use the command line client: xlwings addin install. Technically, this copies the add-in from Python’s installation directory to Excel’s XLSTART folder. If you encounter issues, then you can also download the add-in (xlwings.xlam) from the GitHub Release page (make sure you download the same version as the version of the Python package). Once downloaded, you can install the add-in by going to Developer > Excel Add-in > Browse. If you don’t see Developer as tab in your ribbon, make sure to activate the tab first under File > Options > Customize Ribbon (Mac: Cmd + , > Ribbon & Toolbar).

Then, to use RunPython or UDFs in a workbook, you need to set a reference to xlwings in the VBA editor, see screenshot (Windows: Tools > References..., Mac: it’s on the lower left corner of the VBA editor). Note that when you create a workbook via xlwings quickstart, the reference is already set.

_images/vba_reference.png

Global Settings

While the defaults will often work out-of-the box, you can change the global settings directly in the add-in:

  • Interpreter: This is the path to the Python interpreter. This works also with virtual or conda envs on Mac. If you use conda envs on Windows, then use Conda Base and Conda Env below instead. Examples: "C:\Python35\pythonw.exe" or "/usr/local/bin/python3.5". An empty field defaults to pythonw that expects the interpreter to be set in the PATH on Windows or .bash_profile on Mac.
  • PYTHONPATH: If the source file of your code is not found, add the path here.
  • Conda Base: If you are on Windows and use a conda env, then type here the base name of your Anaconda or Miniconda installation, e.g. C:\Users\Username\Miniconda3 or %USERPROFILE%\Anaconda. NOTE that you need at least conda 4.6!
  • Conda Env: If you are on Windows and use a conda env, then type here the name of your conda env, e.g. myenv. Note that this requires you to either leave the Interpreter blank or set it to python or pythonw.
  • UDF_MODULES: Names of Python modules (without .py extension) from which the UDFs are being imported. Separate multiple modules by “;”. Example: UDF_MODULES = "common_udfs;myproject" The default imports a file in the same directory as the Excel spreadsheet with the same name but ending in .py.
  • Debug UDFs: Check this box if you want to run the xlwings COM server manually for debugging, see Debugging.
  • Log File: Leave empty for default location (see below) or provide the full path, e.g. .
  • RunPython: Use UDF Server: Uses the same COM Server for RunPython as for UDFs. This will be faster, as the interpreter doesn’t shut down after each call.
  • Restart UDF Server: This shuts down the UDF Server/Python interpreter. It’ll be restarted upon the next function call.

Note

If you use Conda Base and Conda Env with UDFs, you currently can’t hide the command prompt that pops up. You can still control if the output is printed to the command prompt or not though by setting the Interpreter to python or pythonw, respectively.

Global Config: Ribbon/Config File

The settings in the xlwings Ribbon are stored in a config file that can also be manipulated externally. The location is

  • Windows: .xlwings\xlwings.conf in your user folder
  • Mac Excel 2016: ~/Library/Containers/com.microsoft.Excel/Data/xlwings.conf

# Mac Excel 2011: ~/.xlwings/xlwings.conf

The format is as follows (keys are uppercase):

"INTERPRETER","pythonw"
"PYTHONPATH",""

Note

Mac Excel 2011 users have to create and edit the config file manually under ~/.xlwings/xlwings.conf as the ribbon is not supported.

Workbook Directory Config: Config file

The global settings of the Ribbon/Config file can be overridden for one or more workbooks by creating a xlwings.conf file in the workbook’s directory.

Workbook Config: xlwings.conf Sheet

Workbook specific settings will override global (Ribbon) and workbook directory config files: Workbook specific settings are set by listing the config key/value pairs in a sheet with the name xlwings.conf. When you create a new project with xlwings quickstart, it’ll already have such a sheet but you need to rename it to xlwings.conf to make it active.

_images/workbook_config.png

Alternative: Standalone VBA module

Sometimes it might be useful to run xlwings code without having to install an add-in first. To do so, you need to use the standalone option when creating a new project: xlwings quickstart myproject --standalone.

This will add the content of the add-in as a single VBA module so you don’t need to set a reference to the add-in anymore. It will still read in the settings from your xlwings.conf if you don’t override them by using a sheet with the name xlwings.conf.

Log File default locations

These log files are used for the error pop-up windows:

  • Windows: %APPDATA%\xlwings.log
  • Mac with Excel 2011: /tmp/xlwings.log
  • Mac with Excel 2016: ~/Library/Containers/com.microsoft.Excel/Data/xlwings.log

VBA: RunPython

xlwings add-in

To get access to the RunPython function, you’ll need the xlwings addin (or VBA module), see Add-in.

For new projects, the easiest way to get started is by using the command line client with the quickstart command, see Command Line Client for details:

$ xlwings quickstart myproject

Call Python with “RunPython”

In the VBA Editor (Alt-F11), write the code below into a VBA module. xlwings quickstart automatically adds a new module with a sample call. If you rather want to start from scratch, you can add new module via Insert > Module.

Sub HelloWorld()
    RunPython ("import hello; hello.world()")
End Sub

This calls the following code in hello.py:

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

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

You can then attach HelloWorld to a button or run it directly in the VBA Editor by hitting F5.

Note

Place xw.Book.caller() within the function that is being called from Excel and not outside as global variable. Otherwise it prevents Excel from shutting down properly upon exiting and leaves you with a zombie process when you use OPTIMIZED_CONNECTION = True.

Function Arguments and Return Values

While it’s technically possible to include arguments in the function call within RunPython, it’s not very convenient. Also, RunPython does not allow you to return values. To overcome these issue, use UDFs, see VBA: User Defined Functions (UDFs) - however, this is currently limited to Windows only.

VBA: User Defined Functions (UDFs)

This tutorial gets you quickly started on how to write User Defined Functions.

Note

  • UDFs are currently only available on Windows.
  • For details of how to control the behaviour of the arguments and return values, have a look at Converters and Options.
  • For a comprehensive overview of the available decorators and their options, check out the corresponding API docs: UDF decorators.

One-time Excel preparations

1) Enable Trust access to the VBA project object model under File > Options > Trust Center > Trust Center Settings > Macro Settings

  1. Install the add-in via command prompt: xlwings addin install (see Add-in).

Workbook preparation

The easiest way to start a new project is to run xlwings quickstart myproject on a command prompt (see Command Line Client). This automatically adds the xlwings reference to the generated workbook.

A simple UDF

The default addin settings expect a Python source file in the way it is created by quickstart:

  • in the same directory as the Excel file
  • with the same name as the Excel file, but with a .py ending instead of .xlsm.

Alternatively, you can point to a specific module via UDF Modules in the xlwings ribbon.

Let’s assume you have a Workbook myproject.xlsm, then you would write the following code in myproject.py:

import xlwings as xw

@xw.func
def double_sum(x, y):
    """Returns twice the sum of the two arguments"""
    return 2 * (x + y)

  • Now click on Import Python UDFs in the xlwings tab to pick up the changes made to myproject.py.

  • Enter the formula =double_sum(1, 2) into a cell and you will see the correct result:

    _images/double_sum.png

  • The docstring (in triple-quotes) will be shown as function description in Excel.

Note

  • You only need to re-import your functions if you change the function arguments or the function name.
  • Code changes in the actual functions are picked up automatically (i.e. at the next calculation of the formula, e.g. triggered by Ctrl-Alt-F9), but changes in imported modules are not. This is the very behaviour of how Python imports work. If you want to make sure everything is in a fresh state, click Restart UDF Server.
  • The @xw.func decorator is only used by xlwings when the function is being imported into Excel. It tells xlwings for which functions it should create a VBA wrapper function, otherwise it has no effect on how the functions behave in Python.

Array formulas: Get efficient

Calling one big array formula in Excel is much more efficient than calling many single-cell formulas, so it’s generally a good idea to use them, especially if you hit performance problems.

You can pass an Excel Range as a function argument, as opposed to a single cell and it will show up in Python as list of lists.

For example, you can write the following function to add 1 to every cell in a Range:

@xw.func
def add_one(data):
    return [[cell + 1 for cell in row] for row in data]

To use this formula in Excel,

  • Click on Import Python UDFs again
  • Fill in the values in the range A1:B2
  • Select the range D1:E2
  • Type in the formula =add_one(A1:B2)
  • Press Ctrl+Shift+Enter to create an array formula. If you did everything correctly, you’ll see the formula surrounded by curly braces as in this screenshot:
_images/array_formula.png

Number of array dimensions: ndim

The above formula has the issue that it expects a “two dimensional” input, e.g. a nested list of the form [[1, 2], [3, 4]]. Therefore, if you would apply the formula to a single cell, you would get the following error: TypeError: 'float' object is not iterable.

To force Excel to always give you a two-dimensional array, no matter whether the argument is a single cell, a column/row or a two-dimensional Range, you can extend the above formula like this:

@xw.func
@xw.arg('data', ndim=2)
def add_one(data):
    return [[cell + 1 for cell in row] for row in data]

Array formulas with NumPy and Pandas

Often, you’ll want to use NumPy arrays or Pandas DataFrames in your UDF, as this unlocks the full power of Python’s ecosystem for scientific computing.

To define a formula for matrix multiplication using numpy arrays, you would define the following function:

import xlwings as xw
import numpy as np

@xw.func
@xw.arg('x', np.array, ndim=2)
@xw.arg('y', np.array, ndim=2)
def matrix_mult(x, y):
    return x @ y

Note

If you are not on Python >= 3.5 with NumPy >= 1.10, use x.dot(y) instead of x @ y.

A great example of how you can put Pandas at work is the creation of an array-based CORREL formula. Excel’s version of CORREL only works on 2 datasets and is cumbersome to use if you want to quickly get the correlation matrix of a few time-series, for example. Pandas makes the creation of an array-based CORREL2 formula basically a one-liner:

import xlwings as xw
import pandas as pd

@xw.func
@xw.arg('x', pd.DataFrame, index=False, header=False)
@xw.ret(index=False, header=False)
def CORREL2(x):
    """Like CORREL, but as array formula for more than 2 data sets"""
    return x.corr()

@xw.arg and @xw.ret decorators

These decorators are to UDFs what the options method is to Range objects: they allow you to apply converters and their options to function arguments (@xw.arg) and to the return value (@xw.ret). For example, to convert the argument x into a pandas DataFrame and suppress the index when returning it, you would do the following:

@xw.func
@xw.arg('x', pd.DataFrame)
@xw.ret(index=False)
def myfunction(x):
   # x is a DataFrame, do something with it
   return x

For further details see the Converters and Options documentation.

Dynamic Array Formulas

Note

If your version of Excel supports the new native dynamic arrays, then you don’t have to do anything special, and you shouldn’t use the expand decorator! To check if your version of Excel supports it, see if you have the =UNIQUE() formula available. Native dynamic arrays were introduced in Office 365 Insider Fast at the end of September 2018.

As seen above, to use Excel’s array formulas, you need to specify their dimensions up front by selecting the result array first, then entering the formula and finally hitting Ctrl-Shift-Enter. In practice, it often turns out to be a cumbersome process, especially when working with dynamic arrays such as time series data. Since v0.10, xlwings offers dynamic UDF expansion:

This is a simple example that demonstrates the syntax and effect of UDF expansion:

import numpy as np

@xw.func
@xw.ret(expand='table')
def dynamic_array(r, c):
    return np.random.randn(int(r), int(c))
_images/dynamic_array1.png
_images/dynamic_array2.png

Note

  • Expanding array formulas will overwrite cells without prompting
  • Pre v0.15.0 doesn’t allow to have volatile functions as arguments, e.g. you cannot use functions like =TODAY() as arguments. Starting with v0.15.0, you can use volatile functions as input, but the UDF will be called more than 1x.
  • Dynamic Arrays have been refactored with v0.15.0 to be proper legacy arrays: To edit a dynamic array with xlwings >= v0.15.0, you need to hit Ctrl-Shift-Enter while in the top left cell. Note that you don’t have to do that when you enter the formula for the first time.

Docstrings

The following sample shows how to include docstrings both for the function and for the arguments x and y that then show up in the function wizard in Excel:

import xlwings as xw

@xw.func
@xw.arg('x', doc='This is x.')
@xw.arg('y', doc='This is y.')
def double_sum(x, y):
    """Returns twice the sum of the two arguments"""
    return 2 * (x + y)

The “vba” keyword

It’s often helpful to get the address of the calling cell. Right now, one of the easiest ways to accomplish this is to use the vba keyword. vba, in fact, allows you to access any available VBA expression e.g. Application. Note, however, that currently you’re acting directly on the pywin32 COM object:

@xw.func
@xw.arg('xl_app', vba='Application')
def get_caller_address(xl_app):
    return xl_app.Caller.Address

Macros

On Windows, as alternative to calling macros via RunPython, you can also use the @xw.sub decorator:

import xlwings as xw

@xw.sub
def my_macro():
    """Writes the name of the Workbook into Range("A1") of Sheet 1"""
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = wb.name

After clicking on Import Python UDFs, you can then use this macro by executing it via Alt + F8 or by binding it e.g. to a button. To to the latter, make sure you have the Developer tab selected under File > Options > Customize Ribbon. Then, under the Developer tab, you can insert a button via Insert > Form Controls. After drawing the button, you will be prompted to assign a macro to it and you can select my_macro.

Call UDFs from VBA

Imported functions can also be used from VBA. For example, for a function returning a 2d array:

Sub MySub()

Dim arr() As Variant
Dim i As Long, j As Long

    arr = my_imported_function(...)

    For j = LBound(arr, 2) To UBound(arr, 2)
        For i = LBound(arr, 1) To UBound(arr, 1)
            Debug.Print "(" & i & "," & j & ")", arr(i, j)
        Next i
    Next j

End Sub

Asynchronous UDFs

New in version v0.14.0.

xlwings offers an easy way to write asynchronous functions in Excel. Asynchronous functions return immediately with #N/A waiting.... While the function is waiting for its return value, you can use Excel to do other stuff and whenever the return value is available, the cell value will be updated.

The only available mode is currently async_mode='threading', meaning that it’s useful for I/O-bound tasks, for example when you fetch data from an API over the web.

You make a function asynchronous simply by giving it the respective argument in the function decorator. In this example, the time consuming I/O-bound task is simulated by using time.sleep:

import xlwings as xw
import time

@xw.func(async_mode='threading')
def myfunction(a):
    time.sleep(5)  # long running tasks
    return a

You can use this function like any other xlwings function, simply by putting =myfunction("abcd") into a cell (after you have imported the function, off course).

Note that xlwings doesn’t use the native asynchronous functions that were introduced with Excel 2010, so xlwings asynchronous functions are supported with any version of Excel.

Debugging

Since xlwings runs in every Python environment, you can use your preferred way of debugging.

  • RunPython: When calling Python through RunPython, you can set a mock_caller to make it easy to switch back and forth between calling the function from Excel and Python.
  • UDFs: For debugging User Defined Functions, xlwings offers a convenient debugging server

To begin with, Excel will show Python errors in a Message Box:

_images/debugging_error.png

Note

On Mac, if the import of a module/package fails before xlwings is imported, the popup will not be shown and the StatusBar will not be reset. However, the error will still be logged in the log file. For the location of the logfile, see Log File default locations.

RunPython

Consider the following sample code of your Python source code my_module.py:

# my_module.py
import os
import xlwings as xw

def my_macro():
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = 1

if __name__ == '__main__':
    # Expects the Excel file next to this source file, adjust accordingly.
    xw.Book('myfile.xlsm').set_mock_caller()
    my_macro()

my_macro() can now easily be run from Python for debugging and from Excel via RunPython without having to change the source code:

Sub my_macro()
    RunPython ("import my_module; my_module.my_macro()")
End Sub

UDF debug server

Windows only: To debug UDFs, just check the Debug UDFs in the Add-in, at the top of the xlwings VBA module. Then add the following lines at the end of your Python source file and run it. Depending on which IDE you use, you might need to run the code in “debug” mode (e.g. in case you’re using PyCharm or PyDev):

if __name__ == '__main__':
    xw.serve()

When you recalculate the Sheet (Ctrl-Alt-F9), the code will stop at breakpoints or output any print calls that you may have.

The following screenshot shows the code stopped at a breakpoint in the community version of PyCharm:

_images/udf_debugging.png

Note

When running the debug server from a command prompt, there is currently no gracious way to terminate it, but closing the command prompt will kill it.

Matplotlib

Using pictures.add(), it is easy to paste a Matplotlib plot as picture in Excel.

Getting started

The easiest sample boils down to:

>>> import matplotlib.pyplot as plt
>>> import xlwings as xw

>>> fig = plt.figure()
>>> plt.plot([1, 2, 3])

>>> sht = xw.Book().sheets[0]
>>> sht.pictures.add(fig, name='MyPlot', update=True)
_images/mpl_basic.png

Note

If you set update=True, you can resize and position the plot on Excel: subsequent calls to pictures.add() with the same name ('MyPlot') will update the picture without changing its position or size.

Full integration with Excel

Calling the above code with RunPython and binding it e.g. to a button is straightforward and works cross-platform.

However, on Windows you can make things feel even more integrated by setting up a UDF along the following lines:

@xw.func
def myplot(n):
    sht = xw.Book.caller().sheets.active
    fig = plt.figure()
    plt.plot(range(int(n)))
    sht.pictures.add(fig, name='MyPlot', update=True)
    return 'Plotted with n={}'.format(n)

If you import this function and call it from cell B2, then the plot gets automatically updated when cell B1 changes:

_images/mpl_udf.png

Properties

Size, position and other properties can either be set as arguments within pictures.add(), or by manipulating the picture object that is returned, see xlwings.Picture().

For example:

>>> sht = xw.Book().sheets[0]
>>> sht.pictures.add(fig, name='MyPlot', update=True,
                     left=sht.range('B5').left, top=sht.range('B5').top)

or:

>>> plot = sht.pictures.add(fig, name='MyPlot', update=True)
>>> plot.height /= 2
>>> plot.width /= 2

Getting a Matplotlib figure

Here are a few examples of how you get a matplotlib figure object:

  • via PyPlot interface:

    import matplotlib.pyplot as plt
fig = plt.figure()
plt.plot([1, 2, 3, 4, 5])

    or:

    import matplotlib.pyplot as plt
plt.plot([1, 2, 3, 4, 5])
fig = plt.gcf()

  • via object oriented interface:

    from matplotlib.figure import Figure
fig = Figure(figsize=(8, 6))
ax = fig.add_subplot(111)
ax.plot([1, 2, 3, 4, 5])

  • via Pandas:

    import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.rand(10, 4), columns=['a', 'b', 'c', 'd'])
ax = df.plot(kind='bar')
fig = ax.get_figure()

Converters and Options

Introduced with v0.7.0, converters define how Excel ranges and their values are converted both during reading and writing operations. They also provide a consistent experience across xlwings.Range objects and User Defined Functions (UDFs).

Converters are explicitely set in the options method when manipulating Range objects or in the @xw.arg and @xw.ret decorators when using UDFs. If no converter is specified, the default converter is applied when reading. When writing, xlwings will automatically apply the correct converter (if available) according to the object’s type that is being written to Excel. If no converter is found for that type, it falls back to the default converter.

All code samples below depend on the following import:

>>> import xlwings as xw

Syntax:

  xw.Range UDFs
reading xw.Range.options(convert=None, **kwargs).value @arg('x', convert=None, **kwargs)
writing xw.Range.options(convert=None, **kwargs).value = myvalue @ret(convert=None, **kwargs)

Note

Keyword arguments (kwargs) may refer to the specific converter or the default converter. For example, to set the numbers option in the default converter and the index option in the DataFrame converter, you would write:

xw.Range('A1:C3').options(pd.DataFrame, index=False, numbers=int).value

Default Converter

If no options are set, the following conversions are performed:

  • single cells are read in as floats in case the Excel cell holds a number, as unicode in case it holds text, as datetime if it contains a date and as None in case it is empty.
  • columns/rows are read in as lists, e.g. [None, 1.0, 'a string']
  • 2d cell ranges are read in as list of lists, e.g. [[None, 1.0, 'a string'], [None, 2.0, 'another string']]

The following options can be set:

  • ndim

    Force the value to have either 1 or 2 dimensions regardless of the shape of the range:

    >>> import xlwings as xw
>>> sht = xw.Book().sheets[0]
>>> sht.range('A1').value = [[1, 2], [3, 4]]
>>> sht.range('A1').value
1.0
>>> sht.range('A1').options(ndim=1).value
[1.0]
>>> sht.range('A1').options(ndim=2).value
[[1.0]]
>>> sht.range('A1:A2').value
[1.0 3.0]
>>> sht.range('A1:A2').options(ndim=2).value
[[1.0], [3.0]]

  • numbers

    By default cells with numbers are read as float, but you can change it to int:

    >>> sht.range('A1').value = 1
>>> sht.range('A1').value
1.0
>>> sht.range('A1').options(numbers=int).value
1

    Alternatively, you can specify any other function or type which takes a single float argument.

    Using this on UDFs looks like this:

    @xw.func
@xw.arg('x', numbers=int)
def myfunction(x):
    # all numbers in x arrive as int
    return x

    Note: Excel always stores numbers internally as floats, which is the reason why the int converter rounds numbers first before turning them into integers. Otherwise it could happen that e.g. 5 might be returned as 4 in case it is represented as a floating point number that is slightly smaller than 5. Should you require Python’s original int in your converter, use raw int instead.

  • dates

    By default cells with dates are read as datetime.datetime, but you can change it to datetime.date:

    • Range:

      >>> import datetime as dt
>>> sht.range('A1').options(dates=dt.date).value

    • UDFs: @xw.arg('x', dates=dt.date)

    Alternatively, you can specify any other function or type which takes the same keyword arguments as datetime.datetime, for example:

    >>> my_date_handler = lambda year, month, day, **kwargs: "%04i-%02i-%02i" % (year, month, day)
>>> sht.range('A1').options(dates=my_date_handler).value
'2017-02-20'

  • empty

    Empty cells are converted per default into None, you can change this as follows:

    • Range: >>> sht.range('A1').options(empty='NA').value
    • UDFs: @xw.arg('x', empty='NA')

  • transpose

    This works for reading and writing and allows us to e.g. write a list in column orientation to Excel:

    • Range: sht.range('A1').options(transpose=True).value = [1, 2, 3]

    • UDFs:

      @xw.arg('x', transpose=True)
@xw.ret(transpose=True)
def myfunction(x):
    # x will be returned unchanged as transposed both when reading and writing
    return x

  • expand

    This works the same as the Range properties table, vertical and horizontal but is only evaluated when getting the values of a Range:

    >>> import xlwings as xw
>>> sht = xw.Book().sheets[0]
>>> sht.range('A1').value = [[1,2], [3,4]]
>>> rng1 = sht.range('A1').expand()
>>> rng2 = sht.range('A1').options(expand='table')
>>> rng1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> rng2.value
[[1.0, 2.0], [3.0, 4.0]]
>>> sht.range('A3').value = [5, 6]
>>> rng1.value
[[1.0, 2.0], [3.0, 4.0]]
>>> rng2.value
[[1.0, 2.0], [3.0, 4.0], [5.0, 6.0]]

    Note

    The expand method is only available on Range objects as UDFs only allow to manipulate the calling cells.

Built-in Converters

xlwings offers several built-in converters that perform type conversion to dictionaries, NumPy arrays, Pandas Series and DataFrames. These build on top of the default converter, so in most cases the options described above can be used in this context, too (unless they are meaningless, for example the ndim in the case of a dictionary).

It is also possible to write and register custom converter for additional types, see below.

The samples below can be used with both xlwings.Range objects and UDFs even though only one version may be shown.

Dictionary converter

The dictionary converter turns two Excel columns into a dictionary. If the data is in row orientation, use transpose:

_images/dict_converter.png 
>>> sht = xw.sheets.active
>>> sht.range('A1:B2').options(dict).value
{'a': 1.0, 'b': 2.0}
>>> sht.range('A4:B5').options(dict, transpose=True).value
{'a': 1.0, 'b': 2.0}

Note: instead of dict, you can also use OrderedDict from collections.

Numpy array converter

options: dtype=None, copy=True, order=None, ndim=None

The first 3 options behave the same as when using np.array() directly. Also, ndim works the same as shown above for lists (under default converter) and hence returns either numpy scalars, 1d arrays or 2d arrays.

Example:

>>> import numpy as np
>>> sht = xw.Book().sheets[0]
>>> sht.range('A1').options(transpose=True).value = np.array([1, 2, 3])
>>> sht.range('A1:A3').options(np.array, ndim=2).value
array([[ 1.],
       [ 2.],
       [ 3.]])

Pandas Series converter

options: dtype=None, copy=False, index=1, header=True

The first 2 options behave the same as when using pd.Series() directly. ndim doesn’t have an effect on Pandas series as they are always expected and returned in column orientation.

index: int or Boolean
When reading, it expects the number of index columns shown in Excel.
When writing, include or exclude the index by setting it to True or False.
header: Boolean
When reading, set it to False if Excel doesn’t show either index or series names.
When writing, include or exclude the index and series names by setting it to True or False.

For index and header, 1 and True may be used interchangeably.

Example:

_images/series_conv.png 
>>> sht = xw.Book().sheets[0]
>>> s = sht.range('A1').options(pd.Series, expand='table').value
>>> s
date
2001-01-01    1
2001-01-02    2
2001-01-03    3
2001-01-04    4
2001-01-05    5
2001-01-06    6
Name: series name, dtype: float64
>>> sht.range('D1', header=False).value = s

Pandas DataFrame converter

options: dtype=None, copy=False, index=1, header=1

The first 2 options behave the same as when using pd.DataFrame() directly. ndim doesn’t have an effect on Pandas DataFrames as they are automatically read in with ndim=2.

index: int or Boolean
When reading, it expects the number of index columns shown in Excel.
When writing, include or exclude the index by setting it to True or False.
header: int or Boolean
When reading, it expects the number of column headers shown in Excel.
When writing, include or exclude the index and series names by setting it to True or False.

For index and header, 1 and True may be used interchangeably.

Example:

_images/df_converter.png 
>>> sht = xw.Book().sheets[0]
>>> df = sht.range('A1:D5').options(pd.DataFrame, header=2).value
>>> df
    a     b
    c  d  e
ix
10  1  2  3
20  4  5  6
30  7  8  9

# Writing back using the defaults:
>>> sht.range('A1').value = df

# Writing back and changing some of the options, e.g. getting rid of the index:
>>> sht.range('B7').options(index=False).value = df

The same sample for UDF (starting in Range('A13') on screenshot) looks like this:

@xw.func
@xw.arg('x', pd.DataFrame, header=2)
@xw.ret(index=False)
def myfunction(x):
   # x is a DataFrame, do something with it
   return x

xw.Range and ‘raw’ converters

Technically speaking, these are “no-converters”.

  • If you need access to the xlwings.Range object directly, you can do:

    @xw.func
@xw.arg('x', xw.Range)
def myfunction(x):
   return x.formula

    This returns x as xlwings.Range object, i.e. without applying any converters or options.

  • The raw converter delivers the values unchanged from the underlying libraries (pywin32 on Windows and appscript on Mac), i.e. no sanitizing/cross-platform harmonizing of values are being made. This might be useful in a few cases for efficiency reasons. E.g:

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

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

Custom Converter

Here are the steps to implement your own converter:

  • Inherit from xlwings.conversion.Converter

  • Implement both a read_value and write_value method as static- or classmethod:

    • In read_value, value is what the base converter returns: hence, if no base has been specified it arrives in the format of the default converter.
    • In write_value, value is the original object being written to Excel. It must be returned in the format that the base converter expects. Again, if no base has been specified, this is the default converter.

    The options dictionary will contain all keyword arguments specified in the xw.Range.options method, e.g. when calling xw.Range('A1').options(myoption='some value') or as specified in the @arg and @ret decorator when using UDFs. Here is the basic structure:

    from xlwings.conversion import Converter

class MyConverter(Converter):

    @staticmethod
    def read_value(value, options):
        myoption = options.get('myoption', default_value)
        return_value = value  # Implement your conversion here
        return return_value

    @staticmethod
    def write_value(value, options):
        myoption = options.get('myoption', default_value)
        return_value = value  # Implement your conversion here
        return return_value

  • Optional: set a base converter (base expects a class name) to build on top of an existing converter, e.g. for the built-in ones: DictCoverter, NumpyArrayConverter, PandasDataFrameConverter, PandasSeriesConverter

  • Optional: register the converter: you can (a) register a type so that your converter becomes the default for this type during write operations and/or (b) you can register an alias that will allow you to explicitly call your converter by name instead of just by class name

The following examples should make it much easier to follow - it defines a DataFrame converter that extends the built-in DataFrame converter to add support for dropping nan’s:

from xlwings.conversion import Converter, PandasDataFrameConverter

class DataFrameDropna(Converter):

    base = PandasDataFrameConverter

    @staticmethod
    def read_value(builtin_df, options):
        dropna = options.get('dropna', False)  # set default to False
        if dropna:
            converted_df = builtin_df.dropna()
        else:
            converted_df = builtin_df
        # This will arrive in Python when using the DataFrameDropna converter for reading
        return converted_df

    @staticmethod
    def write_value(df, options):
        dropna = options.get('dropna', False)
        if dropna:
            converted_df = df.dropna()
        else:
            converted_df = df
        # This will be passed to the built-in PandasDataFrameConverter when writing
        return converted_df

Now let’s see how the different converters can be applied:

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

  • Default converter for DataFrames:

    # Write
sht.range('A1').value = df

# Read
sht.range('A1:C4').options(pd.DataFrame).value

  • DataFrameDropna converter:

    # Write
sht.range('A7').options(DataFrameDropna, dropna=True).value = df

# Read
sht.range('A1:C4').options(DataFrameDropna, dropna=True).value

  • Register an alias (optional):

    DataFrameDropna.register('df_dropna')

# Write
sht.range('A12').options('df_dropna', dropna=True).value = df

# Read
sht.range('A1:C4').options('df_dropna', dropna=True).value

  • Register DataFrameDropna as default converter for DataFrames (optional):

    DataFrameDropna.register(pd.DataFrame)

# Write
sht.range('A13').options(dropna=True).value = df

# Read
sht.range('A1:C4').options(pd.DataFrame, dropna=True).value

These samples all work the same with UDFs, e.g.:

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

Note

Python objects run through multiple stages of a transformation pipeline when they are being written to Excel. The same holds true in the other direction, when Excel/COM objects are being read into Python.

Pipelines are internally defined by Accessor classes. A Converter is just a special Accessor which converts to/from a particular type by adding an extra stage to the pipeline of the default Accessor. For example, the PandasDataFrameConverter defines how a list of list (as delivered by the default Accessor) should be turned into a Pandas DataFrame.

The Converter class provides basic scaffolding to make the task of writing a new Converter easier. If you need more control you can subclass Accessor directly, but this part requires more work and is currently undocumented.

Threading and Multiprocessing

New in version 0.13.0.

Threading

While xlwings is not technically thread safe, it’s still easy to use it in threads as long as you have at least v0.13.0 and stick to a simple rule: Do not pass xlwings objects to threads. This rule isn’t a requirement on macOS, but it’s still recommended if you want your programs to be cross-platform.

Consider the following example that will NOT work:

import threading
from queue import Queue
import xlwings as xw

num_threads = 4


def write_to_workbook():
    while True:
        rng = q.get()
        rng.value = rng.address
        print(rng.address)
        q.task_done()


q = Queue()

for i in range(num_threads):
    t = threading.Thread(target=write_to_workbook)
    t.daemon = True
    t.start()

for cell in ['A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9', 'A10']:
    # THIS DOESN'T WORK - passing xlwings objects to threads will fail!
    rng = xw.Book('Book1.xlsx').sheets[0].range(cell)
    q.put(rng)

q.join()

To make it work, you simply have to fully qualify the cell reference in the thread instead of passing a Book object:

import threading
from queue import Queue
import xlwings as xw

num_threads = 4


def write_to_workbook():
    while True:
        cell_ = q.get()
        xw.Book('Book1.xlsx').sheets[0].range(cell_).value = cell_
        print(cell_)
        q.task_done()


q = Queue()

for i in range(num_threads):
    t = threading.Thread(target=write_to_workbook)
    t.daemon = True
    t.start()

for cell in ['A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9', 'A10']:
    q.put(cell)

q.join()

Multiprocessing

Note

Multiprocessing is only support on Windows!

The same rules apply to multiprocessing as for threading, here’s a working example:

from multiprocessing import Pool
import xlwings as xw


def write_to_workbook(cell):
    xw.Book('Book1.xlsx').sheets[0].range(cell).value = cell
    print(cell)


if __name__ == '__main__':
    with Pool(4) as p:
        p.map(write_to_workbook,
              ['A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9', 'A10'])

Command Line Client

xlwings comes with a command line client that makes it easy to set up workbooks and install the add-in. On Windows, type the commands into a Command Prompt, on Mac, type them into a Terminal.

Quickstart

  • xlwings quickstart myproject

This command is by far the fastest way to get off the ground: It creates a new folder myproject with an Excel workbook that already has the reference to the xlwings addin and a Python file, ready to be used right away:

myproject
  |--myproject.xlsm
  |--myproject.py

If you want to use xlwings via VBA module instead of addin, use the --standalone or -s flag:

xlwings quickstart myproject --standalone

Add-in

The addin command makes it easy on Windows to install/remove the addin. On Mac, you need to install it manually, but xlwings addin install will show you how to do it.

Note

Excel needs to be closed before installing/updating the add-in via command line. If you’re still getting an error, start the Task Manager and make sure there are no EXCEL.EXE processes left.

  • xlwings addin install: Copies the xlwings add-in to the XLSTART folder
  • xlwings addin update: Replaces the current add-in with the latest one
  • xlwings addin remove: Removes the add-in from the XLSTART folder
  • xlwings addin status: Shows if the add-in is installed together with the installation path

After installing the add-in, it will be available as xlwings tab on the Excel Ribbon.

New in version 0.6.0.

RunPython

Only required if you are on Mac, are using Excel 2016 and have xlwings installed via conda or as part of Anaconda. To enable the RunPython calls in VBA, run this one time:

xlwings runpython install

Alternatively, install xlwings with pip.

New in version 0.7.0.

Missing Features

If you’re missing a feature in xlwings, do the following:

  1. Most importantly, open an issue on GitHub. If it’s something bigger or if you want support from other users, consider opening a feature request. Adding functionality should be user driven, so only if you tell us about what you’re missing, it’s eventually going to find its way into the library. By the way, we also appreciate pull requests!

  2. Workaround: in essence, xlwings is just a smart wrapper around pywin32 on Windows and appscript on Mac. You can access the underlying objects by calling the api property:

    >>> sht = xw.Book().sheets[0]
>>> sht.api
<COMObject <unknown>>  # Windows/pywin32
app(pid=2319).workbooks['Workbook1'].worksheets[1]  # Mac/appscript

    This works accordingly for the other objects like sht.range('A1').api etc.

    The underlying objects will offer you pretty much everything you can do with VBA, using the syntax of pywin32 (which pretty much feels like VBA) and appscript (which doesn’t feel like VBA). But apart from looking ugly, keep in mind that it makes your code platform specific (!), i.e. even if you go for option 2), you should still follow option 1) and open an issue so the feature finds it’s way into the library (cross-platform and with a Pythonic syntax).

Example: Workaround to use VBA’s Range.WrapText

# Windows
sht.range('A1').api.WrapText = True

# Mac
sht.range('A1').api.wrap_text.set(True)

xlwings with other Office Apps

xlwings can also be used to call Python functions from VBA within Office apps other than Excel (like Outlook, Access etc.).

Note

New in v0.12.0 and still in a somewhat early stage that involves a bit of manual work. Currently, this functionality is only available on Windows for UDFs. The RunPython functionality is currently not supported.

How To

  1. As usual, write your Python function and import it into Excel (see VBA: User Defined Functions (UDFs)).

  2. Press Alt-F11 to get into the VBA editor, then right-click on the xlwings_udfs VBA module and select Export File.... Save the xlwings_udfs.bas file somewhere.

  3. Switch into the other Office app, e.g. Microsoft Access and click again Alt-F11 to get into the VBA editor. Right-click on the VBA Project and Import File..., then select the file that you exported in the previous step. Once imported, replace the app name in the first line to the one that you are using, i.e. Microsoft Access or Microsoft Outlook etc. so that the first line then reads: #Const App = "Microsoft Access"

  4. Now import the standalone xlwings VBA module (xlwings.bas). You can find it in your xlwings installation folder. To know where that is, do:

    >>> import xlwings as xw
>>> xlwings.__path__

    And finally do the same as in the previous step and replace the App name in the first line with the name of the corresponding app that you are using. You are now able to call the Python function from VBA.

Config

The other Office apps will use the same global config file as you are editing via the Excel ribbon add-in. When it makes sense, you’ll be able to use the directory config file (e.g. you can put it next to your Access or Word file) or you can hardcode the path to the config file in the VBA standalone module, e.g. in the function GetDirectoryConfigFilePath (e.g. suggested when using Outlook that doesn’t really have the same concept of files like the other Office apps). NOTE: For Office apps without file concept, you need to make sure that the PYTHONPATH points to the directory with the Python source file. For details on the different config options, see Config.

Deployment

Zip files

New in version 0.15.2.

To make it easier to distribute, you can zip up your Python code into a zip file. If you use UDFs, this will disable the automatic code reload, so this is a feature meant for distribution, not development. In practice, this means that when your code is inside a zip file, you’ll have to click on re-import to get any changes.

If you name your zip file like your Excel file (but with .zip extension) and place it in the same folder as your Excel workbook, xlwings will automatically find it (similar to how it works with a single python file).

If you want to use a different directory, make sure to add it to the PYTHONPATH in your config (Ribbon or config file):

PYTHONPATH, "C:\path\to\myproject.zip"

RunFrozenPython

Changed in version 0.15.2.

You can use a freezer like PyInstaller, cx_Freeze, py2exe etc. to freeze your Python module into an executable so that the recipient doesn’t have to install a full Python distribution.

Note

  • This does not work with UDFs.
  • Currently only available on Windows, but support for Mac should be easy to add.
  • You need at least 0.15.2 to support arguments

Use it as follows:

Sub MySample()
    RunFrozenPython "C:\path\to\dist\myproject\myproject.exe arg1 arg2"
End Sub

Extensions

It’s easy to extend the xlwings add-in with own code like UDFs or RunPython macros, so that they can be deployed without end users having to import or write the functions themselves. Just add another VBA module to the xlwings addin with the respective code.

UDF extensions can be used from every workbook without having to set a reference.

In-Excel SQL

The xlwings addin comes with a built-in extension that adds in-Excel SQL syntax (sqlite dialect):

=sql(SQL Statement, table a, table b, ...)
_images/sql.png

As this extension uses UDFs, it’s only available on Windows right now.

xlwings with R and Julia

While xlwings is a pure Python package, there are cross-language packages that allow for a relative straightforward use from/with other languages. This means, however, that you’ll always need to have Python with xlwings installed in addition to R or Julia. We recommend the Anaconda distribution, see also Installation.

R

The R instructions are for Windows, but things work accordingly on Mac except that calling the R functions as User Defined Functions is not supported at the moment (but RunPython works, see Call Python with “RunPython”).

Setup:

  • Install R and Python
  • Add R_HOME environment variable to base directory of installation, .e.g C:\Program Files\R\R-x.x.x
  • Add R_USER environment variable to user folder, e.g. C:\Users\<user>
  • Add C:\Program Files\R\R-x.x.x\bin to PATH
  • Restart Windows because of the environment variables (!)

Simple functions with R

Original R function that we want to access from Excel (saved in r_file.R):

myfunction <- function(x, y){
    return(x * y)
}

Python wrapper code:

import xlwings as xw
import rpy2.robjects as robjects
# you might want to use some relative path or place the file in R's current working dir
robjects.r.source(r"C:\path\to\r_file.R")

@xw.func
def myfunction(x, y):
    myfunc = robjects.r['myfunction']
    return tuple(myfunc(x, y))

After importing this function (see: VBA: User Defined Functions (UDFs)), it will be available as UDF from Excel.

Array functions with R

Original R function that we want to access from Excel (saved in r_file.R):

array_function <- function(m1, m2){
  # Matrix multiplication
  return(m1 %*% m2)
}

Python wrapper code:

import xlwings as xw
import numpy as np
import rpy2.robjects as robjects
from rpy2.robjects import numpy2ri

robjects.r.source(r"C:\path\to\r_file.R")
numpy2ri.activate()

@xw.func
@xw.arg("x", np.array, ndim=2)
@xw.arg("y", np.array, ndim=2)
def array_function(x, y):
    array_func = robjects.r['array_function']
    return np.array(array_func(x, y))

After importing this function (see: VBA: User Defined Functions (UDFs)), it will be available as UDF from Excel.

Julia

Setup:

  • Install Julia and Python
  • Run Pkg.add("PyCall") from an interactive Julia interpreter

xlwings can then be called from Julia with the following syntax (the colons take care of automatic type conversion):

julia> using PyCall
julia> @pyimport xlwings as xw

julia> xw.Book()
PyObject <Book [Workbook1]>

julia> xw.Range("A1")[:value] = "Hello World"
julia> xw.Range("A1")[:value]
"Hello World"

Troubleshooting

Issue: dll not found

Solution:

  1. xlwings32-<version>.dll and xlwings64-<version>.dll are both in the same directory as your python.exe. If not, something went wrong with your installation. Reinstall it with pip or conda, see Installation.
  2. Check your Interpreter in the add-in or config sheet. If it is empty, then you need to be able to open a windows command prompt and type python to start an interactive Python session. If you get the error 'python' is not recognized as an internal or external command, operable program or batch file., then you have two options: Either add the path of where your python.exe lives to your Windows path (see https://www.computerhope.com/issues/ch000549.htm) or set the full path to your interpreter in the add-in or your config sheet, e.g. C:\Users\MyUser\anaconda\pythonw.exe

REST API

New in version 0.13.0.

Quickstart

xlwings offers an easy way to expose an Excel workbook via REST API both on Windows and macOS. This can be useful when you have a workbook running on a single computer and want to access it from another computer. Or you can build a Linux based web app that can interact with a legacy Excel application while you are in the progress of migrating the Excel functionality into your web app (if you need help with that, give us a shout).

You can run the REST API server from a command prompt or terminal as follows (this requires Flask>=1.0, so make sure to pip install Flask):

xlwings restapi run

Then perform a GET request e.g. via PowerShell on Windows or Terminal on Mac (while having an unsaved “Book1” open). Note that you need to run the server and the GET request from two separate terminals (or you can use something more convenient like Postman or Insomnia for testing the API):

$ curl "http://127.0.0.1:5000/book/book1/sheets/0/range/A1:B2"
{
  "address": "$A$1:$B$2",
  "color": null,
  "column": 1,
  "column_width": 10.0,
  "count": 4,
  "current_region": "$A$1:$B$2",
  "formula": [
    [
      "1",
      "2"
    ],
    [
      "3",
      "4"
    ]
  ],
  "formula_array": null,
  "height": 32.0,
  "last_cell": "$B$2",
  "left": 0.0,
  "name": null,
  "number_format": "General",
  "row": 1,
  "row_height": 16.0,
  "shape": [
    2,
    2
  ],
  "size": 4,
  "top": 0.0,
  "value": [
    [
      1.0,
      2.0
    ],
    [
      3.0,
      4.0
    ]
  ],
  "width": 130.0
}

In the command prompt where your server is running, press Ctrl-C to shut it down again.

The xlwings REST API is a thin wrapper around the Python API which makes it very easy if you have worked previously with xlwings. It also means that the REST API does require the Excel application to be up and running which makes it a great choice if the data in your Excel workbook is constantly changing as the REST API will always deliver the current state of the workbook without the need of saving it first.

Note

Currently, we only provide the GET methods to read the workbook. If you are also interested in the POST methods to edit the workbook, let us know via GitHub issues. Some other things will also need improvement, most notably exception handling.

Run the server

xlwings restapi run will run a Flask development server on http://127.0.0.1:5000. You can provide --host and --port as command line args and it also respects the Flask environment variables like FLASK_ENV=development.

If you want to have more control, you can run the server directly with Flask, see the Flask docs for more details:

set FLASK_APP=xlwings.rest.api
flask run

If you are on Mac, use export FLASK_APP=xlwings.rest.api instead of set FLASK_APP=xlwings.rest.api.

For production, you can use any WSGI HTTP Server like gunicorn (on Mac) or waitress (on Mac/Windows) to serve the API. For example, with gunicorn you would do: gunicorn xlwings.rest.api:api. Or with waitress (adjust the host accordingly if you want to make the api accessible from outside of localhost):

from xlwings.rest.api import api
from waitress import serve
serve(wsgiapp, host='127.0.0.1', port=5000)

Indexing

While the Python API offers Python’s 0-based indexing (e.g. xw.books[0]) as well as Excel’s 1-based indexing (e.g. xw.books(1)), the REST API only offers 0-based indexing, e.g. /books/0.

Range Options

The REST API accepts Range options as query parameters, see xlwings.Range.options() e.g.

/book/book1/sheets/0/range/A1?expand=table&transpose=true

Remember that options only affect the value property.

Endpoint overview

Endpoint Corresponds to Short Description
/book Book Finds your workbook across all open instances of Excel and will open it if it can’t find it
/books Books Books collection of the active Excel instance
/apps Apps This allows you to specify the Excel instance you want to work with

Endpoint details

/book

GET /book/<fullname_or_name>

Example response:

{
  "app": 1104,
  "fullname": "C:\\Users\\felix\\DEV\\xlwings\\scripts\\Book1.xlsx",
  "name": "Book1.xlsx",
  "names": [
    "Sheet1!myname1",
    "myname2"
  ],
  "selection": "Sheet2!$A$1",
  "sheets": [
    "Sheet1",
    "Sheet2"
  ]
}
GET /book/<fullname_or_name>/names

Example response:

{
  "names": [
    {
      "name": "Sheet1!myname1",
      "refers_to": "=Sheet1!$B$2:$C$3"
    },
    {
      "name": "myname2",
      "refers_to": "=Sheet1!$A$1"
    }
  ]
}
GET /book/<fullname_or_name>/names/<name>

Example response:

{
  "name": "myname2",
  "refers_to": "=Sheet1!$A$1"
}
GET /book/<fullname_or_name>/names/<name>/range

Example response:

{
  "address": "$A$1",
  "color": null,
  "column": 1,
  "column_width": 8.47,
  "count": 1,
  "current_region": "$A$1:$B$2",
  "formula": "=1+1.1",
  "formula_array": "=1+1,1",
  "height": 14.25,
  "last_cell": "$A$1",
  "left": 0.0,
  "name": "myname2",
  "number_format": "General",
  "row": 1,
  "row_height": 14.3,
  "shape": [
    1,
    1
  ],
  "size": 1,
  "top": 0.0,
  "value": 2.1,
  "width": 51.0
}
GET /book/<fullname_or_name>/sheets

Example response:

{
  "sheets": [
    {
      "charts": [
        "Chart 1"
      ],
      "name": "Sheet1",
      "names": [
        "Sheet1!myname1"
      ],
      "pictures": [
        "Picture 3"
      ],
      "shapes": [
        "Chart 1",
        "Picture 3"
      ],
      "used_range": "$A$1:$B$2"
    },
    {
      "charts": [],
      "name": "Sheet2",
      "names": [],
      "pictures": [],
      "shapes": [],
      "used_range": "$A$1"
    }
  ]
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>

Example response:

{
  "charts": [
    "Chart 1"
  ],
  "name": "Sheet1",
  "names": [
    "Sheet1!myname1"
  ],
  "pictures": [
    "Picture 3"
  ],
  "shapes": [
    "Chart 1",
    "Picture 3"
  ],
  "used_range": "$A$1:$B$2"
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/charts

Example response:

{
  "charts": [
    {
      "chart_type": "line",
      "height": 211.0,
      "left": 0.0,
      "name": "Chart 1",
      "top": 0.0,
      "width": 355.0
    }
  ]
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/charts/<chart_name_or_ix>

Example response:

{
  "chart_type": "line",
  "height": 211.0,
  "left": 0.0,
  "name": "Chart 1",
  "top": 0.0,
  "width": 355.0
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/names

Example response:

{
  "names": [
    {
      "name": "Sheet1!myname1",
      "refers_to": "=Sheet1!$B$2:$C$3"
    }
  ]
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/names/<sheet_scope_name>

Example response:

{
  "name": "Sheet1!myname1",
  "refers_to": "=Sheet1!$B$2:$C$3"
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/names/<sheet_scope_name>/range

Example response:

{
  "address": "$B$2:$C$3",
  "color": null,
  "column": 2,
  "column_width": 8.47,
  "count": 4,
  "current_region": "$A$1:$B$2",
  "formula": [
    [
      "",
      ""
    ],
    [
      "",
      ""
    ]
  ],
  "formula_array": "",
  "height": 28.5,
  "last_cell": "$C$3",
  "left": 51.0,
  "name": "Sheet1!myname1",
  "number_format": "General",
  "row": 2,
  "row_height": 14.3,
  "shape": [
    2,
    2
  ],
  "size": 4,
  "top": 14.25,
  "value": [
    [
      null,
      null
    ],
    [
      null,
      null
    ]
  ],
  "width": 102.0
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/pictures

Example response:

{
  "pictures": [
    {
      "height": 100.0,
      "left": 0.0,
      "name": "Picture 3",
      "top": 0.0,
      "width": 100.0
    }
  ]
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/pictures/<picture_name_or_ix>

Example response:

{
  "height": 100.0,
  "left": 0.0,
  "name": "Picture 3",
  "top": 0.0,
  "width": 100.0
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/range

Example response:

{
  "address": "$A$1:$B$2",
  "color": null,
  "column": 1,
  "column_width": 8.47,
  "count": 4,
  "current_region": "$A$1:$B$2",
  "formula": [
    [
      "=1+1.1",
      "a string"
    ],
    [
      "43395.0064583333",
      ""
    ]
  ],
  "formula_array": null,
  "height": 28.5,
  "last_cell": "$B$2",
  "left": 0.0,
  "name": null,
  "number_format": null,
  "row": 1,
  "row_height": 14.3,
  "shape": [
    2,
    2
  ],
  "size": 4,
  "top": 0.0,
  "value": [
    [
      2.1,
      "a string"
    ],
    [
      "Mon, 22 Oct 2018 00:09:18 GMT",
      null
    ]
  ],
  "width": 102.0
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/range/<address>

Example response:

{
  "address": "$A$1:$B$2",
  "color": null,
  "column": 1,
  "column_width": 8.47,
  "count": 4,
  "current_region": "$A$1:$B$2",
  "formula": [
    [
      "=1+1.1",
      "a string"
    ],
    [
      "43395.0064583333",
      ""
    ]
  ],
  "formula_array": null,
  "height": 28.5,
  "last_cell": "$B$2",
  "left": 0.0,
  "name": null,
  "number_format": null,
  "row": 1,
  "row_height": 14.3,
  "shape": [
    2,
    2
  ],
  "size": 4,
  "top": 0.0,
  "value": [
    [
      2.1,
      "a string"
    ],
    [
      "Mon, 22 Oct 2018 00:09:18 GMT",
      null
    ]
  ],
  "width": 102.0
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/shapes

Example response:

{
  "shapes": [
    {
      "height": 211.0,
      "left": 0.0,
      "name": "Chart 1",
      "top": 0.0,
      "type": "chart",
      "width": 355.0
    },
    {
      "height": 100.0,
      "left": 0.0,
      "name": "Picture 3",
      "top": 0.0,
      "type": "picture",
      "width": 100.0
    }
  ]
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/shapes/<shape_name_or_ix>

Example response:

{
  "height": 211.0,
  "left": 0.0,
  "name": "Chart 1",
  "top": 0.0,
  "type": "chart",
  "width": 355.0
}

/books

GET /books

Example response:

{
  "books": [
    {
      "app": 1104,
      "fullname": "Book1",
      "name": "Book1",
      "names": [],
      "selection": "Sheet2!$A$1",
      "sheets": [
        "Sheet1"
      ]
    },
    {
      "app": 1104,
      "fullname": "C:\\Users\\felix\\DEV\\xlwings\\scripts\\Book1.xlsx",
      "name": "Book1.xlsx",
      "names": [
        "Sheet1!myname1",
        "myname2"
      ],
      "selection": "Sheet2!$A$1",
      "sheets": [
        "Sheet1",
        "Sheet2"
      ]
    },
    {
      "app": 1104,
      "fullname": "Book4",
      "name": "Book4",
      "names": [],
      "selection": "Sheet2!$A$1",
      "sheets": [
        "Sheet1"
      ]
    }
  ]
}
GET /books/<book_name_or_ix>

Example response:

{
  "app": 1104,
  "fullname": "C:\\Users\\felix\\DEV\\xlwings\\scripts\\Book1.xlsx",
  "name": "Book1.xlsx",
  "names": [
    "Sheet1!myname1",
    "myname2"
  ],
  "selection": "Sheet2!$A$1",
  "sheets": [
    "Sheet1",
    "Sheet2"
  ]
}
GET /books/<book_name_or_ix>/names

Example response:

{
  "names": [
    {
      "name": "Sheet1!myname1",
      "refers_to": "=Sheet1!$B$2:$C$3"
    },
    {
      "name": "myname2",
      "refers_to": "=Sheet1!$A$1"
    }
  ]
}
GET /books/<book_name_or_ix>/names/<name>

Example response:

{
  "name": "myname2",
  "refers_to": "=Sheet1!$A$1"
}
GET /books/<book_name_or_ix>/names/<name>/range

Example response:

{
  "address": "$A$1",
  "color": null,
  "column": 1,
  "column_width": 8.47,
  "count": 1,
  "current_region": "$A$1:$B$2",
  "formula": "=1+1.1",
  "formula_array": "=1+1,1",
  "height": 14.25,
  "last_cell": "$A$1",
  "left": 0.0,
  "name": "myname2",
  "number_format": "General",
  "row": 1,
  "row_height": 14.3,
  "shape": [
    1,
    1
  ],
  "size": 1,
  "top": 0.0,
  "value": 2.1,
  "width": 51.0
}
GET /books/<book_name_or_ix>/sheets

Example response:

{
  "sheets": [
    {
      "charts": [
        "Chart 1"
      ],
      "name": "Sheet1",
      "names": [
        "Sheet1!myname1"
      ],
      "pictures": [
        "Picture 3"
      ],
      "shapes": [
        "Chart 1",
        "Picture 3"
      ],
      "used_range": "$A$1:$B$2"
    },
    {
      "charts": [],
      "name": "Sheet2",
      "names": [],
      "pictures": [],
      "shapes": [],
      "used_range": "$A$1"
    }
  ]
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>

Example response:

{
  "charts": [
    "Chart 1"
  ],
  "name": "Sheet1",
  "names": [
    "Sheet1!myname1"
  ],
  "pictures": [
    "Picture 3"
  ],
  "shapes": [
    "Chart 1",
    "Picture 3"
  ],
  "used_range": "$A$1:$B$2"
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/charts

Example response:

{
  "charts": [
    {
      "chart_type": "line",
      "height": 211.0,
      "left": 0.0,
      "name": "Chart 1",
      "top": 0.0,
      "width": 355.0
    }
  ]
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/charts/<chart_name_or_ix>

Example response:

{
  "chart_type": "line",
  "height": 211.0,
  "left": 0.0,
  "name": "Chart 1",
  "top": 0.0,
  "width": 355.0
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/names

Example response:

{
  "names": [
    {
      "name": "Sheet1!myname1",
      "refers_to": "=Sheet1!$B$2:$C$3"
    }
  ]
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/names/<sheet_scope_name>

Example response:

{
  "name": "Sheet1!myname1",
  "refers_to": "=Sheet1!$B$2:$C$3"
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/names/<sheet_scope_name>/range

Example response:

{
  "address": "$B$2:$C$3",
  "color": null,
  "column": 2,
  "column_width": 8.47,
  "count": 4,
  "current_region": "$A$1:$B$2",
  "formula": [
    [
      "",
      ""
    ],
    [
      "",
      ""
    ]
  ],
  "formula_array": "",
  "height": 28.5,
  "last_cell": "$C$3",
  "left": 51.0,
  "name": "Sheet1!myname1",
  "number_format": "General",
  "row": 2,
  "row_height": 14.3,
  "shape": [
    2,
    2
  ],
  "size": 4,
  "top": 14.25,
  "value": [
    [
      null,
      null
    ],
    [
      null,
      null
    ]
  ],
  "width": 102.0
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/pictures

Example response:

{
  "pictures": [
    {
      "height": 100.0,
      "left": 0.0,
      "name": "Picture 3",
      "top": 0.0,
      "width": 100.0
    }
  ]
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/pictures/<picture_name_or_ix>

Example response:

{
  "height": 100.0,
  "left": 0.0,
  "name": "Picture 3",
  "top": 0.0,
  "width": 100.0
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/range

Example response:

{
  "address": "$A$1:$B$2",
  "color": null,
  "column": 1,
  "column_width": 8.47,
  "count": 4,
  "current_region": "$A$1:$B$2",
  "formula": [
    [
      "=1+1.1",
      "a string"
    ],
    [
      "43395.0064583333",
      ""
    ]
  ],
  "formula_array": null,
  "height": 28.5,
  "last_cell": "$B$2",
  "left": 0.0,
  "name": null,
  "number_format": null,
  "row": 1,
  "row_height": 14.3,
  "shape": [
    2,
    2
  ],
  "size": 4,
  "top": 0.0,
  "value": [
    [
      2.1,
      "a string"
    ],
    [
      "Mon, 22 Oct 2018 00:09:18 GMT",
      null
    ]
  ],
  "width": 102.0
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/range/<address>

Example response:

{
  "address": "$A$1:$B$2",
  "color": null,
  "column": 1,
  "column_width": 8.47,
  "count": 4,
  "current_region": "$A$1:$B$2",
  "formula": [
    [
      "=1+1.1",
      "a string"
    ],
    [
      "43395.0064583333",
      ""
    ]
  ],
  "formula_array": null,
  "height": 28.5,
  "last_cell": "$B$2",
  "left": 0.0,
  "name": null,
  "number_format": null,
  "row": 1,
  "row_height": 14.3,
  "shape": [
    2,
    2
  ],
  "size": 4,
  "top": 0.0,
  "value": [
    [
      2.1,
      "a string"
    ],
    [
      "Mon, 22 Oct 2018 00:09:18 GMT",
      null
    ]
  ],
  "width": 102.0
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/shapes

Example response:

{
  "shapes": [
    {
      "height": 211.0,
      "left": 0.0,
      "name": "Chart 1",
      "top": 0.0,
      "type": "chart",
      "width": 355.0
    },
    {
      "height": 100.0,
      "left": 0.0,
      "name": "Picture 3",
      "top": 0.0,
      "type": "picture",
      "width": 100.0
    }
  ]
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/shapes/<shape_name_or_ix>

Example response:

{
  "height": 211.0,
  "left": 0.0,
  "name": "Chart 1",
  "top": 0.0,
  "type": "chart",
  "width": 355.0
}

/apps

GET /apps

Example response:

{
  "apps": [
    {
      "books": [
        "Book1",
        "C:\\Users\\felix\\DEV\\xlwings\\scripts\\Book1.xlsx",
        "Book4"
      ],
      "calculation": "automatic",
      "display_alerts": true,
      "pid": 1104,
      "screen_updating": true,
      "selection": "[Book1.xlsx]Sheet2!$A$1",
      "version": "16.0",
      "visible": true
    },
    {
      "books": [
        "Book2",
        "Book5"
      ],
      "calculation": "automatic",
      "display_alerts": true,
      "pid": 7920,
      "screen_updating": true,
      "selection": "[Book5]Sheet2!$A$1",
      "version": "16.0",
      "visible": true
    }
  ]
}
GET /apps/<pid>

Example response:

{
  "books": [
    "Book1",
    "C:\\Users\\felix\\DEV\\xlwings\\scripts\\Book1.xlsx",
    "Book4"
  ],
  "calculation": "automatic",
  "display_alerts": true,
  "pid": 1104,
  "screen_updating": true,
  "selection": "[Book1.xlsx]Sheet2!$A$1",
  "version": "16.0",
  "visible": true
}
GET /apps/<pid>/books

Example response:

{
  "books": [
    {
      "app": 1104,
      "fullname": "Book1",
      "name": "Book1",
      "names": [],
      "selection": "Sheet2!$A$1",
      "sheets": [
        "Sheet1"
      ]
    },
    {
      "app": 1104,
      "fullname": "C:\\Users\\felix\\DEV\\xlwings\\scripts\\Book1.xlsx",
      "name": "Book1.xlsx",
      "names": [
        "Sheet1!myname1",
        "myname2"
      ],
      "selection": "Sheet2!$A$1",
      "sheets": [
        "Sheet1",
        "Sheet2"
      ]
    },
    {
      "app": 1104,
      "fullname": "Book4",
      "name": "Book4",
      "names": [],
      "selection": "Sheet2!$A$1",
      "sheets": [
        "Sheet1"
      ]
    }
  ]
}
GET /apps/<pid>/books/<book_name_or_ix>

Example response:

{
  "app": 1104,
  "fullname": "C:\\Users\\felix\\DEV\\xlwings\\scripts\\Book1.xlsx",
  "name": "Book1.xlsx",
  "names": [
    "Sheet1!myname1",
    "myname2"
  ],
  "selection": "Sheet2!$A$1",
  "sheets": [
    "Sheet1",
    "Sheet2"
  ]
}
GET /apps/<pid>/books/<book_name_or_ix>/names

Example response:

{
  "names": [
    {
      "name": "Sheet1!myname1",
      "refers_to": "=Sheet1!$B$2:$C$3"
    },
    {
      "name": "myname2",
      "refers_to": "=Sheet1!$A$1"
    }
  ]
}
GET /apps/<pid>/books/<book_name_or_ix>/names/<name>

Example response:

{
  "name": "myname2",
  "refers_to": "=Sheet1!$A$1"
}
GET /apps/<pid>/books/<book_name_or_ix>/names/<name>/range

Example response:

{
  "address": "$A$1",
  "color": null,
  "column": 1,
  "column_width": 8.47,
  "count": 1,
  "current_region": "$A$1:$B$2",
  "formula": "=1+1.1",
  "formula_array": "=1+1,1",
  "height": 14.25,
  "last_cell": "$A$1",
  "left": 0.0,
  "name": "myname2",
  "number_format": "General",
  "row": 1,
  "row_height": 14.3,
  "shape": [
    1,
    1
  ],
  "size": 1,
  "top": 0.0,
  "value": 2.1,
  "width": 51.0
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets

Example response:

{
  "sheets": [
    {
      "charts": [
        "Chart 1"
      ],
      "name": "Sheet1",
      "names": [
        "Sheet1!myname1"
      ],
      "pictures": [
        "Picture 3"
      ],
      "shapes": [
        "Chart 1",
        "Picture 3"
      ],
      "used_range": "$A$1:$B$2"
    },
    {
      "charts": [],
      "name": "Sheet2",
      "names": [],
      "pictures": [],
      "shapes": [],
      "used_range": "$A$1"
    }
  ]
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>

Example response:

{
  "charts": [
    "Chart 1"
  ],
  "name": "Sheet1",
  "names": [
    "Sheet1!myname1"
  ],
  "pictures": [
    "Picture 3"
  ],
  "shapes": [
    "Chart 1",
    "Picture 3"
  ],
  "used_range": "$A$1:$B$2"
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/charts

Example response:

{
  "charts": [
    {
      "chart_type": "line",
      "height": 211.0,
      "left": 0.0,
      "name": "Chart 1",
      "top": 0.0,
      "width": 355.0
    }
  ]
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/charts/<chart_name_or_ix>

Example response:

{
  "chart_type": "line",
  "height": 211.0,
  "left": 0.0,
  "name": "Chart 1",
  "top": 0.0,
  "width": 355.0
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/names

Example response:

{
  "names": [
    {
      "name": "Sheet1!myname1",
      "refers_to": "=Sheet1!$B$2:$C$3"
    }
  ]
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/names/<sheet_scope_name>

Example response:

{
  "name": "Sheet1!myname1",
  "refers_to": "=Sheet1!$B$2:$C$3"
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/names/<sheet_scope_name>/range

Example response:

{
  "address": "$B$2:$C$3",
  "color": null,
  "column": 2,
  "column_width": 8.47,
  "count": 4,
  "current_region": "$A$1:$B$2",
  "formula": [
    [
      "",
      ""
    ],
    [
      "",
      ""
    ]
  ],
  "formula_array": "",
  "height": 28.5,
  "last_cell": "$C$3",
  "left": 51.0,
  "name": "Sheet1!myname1",
  "number_format": "General",
  "row": 2,
  "row_height": 14.3,
  "shape": [
    2,
    2
  ],
  "size": 4,
  "top": 14.25,
  "value": [
    [
      null,
      null
    ],
    [
      null,
      null
    ]
  ],
  "width": 102.0
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/pictures

Example response:

{
  "pictures": [
    {
      "height": 100.0,
      "left": 0.0,
      "name": "Picture 3",
      "top": 0.0,
      "width": 100.0
    }
  ]
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/pictures/<picture_name_or_ix>

Example response:

{
  "height": 100.0,
  "left": 0.0,
  "name": "Picture 3",
  "top": 0.0,
  "width": 100.0
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/range

Example response:

{
  "address": "$A$1:$B$2",
  "color": null,
  "column": 1,
  "column_width": 8.47,
  "count": 4,
  "current_region": "$A$1:$B$2",
  "formula": [
    [
      "=1+1.1",
      "a string"
    ],
    [
      "43395.0064583333",
      ""
    ]
  ],
  "formula_array": null,
  "height": 28.5,
  "last_cell": "$B$2",
  "left": 0.0,
  "name": null,
  "number_format": null,
  "row": 1,
  "row_height": 14.3,
  "shape": [
    2,
    2
  ],
  "size": 4,
  "top": 0.0,
  "value": [
    [
      2.1,
      "a string"
    ],
    [
      "Mon, 22 Oct 2018 00:09:18 GMT",
      null
    ]
  ],
  "width": 102.0
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/range/<address>

Example response:

{
  "address": "$A$1:$B$2",
  "color": null,
  "column": 1,
  "column_width": 8.47,
  "count": 4,
  "current_region": "$A$1:$B$2",
  "formula": [
    [
      "=1+1.1",
      "a string"
    ],
    [
      "43395.0064583333",
      ""
    ]
  ],
  "formula_array": null,
  "height": 28.5,
  "last_cell": "$B$2",
  "left": 0.0,
  "name": null,
  "number_format": null,
  "row": 1,
  "row_height": 14.3,
  "shape": [
    2,
    2
  ],
  "size": 4,
  "top": 0.0,
  "value": [
    [
      2.1,
      "a string"
    ],
    [
      "Mon, 22 Oct 2018 00:09:18 GMT",
      null
    ]
  ],
  "width": 102.0
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/shapes

Example response:

{
  "shapes": [
    {
      "height": 211.0,
      "left": 0.0,
      "name": "Chart 1",
      "top": 0.0,
      "type": "chart",
      "width": 355.0
    },
    {
      "height": 100.0,
      "left": 0.0,
      "name": "Picture 3",
      "top": 0.0,
      "type": "picture",
      "width": 100.0
    }
  ]
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/shapes/<shape_name_or_ix>

Example response:

{
  "height": 211.0,
  "left": 0.0,
  "name": "Chart 1",
  "top": 0.0,
  "type": "chart",
  "width": 355.0
}

Python API

Top-level functions

xlwings.view(obj, sheet=None)

Opens a new workbook and displays an object on its first sheet by default. If you provide a sheet object, it will clear the sheet before displaying the object on the existing sheet.

Parameters:
  • obj (any type with built-in converter) – the object to display, e.g. numbers, strings, lists, numpy arrays, pandas dataframes
  • sheet (Sheet, default None) – Sheet object. If none provided, the first sheet of a new workbook is used.

Examples

>>> import xlwings as xw
>>> import pandas as pd
>>> import numpy as np
>>> df = pd.DataFrame(np.random.rand(10, 4), columns=['a', 'b', 'c', 'd'])
>>> xw.view(df)

New in version 0.7.1.

Object model

Apps

class xlwings.main.Apps(impl)

A collection of all app objects:

>>> import xlwings as xw
>>> xw.apps
Apps([<Excel App 1668>, <Excel App 1644>])
active

Returns the active app.

New in version 0.9.0.

add()

Creates a new App. The new App becomes the active one. Returns an App object.

count

Returns the number of apps.

New in version 0.9.0.

keys()

Provides the PIDs of the Excel instances that act as keys in the Apps collection.

New in version 0.13.0.

App

class xlwings.App(visible=None, spec=None, add_book=True, impl=None)

An app corresponds to an Excel instance. New Excel instances can be fired up like so:

>>> import xlwings as xw
>>> app1 = xw.App()
>>> app2 = xw.App()

An app object is a member of the apps collection:

>>> xw.apps
Apps([<Excel App 1668>, <Excel App 1644>])
>>> xw.apps[1668]  # get the available PIDs via xw.apps.keys()
<Excel App 1668>
>>> xw.apps.active
<Excel App 1668>
Parameters:
  • visible (bool, default None) – Returns or sets a boolean value that determines whether the app is visible. The default leaves the state unchanged or sets visible=True if the object doesn’t exist yet.
  • spec (str, default None) –

    Mac-only, use the full path to the Excel application, e.g. /Applications/Microsoft Office 2011/Microsoft Excel or /Applications/Microsoft Excel

    On Windows, if you want to change the version of Excel that xlwings talks to, go to Control Panel > Programs and Features and Repair the Office version that you want as default.

Note

On Mac, while xlwings allows you to run multiple instances of Excel, it’s a feature that is not officially supported by Excel for Mac: Unlike on Windows, Excel will not ask you to open a read-only version of a file if it is already open in another instance. This means that you need to watch out yourself so that the same file is not being overwritten from different instances.

activate(steal_focus=False)

Activates the Excel app.

Parameters:steal_focus (bool, default False) – If True, make frontmost application and hand over focus from Python to Excel.

New in version 0.9.0.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

books

A collection of all Book objects that are currently open.

New in version 0.9.0.

calculate()

Calculates all open books.

New in version 0.3.6.

calculation

Returns or sets a calculation value that represents the calculation mode. Modes: 'manual', 'automatic', 'semiautomatic'

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> wb.app.calculation = 'manual'

Changed in version 0.9.0.

display_alerts

The default value is True. Set this property to False to suppress prompts and alert messages while code is running; when a message requires a response, Excel chooses the default response.

New in version 0.9.0.

hwnd

Returns the Window handle (Windows-only).

New in version 0.9.0.

kill()

Forces the Excel app to quit by killing its process.

New in version 0.9.0.

macro(name)

Runs a Sub or Function in Excel VBA that are not part of a specific workbook but e.g. are part of an add-in.

Parameters:name (Name of Sub or Function with or without module name, e.g. 'Module1.MyMacro' or 'MyMacro') –

Examples

This VBA function:

Function MySum(x, y)
    MySum = x + y
End Function

can be accessed like this:

>>> import xlwings as xw
>>> app = xw.App()
>>> my_sum = app.macro('MySum')
>>> my_sum(1, 2)
3

See also: Book.macro()

New in version 0.9.0.

pid

Returns the PID of the app.

New in version 0.9.0.

quit()

Quits the application without saving any workbooks.

New in version 0.3.3.

range(cell1, cell2=None)

Range object from the active sheet of the active book, see Range().

New in version 0.9.0.

screen_updating

Turn screen updating off to speed up your script. You won’t be able to see what the script is doing, but it will run faster. Remember to set the screen_updating property back to True when your script ends.

New in version 0.3.3.

selection

Returns the selected cells as Range.

New in version 0.9.0.

version

Returns the Excel version number object.

Examples

>>> import xlwings as xw
>>> xw.App().version
VersionNumber('15.24')
>>> xw.apps[10559].version.major
15

Changed in version 0.9.0.

visible

Gets or sets the visibility of Excel to True or False.

New in version 0.3.3.

Books

class xlwings.main.Books(impl)

A collection of all book objects:

>>> import xlwings as xw
>>> xw.books  # active app
Books([<Book [Book1]>, <Book [Book2]>])
>>> xw.apps[10559].books  # specific app, get the PIDs via xw.apps.keys()
Books([<Book [Book1]>, <Book [Book2]>])

New in version 0.9.0.

active

Returns the active Book.

add()

Creates a new Book. The new Book becomes the active Book. Returns a Book object.

open(fullname)

Opens a Book if it is not open yet and returns it. If it is already open, it doesn’t raise an exception but simply returns the Book object.

Parameters:fullname (str) – filename or fully qualified filename, e.g. r'C:\path\to\file.xlsx' or 'file.xlsm'. Without a full path, it looks for the file in the current working directory.
Returns:Book
Return type:Book that has been opened.

Book

class xlwings.Book(fullname=None, impl=None)

A book object is a member of the books collection:

>>> import xlwings as xw
>>> xw.books[0]
<Book [Book1]>

The easiest way to connect to a book is offered by xw.Book: it looks for the book in all app instances and returns an error, should the same book be open in multiple instances. To connect to a book in the active app instance, use xw.books and to refer to a specific app, use:

>>> app = xw.App()  # or something like xw.apps[10559] for existing apps, get the PIDs via xw.apps.keys()
>>> app.books['Book1']
  xw.Book xw.books
New book xw.Book() xw.books.add()
Unsaved book xw.Book('Book1') xw.books['Book1']
Book by (full)name xw.Book(r'C:/path/to/file.xlsx') xw.books.open(r'C:/path/to/file.xlsx')
Parameters:fullname (str, default None) – Full path or name (incl. xlsx, xlsm etc.) of existing workbook or name of an unsaved workbook. Without a full path, it looks for the file in the current working directory.
activate(steal_focus=False)

Activates the book.

Parameters:steal_focus (bool, default False) – If True, make frontmost window and hand over focus from Python to Excel.
api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

app

Returns an app object that represents the creator of the book.

New in version 0.9.0.

classmethod caller()

References the calling book when the Python function is called from Excel via RunPython. Pack it into the function being called from Excel, e.g.:

To be able to easily invoke such code from Python for debugging, use xw.Book.set_mock_caller().

New in version 0.3.0.

close()

Closes the book without saving it.

New in version 0.1.1.

fullname

Returns the name of the object, including its path on disk, as a string. Read-only String.

macro(name)

Runs a Sub or Function in Excel VBA.

Parameters:name (Name of Sub or Function with or without module name, e.g. 'Module1.MyMacro' or 'MyMacro') –

Examples

This VBA function:

Function MySum(x, y)
    MySum = x + y
End Function

can be accessed like this:

>>> import xlwings as xw
>>> wb = xw.books.active
>>> my_sum = wb.macro('MySum')
>>> my_sum(1, 2)
3

See also: App.macro()

New in version 0.7.1.

name

Returns the name of the book as str.

names

Returns a names collection that represents all the names in the specified book (including all sheet-specific names).

Changed in version 0.9.0.

static open_template()

Creates a new Excel file with the xlwings VBA module already included. This method must be called from an interactive Python shell:

>>> xw.Book.open_template()

See also: Command Line Client

New in version 0.3.3.

save(path=None)

Saves the Workbook. If a path is being provided, this works like SaveAs() in Excel. If no path is specified and if the file hasn’t been saved previously, it’s being saved in the current working directory with the current filename. Existing files are overwritten without prompting.

Parameters:path (str, default None) – Full path to the workbook

Example

>>> import xlwings as xw
>>> wb = xw.Book()
>>> wb.save()
>>> wb.save(r'C:\path\to\new_file_name.xlsx')

New in version 0.3.1.

selection

Returns the selected cells as Range.

New in version 0.9.0.

set_mock_caller()

Sets the Excel file which is used to mock xw.Book.caller() when the code is called from Python and not from Excel via RunPython.

Examples

# This code runs unchanged from Excel via RunPython and from Python directly
import os
import xlwings as xw

def my_macro():
    sht = xw.Book.caller().sheets[0]
    sht.range('A1').value = 'Hello xlwings!'

if __name__ == '__main__':
    xw.Book('file.xlsm').set_mock_caller()
    my_macro()

New in version 0.3.1.

sheets

Returns a sheets collection that represents all the sheets in the book.

New in version 0.9.0.

Sheets

class xlwings.main.Sheets(impl)

A collection of all sheet objects:

>>> import xlwings as xw
>>> xw.sheets  # active book
Sheets([<Sheet [Book1]Sheet1>, <Sheet [Book1]Sheet2>])
>>> xw.Book('Book1').sheets  # specific book
Sheets([<Sheet [Book1]Sheet1>, <Sheet [Book1]Sheet2>])

New in version 0.9.0.

active

Returns the active Sheet.

add(name=None, before=None, after=None)

Creates a new Sheet and makes it the active sheet.

Parameters:
  • name (str, default None) – Name of the new sheet. If None, will default to Excel’s default name.
  • before (Sheet, default None) – An object that specifies the sheet before which the new sheet is added.
  • after (Sheet, default None) – An object that specifies the sheet after which the new sheet is added.

Sheet

class xlwings.Sheet(sheet=None, impl=None)

A sheet object is a member of the sheets collection:

>>> import xlwings as xw
>>> wb = xw.Book()
>>> wb.sheets[0]
<Sheet [Book1]Sheet1>
>>> wb.sheets['Sheet1']
<Sheet [Book1]Sheet1>
>>> wb.sheets.add()
<Sheet [Book1]Sheet2>

Changed in version 0.9.0.

activate()

Activates the Sheet and returns it.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

autofit(axis=None)

Autofits the width of either columns, rows or both on a whole Sheet.

Parameters:axis (string, default None) –
  • To autofit rows, use one of the following: rows or r
  • To autofit columns, use one of the following: columns or c
  • To autofit rows and columns, provide no arguments

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> wb.sheets['Sheet1'].autofit('c')
>>> wb.sheets['Sheet1'].autofit('r')
>>> wb.sheets['Sheet1'].autofit()

New in version 0.2.3.

book

Returns the Book of the specified Sheet. Read-only.

cells

Returns a Range object that represents all the cells on the Sheet (not just the cells that are currently in use).

New in version 0.9.0.

charts

See Charts

New in version 0.9.0.

clear()

Clears the content and formatting of the whole sheet.

clear_contents()

Clears the content of the whole sheet but leaves the formatting.

delete()

Deletes the Sheet.

index

Returns the index of the Sheet (1-based as in Excel).

name

Gets or sets the name of the Sheet.

names

Returns a names collection that represents all the sheet-specific names (names defined with the “SheetName!” prefix).

New in version 0.9.0.

pictures

See Pictures

New in version 0.9.0.

range(cell1, cell2=None)

Returns a Range object from the active sheet of the active book, see Range().

New in version 0.9.0.

select()

Selects the Sheet. Select only works on the active book.

New in version 0.9.0.

shapes

See Shapes

New in version 0.9.0.

used_range

Used Range of Sheet.

Returns:
Return type:xw.Range

New in version 0.13.0.

Range

class xlwings.Range(cell1=None, cell2=None, **options)

Returns a Range object that represents a cell or a range of cells.

Parameters:
  • cell1 (str or tuple or Range) – Name of the range in the upper-left corner in A1 notation or as index-tuple or as name or as xw.Range object. It can also specify a range using the range operator (a colon), .e.g. ‘A1:B2’
  • cell2 (str or tuple or Range, default None) – Name of the range in the lower-right corner in A1 notation or as index-tuple or as name or as xw.Range object.

Examples

Active Sheet:

import xlwings as xw
xw.Range('A1')
xw.Range('A1:C3')
xw.Range((1,1))
xw.Range((1,1), (3,3))
xw.Range('NamedRange')
xw.Range(xw.Range('A1'), xw.Range('B2'))

Specific Sheet:

xw.books['MyBook.xlsx'].sheets[0].range('A1')

Adds a hyperlink to the specified Range (single Cell)

Parameters:
  • address (str) – The address of the hyperlink.
  • text_to_display (str, default None) – The text to be displayed for the hyperlink. Defaults to the hyperlink address.
  • screen_tip (str, default None) – The screen tip to be displayed when the mouse pointer is paused over the hyperlink. Default is set to ‘<address> - Click once to follow. Click and hold to select this cell.’

New in version 0.3.0.

address

Returns a string value that represents the range reference. Use get_address() to be able to provide paramaters.

New in version 0.9.0.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

autofit()

Autofits the width and height of all cells in the range.

  • To autofit only the width of the columns use xw.Range('A1:B2').columns.autofit()
  • To autofit only the height of the rows use xw.Range('A1:B2').rows.autofit()

Changed in version 0.9.0.

clear()

Clears the content and the formatting of a Range.

clear_contents()

Clears the content of a Range but leaves the formatting.

color

Gets and sets the background color of the specified Range.

To set the color, either use an RGB tuple (0, 0, 0) or a color constant. To remove the background, set the color to None, see Examples.

Returns:RGB
Return type:tuple

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> xw.Range('A1').color = (255,255,255)
>>> xw.Range('A2').color
(255, 255, 255)
>>> xw.Range('A2').color = None
>>> xw.Range('A2').color is None
True

New in version 0.3.0.

column

Returns the number of the first column in the in the specified range. Read-only.

Returns:
Return type:Integer

New in version 0.3.5.

column_width

Gets or sets the width, in characters, of a Range. One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.

If all columns in the Range have the same width, returns the width. If columns in the Range have different widths, returns None.

column_width must be in the range: 0 <= column_width <= 255

Note: If the Range is outside the used range of the Worksheet, and columns in the Range have different widths, returns the width of the first column.

Returns:
Return type:float

New in version 0.4.0.

columns

Returns a RangeColumns object that represents the columns in the specified range.

New in version 0.9.0.

count

Returns the number of cells.

current_region

This property returns a Range object representing a range bounded by (but not including) any combination of blank rows and blank columns or the edges of the worksheet. It corresponds to Ctrl-* on Windows and Shift-Ctrl-Space on Mac.

Returns:
Return type:Range object
end(direction)

Returns a Range object that represents the cell at the end of the region that contains the source range. Equivalent to pressing Ctrl+Up, Ctrl+down, Ctrl+left, or Ctrl+right.

Parameters:direction (One of 'up', 'down', 'right', 'left') –

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> xw.Range('A1:B2').value = 1
>>> xw.Range('A1').end('down')
<Range [Book1]Sheet1!$A$2>
>>> xw.Range('B2').end('right')
<Range [Book1]Sheet1!$B$2>

New in version 0.9.0.

expand(mode='table')

Expands the range according to the mode provided. Ignores empty top-left cells (unlike Range.end()).

Parameters:mode (str, default 'table') – One of 'table' (=down and right), 'down', 'right'.
Returns:
Return type:Range

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> xw.Range('A1').value = [[None, 1], [2, 3]]
>>> xw.Range('A1').expand().address
$A$1:$B$2
>>> xw.Range('A1').expand('right').address
$A$1:$B$1

New in version 0.9.0.

formula

Gets or sets the formula for the given Range.

formula_array

Gets or sets an array formula for the given Range.

New in version 0.7.1.

get_address(row_absolute=True, column_absolute=True, include_sheetname=False, external=False)

Returns the address of the range in the specified format. address can be used instead if none of the defaults need to be changed.

Parameters:
  • row_absolute (bool, default True) – Set to True to return the row part of the reference as an absolute reference.
  • column_absolute (bool, default True) – Set to True to return the column part of the reference as an absolute reference.
  • include_sheetname (bool, default False) – Set to True to include the Sheet name in the address. Ignored if external=True.
  • external (bool, default False) – Set to True to return an external reference with workbook and worksheet name.
Returns:
Return type:

str

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> xw.Range((1,1)).get_address()
'$A$1'
>>> xw.Range((1,1)).get_address(False, False)
'A1'
>>> xw.Range((1,1), (3,3)).get_address(True, False, True)
'Sheet1!A$1:C$3'
>>> xw.Range((1,1), (3,3)).get_address(True, False, external=True)
'[Book1]Sheet1!A$1:C$3'

New in version 0.2.3.

height

Returns the height, in points, of a Range. Read-only.

Returns:
Return type:float

New in version 0.4.0.

Returns the hyperlink address of the specified Range (single Cell only)

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> xw.Range('A1').value
'www.xlwings.org'
>>> xw.Range('A1').hyperlink
'http://www.xlwings.org'

New in version 0.3.0.

last_cell

Returns the bottom right cell of the specified range. Read-only.

Returns:
Return type:Range

Example

>>> import xlwings as xw
>>> wb = xw.Book()
>>> rng = xw.Range('A1:E4')
>>> rng.last_cell.row, rng.last_cell.column
(4, 5)

New in version 0.3.5.

left

Returns the distance, in points, from the left edge of column A to the left edge of the range. Read-only.

Returns:
Return type:float

New in version 0.6.0.

name

Sets or gets the name of a Range.

New in version 0.4.0.

number_format

Gets and sets the number_format of a Range.

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> xw.Range('A1').number_format
'General'
>>> xw.Range('A1:C3').number_format = '0.00%'
>>> xw.Range('A1:C3').number_format
'0.00%'

New in version 0.2.3.

offset(row_offset=0, column_offset=0)

Returns a Range object that represents a Range that’s offset from the specified range.

Returns:Range object
Return type:Range

New in version 0.3.0.

options(convert=None, **options)

Allows you to set a converter and their options. Converters define how Excel Ranges and their values are being converted both during reading and writing operations. If no explicit converter is specified, the base converter is being applied, see Converters and Options.

Parameters:

convert (object, default None) – A converter, e.g. dict, np.array, pd.DataFrame, pd.Series, defaults to default converter
Keyword Arguments:
 
  • ndim (int, default None) – number of dimensions
  • numbers (type, default None) – type of numbers, e.g. int
  • dates (type, default None) – e.g. datetime.date defaults to datetime.datetime
  • empty (object, default None) – transformation of empty cells
  • transpose (Boolean, default False) – transpose values
  • expand (str, default None) –

    One of 'table', 'down', 'right'

    => For converter-specific options, see Converters and Options.
Returns:
Return type:

Range object

New in version 0.7.0.

raw_value

Gets and sets the values directly as delivered from/accepted by the engine that is being used (pywin32 or appscript) without going through any of xlwings’ data cleaning/converting. This can be helpful if speed is an issue but naturally will be engine specific, i.e. might remove the cross-platform compatibility.

resize(row_size=None, column_size=None)

Resizes the specified Range

Parameters:
  • row_size (int > 0) – The number of rows in the new range (if None, the number of rows in the range is unchanged).
  • column_size (int > 0) – The number of columns in the new range (if None, the number of columns in the range is unchanged).
Returns:

Range object
Return type:

Range

New in version 0.3.0.

row

Returns the number of the first row in the specified range. Read-only.

Returns:
Return type:Integer

New in version 0.3.5.

row_height

Gets or sets the height, in points, of a Range. If all rows in the Range have the same height, returns the height. If rows in the Range have different heights, returns None.

row_height must be in the range: 0 <= row_height <= 409.5

Note: If the Range is outside the used range of the Worksheet, and rows in the Range have different heights, returns the height of the first row.

Returns:
Return type:float

New in version 0.4.0.

rows

Returns a RangeRows object that represents the rows in the specified range.

New in version 0.9.0.

select()

Selects the range. Select only works on the active book.

New in version 0.9.0.

shape

Tuple of Range dimensions.

New in version 0.3.0.

sheet

Returns the Sheet object to which the Range belongs.

New in version 0.9.0.

size

Number of elements in the Range.

New in version 0.3.0.

top

Returns the distance, in points, from the top edge of row 1 to the top edge of the range. Read-only.

Returns:
Return type:float

New in version 0.6.0.

value

Gets and sets the values for the given Range.

Returns:object
Return type:returned object depends on the converter being used, see xlwings.Range.options()
width

Returns the width, in points, of a Range. Read-only.

Returns:
Return type:float

New in version 0.4.0.

RangeRows

class xlwings.RangeRows(rng)

Represents the rows of a range. Do not construct this class directly, use Range.rows instead.

Example

import xlwings as xw

rng = xw.Range('A1:C4')

assert len(rng.rows) == 4  # or rng.rows.count

rng.rows[0].value = 'a'

assert rng.rows[2] == xw.Range('A3:C3')
assert rng.rows(2) == xw.Range('A2:C2')

for r in rng.rows:
    print(r.address)
autofit()

Autofits the height of the rows.

count

Returns the number of rows.

New in version 0.9.0.

RangeColumns

class xlwings.RangeColumns(rng)

Represents the columns of a range. Do not construct this class directly, use Range.columns instead.

Example

import xlwings as xw

rng = xw.Range('A1:C4')

assert len(rng.columns) == 3  # or rng.columns.count

rng.columns[0].value = 'a'

assert rng.columns[2] == xw.Range('C1:C4')
assert rng.columns(2) == xw.Range('B1:B4')

for c in rng.columns:
    print(c.address)
autofit()

Autofits the width of the columns.

count

Returns the number of columns.

New in version 0.9.0.

Shapes

class xlwings.main.Shapes(impl)

A collection of all shape objects on the specified sheet:

>>> import xlwings as xw
>>> xw.books['Book1'].sheets[0].shapes
Shapes([<Shape 'Oval 1' in <Sheet [Book1]Sheet1>>, <Shape 'Rectangle 1' in <Sheet [Book1]Sheet1>>])

New in version 0.9.0.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

count

Returns the number of objects in the collection.

Shape

class xlwings.Shape(*args, **options)

The shape object is a member of the shapes collection:

>>> import xlwings as xw
>>> sht = xw.books['Book1'].sheets[0]
>>> sht.shapes[0]  # or sht.shapes['ShapeName']
<Shape 'Rectangle 1' in <Sheet [Book1]Sheet1>>

Changed in version 0.9.0.

activate()

Activates the shape.

New in version 0.5.0.

delete()

Deletes the shape.

New in version 0.5.0.

height

Returns or sets the number of points that represent the height of the shape.

New in version 0.5.0.

left

Returns or sets the number of points that represent the horizontal position of the shape.

New in version 0.5.0.

name

Returns or sets the name of the shape.

New in version 0.5.0.

parent

Returns the parent of the shape.

New in version 0.9.0.

top

Returns or sets the number of points that represent the vertical position of the shape.

New in version 0.5.0.

type

Returns the type of the shape.

New in version 0.9.0.

width

Returns or sets the number of points that represent the width of the shape.

New in version 0.5.0.

Charts

class xlwings.main.Charts(impl)

A collection of all chart objects on the specified sheet:

>>> import xlwings as xw
>>> xw.books['Book1'].sheets[0].charts
Charts([<Chart 'Chart 1' in <Sheet [Book1]Sheet1>>, <Chart 'Chart 1' in <Sheet [Book1]Sheet1>>])

New in version 0.9.0.

add(left=0, top=0, width=355, height=211)

Creates a new chart on the specified sheet.

Parameters:
  • left (float, default 0) – left position in points
  • top (float, default 0) – top position in points
  • width (float, default 355) – width in points
  • height (float, default 211) – height in points
Returns:
Return type:

Chart

Examples

>>> import xlwings as xw
>>> sht = xw.Book().sheets[0]
>>> sht.range('A1').value = [['Foo1', 'Foo2'], [1, 2]]
>>> chart = sht.charts.add()
>>> chart.set_source_data(sht.range('A1').expand())
>>> chart.chart_type = 'line'
>>> chart.name
'Chart1'
api

Returns the native object (pywin32 or appscript obj) of the engine being used.

count

Returns the number of objects in the collection.

Chart

class xlwings.Chart(name_or_index=None, impl=None)

The chart object is a member of the charts collection:

>>> import xlwings as xw
>>> sht = xw.books['Book1'].sheets[0]
>>> sht.charts[0]  # or sht.charts['ChartName']
<Chart 'Chart 1' in <Sheet [Book1]Sheet1>>
api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

chart_type

Returns and sets the chart type of the chart.

New in version 0.1.1.

delete()

Deletes the chart.

height

Returns or sets the number of points that represent the height of the chart.

left

Returns or sets the number of points that represent the horizontal position of the chart.

name

Returns or sets the name of the chart.

parent

Returns the parent of the chart.

New in version 0.9.0.

set_source_data(source)

Sets the source data range for the chart.

Parameters:source (Range) – Range object, e.g. xw.books['Book1'].sheets[0].range('A1')
top

Returns or sets the number of points that represent the vertical position of the chart.

width

Returns or sets the number of points that represent the width of the chart.

Pictures

class xlwings.main.Pictures(impl)

A collection of all picture objects on the specified sheet:

>>> import xlwings as xw
>>> xw.books['Book1'].sheets[0].pictures
Pictures([<Picture 'Picture 1' in <Sheet [Book1]Sheet1>>, <Picture 'Picture 2' in <Sheet [Book1]Sheet1>>])

New in version 0.9.0.

add(image, link_to_file=False, save_with_document=True, left=0, top=0, width=None, height=None, name=None, update=False)

Adds a picture to the specified sheet.

Parameters:
  • image (str or matplotlib.figure.Figure) – Either a filepath or a Matplotlib figure object.
  • left (float, default 0) – Left position in points.
  • top (float, default 0) – Top position in points.
  • width (float, default None) – Width in points. If PIL/Pillow is installed, it defaults to the width of the picture. Otherwise it defaults to 100 points.
  • height (float, default None) – Height in points. If PIL/Pillow is installed, it defaults to the height of the picture. Otherwise it defaults to 100 points.
  • name (str, default None) – Excel picture name. Defaults to Excel standard name if not provided, e.g. ‘Picture 1’.
  • update (bool, default False) – Replace an existing picture with the same name. Requires name to be set.
Returns:
Return type:

Picture

Examples

  1. Picture
>>> import xlwings as xw
>>> sht = xw.Book().sheets[0]
>>> sht.pictures.add(r'C:\path\to\file.jpg')
<Picture 'Picture 1' in <Sheet [Book1]Sheet1>>
  1. Matplotlib
>>> import matplotlib.pyplot as plt
>>> fig = plt.figure()
>>> plt.plot([1, 2, 3, 4, 5])
>>> sht.pictures.add(fig, name='MyPlot', update=True)
<Picture 'MyPlot' in <Sheet [Book1]Sheet1>>
api

Returns the native object (pywin32 or appscript obj) of the engine being used.

count

Returns the number of objects in the collection.

Picture

class xlwings.Picture(impl=None)

The picture object is a member of the pictures collection:

>>> import xlwings as xw
>>> sht = xw.books['Book1'].sheets[0]
>>> sht.pictures[0]  # or sht.charts['PictureName']
<Picture 'Picture 1' in <Sheet [Book1]Sheet1>>

Changed in version 0.9.0.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

delete()

Deletes the picture.

New in version 0.5.0.

height

Returns or sets the number of points that represent the height of the picture.

New in version 0.5.0.

left

Returns or sets the number of points that represent the horizontal position of the picture.

New in version 0.5.0.

name

Returns or sets the name of the picture.

New in version 0.5.0.

parent

Returns the parent of the picture.

New in version 0.9.0.

top

Returns or sets the number of points that represent the vertical position of the picture.

New in version 0.5.0.

update(image)

Replaces an existing picture with a new one, taking over the attributes of the existing picture.

Parameters:image (str or matplotlib.figure.Figure) – Either a filepath or a Matplotlib figure object.

New in version 0.5.0.

width

Returns or sets the number of points that represent the width of the picture.

New in version 0.5.0.

Names

class xlwings.main.Names(impl)

A collection of all name objects in the workbook:

>>> import xlwings as xw
>>> sht = xw.books['Book1'].sheets[0]
>>> sht.names
[<Name 'MyName': =Sheet1!$A$3>]

New in version 0.9.0.

add(name, refers_to)

Defines a new name for a range of cells.

Parameters:
  • name (str) – Specifies the text to use as the name. Names cannot include spaces and cannot be formatted as cell references.
  • refers_to (str) – Describes what the name refers to, in English, using A1-style notation.
Returns:
Return type:

Name

New in version 0.9.0.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

count

Returns the number of objects in the collection.

Name

class xlwings.Name(impl)

The name object is a member of the names collection:

>>> import xlwings as xw
>>> sht = xw.books['Book1'].sheets[0]
>>> sht.names[0]  # or sht.names['MyName']
<Name 'MyName': =Sheet1!$A$3>

New in version 0.9.0.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

delete()

Deletes the name.

New in version 0.9.0.

name

Returns or sets the name of the name object.

New in version 0.9.0.

refers_to

Returns or sets the formula that the name is defined to refer to, in A1-style notation, beginning with an equal sign.

New in version 0.9.0.

refers_to_range

Returns the Range object referred to by a Name object.

New in version 0.9.0.

UDF decorators

xlwings.func(category="xlwings", volatile=False, call_in_wizard=True)

Functions decorated with xlwings.func will be imported as Function to Excel when running “Import Python UDFs”.

category : int or str, default “xlwings”

1-14 represent built-in categories, for user-defined categories use strings

New in version 0.10.3.

volatile : bool, default False

Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it’s not inside a user-defined function used to calculate a worksheet cell.

New in version 0.10.3.

call_in_wizard : bool, default True

Set to False to suppress the function call in the function wizard.

New in version 0.10.3.

xlwings.sub()

Functions decorated with xlwings.sub will be imported as Sub (i.e. macro) to Excel when running “Import Python UDFs”.

xlwings.arg(arg, convert=None, **options)

Apply converters and options to arguments, see also Range.options().

Examples:

Convert x into a 2-dimensional numpy array:

import xlwings as xw
import numpy as np

@xw.func
@xw.arg('x', np.array, ndim=2)
def add_one(x):
    return x + 1
xlwings.ret(convert=None, **options)

Apply converters and options to return values, see also Range.options().

Examples

  1. Suppress the index and header of a returned DataFrame:
import pandas as pd

@xw.func
@xw.ret(index=False, header=False)
def get_dataframe(n, m):
    return pd.DataFrame(np.arange(n * m).reshape((n, m)))
  1. Dynamic array:

expand='table' turns the UDF into a dynamic array. Currently you must not use volatile functions as arguments of a dynamic array, e.g. you cannot use =TODAY() as part of a dynamic array. Also note that a dynamic array needs an empty row and column at the bottom and to the right and will overwrite existing data without warning.

Unlike standard Excel arrays, dynamic arrays are being used from a single cell like a standard function and auto-expand depending on the dimensions of the returned array:

import xlwings as xw
import numpy as np

@xw.func
@xw.ret(expand='table')
def dynamic_array(n, m):
    return np.arange(n * m).reshape((n, m))

New in version 0.10.0.

License

xlwings is distributed under a BSD 3-clause license.

Copyright (C) Zoomer Analytics LLC. All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

  • Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
  • Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
  • Neither the name of the copyright holder nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS “AS IS” AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.