xlwings (Open Source) requires an installation of Excel and therefore only works on Windows and macOS. Note that macOS currently does not support UDFs.
xlwings PRO offers additional features:
File Reader (new in v0.28.0): Runs additionally on Linux and doesn’t require an installation of Excel.
xlwings Server (new in v0.26.0). Runs additionally on Linux and doesn’t require a local installation of Python. Works with Desktop Excel on Windows and macOS as well as with Excel on the web and Google Sheets.
xlwings requires at least Python 3.7.
Here are previous versions of xlwings that support older versions of Python:
WinPython (Windows only) Make sure not to take the dot version as this only contains Python.
If you are new to Python or have trouble installing xlwings, one of these distributions is highly recommended. Otherwise, you can also install it with pip:
pipinstallxlwings
or conda:
condainstallxlwings
Note that the official conda package might be a few releases behind. You can, however,
use the conda-forge channel (replace install with upgrade if xlwings is already installed):
To automate Excel from Python, you don’t need an add-in. Also, you can use a single file VBA module (standalone workbook) instead of the add-in. For more details, see Add-in & Settings.
Note
The add-in needs to be the same version as the Python package. Make sure to run xlwingsaddinstall again after upgrading the xlwings package.
Note
When you are on macOS and are using the VBA standalone module instead of the add-in, you need to run $xlwingsrunpythoninstall once.
For automating Excel, you’ll need the following dependencies:
Windows: pywin32
Mac: psutil, appscript
The dependencies are automatically installed via conda or pip.
If you would like to install xlwings without dependencies, you can run pipinstallxlwings--no-deps.
These packages are not required but highly recommended as they play very nicely with xlwings. They are all pre-installed with Anaconda. With pip, you can install xlwings with all optional dependencies as follows:
To uninstall xlwings completely, first uninstall the add-in, then uninstall the xlwings package using the same method (pip or conda) that you used for installing it:
xlwingsaddinremove
Then
pipuninstallxlwings
or:
condaremovexlwings
Finally, manually remove the .xlwings directory in your home folder if it exists.
This guide assumes you have xlwings already installed. If that’s not the case, head over to Installation.
1. Interacting with Excel from a Jupyter notebook#
If you’re just interested in getting a pandas DataFrame in and out of your Jupyter notebook, you can use the view and load functions, see Jupyter Notebooks: Interact with Excel.
2. Scripting: Automate/interact with Excel from Python#
Establish a connection to a workbook:
>>> importxlwingsasxw>>> wb=xw.Book()# this will open a new workbook>>> wb=xw.Book('FileName.xlsx')# connect to a file that is open or in the current working directory>>> wb=xw.Book(r'C:\path\to\file.xlsx')# on Windows: use raw strings to escape backslashes
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:
>>> sheet=wb.sheets['Sheet1']
Reading/writing values to/from ranges is as easy as:
You can call Python functions either by clicking the Run button (new in v0.16) in the add-in or from VBA using the RunPython function:
The Run button expects a function called main in a Python module with the same name as your workbook. The
great thing about that approach is that you don’t need your workbooks to be macro-enabled, you can save it as xlsx.
If you want to call any Python function no matter in what module it lives or what name it has, use RunPython:
Per default, RunPython expects hello.py in the same directory as the Excel file with the same name, but you can change both of these things: if your Python file is an a different folder, add that folder to the PYTHONPATH in the config. If the file has a different name, change the RunPython command accordingly.
Refer to the calling Excel book by using xw.Book.caller():
To make this run, you’ll need to have the xlwings add-in installed or have the workbooks setup in the standalone mode. 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: xlwingsquickstartmyproject.
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']
Note that you usually should use App as a context manager as this will make sure that the Excel instance is closed and cleaned up again properly:
withxw.App()asapp:book=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.
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.
Unlike RunPython, UDFs don’t need a call to xw.Book.caller(), see User Defined Functions (UDFs).
You’ll usually use the caller argument which returns the xlwings range object from where you call the function.
# 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>>>sheet=xw.sheets.active# in active book>>>sheet=wb.sheets.active# in specific book
A Range can be instantiated with A1 notation, a tuple of Excel’s 1-based indices, or a named range:
importxlwingsasxwsheet1=xw.Book("MyBook.xlsx").sheets[0]sheet1.range("A1")sheet1.range("A1:C3")sheet1.range((1,1))sheet1.range((1,1),(3,3))sheet1.range("NamedRange")# Or using index/slice notationsheet1["A1"]sheet1["A1:C3"]sheet1[0,0]sheet1[0:4,0:4]sheet1["NamedRange"]
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:
If you want to open a new Excel instance via App(), you usually should use App as a context manager as this will make sure that the Excel instance is closed and cleaned up again properly:
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:
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:
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:
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:
To write a list in column orientation to Excel, use transpose: sheet.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”):
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:
Try to minimize the number of interactions with Excel. It is always more efficient to do
sheet.range('A1').value=[[1,2],[3,4]] than sheet.range('A1').value=[1,2] and sheet.range('A2').value=[3,4].
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:
'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 its size without having to adjust your code, e.g. by using
something like sheet.range('NamedRange').expand().value.
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:
When you read and write from or to big ranges, you may have to chunk them or you will hit a timeout or a memory error. The ideal chunksize will depend on your system and size of the array, so you will have to try out a few different chunksizes to find one that works well:
# As DataFramedf=sheet['A1'].expand().options(pd.DataFrame,chunksize=10_000).value# As list of listdf=sheet['A1'].expand().options(chunksize=10_000).value
The xlwings add-in is the preferred way to be able to use the Runmain button, RunPython or UDFs.
Note that you don’t need an add-in if you just want to manipulate Excel by running a Python script.
Note
The ribbon of the add-in is compatible with Excel >= 2007 on Windows and >= 2016 on macOS.
On macOS, 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. Alternatively, you can also install the add-in via xlwingsaddininstall--unprotected.
The Runmain button is the easiest way to run your Python code: It runs a function called main in a Python
module that has the same name as your workbook. This allows you to save your workbook as xlsx without enabling macros.
The xlwingsquickstart command will create a workbook that will automatically work with the Run button.
To install the add-in, use the command line client:
xlwingsaddininstall
Technically, this copies the add-in from Python’s installation directory to Excel’s XLSTART folder. 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 xlwingsquickstart, the reference should already be set.
When you install the add-in for the first time, it will get auto-configured and therefore, a quickstart project should work out of the box. For fine-tuning, here are the available settings:
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 leave this empty and use CondaPath and CondaEnv below instead. Examples:
"C:\Python39\pythonw.exe" or "/usr/local/bin/python3.9". Note that in the settings,
this is stored as Interpreter_Win or Interpreter_Mac, respectively, see below!
PYTHONPATH: If the source file of your code is not found, add the path to its directory here.
CondaPath: If you are on Windows and use Anaconda or Miniconda, then type here the path to your
installation, e.g. C:\Users\Username\Miniconda3 or %USERPROFILE%\Anaconda. NOTE that you need at least conda 4.6!
You also need to set CondaEnv, see next point.
CondaEnv: If you are on Windows and use Anaconda or Miniconda, type here the name of your conda env, e.g. base
for the base installation or myenv for a conda env with the name myenv.
UDFModules: 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.
DebugUDFs: Check this box if you want to run the xlwings COM server manually for debugging, see Debugging.
RunPython:UseUDFServer: Uses the same COM Server for RunPython as for UDFs. This will be faster, as the
interpreter doesn’t shut down after each call.
RestartUDFServer: This restarts the UDF Server/Python interpreter.
ShowConsole: Check the box in the ribbon or set the config to TRUE if you want the command prompt to pop up. This currently only works on Windows.
ADD_WORKBOOK_TO_PYTHONPATH: Uncheck this box to not automatically add the directory of your workbook to the PYTHONPATH. This can be helpful if you experience issues with OneDrive/SharePoint: uncheck this box and provide the path where your source file is manually via the PYTHONPATH setting.
If you use Anaconda or Miniconda on Windows, you will need to set your CondaPath and CondaEnv settings, as you will
otherwise get errors when using NumPy etc. In return, leave Interpreter empty.
The ONEDRIVE_WIN/_MAC setting has to be edited directly in the file, there is currently no possibility to edit it via the ribbon. Usually, it is only required if you are either on macOS or if your environment variables on Windows are not correctly set or if you have a private and corporate location and don’t want to go with the default one. ONEDRIVE_WIN/_MAC has to point to the root folder of your local OneDrive folder.
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 xlwingsquickstart, it’ll already have such a sheet but you need to rename
it to xlwings.conf to make it active.
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: xlwingsquickstartmyproject--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 also include Dictionary.cls as this is required on macOS.
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.
To get access to Runmain (new in v0.16) button or the RunPython VBA function, you’ll need the xlwings addin (or VBA module), see Add-in & Settings.
For new projects, the easiest way to get started is by using the command line client with the quickstart command,
see Command Line Client (CLI) for details:
In the VBA Editor (Alt-F11), write the code below into a VBA module. xlwingsquickstart automatically
adds a new module with a sample call. If you rather want to start from scratch, you can add a new module via Insert>Module.
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 UseUDFServer=True.
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 issues, use UDFs, see User Defined Functions (UDFs) - however,
this is currently limited to Windows only.
Enable TrustaccesstotheVBAprojectobjectmodel under File>Options>TrustCenter>TrustCenterSettings>MacroSettings. You only need to do this once. Also, this is only required for importing the functions, i.e. end users won’t need to bother about this.
Install the add-in via command prompt: xlwingsaddininstall (see Add-in & Settings).
The easiest way to start a new project is to run xlwingsquickstartmyproject on a command prompt (see Command Line Client (CLI)).
This automatically adds the xlwings reference to the generated workbook.
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 UDFModules in the xlwings ribbon.
Let’s assume you have a Workbook myproject.xlsm, then you would write the following code in myproject.py:
importxlwingsasxw@xw.funcdefdouble_sum(x,y):"""Returns twice the sum of the two arguments"""return2*(x+y)
Now click on ImportPythonUDFs 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:
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 RestartUDFServer.
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.
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:
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:
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'objectisnotiterable.
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:
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:
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:
importxlwingsasxwimportpandasaspd@xw.func@xw.arg('x',pd.DataFrame,index=False,header=False)@xw.ret(index=False,header=False)defCORREL2(x):"""Like CORREL, but as array formula for more than 2 data sets"""returnx.corr()
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)defmyfunction(x):# x is a DataFrame, do something with itreturnx
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:
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.
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:
importxlwingsasxw@xw.func@xw.arg('x',doc='This is x.')@xw.arg('y',doc='This is y.')defdouble_sum(x,y):"""Returns twice the sum of the two arguments"""return2*(x+y)
You often need to know which cell called the UDF. For this, xlwings offers the reserved argument caller which returns the calling cell as xlwings range object:
@xw.funcdefget_caller_address(caller):# caller will not be exposed in Excel, so use it like so:# =get_caller_address()returncaller.address
Note that caller will not be exposed in Excel but will be provided by xlwings behind the scenes.
By using the vba keyword, you can get access to any Excel VBA object in the form of a pywin32 object. For example, if you wanted to pass the sheet object in the form of its CodeName, you can do it as follows:
@xw.func@xw.arg('sheet1',vba='Sheet1')defget_name(sheet1):# call this function in Excel with:# =get_name()returnsheet1.Name
Note that vba arguments are not exposed in the UDF but automatically provided by xlwings.
On Windows, as an alternative to calling macros via RunPython, you can also use the @xw.sub
decorator:
importxlwingsasxw@xw.subdefmy_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 ImportPythonUDFs, you can then use this macro by executing it via Alt+F8 or by
binding it e.g. to a button. To do the latter, make sure you have the Developer tab selected under File>Options>CustomizeRibbon. Then, under the Developer tab, you can insert a button via Insert>FormControls.
After drawing the button, you will be prompted to assign a macro to it and you can select my_macro.
xlwings offers an easy way to write asynchronous functions in Excel. Asynchronous functions return immediately with
#N/Awaiting.... 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:
importxlwingsasxwimporttime@xw.func(async_mode='threading')defmyfunction(a):time.sleep(5)# long running tasksreturna
You can use this function like any other xlwings function, simply by putting =myfunction("abcd") into a cell
(after you have imported the function, of 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.
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.
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.funcdefmyplot(n,caller):fig=plt.figure()plt.plot(range(int(n)))caller.sheet.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:
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().
When working with Google Sheets, you can use a maximum of 1 million pixels per picture. Total pixels is a function of figure size and dpi: (width in inches * dpi) * (height in inches * dpi). For example, fig=plt.figure(figsize=(6,4)) with 200 dpi (default dpi when using pictures.add()) will result in (6 * 200) * (4 * 200) = 960,000 px. To change the dpi, provide export_options: pictures.add(fig,export_options={"bbox_inches":"tight","dpi":300}). Existing figure size can be checked via fig.get_size_inches(). pandas also accepts figsize like so: ax=df.plot(figsize=(3,3)). Note that "bbox_inches":"tight" crops the image and therefore will reduce the number of pixels in a non-deterministic way. export_options will be passed to figure.figsave() when using Matplotlib and to figure.write_image() when using Plotly.
It works the same as with Matplotlib, however, rendering a Plotly chart takes slightly longer. Here is a sample:
importxlwingsasxwimportplotly.expressaspx# Plotly chartdf=px.data.iris()fig=px.scatter(df,x="sepal_width",y="sepal_length",color="species")# Add it to Excelwb=xw.Book()wb.sheets[0].pictures.add(fig,name='IrisScatterPlot',update=True)
When you work with Jupyter notebooks, you may use Excel as an interactive data viewer or scratchpad from where you can load DataFrames. The two convenience functions view and load make this really easy.
Note
The view and load functions should exclusively be used for interactive work. If you write scripts, use the xlwings API as introduced under Quickstart and Syntax Overview.
The view function accepts pretty much any object of interest, whether that’s a number, a string, a nested list or a NumPy array or a pandas DataFrame. By default, it writes the data into an Excel table in a new workbook. If you wanted to reuse the same workbook, provide a sheet object, e.g. view(df,sheet=xw.sheets.active), for further options see view.
Changed in version 0.22.0: Earlier versions were not formatting the output as Excel table
To load in a range in an Excel sheet as pandas DataFrame, use the load function. If you only select one cell, it will auto-expand to cover the whole range. If, however, you select a specific range that is bigger than one cell, it will load in only the selected cells. If the data in Excel does not have an index or header, set them to False like this: xw.load(index=False), see also load.
xlwings comes with a command line client. On Windows, type the commands into a Command Prompt or Anaconda Prompt, on Mac, type them into a Terminal. To get an overview of all commands, simply type xlwings and hit Enter:
addin Run "xlwings addin install" to install the Excel add-
in (will be copied to the user's XLSTART folder).
Instead of "install" you can also use "update",
"remove" or "status". Note that this command may take
a while. You can install your custom add-in by
providing the name or path via the --file/-f flag,
e.g. "xlwings add-in install -f custom.xlam or copy
all Excel files in a directory to the XLSTART folder
by providing the path via the --dir flag." To install
the add-in for every user globally, use the --glob/-g
flag and run this command from an Elevated Command
Prompt.
(New in 0.6.0, the --dir flag was added in 0.24.8 and the
--glob flag in 0.28.4)
quickstart Run "xlwings quickstart myproject" to create a folder
called "myproject" in the current directory with an
Excel file and a Python file, ready to be used. Use
the "--standalone" flag to embed all VBA code in the
Excel file and make it work without the xlwings add-
in. Use "--fastapi" for creating a project that uses a
remote Python interpreter. Use "--addin --ribbon" to
create a template for a custom ribbon addin. Leave
away the "--ribbon" if you don't want a ribbon tab.
runpython macOS only: run "xlwings runpython install" if you
want to enable the RunPython calls without installing
the add-in. This will create the following file:
~/Library/Application
Scripts/com.microsoft.Excel/xlwings.applescript
(new in 0.7.0)
restapi Use "xlwings restapi run" to run the xlwings REST API
via Flask dev server. Accepts "--host" and "--port" as
optional arguments.
license xlwings PRO: Use "xlwings license update -k KEY" where
"KEY" is your personal (trial) license key. This will
update ~/.xlwings/xlwings.conf with the LICENSE_KEY
entry. If you have a paid license, you can run
"xlwings license deploy" to create a deploy key. This
is not available for trial keys.
config Run "xlwings config create" to create the user config
file (~/.xlwings/xlwings.conf) which is where the
settings from the Ribbon add-in are stored. It will
configure the Python interpreter that you are running
this command with. To reset your configuration, run
this with the "--force" flag which will overwrite your
current configuration.
(New in 0.19.5)
code Run "xlwings code embed" to embed all Python modules
of the workbook's dir in your active Excel file. Use
the "--file" flag to only import a single file by
providing its path. Requires xlwings PRO.
(Changed in 0.23.4)
permission "xlwings permission cwd" prints a JSON string that can
be used to permission the execution of all modules in
the current working directory via GET request.
"xlwings permission book" does the same for code that
is embedded in the active workbook.
(New in 0.23.4)
release Run "xlwings release" to configure your active
workbook to work with a one-click installer for easy
deployment. Requires xlwings PRO.
(New in 0.23.4)
copy Run "xlwings copy os" to copy the xlwings Office
Scripts module. Run "xlwings copy gs" to copy the
xlwings Google Apps Script module.
(New in 0.26.0)
auth Microsoft Azure AD: "xlwings auth azuread", see
https://docs.xlwings.org/en/stable/server_authentication.html
(New in 0.28.6)
vba This functionality allows you to easily write VBA code
in an external editor: run "xlwings vba edit" to
update the VBA modules of the active workbook from
their local exports everytime you hit save. If you run
this the first time, the modules will be exported from
Excel into your current working directory. To
overwrite the local version of the modules with those
from Excel, run "xlwings vba export". To overwrite the
VBA modules in Excel with their local versions, run
"xlwings vba import". The "--file/-f" flag allows you
to specify a file path instead of using the active
Workbook. Requires "Trust access to the VBA project
object model" enabled. NOTE: Whenever you change
something in the VBA editor (such as the layout of a
form or the properties of a module), you have to run
"xlwings vba export".
(New in 0.26.3, changed in 0.27.0)
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):
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 whereas the syntax changed in 0.15.6
Since v0.27.4, xlwings works with locally synced files on OneDrive, OneDrive for Business, and SharePoint. Some constellations will work out-of-the-box, while others require you to edit the configuration via the xlwings.conf file (see User Config) or the workbook’s xlwings.conf sheet (see Workbook Config).
Note
This documentation is for OneDrive and SharePoint files that are synced to a local folder. This means that both, the Excel and Python file, need to show the green check mark in the File Explorer/Finder as status—a cloud icon will not work. If, in turn, you are looking for the documentation to run xlwings with Excel on the web, see xlwings Server PRO.
An easy workaround if you run into issues is to:
Disable the ADD_WORKBOOK_TO_PYTHONPATH setting (either via the checkbox on the Ribbon or via the settings in the xlwings.conf sheet).
Add the directory of your Python source file to the PYTHONPATH—again, either via Ribbon or xlwings.conf sheet.
If you are using the PRO version, you could instead also embed your code to get around these issues.
For a bit more flexibility, follow the solutions below.
Default setups work out-of-the-box on Windows and macOS. If you get an error message, add the following setting with the correct path to the local root directory of your OneDrive. If possible, make use of environment variables (as shown in the examples) so the configuration will work across different users with the same setup:
Windows: Default setups work out-of-the-box. If you get an error message, add the following setting with the correct path to the local root directory of your OneDrive for Business. If possible, make use of environment variables (as shown in the examples) so the configuration will work across different users with the same setup:
ONEDRIVE_COMMERCIAL_WIN
%USERPROFILE%\OneDrive-MyCompanyLLC
macOS: macOS always requires the following setting with the correct path to the local root directory of your OneDrive for Business. If possible, make use of environment variables (as shown in the examples) so the configuration will work across different users with the same setup:
On Windows, the location of the local root folder of SharePoint can sometimes be derived from the OneDrive environment variables. Most of the time though, you’ll have to provide the following setting (on macOS this is a must):
A lot of the xlwings functionality depends on the workbook’s FullName property (via VBA/COM) that returns the local path of the file unless it is saved on OneDrive, OneDrive for Business or SharePoint with AutoSave enabled. In this case, it returns a URL instead.
URLs for OneDrive and OneDrive for Business can be translated fairly straight forward to the local equivalent. You will need to know the root directory of the local drive though: on Windows, these are usually provided via environment variables for OneDrive. On macOS they don’t exist, which is the reason why you need to provide the root directory for OneDrive. On Windows, the root directory for SharePoint can sometimes be derived from the env vars, too, but this is not guaranteed. On macOS, you’ll need to provide it always anyway.
SharePoint, unfortunately, allows you to map the drives locally in any way you want and there’s no way to reliably get the local path for these files. On Windows, xlwings first checks the registry for the mapping. If this doesn’t work, xlwings checks if the local path is mapped by using the defaults and if the file can’t be found, it checks all existing local files on SharePoint. If it finds one with the same name, it’ll use this. If, however, it finds more than one with the same name, you will get an error message. In this case, you can either rename the file to something unique across all the locally synced SharePoint files or you can change the SHAREPOINT_WIN/MAC setting to not stop at the root folder but include additional folders. As an example, assume you have the following file structure on your local SharePoint:
My Company LLC/
└── sitename1/
└── myfile.xlsx
└── sitename2 - Documents/
└── myfile.xlsx
In this case, you could either rename one of the files, or you could add a path that goes beyond the root folder (preferably under the xlwings.conf sheet):
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.
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'isnotrecognizedasaninternalorexternalcommand,operableprogramorbatchfile., 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
Issue: Files that are saved on OneDrive or SharePoint cause an error to pop up#
Solution:
See the dedicated page about how to configure OneDrive and Sharepoint: OneDrive and SharePoint.
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 explicitly 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:
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:
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,'astring']
2d cell ranges are read in as list of lists, e.g. [[None,1.0,'astring'],[None,2.0,'anotherstring']]
The following options can be set:
ndim
Force the value to have either 1 or 2 dimensions regardless of the shape of the range:
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)defmyfunction(x):# all numbers in x arrive as intreturnx
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:
The expand method is only available on Range objects as UDFs only allow to manipulate the calling cells.
chunksize
When you read and write from or to big ranges, you may have to chunk them or you will hit a timeout or a memory error. The ideal chunksize will depend on your system and size of the array, so you will have to try out a few different chunksizes to find one that works well:
# As DataFramedf=sheet['A1'].expand().options(pd.DataFrame,chunksize=10_000).value# As list of listdf=sheet['A1'].expand().options(chunksize=10_000).value
err_to_str (new in v0.28.0)
If True, will include cell errors such as #N/A as strings. By default, they
will be converted to None.
formatter (new in v0.28.1)
Note
You can’t use formatters with Excel tables.
The formatter option accepts the name of a function. The function will be called after writing the values to Excel and allows you to easily style the range in a very flexible way. How it works is best shown with a little example:
importpandasaspdimportxlwingsasxwsheet=xw.Book().sheets[0]deftable(rng:xw.Range,df:pd.DataFrame):"""This is the formatter function"""# Headerrng[0,:].color="#A9D08E"# Rowsforix,rowinenumerate(rng.rows[1:]):ifix%2==0:row.color="#D0CECE"# Even rows# Columnsforix,colinenumerate(df.columns):if"two"incol:rng[1:,ix].number_format="0.0%"df=pd.DataFrame(data={"one":[1,2,3,4],"two":[5,6,7,8]})sheet["A1"].options(formatter=table,index=False).value=df
Running this code will format the DataFrame like this:
The formatter’s signature is: defmyformatter(myrange,myvalues) where myrange corresponds to the range where myvalues are written to. myvalues is simply what you assign to the value property in the last line of the example. Since we’re using this with a DataFrame, it makes sense to name the argument accordingly and using type hints will help your editor with auto-completion. If you would use a nested list instead of a DataFrame, you would write something like this instead:
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 a 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.
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.
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.
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:
>>> sheet=xw.Book().sheets[0]>>> df=sheet['A1:D5'].options(pd.DataFrame,header=2).value>>> df a b c d eix10 1 2 320 4 5 630 7 8 9# Writing back using the defaults:>>> sheet['A1'].value=df# Writing back and changing some of the options, e.g. getting rid of the index:>>> sheet['B7'].options(index=False).value=df
The same sample for UDF (starting in cell A13 on screenshot) looks like this:
@xw.func@xw.arg('x',pd.DataFrame,header=2)@xw.ret(index=False)defmyfunction(x):# x is a DataFrame, do something with itreturnx
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:
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 options method, e.g. when calling myrange.options(myoption='somevalue') or as specified in
the @arg and @ret decorator when using UDFs. Here is the basic structure:
fromxlwings.conversionimportConverterclassMyConverter(Converter):@staticmethoddefread_value(value,options):myoption=options.get('myoption',default_value)return_value=value# Implement your conversion herereturnreturn_value@staticmethoddefwrite_value(value,options):myoption=options.get('myoption',default_value)return_value=value# Implement your conversion herereturnreturn_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: DictConverter, 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:
fromxlwings.conversionimportConverter,PandasDataFrameConverterclassDataFrameDropna(Converter):base=PandasDataFrameConverter@staticmethoddefread_value(builtin_df,options):dropna=options.get('dropna',False)# set default to Falseifdropna:converted_df=builtin_df.dropna()else:converted_df=builtin_df# This will arrive in Python when using the DataFrameDropna converter for readingreturnconverted_df@staticmethoddefwrite_value(df,options):dropna=options.get('dropna',False)ifdropna:converted_df=df.dropna()else:converted_df=df# This will be passed to the built-in PandasDataFrameConverter when writingreturnconverted_df
Now let’s see how the different converters can be applied:
# Fire up a Workbook and create a sample DataFramesheet=xw.Book().sheets[0]df=pd.DataFrame([[1.,10.],[2.,np.nan],[3.,30.]])
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 lists (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.
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:
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 (/Users/<User>/Library/Containers/com.microsoft.Excel/Data/xlwings.log).
Consider the following sample code of your Python source code my_module.py:
# my_module.pyimportosimportxlwingsasxwdefmy_macro():wb=xw.Book.caller()wb.sheets[0]['A1'].value=1if__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:
Windows only: To debug UDFs, just check the DebugUDFs in the Add-in & Settings, 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:
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.
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.
Custom add-ins work on Windows and macOS and are white-labeled xlwings add-ins that include all your RunPython functions and UDFs (as usual, UDFs work on Windows only). You can build add-ins with and without an Excel ribbon.
The useful thing about add-in is that UDFs and RunPython calls will be available in all workbooks right out of the box without having to add any references via the VBA editor’s Tools > References.... You can also work with standard xlsx files rather than xlsm files. This tutorial assumes you’re familiar with how xlwings and its configuration works.
Start by running the following command on a command line (to create an add-in without a ribbon, you would leave away the --ribbon flag):
$ xlwings quickstart myproject --addin --ribbon
This will create the familiar quickstart folder with a Python file and an Excel file, but this time, the Excel file is in the xlam format.
Double-click the Excel add-in to open it in Excel
Add a new empty workbook (Ctrl+N on Windows or Command+N on macOS)
You should see a new ribbon tab called MyAddin like this:
The add-in and VBA project are currently always called myaddin, no matter what name you chose in the quickstart command. We’ll see towards the end of this tutorial how we can change that, but for now we’ll stick to it.
Compared to the xlwings add-in, the custom add-in offers an additional level of configuration: the configuration sheet of the add-in itself which is the easiest way to configure simple add-ins with a static configuration.
Let’s open the VBA editor by clicking on Alt+F11 (Windows) or Option+F11 (macOS). In our project, select ThisWorkbook, then change the Property IsAddin from True to False, see the following screenshot:
This will make the sheet _myaddin.conf visible (again, we’ll see how to change the name of myaddin at the end of this tutorial):
Activate the sheet config by renaming it from _myaddin.conf to myaddin.conf
Set your Interpreter_Win/_Mac or Conda settings (you may want to take them over from the xlwings settings for now)
Once done, switch back to the VBA editor, select ThisWorkbook again, and change IsAddin back to True before you save your add-in from the VBA editor. Switch back to Excel and click the Run button under the MyAddin ribbon tab and if you’ve configured the Python interpreter correctly, it will print Helloxlwings! into cell A1 of the active workbook.
To change the buttons and items in the ribbon menu or the Backstage View, download and install the Office RibbonX Editor. While it is only available for Windows, the created ribbons will also work on macOS. Open your add-in with it so you can change the XML code that defines your buttons etc. You will find a good tutorial here. The callback function for the demo Run button is in the RibbonMyAddin VBA module that you’ll find in the VBA editor.
To import your UDFs into the custom add-in, run the ImportPythonUDFsToAddin Sub towards the end of the xlwings module (click into the Sub and hit F5). Remember, you only have to do this whenever you change the function name, argument or decorator, so your end users won’t have to deal with this.
If you are only deploying UDFs via your add-in, you probably don’t need a Ribbon menu and can leave away the --ribbon flag in the quickstart command.
As mentioned before, configuration works the same as with xlwings, so you could have your users override the default configuration we did above by adding a myaddin.conf sheet on their workbook or you could use the myaddin.conf file in the user’s home directory. For details see Add-in & Settings.
This, however, means that you will need to adjust the PYTHONPATH for it to find your Python code (or move your Python code to somewhere where Python looks for it—more about that below under deployment). The command will copy your add-in to the XLSTART folder, a special folder from where Excel will open all files everytime you start it.
Admittedly, this part is a bit cumbersome for now. Let’s assume, we would like to rename the addin from MyAddin to Demo:
In the xlwings VBA module, change PublicConstPROJECT_NAMEAsString="myaddin" to PublicConstPROJECT_NAMEAsString="demo". You’ll find this line at the top, right after the Declare statements.
If you rely on the myaddin.conf sheet for your configuration, rename it to demo.conf
Right-click the VBA project, select MyAddinProperties... and rename the ProjectName from MyAddin to Demo.
If you use the ribbon, you want to rename the RibbonMyAddin VBA module to RibbonDemo. To do this, select the module in the VBA editor, then rename it in the Properties window. If you don’t see the Properties window, hit F4.
Open the add-in in the Office RibbonX Editor (see above) and replace all occurrences of MyAddin with Demo in the XML code.
And finally, you may want to rename your myproject.xlam file in the Windows explorer, but I assume you have already run the quickstart command with the correct name, so this won’t be necessary.
By far the easiest way to deploy your add-in to your end-users is to build an installer via the xlwings PRO offering. This will take care of everything and your end users literally just need to double-click the installer and they are all set (no existing Python installation required and no manual installation of the add-in or adjusting of settings required).
If you want it the free (but hard) way, you either need to build an installer yourself or you need your users to install Python and the add-in and take care of placing the Python code in the correct directory. This normally involves tweaking the following settings, for example in the myaddin.conf sheet:
Interpreter_Win/_Mac: if your end-users have a working version of Python, you can use environment variables to dynamically resolve to the correct path. For example, if they have Anaconda installed in the default location, you could use the following configuration:
Conda Path: %USERPROFILE%\anaconda3
Conda Env: base
Interpreter_Mac: $HOME/opt/anaconda3/bin/python
PYTHONPATH: since you can’t have your Python source code in the XLSTART folder next to the add-in, you’ll need to adjust the PYTHONPATH setting and add the folder to where the Python code will be. You could point this to a shared drive or again make use of environment variables so the users can place the file into a folder called MyAddin in their home directory, for example. However, you can also place your Python code where Python looks for it, for example by placing them in the site-packages directory of the Python distribution—an easy way to achieve this is to build a Python package that you can install via pip.
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:
importthreadingfromqueueimportQueueimportxlwingsasxwnum_threads=4defwrite_to_workbook():whileTrue:myrange=q.get()myrange.value=myrange.addressprint(myrange.address)q.task_done()q=Queue()foriinrange(num_threads):t=threading.Thread(target=write_to_workbook)t.daemon=Truet.start()forcellin['A1','A2','A3','A4','A5','A6','A7','A8','A9','A10']:# THIS DOESN'T WORK - passing xlwings objects to threads will fail!myrange=xw.Book('Book1.xlsx').sheets[0].range(cell)q.put(myrange)q.join()
To make it work, you simply have to fully qualify the cell reference in the thread instead of passing a Book object:
If you’re missing a feature in xlwings, do the following:
Most importantly, open an issue on GitHub.
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!
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:
>>> sheet=xw.Book().sheets[0]>>> sheet.api# Windows (pywin32)<win32com.gen_py.Microsoft Excel 16.0 Object Library._Worksheet instance at 0x2260624985352># macOS (appscript)app(pid=2319).workbooks['Workbook1'].worksheets[1]
This works accordingly for the other objects like sheet.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).
xlwings can also be used to call Python functions from VBA within Office apps other than Excel (like Outlook, Access etc.).
Note
This is an experimental feature and may be removed in the future.
Currently, this functionality is only available on Windows for UDFs. The RunPython functionality
is currently not supported.
Press Alt-F11 to get into the VBA editor, then right-click on the xlwings_udfs VBA module and select ExportFile....
Save the xlwings_udfs.bas file somewhere.
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 ImportFile..., 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. MicrosoftAccess or MicrosoftOutlook etc. so that the first
line then reads: #ConstApp="MicrosoftAccess"
Now import the standalone xlwings VBA module (xlwings.bas). You can find it in your xlwings installation folder. To know where that is, do:
>>> importxlwingsasxw>>> 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.
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.
Ultra Fast File Reader: Similar to pandas.read_excel() but 5-25 times faster and you can leverage the convenient xlwings syntax. Works without an Excel installation and therefore on all platforms including Linux.
xlwings Server: With xlwings Server, you don’t need to install Python locally anymore. Instead, run it as a web app on a server. Works with Desktop Excel on Windows and macOS and with Google Sheet and Excel on the web. Runs on all platforms, including Linux, WSL and Docker.
Embedded code: Store your Python source code directly in Excel for easy deployment.
xlwings Reports: A template-based reporting framework, allowing business users to change the layout of the report without having to touch the Python code.
Markdown Formatting: Support for Markdown formatting of text in cells and shapes like e.g., text boxes.
Permissioning: Control which users can run which Python modules via xlwings.
Paid plans come with additional services like:
1-click Installer: Easily build your own Python installer including all dependencies—your end users don’t need to know anything about Python
To use xlwings PRO, you need to install a license key on a Terminal/Command Prompt like so:
xlwingslicenseupdate-kYOUR_LICENSE_KEY
Make sure to replace LICENSE_KEY with your personal key (see below). This will store the license key in your xlwings.conf file (see User Config: Ribbon/Config File for where this is on your system). Instead of running this command, you can also store the license key as an environment variable with the name XLWINGS_LICENSE_KEY.
To use xlwings PRO in a commercial context beyond the trial, you need to enroll in a paid plan (they include additional services like support and the ability to create one-click installers): https://www.xlwings.org/pricing
xlwings PRO licenses are developer licenses, are verified offline (i.e., no telemetry/license server involved) and allow royalty-free deployments to unlimited internal end-users and servers for a hassle-free management. External end-users are included with the business plan. Deployments use deploy keys that don’t expire but instead are bound to a specific version of xlwings.
License Key for non-commercial Purpose:
To use xlwings PRO for free in a non-commercial context, use the following license key: noncommercial (Note that you need at least xlwings 0.26.0).
xlwings PRO offers a simple way to deploy your xlwings tools to your end users without the usual hassle that’s involved when installing and configuring Python and xlwings. End users don’t need to know anything about Python as they only need to:
Run an installer (one installer can power many different Excel workbooks)
Use the Excel workbook as if it was a normal macro-enabled workbook
Advantages:
Zero-config: The end user doesn’t have to configure anything throughout the whole process.
No add-in required: No installation of the xlwings add-in required.
Easy to update: If you want to deploy an update of your Python code, it’s often good enough to distribute a new version of your workbook.
No conflicts: The installer doesn’t touch any environment variables or registry keys and will therefore not conflict with any existing Python installations.
Deploy key: The release command will add a deploy key as your LICENSE_KEY. A deploy key won’t expire and end users won’t need a paid subscription.
You as a developer need to create the one-click installer and run the xlwingsrelease command on the workbook. Let’s go through these two steps in detail!
As a subscriber of one of our paid plans, you will get access to a private GitHub repository, where you can build your one-click installer:
Update your requirements.txt file with your dependencies: in your repository, start by clicking on the requirements.txt file. This will open the following screen where you can click on the pencil icon to edit the file (if you know your way around Git, you can also clone the repository and use your local commit/push workflow instead):
After you’re done with your edits, click on the green Commitchanges button.
Note
If you are unsure about your dependencies, it’s best to work locally with a virtual or Conda environment. In the virtual/Conda environment, only install packages that you need, then run: piplist--format=freeze.
On the right-hand side of the landing page, click on Releases:
On the next screen, click on Draftanewrelease (note, the very first time, you will see a green button called Createanewrelease instead):
This will bring up the following screen, where you’ll only have to fill in a Tagversion (e.g., 1.0.0), then click on the green button Publishrelease:
After 3-5 minutes (you can follow the progress under the Actions tab), you’ll find the installer ready for download under Releases (ignore the zip and tar.gz files):
Note
The one-click installer is a normal Python installation that you can use with multiple Excel workbooks. Hence, you don’t need to create a separate installer for each workbook as long as they all work with the same set of dependencies as defined by the requirements.txt file.
The release command is part of the xlwings CLI (command-line client) and will prepare your Excel file to work with the one-click installer generated in the previous step. Before anything else:
Make sure that you have enabled TrustaccesstotheVBAprojectobjectmodel under File>Options>TrustCenter>TrustCenterSettings>MacroSettings. You only need to do this once and since this is a developer setting, your end users won’t need to bother about this. This setting is needed so that xlwings can update the Excel file with the correct version of the VBA code.
Run the installer from the previous step. This will not interfere with your existing Python installation as it won’t touch your environment variables or registry. Instead, it will only write to the following folder: %LOCALAPPDATA%\<installer-name>.
Make sure that your local version of xlwings corresponds to the version of xlwings in the requirements.txt from the installer. The easiest way to double-check this is to run pipfreeze on a Command Prompt or Anaconda Prompt. If your local version of xlwings differs, install the same version as the installer uses via: pipinstallxlwings==<versionfrominstaller>.
To work with the release command, you should have your workbook in the xlsm format next to your Python code:
Make sure that your Excel workbook is the active workbook, then run the following command on a Command/Anaconda Prompt:
xlwingsrelease
If this is the first time you run this command, you will be asked a few questions. If you are shown a [Y/n], you can hit Enter to accept the default as expressed by the capitalized letter:
Nameofyourone-clickinstaller? Type in the name of your one-click installer. If you want to use a different Python distribution (e.g., Anaconda), you can leave this empty (but you will need to update the xlwings.conf sheet with the Conda settings once the release command has been run).
EmbedyourPythoncode?[Y/n] This will copy the Python code into the sheets of the Excel file.
Hidetheconfigsheet?[Y/n] This will hide the xlwings.conf sheet.
HidethesheetswiththeembeddedPythoncode?[Y/n] If you embed your Python code, this will hide all sheets with a .py ending.
Allowyourtooltorunwithoutthexlwingsadd-in?[Y/n] This will remove the VBA reference to xlwings and copy in the xlwings VBA modules so that the end users don’t need to have the xlwings add-in installed. Note that in this case, you will need to have your RunPython calls bound to a button as you can’t use the Ribbon’s Run main button anymore.
Whatever answers you pick, you can always change them later by editing the xlwings.conf sheet or by deleting the xlwings.conf sheet and re-running the xlwingsrelease command. If you go with the defaults, you only need to provide your end users with the one-click installer and the Excel workbook, no external Python files are required.
To edit your Python code, it’s easiest to work with external Python files and not with embedded code. To stop xlwings from using the embedded code, simply delete all sheets with a .py ending and the workbook will again use the external Python modules. Once you are done editing the files, simply run the xlwingsrelease command again, which will embed the updated code. If you haven’t done any changes to your dependencies (i.e., you haven’t upgraded a package or introduced a new one), you only need to redeploy your Excel workbook to have the end users get the update.
If you did make changes to the requirements.txt and release a new one-click installer, you will need to have the users install the new version of the installer first.
Note
Every time you change the xlwings version in requirements.txt of your one-click installer, make sure to upgrade your local xlwings installatino to the same version and run xlwingsrelease again!
When you run the xlwingsrelease command, your code will be embedded automatically (except if you switch this behavior off). You can, however, also embed code directly: on a command line, run the following command:
xlwingscodeembed
This will import all Python files from the current directory and paste them into Excel sheets of the currently active workbook. Now, you can use RunPython as usual: RunPython"importmymodule;mymodule.myfunction()".
Note that you can have multiple Excel sheets and import them like normal Python files. Consider this example:
UDFs modules don’t have to be added to the UDFModules explicitly when using embedded code. However, in contrast to how it works with external files, you currently need to re-import the functions when you change them.
While you can hide your sheets with your code, they will be written to a temporary directory in clear text.
xlwings allows you to control which Python modules are allowed to run from Excel. In order to use this functionality, you need to run your own web server. You can choose between an HTTP POST and a GET request for the permissioning:
GET: This is the simpler option as you only need to host a static JSON file that you can generate via the xlwings CLI. You can use any web server that is capable of serving static files (e.g., nginx) or use a free external service like GitHub pages. However, every permission change requires you to update the JSON file on the server.
POST: This option relies on the web server to validate the incoming payload of the POST request. While this requires custom logic on your end, you are able to connect it with any internal system (such as a database or LDAP server) to dynamically decide whether a user should be able to run a specific Python module through xlwings.
Before looking at both of these options in more detail, let’s go through the prerequisites and configuration.
Note
This feature does not stop users from running arbitrary Python code through Python directly. Rather, think of it as a mechanism to prevent accidental execution of Python code from Excel via xlwings.
While xlwings offers various ways to configure your workbook (see Configuration), it will only respect the permissioning settings in the config file in the user’s home folder (on Windows, this is %USERPROFILE%\.xlwings\xlwings.conf):
To prevent end users from overwriting xlwings.conf, you’ll need to make sure that the file is owned by the Administrator while giving end users read-only permissions.
Add the following settings while replacing the PERMISSION_CHECK_URL and PERMISSION_CHECK_METHOD (POST or GET) with the appropriate value for your case. PERMISSION_CHECK_AUTHORIZATION is an optional setting that allows you to send a token with POST requests via the Authorization header:
Add additional machines names e.g., "machine_names:[""DESKTOP-QQ27RP3","DESKTOP-XY12AS2"]. Alternatively, you can use the "*" wildcard if you want to allow the module to be used on all end user’s computers. In case of the wildcard, it will still make sure that the file’s content hasn’t been changed by looking at its sha256 hash. xlwings uses importsocket;socket.gethostname() as the machine name.
Make this JSON file accessible via your web server and update the settings in the xlwings.conf file accordingly (see above).
Currently, RunPython and user-defined functions (UDFs) are supported. RunFrozenPython is not supported.
Permissions checks are only done when the Python module is run via Excel/xlwings, it has no effect on Python code that is run from Python directly.
RunPython won’t allow you to run code that uses the fromximporty syntax. Use importx;x.y instead.
The answer of the permissioning server is cached for the duration of the Python session. For UDFs, this means until the functions are re-imported or the RestartUDFServer button is clicked or until Excel is restarted. The same is true if you run RunPython with the UseUDFServer option. By default, however, RunPython starts a new Python session every time, so it will contact the server whenever you call RunPython.
Only top-level modules are checked, i.e. modules that are imported as UDFs or run via RunPython call. Any modules that are imported as dependencies of these modules are not checked.
RunPython with external Python source files depends on logic in the VBA part of xlwings. UDFs and RunPython calls that use embedded code will only rely on Python to perform the permissioning check.
xlwings PRO comes with an ultra fast Excel file reader. Compared with pandas.read_excel(), you should be able to see speedups anywhere between 5 to 25 times when reading a single sheet. The exact speed will depend on your content, file format, and Python version. The following Excel file formats are supported:
xlsx / xlsm / xlam
xlsb
xls
Other advantages include:
Support for named ranges
Support for dynamic ranges via myrange.expand() or myrange.options(expand="table"), respectively.
Support for converters so you can read in ranges not just as pandas DataFrames, but also as NumPy arrays, lists, scalar values, dictionaries, etc.
You can read out cell errors like #DIV/0! or #N/A as strings instead of converting them all into NaN
Unlike the classic (“interactive”) use of xlwings that requires Excel to be installed, reading a file doesn’t depend on an installation of Excel and therefore works everywhere where Python runs. However, reading directly from a file requires the workbook to be saved before xlwings is able to pick up any changes.
To open a file in read mode, provide the mode="r" argument: xw.Book("myfile.xlsx",mode="r"). You usually want to use Book as a context manager so that the file is automatically closed and resources cleaned up once the code leaves the body of the with statement:
You can use the usual converters, for example to read in a range as a DataFrame:
withxw.Book("myfile.xlsx",mode="r")asbook:sheet1=book.sheets[0]df=sheet1["A1:B2"].options("df").value# As usual, you can also provide more optionsdf=sheet1["A1:B2"].options("df",index=False).value
While xlwings reads in cell errors such as #N/A as None by default, you may want to read them in as strings if you’re specifically looking for these by using the err_to_str option:
The reader is currently only available via pipinstallxlwings. Installation via conda is not yet supported, but you can still use pip to install xlwings into a Conda environment!
Date cells: Excel cells with a Date/Time are currently only converted to a datetime object in Python for xlsx file formats. For xlsb format, pandas has the same restriction though (it uses pyxlsb under the hood).
Dynamic ranges: myrange.expand() is currently inefficient, so will slow down the reading considerably if the dynamic range is big.
Named ranges: Named ranges with sheet scope are currently not shown with their proper name: E.g. mybook.names[0].name will show the name mylocalname instead of including the sheet name like so Sheet1!mylocalname. Along the same lines, the names property can only be accessed via book object, not via sheet object. Other defined names (formulas and constants) are currently not supported.
Excel tables: Accessing data via table names isn’t supported at the moment.
Options: except for err_to_str, non-default options are currently inefficient and will slow down the read operation. This includes dates, empty, and numbers.
Formulas: currently only the cell values are supported, but not the cell formulas.
This is only a file reader, writing files is currently not supported.
xlwings Reports is a solution for template-based Excel and PDF reporting, making the generation of pixel-perfect factsheets really simple. xlwings Reports allows business users without Python knowledge to create and maintain Excel templates without having to rely on a Python developer after the initial setup has been done: xlwings Reports separates the Python code (pre- and post-processing) from the Excel template (layout/formatting).
xlwings Reports supports all commonly required components:
Text: Easily format your text via Markdown syntax.
Tables (dynamic): Write pandas DataFrames to Excel cells and Excel tables and format them dynamically based on the number of rows.
Charts: Use your favorite charting engine: Excel charts, Matplotlib, or Plotly.
Images: You can include both raster (e.g., png) or vector (e.g., svg) graphics, including dynamically generated ones, e.g., QR codes or plots.
Multi-column Layout: Split your content up into e.g. a classic two column layout by using Frames.
Single Template: Generate reports in various languages, for various funds etc. based on a single template.
PDF Report: Generate PDF reports automatically and “print” the reports on PDFs in your corporate layout for pixel-perfect results including headers, footers, backgrounds and borderless graphics.
Easy Pre-processing: Since everything is based on Python, you can connect with literally any data source and clean it with pandas or some other library.
Easy Post-processing: Again, with Python you’re just a few lines of code away from sending an email with the reports as attachment or uploading the reports to your web server, S3 bucket etc.
mysheet.render_template(**data): replaces the placeholders in mysheet
mybook.render_template(**data): replaces the placeholders in all sheets of mybook
myapp.render_template(template,output,**data): convenience wrapper that copies a template book before replacing the placeholder with the values. Since this approach allows you to work with hidden Excel instances, it is the most commonly used method for production.
Let’s go through a typical example: start by creating the following Python script report.py:
# report.pyfrompathlibimportPathimportpandasaspdimportxlwingsasxw# We'll place this file in the same directory as the Excel templatethis_dir=Path(__file__).resolve().parentdata=dict(title='MyTitle',df=pd.DataFrame(data={'one':[1,2],'two':[3,4]}))# Change visible=False to run this in a hidden Excel instancewithxw.App(visible=True)asapp:book=app.render_template(this_dir/'mytemplate.xlsx',this_dir/'myreport.xlsx',**data)book.to_pdf(this_dir/'myreport.pdf')
Then create the following Excel file called mytemplate.xlsx:
Run the Python script (or run the code from a Jupyter notebook):
pythonreport.py
This will copy the template and create the following output by replacing the variables in double curly braces with
the value from the Python variable:
If you like, you could also create a classic xlwings tool to call this script or you could design a GUI app by using a framework like PySimpleGUI and turn it into an executable by using a freezer (e.g., PyInstaller). This, however, is beyond the scope of this tutorial.
Note
By default, xlwings Reports overwrites existing values in templates if there is not enough free space for your variable. If you want your rows to dynamically shift according to the height of your array, use Frames.
Note
Unlike xlwings, xlwings Reports never writes out the index of pandas DataFrames. If you need the index to appear in Excel, use df.reset_index(), see DataFrames.
To write DataFrames in a consistent manner to Excel, xlwings Reports ignores the DataFrame indices. If you need to pass the index over to Excel, reset the index before passing in the DataFrame to render_template: df.reset_index().
When working with pandas DataFrames, the report designer often needs to tweak the data. Thanks to filters, they can do the most common operations directly in the template without the need to write Python code. A filter is added to the placeholder in Excel by using the pipe character: {{myplaceholder|myfilter}}. You can combine multiple filters by using multiple pipe characters: they are applied from left to right, i.e. the result from the first filter will be the input for the next filter. Let’s start with an example before listing each filter with its details:
Apply an arithmetic operation (multiply, divide, sum, subtract) on a column (indices are zero-based)
Syntax:
{{df|operation(value,col_ix[,fill_value])}}
fill_value is optional and determines whether empty cells are included in the operation or not. To include empty values and thus make it behave like in Excel, set it to 0.
Example: multiply the first column by 100:
{{df|mul(100,0)}}
Example: multiply the first column by 100 and the second column by 2:
{{df|mul(100,0)|mul(2,1)}}
Example: add 100 to the first column including empty cells:
Maximum number of rows (currently, only sum is supported as aggregation function)
If your DataFrame has 12 rows and you use maxrows(10,"Other") as filter, you’ll get a table that shows the first 9 rows as-is and sums up the remaining 3 rows under the label Other. If your data is unsorted, make sure to call sortasc/sortdesc first to make sure the correct rows are aggregated.
See also: aggsmall, head, tail, rowslice
Syntax:
{{df|maxrows(number_rows,label[,label_col_ix])}}
label_col_ix is optional: if left away, it will label the first column of the DataFrame (index is zero-based)
label_col_ix and min_rows are optional: if label_col_ix is left away, it will label the first column of the DataFrame (indices are zero-based). min_rows has the effect that it skips rows from aggregating if it otherwise the number of rows falls below min_rows. This prevents you from ending up with only one row called “Other” if you only have a few rows that are all below the threshold. NOTE that this parameter only makes sense if the data is sorted!
Merge cells vertically for adjacent cells with the same value — can be used to represent hierarchies
Note
The vmerge filter does not work in Excel tables, as Excel tables don’t support merged cells!
Note that the screenshot uses 4 Frames and the text is centered/vertically aligned in the template.
Syntax (arguments are optional):
{{df|vmerge(col_index1,col_index2,...)}}
Example (default): Hierarchical mode across all columns — this is helpful if the number of columns is dynamic. In hierarchical mode, cells are merged vertically in the first column (indices are zero-based) and cells in the next columns are merged only within the merged cells of the previous column:
{{df|vmerge}}
Example: Hierarchical mode across the specified columns only:
{{df|vmerge(0,1)}}
Example: Independent mode: If you want to merge cells within columns independently of each other, use the filter multiple times. This sample merge cells vertically in the first two columns (indices are zero-based):
The formatter filter accepts the name of a function. The function will be called after writing the values to Excel and allows you to easily style the range in a very flexible way:
{{df|formatter("myformatter")}}
The formatter’s signature is: defmyformatter(rng,df) where rng corresponds to the range where the original DataFrame df is written to. Adding type hints (as shown in the example below) will help your editor with auto-completion.
Note
Within the reports framework, formatters need to be decorated with xlwings.reports.formatter (see example below)! This isn’t necessary though when you use them as part of the standard xlwings API.
Let’s run through the Quickstart example again, amended by a formatter.
Example:
frompathlibimportPathimportpandasaspdimportxlwingsasxwfromxlwings.reportsimportformatter# We'll place this file in the same directory as the Excel templatethis_dir=Path(__file__).resolve().parent@formatterdeftable(rng:xw.Range,df:pd.DataFrame):"""This is the formatter function"""# Headerrng[0,:].color="#A9D08E"# Rowsforix,rowinenumerate(rng.rows[1:]):ifix%2==0:row.color="#D0CECE"# Even rows# Columnsforix,colinenumerate(df.columns):if'two'incol:rng[1:,ix].number_format='0.0%'data=dict(title='MyTitle',df=pd.DataFrame(data={'one':[1,2,3,4],'two':[5,6,7,8]}))# Change visible=False to run this in a hidden Excel instancewithxw.App(visible=True)asapp:book=app.render_template(this_dir/'mytemplate.xlsx',this_dir/'myreport.xlsx',**data)
Using Excel tables is the recommended way to format tables as the styling can be applied dynamically across columns and rows. You can also use themes and apply alternating colors to rows/columns. Go to Insert > Table and make sure that you activate Mytablehasheaders before clicking on OK. Add the placeholder as usual on the top-left of your Excel table (note that this example makes use of Frames):
Headers of Excel tables are relatively strict, e.g. you can’t have multi-line headers or merged cells. To get around these limitations, uncheck the HeaderRow checkbox under TableDesign and use the noheader filter (see DataFrame filters). This will allow you to design your own headers outside of the Excel Table.
Note
At the moment, you can only assign pandas DataFrames to tables
To use Excel charts in your reports, follow this process:
Add some sample/dummy data to your Excel template:
If your data source is dynamic, turn it into an Excel Table (Insert > Table). Make sure you do this before adding the chart in the next step.
Add your chart and style it:
Reduce the Excel table to a 2 x 2 range and add the placeholder in the top-left corner (in our example {{chart_data}}) . You can leave in some dummy data or clear the values of the Excel table:
Assuming your file is called mytemplate.xlsx and your sheet template like on the previous screenshot, you can run the following code:
This will produce the following report, with the chart source correctly adjusted:
Note
If you don’t want the source data on your report, you can place it on a separate sheet. It’s easiest if you add and design the chart on the separate sheet, before cutting the chart and pasting it on your report template. To prevent the data sheet from being printed when calling to_pdf, you can give it a name that starts with # and it will be ignored. NOTE that if you start your sheet name with ##, it won’t be printed but also not rendered!
Images are inserted so that the cell with the placeholder will become the top-left corner of the image. For example, write the following placeholder into you desired cell: {{logo}}, then run the following code:
Image also accepts a pathlib.Path object instead of a string.
If you want to use vector-based graphics, you can use svg on Windows and pdf on macOS. You can control the appearance of your image by applying filters on your placeholder.
Available filters for Images:
width: Set the width in pixels (height will be scaled proportionally).
Example:
{{logo|width(200)}}
height: Set the height in pixels (width will be scaled proportionally).
Example:
{{logo|height(200)}}
width and height: Setting both width and height will distort the proportions of the image!
Example:
{{logo|height(200)|width(200)}}
scale: Scale your image using a factor (height and width will be scaled proportionally).
Example:
{{logo|scale(1.2)}}
top: Top margin. Has the effect of moving the image down (positive pixel number) or up (negative pixel number), relative to the top border of the cell. This is very handy to fine-tune the position of graphics object.
See also: left
Example:
{{logo|top(5)}}
left: Left margin. Has the effect of moving the image right (positive pixel number) or left (negative pixel number), relative to the left border of the cell. This is very handy to fine-tune the position of graphics object.
For a general introduction on how to handle Matplotlib and Plotly, see also: Matplotlib. There, you’ll also find the prerequisites to be able to export Plotly charts as pictures.
Write the following placeholder in the cell where you want to paste the Matplotlib plot: {{lineplot}}. Then run the following code to get your Matplotlib Figure object:
importplotly.expressaspximportxlwingsasxwfig=px.line(x=["a","b","c"],y=[1,3,2],title="A line plot")book=xw.Book('Book1.xlsx')sheet=book.sheets['template'].copy(name='report')sheet.render_template(lineplot=fig)
To change the appearance of the Matplotlib or Plotly plot, you can use the same filters as with images. Additionally, you can use the following filter:
format: allows to change the default image format from png to e.g., vector, which will export the plot as vector graphics (svg on Windows and pdf on macOS). As an example, to make the chart smaller and use the vector format, you would write the following placeholder:
You can work with placeholders in text that lives in cells or shapes like text boxes. If you have more than just a few words, text boxes usually make more sense as they won’t impact the row height no matter how you style them. Using the same gird formatting across worksheets is key to getting a consistent multi-page report.
While this works for simple text, you will lose the formatting if you have any. To prevent that, use a Markdown object, as explained in the next section.
If you will be printing on a PDF Layout with a dark background, you may need to change the font color to white. This has the nasty side effect that you won’t see anything on the screen anymore. To solve that issue, use the fontcolor filter:
fontcolor: Change the color of the whole (!) cell or shape. The primary purpose of this filter is to make white fonts visible in Excel. For most other colors, you can just change the color in Excel itself. Note that this filter changes the font of the whole cell or shape and only has an effect if there is just a single placeholder—if you need to manipulate single words, use Markdown instead, see below. Black and white can be used as word, otherwise use a hex notation of your desired color.
You can format text in cells or shapes via Markdown syntax. Note that you can also use placeholders in the Markdown text that will take the values from the variables you supply via the render_template method:
importxlwingsasxwfromxlwings.reportsimportMarkdownmytext="""\# TitleText **bold** and *italic** A first bullet* A second bullet# {{ second_title }}This paragraph has a line break.Another line."""# The first sheet requires a shape as shown on the screenshotsheet=xw.sheets.activesheet.render_template(myplaceholder=Markdown(mytext),second_title='Another Title')
This will render this template with the placeholder in a cell and a shape:
Like this (this uses the default formatting):
For more details about Markdown, especially about how to change the styling, see Markdown Formatting PRO.
If a placeholder corresponds to a Python datetime object, by default, Excel will format that cell as a date-formatted cell. This isn’t always desired as the formatting depends on the user’s regional settings. To prevent that, format the cell in the Text format or use a TextBox and use the datetime filter to format the date in the desired format. The datetime filter accepts the strftime syntax—for a good reference, see e.g., strftime.org.
To control the language of month and weekday names, you’ll need to set the locale in your Python code. For example, for German, you would use the following:
The format filter allows you to format numbers by using the same mechanism as offered by Python’s f-strings. For example, to format the placeholder performance=0.13 as 13.0%, you would do the following:
{{performance|format(".1%")}}
This corresponds to the following f-string in Python: f"{performance:0.1%}". To get an introduction to the formatting string syntax, have a look at the Python String Format Cookbook.
Frames are vertical containers in which content is being aligned according to their height. That is,
within Frames:
Variables do not overwrite existing cell values as they do without Frames.
Formatting is applied dynamically, depending on the number of rows your object uses in Excel
To use Frames, insert a Note with the text <frame> into row 1 of your Excel template wherever you want a new dynamic column
to start. Frames go from one <frame> to the next <frame> or the right border of the used range.
How Frames behave is best demonstrated with an example:
The following screenshot defines two frames. The first one goes from column A to column E and the second one
goes from column F to column I, since this is the last column that is used.
You can define and format DataFrames by formatting
one header and
one data row
If you use the noheader filter for DataFrames, you can leave the header away and format a single data row.
Alternatively, you could also use Excel Tables, as they can make formatting easier.
Using the layout parameter in the to_pdf() command, you can “print” your Excel workbook on professionally designed PDFs for pixel-perfect reports in your corporate layout including headers, footers, backgrounds and borderless graphics:
Note that the layout PDF either needs to consist of a single page (will be used for each reporting page) or will need to have the same number of pages as the report (each report page will be printed on the corresponding layout page).
To create your layout PDF, you can use any program capable of exporting a file in PDF format such as PowerPoint or Word, but for the best results consider using a professional desktop publishing software such as Adobe InDesign.
Markdown offers an easy and intuitive way of styling text components in your cells and shapes. For an introduction to Markdown, see e.g., Mastering Markdown.
Markdown support is in an early stage and currently only supports:
First-level headings
Bold (i.e., strong)
Italic (i.e., emphasis)
Unordered lists
It doesn’t support nested objects yet such as 2nd-level headings, bold/italic within bullet points or nested bullet points.
Let’s go through an example to see how everything works!
fromxlwings.reportsimportMarkdown,MarkdownStylemytext="""\# TitleText **bold** and *italic** A first bullet* A second bullet# Another TitleThis paragraph has a line break.Another line."""sheet=xw.Book("Book1.xlsx").sheets[0]# Rangesheet['A1'].clear()sheet['A1'].value=Markdown(mytext)# Shape: The following expects a shape like a Rectangle on the sheetsheet.shapes[0].text=""sheet.shapes[0].text=Markdown(mytext)
Running this code will give you this nicely formatted text:
But why not make things a tad more stylish? By providing a MarkdownStyle object, you can define your style. Let’s change the previous example like this:
fromxlwings.reportsimportMarkdown,MarkdownStylemytext="""\# TitleText **bold** and *italic** A first bullet* A second bullet# Another TitleThis paragraph has a line break.Another line."""sheet=xw.Book("Book1.xlsx").sheets[0]# Stylingstyle=MarkdownStyle()style.h1.font.color=(255,0,0)style.h1.font.size=14style.h1.font.name='Comic Sans MS'# No, that's not a font recommendation...style.h1.blank_lines_after=0style.unordered_list.bullet_character='\N{heavy black heart}'# Emojis are fun!# Rangesheet['A1'].clear()sheet['A1'].value=Markdown(mytext,style)# <= provide your style object here# Shape: The following expects a shape like a Rectangle on the sheetsheet.shapes[0].text=""sheet.shapes[0].text=Markdown(mytext,style)
Here is the output of this:
You can override all properties, i.e., you can change the emphasis from italic to a red font or anything else you want:
Markdown objects can also be used with template-based reporting, see xlwings Reports PRO.
Note
macOS currently doesn’t support the formatting (bold, italic, color etc.) of Markdown text due to a bug with AppleScript/Excel. The text will be rendered correctly though, including bullet points.
Instead of installing Python on each end-user’s machine, you can work with a server-based Python installation. It’s essentially a web application, but uses your spreadsheet as the frontend instead of a web page in a browser. xlwings Server doesn’t just work with the Desktop versions of Excel on Windows and macOS but additionally supports Google Sheets and Excel on the web for a full cloud experience. xlwings Server runs everywhere where Python runs, including Linux, Docker and WSL (Windows Subsystem for Linux). it can run on your local machine, as a (serverless) cloud service, or on an on-premise server.
Important
This feature currently only covers parts of the RunPython API (UDFs are not yet supported). See also Limitations and Roadmap.
Having to install a local installation of Python with the correct dependencies is the number one friction when using xlwings. Most excitingly though, xlwings Server adds support for the web-based spreadsheets: Google Sheets and Excel on the web.
To automate Office on the web, you have to use Office Scripts (i.e., TypeScript, a typed superset of JavaScript) and for Google Sheets, you have to use Apps Script (i.e., JavaScript). If you don’t feel like learning JavaScript, xlwings allows you to write Python code instead. But even if you are comfortable with JavaScript, you are very limited in what you can do, as both Office Scripts and Apps Script are primarily designed to automate simple spreadsheet tasks such as inserting a new sheet or formatting cells rather than performing data-intensive tasks. They also make it very hard/impossible to use external JavaScript libraries and run in environments with minimal resources.
Note
From here on, when I refer to the xlwings JavaScript module, I mean either the xlwings Apps Script module if you use Google Sheets or the xlwings Office Scripts module if you use Excel on the web.
On the other hand, xlwings Server brings you these advantages:
Work with the whole Python ecosystem: including pandas, machine learning libraries, database packages, web scraping, boto (for AWS S3), etc. This makes xlwings a great alternative for Power Query, which isn’t currently available for Excel on the web or Google Sheets.
Leverage your existing development workflow: use your favorite IDE/editor (local or cloud-based) with full Git support, allowing you to easily track changes, collaborate and perform code reviews. You can also write unit tests using pytest.
Remain in control of your data and code: except for the data you expose in Excel or Google Sheets, everything stays on your server. This can include database passwords and other sensitive info such as customer data. There’s also no need to give the Python code to end-users: the whole business logic with your secret sauce is protected on your own infrastructure.
Choose the right machine for the job: whether that means using a GPU, a ton of CPU cores, lots of memory, or a gigantic hard disc. As long as Python runs on it, you can go from serverless functions as offered by the big cloud vendors all the way to a self-managed Kubernetes cluster under your desk (see Production Deployment).
Headache-free deployment and maintenance: there’s only one location (usually a Linux server) where your Python code lives and you can automate the whole deployment process with continuous integration pipelines like GitHub actions etc.
Cross-platform: xlwings Server works with Google Sheets, Excel on the web and the Desktop apps of Excel on Windows and macOS.
Either the xlwings add-in installed or a workbook that has been set up in standalone mode
At least xlwings 0.27.0
You need the Automate tab enabled in order to access Office Scripts. Note that Office Scripts currently requires OneDrive for Business or SharePoint (it’s not available on the free office.com), see also Office Scripts Requirements.
The fetch command in Office Scripts must not be disabled by your Microsoft 365 administrator.
Note that Office Scripts is available for Excel on the web and more recently also for Desktop Excel if you use Microsoft 365 (macOS and Windows), you may need to be on the beta channel though.
At least xlwings 0.27.0
New sheets: no special requirements.
Older sheets: make sure that Chrome V8 runtime is enabled under Extensions > AppsScript > ProjectSettings > EnableChromeV8runtime.
Frontend: the xlwings JavaScript module (for Google Sheets/Excel via Office Scripts) or the VBA code in the form of the add-in or standalone modules (Desktop Excel via VBA)
The backend exposes your Python functions by using a Python web framework. In more detail, you need to handle a POST request along these lines (the sample shows an excerpt that uses FastAPI as the web framework, but it works accordingly with any other web framework like Django or Flask):
@app.post("/hello")defhello(data:dict=Body):# Instantiate a Book object with the deserialized request bodybook=xw.Book(json=data)# Use xlwings as usualsheet=book.sheets[0]sheet["A1"].value='Hello xlwings!'# Pass the following back as the responsereturnbook.json()
For Desktop Excel, you can run the web server locally and call the respective function
from VBA (requires the add-in installed) or
from Office Scripts
For the cloud-based spreadsheets, you have to run this on a web server that can be reached from Google Sheets or Excel on the web, and you have to paste the xlwings JavaScript module into the respective editor. How this all works, will be shown in detail under Cloud-based development with Gitpod.
The next section shows you how you can play around with the xlwings Server on your local desktop before we’ll dive into developing against the cloud-based spreadsheets.
The easiest way to try things out is to run the web server locally against your Desktop version of Excel. We’re going to use FastAPI as our web framework. While you can use any web framework you like, no quickstart command exists for these yet, so you’d have to set up the boilerplate yourself.
Start by running the following command on a Terminal/Command Prompt. Feel free to replace demo with another project name and make sure to run this command in the desired directory:
$ xlwings quickstart demo --fastapi
This creates a folder called demo in the current directory with the following files:
demo.xlsmmain.pyrequirements.txt
I would recommend you to create a virtual or Conda environment where you install the dependencies via pipinstall-rrequirements.txt. To run this server locally, run pythonmain.py in your Terminal/Command Prompt or use your code editor/IDE’s run button. You should see something along these lines:
$ python main.py
INFO: Will watch for changes in these directories: ['/Users/fz/Dev/demo']
INFO: Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO: Started reloader process [36073] using watchgod
INFO: Started server process [36075]
INFO: Waiting for application startup.
INFO: Application startup complete.
Your web server is now listening, so let’s open demo.xlsm.
If you want to use VBA, press Alt+F11 to open the VBA editor, and in Module1, place your cursor somewhere inside the following function:
Then hit F5 to run the function—you should see Helloxlwings! in cell A1 of the first sheet.
If, however, you want to use Office Scripts, you can basically start from an empty file (it can be xlsx, it doesn’t have to be xlsm), and run xlwings copy os on the Terminal/Command Prompt/Anaconda Prompt. Then add a new Office Script and paste the code from the clipboard before clicking on Run.
To move this to production, you need to deploy the backend to a server, set up authentication, and point the URL to the production server, see Production Deployment.
The next sections, however, show you how you can make this work with Google Sheets and Excel on the web.
Using Gitpod is the easiest solution if you’d like to develop against either Google Sheets or Excel on the web.
If you want to have a development environment up and running in less than 5 minutes (even if you’re new to web development), simply click the OpeninGitpod button to open a sample project in Gitpod (Gitpod is a cloud-based development environment with a generous free tier):
Opening the project in Gitpod will require you to sign in with your GitHub account. A few moments later, you should see an online version of VS Code. In the Terminal, it will ask you to paste the xlwings license key (get a free trial key if you want to try this out in a commercial context or use the noncommercial license key if your usage qualifies as noncommercial). Note that your browser will ask you for permission to paste. Once you confirm your license key by hitting Enter, the server will automatically start with everything properly configured. You can then open the app directory and look at the main.py file, where you’ll see the hello function. This is the function we’re going to call from Google Sheets/Excel on the web in just a moment. Let’s now look at the js folder and open the file according to your platform:
xlwings_google.js
xlwings_excel.ts
Copy all the code, then switch to Google Sheets or Excel, respectively, and continue as follows:
Click on Extensions > AppsScript. This will open a separate browser tab and open a file called Code.gs with a function stub. Replace this function stub with the copied code from xlwings_google.js and click on the Save icon. Then hit the Run button (the hello function should be automatically selected in the dropdown to the right of it). If you run this the very first time, Google Sheets will ask you for the permissions it needs. Once approved, the script will run the hello function and write Helloxlwings! into cell A1.
To add a button to a sheet to run this function, switch from the Apps Script editor back to Google Sheets, click on Insert > Drawing and draw a rounded rectangle. After hitting SaveandClose, the rectangle will appear on the sheet. Select it so that you can click on the 3 dots on the top right of the shape. Select AssignScript and write hello in the text box, then hit OK.
In the Automate tab, click on NewScript. This opens a code editor pane on the right-hand side with a function stub. Replace this function stub with the copied code from xlwings_excel.ts. Make sure to click on Savescript before clicking on Run: the script will run the hello function and write Helloxlwings! into cell A1.
To run this script from a button, click on the 3 dots in the Office Scripts pane (above the script), then select +Addbutton.
Any changes you make to the hello function in app/main.py in Gitpod are automatically saved and reloaded by the web server and will be reflected the next time you run the script from Google Sheets or Excel on the web.
Note
While Excel on the web requires you to create a separate script with a function called main for each Python function, Google Sheets allows you to add multiple functions with any name.
Please note that clicking the Gitpod button gets you up and running quickly, but if you want to save your changes (i.e., commit them to Git), you should first fork the project on GitHub to your own account and open it by prepending https://gitpod.io/# to your GitHub URL instead of clicking the button (this works with GitLab and Bitbucket too). Or continue with the next section, which shows you how you can start a project from scratch on your local machine.
An alternative for Gitpod is GitHub Codespaces, but unlike Gitpod, GitHub Codespaces only works with GitHub.
Local Development with Google Sheets or Excel (via Office Scripts)#
This section walks you through a local development workflow as an alternative to using Gitpod/GitHub Codespaces. What’s making this a little harder than using a preconfigured online IDE like Gitpod is the fact that we need to expose our local web server to the internet for easy development (even if we use the Desktop version of Excel).
As before, we’re going to use FastAPI as our web framework. While you can use any web framework you like, no quickstart command exists for these yet, so you’d have to set up the boilerplate yourself. Let’s start with the server before turning our attention to the client side (i.e, Google Sheets or Excel on the web).
Start a new quickstart project by running the following command on a Terminal/Command Prompt. Feel free to replace demo with another project name and make sure to run this command in the desired directory:
$ xlwings quickstart demo --fastapi
This creates a folder called demo in the current directory with a few files:
main.pydemo.xlsmrequirements.txt
I would recommend you to create a virtual or Conda environment where you install the dependencies via pipinstall-rrequirements.txt. In app.py, you’ll find the FastAPI boilerplate code and in main.py, you’ll find the hello function that is exposed under the /hello endpoint.
To run this server locally, run pythonmain.py in your Terminal/Command Prompt or use your code editor/IDE’s run button. You should see something along these lines:
$ python main.py
INFO: Will watch for changes in these directories: ['/Users/fz/Dev/demo']
INFO: Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO: Started reloader process [36073] using watchgod
INFO: Started server process [36075]
INFO: Waiting for application startup.
INFO: Application startup complete.
Your web server is now listening, however, to enable it to communicate with Google Sheets or Excel via Office Scripts, you need to expose the port used by your local server (port 8000 in your example) securely to the internet. There are many free and paid services available to help you do this. One of the more popular ones is ngrok whose free version will do the trick (for a list of ngrok alternatives, see Awesome Tunneling):
For the sake of this tutorial, let’s assume you’ve installed ngrok, in which case you would run the following on your Terminal/Command Prompt to expose your local server to the public internet:
$ ngrok http 8000
Note that the number of the port (8000) has to correspond to the port that is configured on your local development server as specified at the bottom of main.py. ngrok will print something along these lines:
To configure the xlwings client in the next step, we’ll need the https version of the Forwarding address that ngrok prints, i.e., https://xxxx-xxxx-xx-xx-xxx-xxxx-xxxx-xxxx-xxx.ngrok.io.
Note
When you’re not actively developing, you should stop your ngrok session by hitting Ctrl-C in the Terminal/Command Prompt.
Now it’s time to switch to Google Sheets or Excel! To paste the xlwings JavaScript module, follow these 3 steps:
Copy the xlwings JavaScript module: On a Terminal/Command Prompt on your local machine, run the following command:
$ xlwings copy gs
$ xlwings copy os
This will copy the correct xlwings JavaScript module to the clipboard so we can paste it in the next step.
Paste the xlwings JavaScript module
Click on Extensions > AppsScript. This will open a separate browser tab and open a file called Code.gs with a function stub. Replace this function stub with the copied code from the previous step and click on the Save icon. Then hit the Run button (the hello function should be automatically selected in the dropdown to the right of it). If you run this the very first time, Google Sheets will ask you for the permissions it needs. Once approved, the script will run the hello function and write Helloxlwings! into cell A1.
To add a button to a sheet to run this function, switch from the Apps Script editor back to Google Sheets, click on Insert > Drawing and draw a rounded rectangle. After hitting SaveandClose, the rectangle will appear on the sheet. Select it so that you can click on the 3 dots on the top right of the shape. Select AssignScript and write hello in the text box, then hit OK.
In the Automate tab, click on NewScript. This opens a code editor pane on the right-hand side with a function stub. Replace this function stub with the copied code from the previous step. Make sure to click on Savescript before clicking on Run: the script will run the hello function and write Helloxlwings! into cell A1.
To run this script from a button, click on the 3 dots in the Office Scripts pane (above the script), then select +Addbutton.
Configuration: The final step is to configure the xlwings JavaScript module properly, see the next section Configuration.
Via arguments in the runPython (via Apps Script / Office Scripts) or RunRemotePython (via VBA) function, respectively.
Via xlwings.conf sheet (in this case, the keys are UPPER_CASE with underscore instead of camelCase, see the screenshot below).
If you provide a value via config sheet and via function argument, the function argument wins. Let’s see what the available settings are:
url (required): This is the full URL of your function. In the above example under Local Development with Google Sheets or Excel on the web, this would be https://xxxx-xxxx-xx-xx-xxx-xxxx-xxxx-xxxx-xxx.ngrok.io/hello, i.e., the ngrok URL with the /hello endpoint appended.
auth (optional): This is a shortcut to set the Authorization header. See the section about Server Auth for the options.
headers (optional): A dictionary (VBA) or object literal (JS) with name/value pairs. If you set the Authorization header, the auth argument will be ignored.
exclude (optional): By default, xlwings sends over the complete content of the whole workbook to the server. If you have sheets with big amounts of data, this can make the calls slow or you could even hit a timeout. If your backend doesn’t need the content of certain sheets, you can exclude them from being sent over via this setting. Currently, you can only exclude entire sheets as comma-delimited string like so: "Sheet1,Sheet2".
include (optional): It’s the counterpart to exclude and allows you to submit the names of the sheets that you want to send to the server. Like exclude, include accepts a comma-delimited string, e.g., "Sheet1,Sheet2".
The xlwings web server can be built with any web framework and can therefore be deployed using any solution capable of running a Python backend or function. Here is a list for inspiration (non-exhaustive):
For Google Sheets, you can take advantage of the integrated Triggers (accessible from the menu on the left-hand side of the Apps Script editor). You can trigger your xlwings functions on a schedule or by an event, such as opening or editing a sheet.
Normally, you would use Power Automate to achieve similar things as with Google Sheets Triggers, but unfortunately, Power Automate can’t run Office Scripts that contain a fetch command like xlwings does, so for the time being, you can only trigger xlwings calls manually on Excel on the web. Alternatively, you can open your Excel file with Google Sheets and leverage the Triggers that Google Sheets offers. This, however, requires you to store your Excel file on Google Drive.
Currently, only a subset of the xlwings API is covered, mainly the Range and Sheet classes with a focus on reading and writing values and sending pictures (including Matplotlib plots). This, however, includes full support for type conversion including pandas DataFrames, NumPy arrays, datetime objects, etc.
You are moving within the web’s request/response cycle, meaning that values that you write to a range will only be written back to Google Sheets/Excel once the function call returns. Put differently, you’ll get the state of the sheets at the moment the call was initiated, but you can’t read from a cell you’ve just written to until the next call.
You will need to use the same xlwings version for the Python package and the JavaScript module, otherwise, the server will raise an error.
Currently, custom functions (a.k.a. user-defined functions or UDFs) are not supported.
For users with no experience in web development, this documentation may not be quite good enough just yet.
xlwings relies on the fetch command in Office Scripts that cannot be used via Power Automate and that can be disabled by your Microsoft 365 administrator.
While Excel on the web feels generally slow, it seems to have an extreme lag depending on where in the world you open the browser with Excel on the web. For example, a hello world call takes ~4.5s if you open a browser in Amsterdam/Netherlands while it takes ~8.5s if you do it Buenos Aires/Argentina.
Authentication (and potentially authorization) is an important step in securing your xlwings Server app. On the server side, you can handle authentication
within your app (via your web framework)
outside of your app (via e.g. a reverse proxy such as nginx or oauth2-proxy that sits in front of your app)
Furthermore, you can use different authentication techniques such as HTTP Basic Auth or Bearer tokens in the form of API keys or OAuth2 access tokens.
On the client side, you set the Authorization header when you make a request from Excel or Google Sheets to your xlwings backend. To set the Authorization header, xlwings offers the auth parameter:
Your backend will then have to validate the Authorization header. Let’s get started with the simplest implementation of an API key before looking at HTTP Basic Auth and more advanced options like Azure AD and Google access tokens (for Google Sheets).
Basic auth is a simple and popular method that sends the username and password via the Authorization header.
Reverse proxies such as nginx allow you to easily protect your app with HTTP Basic Auth but you can also handle it directly in your app.
With your username and password, run the following Python script to get the value that you need to provide for auth:
If you use ngrok, there’s an easy way to protect the exposed URL via Basic auth:
ngrok http 8000 -auth='myusername:mypassword'
Warning
ngrok HTTP Basic auth will NOT work with Excel via Office Scripts as it doesn’t support CORS. It’s, however, an easy method for protecting your app during development if you use xlwings via VBA or Google Sheets.
Azure AD authentication is only available for Desktop Excel via VBA.
Azure Active Directory (Azure AD) is Microsoft’s enterprise identity service. If you’re using the xlwings add-in or VBA standalone module, xlwings allows you to comfortably log in users on their desktops, allowing you to securely validate their identity on the server and optionally implement role-base access control (RBAC).
Download xlwings.exe, the standalone xlwings CLI, from the GitHub Release page and place it in a specific folder, e.g., under C:\ProgramandFiles\xlwings\xlwings.exe or %LOCALAPPDATA%\xlwings\xlwings.exe.
Use port if the randomly assigned default port causes issues
Use username if the user is logged in with multiple Microsoft accounts
Note
Instead of relying on xlwings.exe, you could also use a normal Python installation with xlwings and msal installed. In this case, simply leave away the cliPath argument.
You can also use the xlwings.conf file or xlwings.conf sheet for configuration. In this case, the settings are the following:
Note that if you use the xlwings add-in rather than relying on the xlwings standalone VBA module, you will need to make sure that there’s a reference set to xlwings in the VBA editor under Tools > References.
When you now call the Main function the very first time, a browser Window will open where the user needs to login to Azure AD. The acquired OAuth2 access token is then cached for 60-90 minutes. Once an access token has expired, a new one will be requested using the refresh token, i.e., without user intervention, but it will slow that that request.
Feature xlwings now allows to authenticate and authorize users via Azure AD in connection with the Ribbon add-in or VBA standalone module. This is useful in connection with a server component, such as xlwings Server, where the acquired access tokens can be validated, see Server Auth (GH2122).
EnhancementPRO xlwings Server: added support for reading the Names collection via mybook.names and mysheet.names (GH2123).
Feature The xlwings CLI (command-line interface) is now also available as a standalone executable for a limited set of uses cases. It can be downloaded from the GitHub Release page and can be useful to run xlwingsvba..., xlwingsauth..., and xlwingsaddin...-f without having to install a full Python installation (GH2121).
Breaking ChangePRO: xlwings Server: auth replaces the apiKey argument in the runPython and RunRemotePython calls respectively. Technically it’s only a deprecation, so apiKey still works for now (GH2104).
Bug FixPRO xlwings Server: Fixed an error with setting custom headers in VBA (GH2081).
Breaking ChangePRO xlwings File Reader: The reader was including Chartsheets etc. in mybook.sheets, which was inconsistent with the rest of the API. Accordingly, it now only shows Worksheets (GH2058).
Bug FixPRO xlwings File Reader: With xlsb formats, slightly unusual defined names caused the reader to fail (GH2057).
EnhancementPRO xlwings Reports: the imports have been flattened. What previously was available via xlwings.pro.reports is now also available via xlwings.reports (GH2055).
EnhancementPRO xlwings Reports: the registration of formatters for use with templates has been simplified by allowing you to use the @formatter decorator instead of having to register the function via register_formatter(myfunc) (GH2055).
Feature You can now use formatters to format the data you write to Excel or Google Sheets in a very flexible manner (see also Default Converter):
importpandasaspdimportxlwingsasxwsheet=xw.Book().sheets[0]deftable(rng:xw.Range,df:pd.DataFrame):"""This is the formatter function"""# Headerrng[0,:].color="#A9D08E"# Rowsforix,rowinenumerate(rng.rows[1:]):ifix%2==0:row.color="#D0CECE"# Even rows# Columnsforix,colinenumerate(df.columns):if"two"incol:rng[1:,ix].number_format="0.0%"df=pd.DataFrame(data={"one":[1,2,3,4],"two":[5,6,7,8]})sheet["A1"].options(formatter=table,index=False).value=df
FeaturePRO Formatters are also available for xlwings Reports via filters: {{df|formatter("myformatter")}}, see DataFrames Filters.
Feature You can now export a sheet to an HTML page via mysheet.to_html()
Feature New convenience property to get a list of the sheet names: mybook.sheet_names
EnhancementPRO The Excel File Reader now supports the Names collection. I.e., you can now run code like this:
EnhancementBook can now be used as context manager (i.e., with the with statement, see previous bullet point), which will close the book automatically when leaving the body of the with statement.
Enhancement The new option err_to_str allows you to deliver cell errors like #N/A as strings instead of None (default): xw.Book("mybook.xlsx").options(err_to_str=True).value.
Breaking ChangePRO xlwings Server used to deliver cell errors as strings, which wasn’t consistent with the rest of xlwings. This has now been fixed by delivering them as None by default. To get the previous behavior, use the err_to_str option, see the previous bullet point.
EnhancementPRO The Remote Interpreter has been rebranded to xlwings Server.
EnhancementPRO: xlwings Server: added support for named ranges via mysheet["myname"] or mysheet.range("myname") (GH1975).
EnhancementPRO: xlwings Server: in addition to Google Sheets, pictures.add() is now also supported on Desktop Excel (Windows and macOS). This includes support for Matplotlib plots (GH1974).
FeaturePRO Google Sheets now support pictures via mysheet.pictures.add() incl. Matplotlib/Plotly (note that Excel on the web and Desktop Excel via xlwings Server are not yet supported). Also note that Google Sheets allows a maximum of 1 million pixels as calculated by (width in inches * dpi) * (height in inches * dpi), see also Matplotlib & Plotly Charts (GH1906).
Breaking Change Matplotlib plots are now written to Excel/Google Sheets with a default of 200 dpi instead of 300 dpi. You can change this (and all other options that Matplotlib’s savefig() and Plotly’s write_image() offer via sheet.pictures.add(image=myfigure,export_options={"bbox_inches":"tight","dpi":300}) (GH665, GH519).
Bug Fix macOS: Python modules on OneDrive Personal are now found again in the default setup even if they have been migrated to the new location (GH1891).
EnhancementPRO xlwings Server now shows nicely formatted error messages across all platforms (GH1889).
Enhancement Further SharePoint enhancements on Windows, increasing the chance that mybook.fullname returns the proper local filepath (by taking into account the info in the registry) (GH1829).
Enhancement The ribbon, i.e., the config, now allows you to uncheck the box AddworkbooktoPYTHONPATH to not automatically add the directory of your workbook to the PYTHONPATH. The respective config is called ADD_WORKBOOK_TO_PYTHONPATH. This can be helpful if you experience issues with OneDrive/SharePoint: uncheck this box and provide the path where your source file is manually via the PYTHONPATH setting (GH1873).
EnhancementPRO Added support for myrange.add_hyperlink() with remote interpreter (GH1882).
EnhancementPRO Added a new optional parameter include in connection with runPython (JS) and RunRemotePython (VBA), respectively. It’s the counterpart to exclude and allows you to submit the names of the sheets that you want to send to the server. Like exclude, include accepts a comma-delimited string, e.g., “Sheet1,Sheet2” (GH1882).
EnhancementPRO On Google Sheets, the xlwings JS module now automatically asks for the proper permission to allow authentication based on OAuth Token (GH1876).
FeaturePRO This release adds support for xlwings Server to the Excel Desktop apps on both Windows and macOS. The new VBA function RunRemotePython is equivalent to runPython in the JavaScript modules of Google Sheets and Excel on the web, see xlwings Server (GH1841).
Enhancement The xlwings package is now uploaded as wheel to PyPI in addition to the source format (GH1855).
Enhancement The xlwings package is now compatible with Poetry (GH1265).
Enhancement The add-in and the dll files are now code signed (GH1848).
Breaking ChangePRO The JavaScript modules (Google Sheet/Excel on the web ) changed the parameters in runPython, see xlwings Server (GH1852).
Breaking Changexlwingsvbaedit has been refactored and there is an additional command xlwingsvbaimport to edit your VBA code outside of the VBA editor, e.g., in VS Code or any other editor, see Command Line Client (CLI) (GH1843).
Breaking Change The --unprotected flag has been removed from the xlwingsaddininstall command. You can still manually remove the password (xlwings) though (GH1850).
Bug FixPRO The Markdown class has been fixed in case the first line was empty (GH1856).
Bug FixPRO 0.27.1 fixes an issue with the version string in the new RunRemotePython VBA call (GH1859).
Feature If you still have to write VBA code, you can now use the new CLI command xlwingsvbaedit: this will export all the VBA modules locally so that you can edit them with any editor like e.g., VS Code. Every local change is synced back whenever you save the local file, see Command Line Client (CLI) (GH1839).
EnhancementPRO The permissioning feature now allows you to send an Authorization header via the new PERMISSION_CHECK_AUTHORIZATION setting (GH1840).
Feature Added support for myrange.clear_formats and mysheet.clear_formats (GH1802).
Feature Added support for mychart.to_pdf() and myrange.to_pdf() (GH1708).
FeaturePRO xlwings Server: added support for mybook.selection (GH1819).
Enhancement The quickstart command now makes sure that the project name is a valid Python module name (GH1773).
Enhancement The to_pdf method now accepts an additional parameter quality that defaults to "standard" but can be set to "minimum" for smaller PDFs (GH1697).
Bug Fix Allow space in path to Python interpreter when using UDFs / UDF Server (GH974).
Bug Fix A few issues were fixed in case your files are synced with OneDrive or SharePoint (GH1813 and GH1810).
Bug FixPRO Reports: fixed the aggsmall filter to work without the optional min_rows parameter (GH1824).
Bug Fix Finally, xlwings adds proper support for OneDrive, OneDrive for Business, and SharePoint. This means that the quickstart setup (Excel file and Python file in the same folder with the same name) works even if the files are stored on OneDrive/SharePoint—as long as they are being synced locally. It also makes mybook.fullname return the local file path instead of a URL. Sometimes, this requires editing the configuration, see: OneDrive and SharePoint for the details (GH1630).
Feature The update() method of Excel tables has been moved from PRO to open source. You can now easily update an existing table in Excel with the data from a new pandas DataFrame without messing up any formulas that reference that table: mytable.update(df), see: Table.update() (GH1751).
PROBreaking Change: Reports: create_report() is now deprecated in favor of render_template() that is available via app, book (new), and sheet objects, see: xlwings Reports PRO (GH1738).
Bug Fix Running UDFs from other Office apps has been fixed (GH1729).
Bug Fix Writing to a protected sheet or using an invalid sheet name etc. caused xlwings to hang instead of raising an Exception (GH1725).
Enhancement You can now use the alias 'df' to convert to a pandas DataFrame: mysheet['A1:C3'].options('df').value is equivalent to importpandasaspd;mysheet['A1:C3'].options(pd.DataFrame).value (GH1533).
Enhancement Added --dir option to xlwingsaddininstall to allow the installation of all files in a directory as add-ins (GH1702).
Bug Fix Pandas DataFrames now properly work with PeriodIndex / PeriodDtype (GH1084).
PRO Reports: If there’s just one Frame, keep height of rows (GH1698).
PROBreaking Change: Reports: Changed the order of the arguments of the arithmetic DataFrame filters: sum, div, mul and div to align them with the other filters. E.g., to multiply column 2 by 100, you now have to write your filter as {{df|mul(100,2)}} (GH1696).
PROBug Fix Reports: Fixed an issue with images when pillow wasn’t installed (GH1695).
Enhancement You can now also define the color of cells, shapes and font objects with a hex string instead of just an RGB tuple, e.g., mysheet["A1"].color="#efefef" (GH1535).
Enhancement When you print a workbook or sheet to a pdf, you can now automatically open the PDF document via the new show argument: mybook.to_pdf(show=True) (GH1683).
Bug Fix: This release includes another round of fixing the cleanup actions of the App() context manager (GH1687).
PROEnhancement Reports: New filter fontcolor, allowing you to write text in black and turn it into e.g., white for the report. This gets around the issue that white text isn’t visible in Excel on a white background: {{myplaceholder|fontcolor("white")}}. Alternatively, you can also use a hex color (GH1692).
Featuremyapp.properties is a new context manager that allows you to easily change the app’s properties temporarily. Once the code leaves the with block, the properties are changed back to their previous state (GH254). For example:
importxlwingsasxwapp=App()withapp.properties(display_alerts=False):# Alerts are disabled until you leave the with block again
Enhancement The app properties myapp.enable_events and myapp.interactive are now supported (GH254).
Enhancementmybook.to_pdf now ignores sheet names that start with a #. This can be changed by setting the new parameter exclude_start_string (GH1667).
Bug Fix The new App context manager introduced with v0.24.3 was sometimes causing an error on Windows during the cleanup actions (GH1668).
PROxlwings.pro.reports:
Breaking Change: DataFrame placeholders will now ignore the DataFrame’s index. If you need the index, reset it via : df.reset_index() before passing the DataFrame to create_report or render_template. This was required as the same column index used in filters would point to seemingly different columns in Excel depending on whether the index was included or not. This also means that the noindex and body filters are no obsolete and have been removed (GH1676).
Enhancement Dataframe filters now respect the order in which they are called and can be used multiple times (GH1675).
Enhancement New filters: format (to apply f-string like formatting), datetime (to format datetime objects), top and left (to position graphics outside of the grid structure) header, add, sub, mul, div (to only return the header of a DataFrame or apply an arithmetic operation, respectively) (GH1666, GH1660, GH1677).
Enhancement: create_report can now be accessed as method of the app object like so: myapp.create_report (GH1665).
Bug Fix: Excel tables that had the Header Row unchecked were sometimes causing row shifts in the template (GH1663).
Bug Fix: Rendering a template was sometimes causing the following error PasteSpecialmethodofRangeclassfailed (GH1672).
Enhancementxlwings.App() can now be used as context manager, making sure that there are no zombie processes left over on Windows, even if you use a hidden instance and your code fails. It is therefore recommended to use it whenever you can, like so:
withxw.App(visible=True)asapp:print(app.books)
Enhancementmysheet.pictures.add now accepts a new anchor argument that you can use as an alternative to top/left to position the picture by providing an anchor range object, e.g.: mysheet.pictures.add(img,anchor=mysheet['A1']) (GH1648).
Bug Fix macOS: Plots are now sent to Excel in PDF format when you set format='vector' which is supporting transparency unlike the previously used eps format (GH1647).
PROEnhancementmybook.to_pdf now accepts a layout parameter so you can “print” your reports onto a PDF with your corporate layout including headers, footers and borderless graphics. See PDF Layout.
Enhancement DataFrames are now displayed in Excel tables with empty column names if the DataFrame doesn’t have a column or index name. This effect is e.g. visible when using xw.view() (GH1643).
Enhancementmysheet.pictures.add() now supports format='vector' which translates to 'svg' on Windows and 'eps' on macOS (GH1640).
PROEnhancement: The reports package now offers the additional DataFrame filters rowslice and colslice, see xlwings Reports (GH1645).
PROBug Fix: Bug fix with handling Excel tables without headers.
Breaking Change
PROEnhancement: <frame> markers now have to be defined as cell notes in the first row, see Frames: Multi-column Layout. This has the advantage that the Layout view corresponds to the print view (GH1641). Also, the print area is now preserved even if you use Frames.
Enhancementpictures.add() now accepts every picture format (including vector-based formats) that your Excel version supports. For example, on Windows you can use the svg format (only supported with Excel that comes with Microsoft 365) and on macOS, you can use eps (GH1624).
[Enhancements] Support for Plotly images was moved from PRO to the Open Source version, i.e. you can now provide a Plotly image directly to pictures.add().
Enhancement Matplotlib and Plotly plots can now be sent to Excel in a vector-based format by providing the format argument, e.g. svg on Windows or eps on macOS.
Enhancement Removed dependency on pillow/PIL to properly size images via pictures.add().
Bug Fix Various fixes with scaling and positioning images via pictures.add() (GH1491).
PROFeature: Reports: DataFrames and Images are now offering various filters to influence the behavior of how DataFrames and Images are displayed, giving the template designer the ability to change a lot of things that previously had to be taken care of by the Python developer. For example, to hide a DataFrame’s index, you can now do {{df|noindex}} or to scale the image to double its size, you can do {{img|scale(2)}}. You’ll find all available filters under xlwings Reports (GH1602).
Breaking Change
Enhancement: When using pictures.add(), pictures arrive now in Excel in the same size as if you would manually add them via the Excel UI and setting width/height now behaves consistently during initial adding and resizing. Consequently, you may have to fix your image sizes when you upgrade. (GH1491).
PRO The default MarkdownStyle removed the empty space after a h1 heading. You can always reintroduce it by applying a custom style (GH1628).
Bug Fix Windows: Fixed the ImportUDFs function in the VBA standalone module (GH1601).
Bug Fix Fixed configuration hierarchy: if you have a setting with an empty value in the xlwings.conf sheet, it will not be overridden by the same key in the directory or user config file anymore. If you wanted it to be overridden, you’d have to get the key out of the “xlwings.conf” sheet (GH1617).
PROFeature Added the ability to block the execution of Python modules based on the file hash and/or machine name (GH1586), see Permissioning PRO.
PROFeature Added the xlwingsrelease command for an easy release management in connection with the one-click installer, see 1-click installer PRO. (GH1429).
Feature Added support for the chunksize option: when you read and write from or to big ranges, you may have to chunk them or you will hit a timeout or a memory error. The ideal chunksize will depend on your system and size of the array, so you will have to try out a few different chunksizes to find one that works well (GH77):
# As DataFramedf=sheet['A1'].expand().options(pd.DataFrame,chunksize=10_000).value# As list of listdf=sheet['A1'].expand().options(chunksize=10_000).value
Enhancementxw.load() now expands to the current_region instead of relying on expand() (GH1565).
Enhancement The OneDrive setting has been split up into a Windows and macOS-specific paths: ONEDRIVE_WIN and ONEDRIVE_MAC (GH1556).
Bug Fix macOS: There are no more timeouts when opening or saving large workbooks that take longer than 60 seconds (GH618).
Bug FixRunPython was failing when there was a & in the Excel file name (GH1557).
PROFeature: This release adds support for Markdown-based formatting of text, both in cells as well as in shapes, see Markdown Formatting PRO for the details. This is also supported for template-based reports.
fromxlwings.proimportMarkdown,MarkdownStylemytext="""\# TitleText **bold** and *italic** A first bullet* A second bullet# Another TitleThis paragraph has a line break.Another line."""sheet=xw.Book("Book1.xlsx").sheets[0]sheet['A1'].value=Markdown(mytext)sheet.shapes[0].text=Markdown(mytext)
Running this code will give you this nicely formatted text, but you can also define your own style to match your corporate style guide as explained under Markdown Formatting PRO:
Feature Added support for the Font object via range or shape objects, see Font (GH897 and GH559).
Feature Added support for the Characters object via range or shape objects, see Characters.
Feature While it’s always been possible to somehow create your own xlwings-based add-ins, this release adds a toolchain to make it a lot easier to create your own white-labeled add-in, see Custom Add-ins (GH1488).
Enhancementxw.view now formats the pandas DataFrames as Excel table and with the new xw.load function, you can easily load a DataFrame from your active workbook into a Jupyter notebook. See Jupyter Notebooks: Interact with Excel for a full tutorial (GH1487).
Enhancement New property Shape.text to read and write text to the text frame of shapes (GH1456).
PROFeature: xlwings Reports now supports template text in shapes, see xlwings Reports.
v0.21.3 (Nov 22, 2020)
PROBreaking Change: The Table.update method has been changed to treat the DataFrame’s index consistently whether or not it’s being written to an Excel table: by default, the index is now transferred to Excel in both cases.
v0.21.2 (Nov 15, 2020)
Bug Fix The default quickstart setup now also works when you store your workbooks on OneDrive (GH1275)
Bug Fix Excel files that have single quotes in their paths are now working correctly (GH1021)
v0.21.1 (Nov 13, 2020)
Enhancement Added new method Book.to_pdf() to easily export PDF reports. Needless to say, this integrates very nicely with xlwings Reports (GH1363).
Enhancement: When using UDFs, you can now use 'range' for the convert argument where you would use before xw.Range. The latter will be removed in a future version (GH1455).
Enhancement Windows: The comtypes requirement has been dropped (GH1443).
PROFeature: Table.update offers an easy way to keep your Excel tables in sync with your DataFrame source (GH1454).
PROEnhancement: The reports package now supports Excel tables in the templates. This is e.g. helpful to style the tables with striped rows, see Excel Tables (GH1364).
v0.20.8 (Oct 18, 2020)
Enhancement Windows: With UDFs, you can now get easy access to the caller (an xlwings range object) by using caller as a function argument (GH1434). In that sense, caller is now a reserved argument by xlwings and if you have any existing arguments with this name, you’ll need to rename them:
@xw.funcdefget_caller_address(caller):# caller will not be exposed in Excel, so use it like so:# =get_caller_address()returncaller.address
Bug Fix Windows: The setting ShowConsole now also shows/hides the command prompt properly when using the UDF server with Conda. There is no more switching between python and pythonw required (GH1435 and GH1421).
Bug Fix Windows: Functions called via RunPython with UseUDFServer activated don’t require the xw.sub decorator anymore (GH1418).
v0.20.7 (Sep 3, 2020)
Bug Fix Windows: Fix a regression introduced with 0.20.0 that would cause an AttributeError:Range.CLSID with async and legacy dynamic array UDFs (GH1404).
Enhancement: Matplotlib figures are now converted to 300 dpi pictures for better quality when using them with pictures.add (GH1402).
v0.20.6 (Sep 1, 2020)
Bug Fix macOS: App(visible=False) has been fixed (GH652).
Bug Fix macOS: The regression with Book.fullname that was introduced with 0.20.1 has been fixed (GH1390).
Bug Fix Windows: The retry mechanism has been improved (GH1398).
v0.20.5 (Aug 27, 2020)
Bug Fix The conda version check was failing with spaces in the installation path (GH1396).
Bug Fix Windows: when running app.quit(), the application is now properly closed without leaving a zombie process behind (GH1397).
v0.20.4 (Aug 20, 2020)
Enhancement The add-in can now optionally be installed without the password protection: xlwingsaddininstall--unprotected (GH1392).
v0.20.3 (Aug 15, 2020)
Bug Fix The conda version check was erroneously triggered when importing UDFs on systems without conda. (GH1389).
v0.20.2 (Aug 13, 2020)
PROFeature: Code can now be embedded by calling the new xlwingscodeembed[--file] CLI command (GH1380).
Bug Fix Made the import UDFs functionality more robust to prevent an Automation 440 error that some users would see (GH1381).
Enhancement The standalone Excel file now includes all VBA dependencies to make it work on Windows and macOS (GH1349).
Enhancement xlwings now blocks the call if the Conda Path/Env settings are used with legacy Conda installations (GH1384).
v0.20.1 (Aug 7, 2020)
Bug Fix macOS: password-protected sheets caused an alert when calling xw.Book (GH1377).
Bug Fix macOS: calling wb.save('newname.xlsx') wasn’t updating the wb object properly and caused an alert (GH1129 and GH626 and GH957).
Enhancementxlwings.view() now becomes the active window, making it easier to work with in interactive workflows (please speak up if you feel differently) (GH1353).
Bug Fix The UDF server has received a serious upgrade by njwhite, getting rid of the many issues that were around with using a combination of async functions and legacy dynamic arrays. You can now also call functions defined via asyncdef, although for the time being they are still called synchronously from Excel (GH1010 and GH1164).
v0.19.5 (Jul 5, 2020)
Enhancement When you install the add-in via xlwingsaddininstall, it autoconfigures the add-in if it can’t find an existing user config file (GH1322).
Feature New xlwingsconfigcreate[--force] command that autogenerates the user config file with the Python settings from which you run the command. Can be used to reset the add-in settings with the --force option (GH1322).
Feature: There is a new option to show/hide the console window. Note that with CondaPath and CondaEnv set, the console always pops up when using the UDF server. Currently only available on Windows (GH1182).
Enhancement The Interpreter setting has been deprecated in favor of platform-specific settings: Interpreter_Win and Interpreter_Mac, respectively. This allows you to use the sheet config unchanged on both platforms (GH1345).
Enhancement On macOS, you can now use a few environment-like variables in your settings: $HOME, $APPLICATIONS, $DOCUMENTS, $DESKTOP (GH615).
Bug Fix: Async functions sometimes caused an error on older Excel versions without dynamic arrays (GH1341).
v0.19.4 (May 20, 2020)
Featurexlwingsaddininstall is now available on macOS. On Windows, it has been fixed so it should now work reliably (GH704).
Bug Fix Fixed a dllloadfailed issue with pywin32 when installed via pip on Python 3.8 (GH1315).
v0.19.3 (May 19, 2020)
PROFeature: Added possibility to create deployment keys.
Bug Fix Sometimes, xlwings would show an error of a previous run. Moreover, 0.16.2 introduced an issue that would
not show errors at all on non-conda setups (GH1158 and GH1206)
Enhancement The xlwings CLI now prints the version number (GH1200)
Breaking Change
LOGFILE has been retired and removed from the configuration/add-in.
v0.16.2 (Dec 5, 2019)
Bug FixRunPython can now be called in parallel from different Excel instances (GH1196).
v0.16.1 (Dec 1, 2019)
Enhancementxlwings.Book() and myapp.books.open() now accept parameters like
update_links, password etc. (GH1189).
Bug FixCondaEnv now works correctly with base for UDFs, too (GH1110).
Bug FixCondaBase now allows spaces in the path (GH1176).
Enhancement The UDF server timeout has been increased to 2 minutes (GH1168).
v0.16.0 (Oct 13, 2019)
This release adds a small but very powerful feature: There’s a new Runmain button in the add-in.
With that, you can run your Python scripts from standard xlsx files - no need to save your workbook
as macro-enabled anymore!
The only condition to make that work is that your Python script has the same name as your workbook and that it contains
a function called main, which will be called when you click the Run button. All settings from your config file or
config sheet are still respected, so this will work even if you have the source file in a different directory
than your workbook (as long as that directory is added to the PYTHONPATH in your config).
The xlwingsquickstartmyproject has been updated accordingly. It still produces an xlsm file at the moment
but you can save it as xlsx file if you intend to run it via the new Run button.
v0.15.10 (Aug 31, 2019)
Bug Fix Fixed a Python 2.7 incompatibility introduced with 0.15.9.
v0.15.9 (Aug 31, 2019)
Enhancement The sql extension now uses the native dynamic arrays if available (GH1138).
Enhancement xlwings now support Path objects from pathlib for all file paths (GH1126).
Bug Fix Fixed an issue introduced with the previous release that always showed the command prompt when running UDFs,
not just when using conda envs (GH1098).
v0.15.7 (May 5, 2019)
Bug FixCondaBase and CondaEnv weren’t stored correctly in the config file from the ribbon (GH1090).
Bug Fix UDFs now work correctly with CondaBase and CondaEnv. Note, however, that currently there is no
way to hide the command prompt in that configuration (GH1090).
EnhancementRestartUDFServer 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).
EnhancementImportFunctions 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 FixRunFronzenPython 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","arg1arg2" (GH1063).
v0.15.5 (Mar 25, 2019)
Enhancementwb.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: exportINSTALL_ON_LINUX=1;pipinstallxlwings (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 xlwingsrunpythoninstall 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.exearg1arg2" (GH588).
Breaking Change
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)
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:
importxlwingsasxwimporttime@xw.func(async_mode='threading')defmyfunction(a):time.sleep(5)# long running tasksreturna
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 Change
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 Change
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 xlwingsquickstart:
[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 wouldn’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 Making use of Environment Variables (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):
[Mac] On Mac Excel 2016, the ribbon now only shows the available functionality (GH723):
[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 been created/edited manually, see Making use of Environment Variables (GH714).
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)
Bug Fix: The sql extension installs now correctly (GH695)
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
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
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 Change
RunFrozenPython now requires the full path to the executable.
The xlwings CLI xlwingstemplate functionality has been removed. Use quickstart instead.
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
Check where xlwings is currently installed
>>> importxlwings>>> xlwings.__path__
If you installed xlwings with pip, for once, you should first uninstall xlwings: pipuninstallxlwings
Check the directory that you got under 1): if there are any files left over, delete the xlwings folder and the
remaining files manually
Install the latest xlwings version: pipinstallxlwings
Verify that you have >= 0.11 by doing
>>> importxlwings>>> xlwings.__version__
Install the add-in
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: xlwingsaddinremove.
Close Excel. Run xlwingsaddininstall 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 Exceldefaultlocation:UserStartUp. Restart Excel and you should see the add-in.
Upgrade existing workbooks
Make a backup of your Excel file
Open the file and go to the VBA Editor (Alt-F11)
Remove the xlwings VBA module
Add a reference to the xlwings addin, see Installation
Note: To import UDFs, you need to have the reference to the xlwings add-in set!
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):
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:
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).
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:unknownencoding:mbcs (GH544)
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:
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:
See here
for details about which bugs have been fixed.
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:
# 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:
[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:
[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).
[Mac]: xlwingsrunpythoninstall 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:
Range object:
>>> importxlwingsasxw>>> importpandasaspd>>> wb=xw.Workbook()>>> df=xw.Range('A1:D5').options(pd.DataFrame,header=2).value>>> df a b c d eix10 1 2 320 4 5 630 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)defmyfunction(x):# x is a DataFrame, do something with itreturnx
All these options work the same with decorators for UDFs, e.g. for transpose:
@xw.arg('x',transpose=True)@xw.ret(transpose=True)defmyfunction(x):# x will be returned unchanged as transposed both when reading and writingreturnx
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:
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):
importpandasaspd# unchanged behaviourRange('A1').value=pd.Series([1,2,3])# Changed behaviour: This will print a header row in Excels=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.
Quickstart: It’s now easier than ever to start a new xlwings project, simply use the command line client (GH306):
xlwingsquickstartmyproject will produce a folder with the following files, ready to be used (see Command Line Client (CLI)):
myproject|--myproject.xlsm|--myproject.py
New documentation about how to use xlwings with other languages like 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 xlwingsaddinupdate.
[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.
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:
fromxlwingsimportxlfunc@xlfuncdefdouble_sum(x,y):"""Returns twice the sum of the two arguments"""return2*(x+y)
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:
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')
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 Filenameisalreadyopen... 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.
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 ControlPanel>ProgramsandFeatures 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).
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:
Newly included Excel template which includes the xlwings VBA module and boilerplate code. This is currently
accessible from an interactive interpreter session only:
[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.
New method xlwings.Workbook.set_mock_caller() (GH129). This makes calling files from both
Excel and Python much easier:
importosfromxlwingsimportWorkbook,Rangedefmy_macro():wb=Workbook.caller()Range('A1').value=1if__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
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.
[Mac]: The VBA module finds now automatically the default Python installation as per PATH variable on
.bash_profile when PYTHON_MAC="" (the default in the VBA settings) (GH95).
The VBA error pop-up can now be muted by setting SHOW_LOG=False in the VBA settings. To be used with
care, but it can be useful on Mac, as the pop-up window is currently showing printed log messages even if no error
occurred(GH94).
Bug Fixes
[Mac]: Environment variables from .bash_profile are now available when called from VBA, e.g. by using:
os.environ['USERNAME'] (GH95)
>>> 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.index4
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):
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()foriinSheet.all()]['sheet1', 'sheet2']>>> [i.autofit()foriinSheet.all()]
Bug Fixes
xlwings works now also with NumPy < 1.7.0. Before, doing something like Range('A1').value='Foo' was causing
a NotImplementedError:Notimplementedforthistype 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):
New Sheet class: The new class handles everything directly related to a Sheet. See the Python API section about
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).
# Autofit column ARange('A:A').autofit()# Autofit row 1Range('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.
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):
[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:
Calling Python from within Excel VBA is now also supported on Mac, i.e. Python functions can be called like
this: RunPython("importbar;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).valuearray([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.
Excel constants have been added under their original Excel name, but categorized under their enum (GH18),
e.g.:
# Extra long versionimportxlwingsasxlxl.constants.ChartType.xlArea# Long versionfromxlwingsimportconstantsconstants.ChartType.xlArea# Short versionfromxlwingsimportChartTypeChartType.xlArea
Slightly enhanced Chart support to control the ChartType (GH1):
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).
xlwings (Open Source) is a distributed under a BSD-3-Clause license. xlwings (Open Source) includes all files in the xlwings package except the pro folder, i.e., the xlwings.pro subpackage.
Unless stated in the specific source file, this work is
Copyright (c) 1996-2008, Greg Stein and Mark Hammond.
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 names of Greg Stein, Mark Hammond nor the name of 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 REGENTS 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.
win32 subpackage
Unless stated in the specfic source file, this work is
Copyright (c) 1994-2008, Mark Hammond
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 name of Mark Hammond nor the name of 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 REGENTS 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.
Pythonwin subpackage
Unless stated in the specfic source file, this work is
Copyright (c) 1994-2008, Mark Hammond
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 name of Mark Hammond nor the name of 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 REGENTS 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.
Copyright (c) 2009, Jay Loden, Dave Daeschler, Giampaolo Rodola’
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 psutil authors 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 OWNER 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.
Copyright: Copyright (c) 2007 Apple Inc. All Rights Reserved.
Disclaimer: IMPORTANT: This Apple software is supplied to you by Apple Inc.
(“Apple”) in consideration of your agreement to the following
terms, and your use, installation, modification or
redistribution of this Apple software constitutes acceptance of
these terms. If you do not agree with these terms, please do
not use, install, modify or redistribute this Apple software.
In consideration of your agreement to abide by the following
terms, and subject to these terms, Apple grants you a personal,
non-exclusive license, under Apple’s copyrights in this
original Apple software (the “Apple Software”), to use,
reproduce, modify and redistribute the Apple Software, with or
without modifications, in source and/or binary forms; provided
that if you redistribute the Apple Software in its entirety and
without modifications, you must retain this notice and the
following text and disclaimers in all such redistributions of
the Apple Software. Neither the name, trademarks, service marks
or logos of Apple Inc. may be used to endorse or promote
products derived from the Apple Software without specific prior
written permission from Apple. Except as expressly stated in
this notice, no other rights or licenses, express or implied,
are granted by Apple herein, including but not limited to any
patent rights that may be infringed by your derivative works or
by other works in which the Apple Software may be incorporated.
The Apple Software is provided by Apple on an “AS IS” basis.
APPLE MAKES NO WARRANTIES, EXPRESS OR IMPLIED, INCLUDING
WITHOUT LIMITATION THE IMPLIED WARRANTIES OF NON-INFRINGEMENT,
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, REGARDING
THE APPLE SOFTWARE OR ITS USE AND OPERATION ALONE OR IN
COMBINATION WITH YOUR PRODUCTS.
IN NO EVENT SHALL APPLE BE LIABLE FOR ANY SPECIAL, INDIRECT,
INCIDENTAL OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) ARISING IN ANY WAY
OUT OF THE USE, REPRODUCTION, MODIFICATION AND/OR DISTRIBUTION
OF THE APPLE SOFTWARE, HOWEVER CAUSED AND WHETHER UNDER THEORY
OF CONTRACT, TORT (INCLUDING NEGLIGENCE), STRICT LIABILITY OR
OTHERWISE, EVEN IF APPLE HAS BEEN ADVISED OF THE POSSIBILITY OF
SUCH DAMAGE.
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 creator 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.
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the “Software”), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the “Software”), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
Copyright (c) 2017, 2018, 2019, 2020, 2021, 2022 Samuel Colvin
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the “Software”), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
Copyright (c) Microsoft Corporation.
All rights reserved.
This code is licensed under the MIT License.
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files(the “Software”), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and / or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions :
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
Loads the selected cell(s) of the active workbook into a pandas DataFrame. If you
select a single cell that has adjacent cells, the range is auto-expanded (via
current region) and turned into a pandas DataFrame. If you don’t have pandas
installed, it returns the values as nested lists.
Note
Only use this in an interactive context like e.g. a Jupyter notebook! Don’t use
this in a script as it depends on the active book.
Parameters
index (bool or int, default 1) – Defines the number of columns on the left that will be turned into the
DataFrame’s index
header (bool or int, default 1) – Defines the number of rows at the top that will be turned into the DataFrame’s
columns
chunksize (int, default 5000) – Chunks the loading of big arrays.
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.
Note
Only use this in an interactive context like e.g., a Jupyter notebook! Don’t use
this in a script as it depends on the active book.
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.
table (bool, default True) – If your object is a pandas DataFrame, by default it is formatted as an Excel
Table
chunksize (int, default 5000) – Chunks the loading of big arrays.
An app corresponds to an Excel instance and should normally be used as context
manager to make sure that everything is properly cleaned up again and to prevent
zombie processes. New Excel instances can be fired up like so:
>>> xw.appsApps([<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/MicrosoftOffice2011/MicrosoftExcel or
/Applications/MicrosoftExcel
On Windows, if you want to change the version of Excel that xlwings talks to, go
to ControlPanel>ProgramsandFeatures 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.
This corresponds to MsgBox in VBA, shows an alert/message box and returns
the value of the pressed button. For the remote interpreter, instead of
returning a value, the function accepts the name of a callback to which it will
supply the value of the pressed button.
Parameters
prompt (str, default None) – The message to be displayed.
title (str, default None) – The title of the alert.
buttons (str, default "ok") – Can be either "ok", "ok_cancel", "yes_no", or
"yes_no_cancel".
mode (str, default None) – Can be "info" or "critical". Not supported by Google Sheets.
callback (str, default None) – Only used by the remote interpreter: you can provide the name of a
function that will be called with the value of the pressed button as
argument. The function has to exist on the client side, i.e., in VBA or
JavaScript.
Returns
button_value – Returns None when used with the remote interpreter, otherwise the value
of the pressed button in lowercase: "ok", "cancel", "yes",
"no".
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.
True if Excel is in interactive mode. If you set this property to False,
Excel blocks all input from the keyboard and mouse (except input to dialog boxes
that are displayed by your code). Read/write Boolean.
NOTE: Not supported on macOS.
Context manager that allows you to easily change the app’s properties
temporarily. Once the code leaves the with block, the properties are changed
back to their previous state.
Note: Must be used as context manager or else will have no effect. Also, you can
only use app properties that you can both read and write.
Examples
importxlwingsasxwapp=App()# Sets app.display_alerts = Falsewithapp.properties(display_alerts=False):# do stuff# Sets app.calculation = 'manual' and app.enable_events = Truewithapp.properties(calculation='manual',enable_events=True):# do stuff# Makes sure the status bar is reset even if an error happens in the with blockwithapp.properties(status_bar='Calculating...'):# do stuff
Writes the values of all key word arguments to the output file according to
the template and the variables contained in there (Jinja variable syntax).
Following variable types are supported:
template (str or path-like object) – Path to your Excel template, e.g. r'C:\Path\to\my_template.xlsx'
output (str or path-like object) – Path to your Report, e.g. r'C:\Path\to\my_report.xlsx'
book_settings (dict, default None) – A dictionary of xlwings.Book parameters, for details see:
xlwings.Book.
For example: book_settings={'update_links':False}.
data (kwargs) – All key/value pairs that are used in the template.
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.
>>> importxlwingsasxw>>> xw.books# active appBooks([<Book [Book1]>, <Book [Book2]>])>>> xw.apps[10559].books# specific app, get the PIDs via xw.apps.keys()Books([<Book [Book1]>, <Book [Book2]>])
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 or path-like object) – 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.
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 xw.apps[10559] (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 or path-like object, 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.
update_links (bool, default None) – If this argument is omitted, the user is prompted to specify how links will be
updated
read_only (bool, default False) – True to open workbook in read-only mode
format (str) – If opening a text file, this specifies the delimiter character
password (str) – Password to open a protected workbook
write_res_password (str) – Password to write to a write-reserved workbook
ignore_read_only_recommended (bool, default False) – Set to True to mute the read-only recommended message
origin (int) – For text files only. Specifies where it originated. Use Platform constants.
delimiter (str) – If format argument is 6, this specifies the delimiter.
editable (bool, default False) – This option is only for legacy Microsoft Excel 4.0 addins.
notify (bool, default False) – Notify the user when a file becomes available If the file cannot be opened in
read/write mode.
converter (int) – The index of the first file converter to try when opening the file.
add_to_mru (bool, default False) – Add this workbook to the list of recently added workbooks.
local (bool, default False) – If True, saves files against the language of Excel, otherwise against the
language of VBA. Not supported on macOS.
corrupt_load (int, default xlNormalLoad) – Can be one of xlNormalLoad, xlRepairFile or xlExtractData.
Not supported on macOS.
json (dict) –
A JSON object as delivered by the MS Office Scripts or Google Apps Script
xlwings module but in a deserialized form, i.e., as dictionary.
New in version 0.26.0.
mode (str, default None) –
Either "i" (interactive (default)) or "r" (read). In interactive mode,
xlwings opens the workbook in Excel, i.e., Excel needs to be installed. In read
mode, xlwings reads from the file directly, without requiring Excel to be
installed. Read mode requires xlwings PRO.
Returns a JSON serializable object as expected by the MS Office Scripts or
Google Apps Script xlwings module. Only available with book objects that have
been instantiated via xw.Book(json=...).
Saves the Workbook. If a path is provided, this works like SaveAs() in
Excel. If no path is specified and if the file hasn’t been saved previously,
it’s saved in the current working directory with the current filename.
Existing files are overwritten without prompting. To change the file type,
provide the appropriate extension, e.g. to save myfile.xlsx in the xlsb
format, provide myfile.xlsb as path.
Parameters
path (str or path-like object, default None) – Path where you want to save the Book.
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 directlyimportosimportxlwingsasxwdefmy_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()
Exports the whole Excel workbook or a subset of the sheets to a PDF file.
If you want to print hidden sheets, you will need to list them explicitely
under include.
Parameters
path (str or path-like object, default None) – Path to the PDF file, defaults to the same name as the workbook, in the same
directory. For unsaved workbooks, it defaults to the current working
directory instead.
include (int or str or list, default None) – Which sheets to include: provide a selection of sheets in the form of sheet
indices (1-based like in Excel) or sheet names. Can be an int/str for a
single sheet or a list of int/str for multiple sheets.
exclude (int or str or list, default None) – Which sheets to exclude: provide a selection of sheets in the form of sheet
indices (1-based like in Excel) or sheet names. Can be an int/str for a
single sheet or a list of int/str for multiple sheets.
layout (str or path-like object, default None) –
This argument requires xlwings PRO.
Path to a PDF file on which the report will be printed. This is ideal for
headers and footers as well as borderless printing of graphics/artwork. The
PDF file either needs to have only 1 page (every report page uses the same
layout) or otherwise needs the same amount of pages as the report (each
report page is printed on the respective page in the layout PDF).
New in version 0.24.3.
exclude_start_string (str, default '#') –
Sheet names that start with this character/string will not be printed.
New in version 0.24.4.
show (bool, default False) –
Once created, open the PDF file with the default application.
New in version 0.24.6.
quality (str, default 'standard') –
Quality of the PDF file. Can either be 'standard' or 'minimum'.
Copy a sheet to the current or a new Book. By default, it places the copied
sheet after all existing sheets in the current Book. Returns the copied sheet.
New in version 0.22.0.
Parameters
before (sheet object, default None) – The sheet object before which you want to place the sheet
after (sheet object, default None) – The sheet object after which you want to place the sheet,
by default it is placed after all existing sheets
name (str, default None) – The sheet name of the copy
# Create two books and add a value to the first sheet of the first bookfirst_book=xw.Book()second_book=xw.Book()first_book.sheets[0]['A1'].value='some value'# Copy to same Book with the default location and namefirst_book.sheets[0].copy()# Copy to same Book with custom sheet namefirst_book.sheets[0].copy(name='copied')# Copy to second Book requires to use before or afterfirst_book.sheets[0].copy(after=second_book.sheets[0])
path (str or path-like object, default None) – Path to the PDF file, defaults to the name of the sheet in the same
directory of the workbook. For unsaved workbooks, it defaults to the current
working directory instead.
layout (str or path-like object, default None) –
This argument requires xlwings PRO.
Path to a PDF file on which the report will be printed. This is ideal for
headers and footers as well as borderless printing of graphics/artwork. The
PDF file either needs to have only 1 page (every report page uses the same
layout) or otherwise needs the same amount of pages as the report (each
report page is printed on the respective page in the layout PDF).
New in version 0.24.3.
show (bool, default False) –
Once created, open the PDF file with the default application.
New in version 0.24.6.
quality (str, default 'standard') –
Quality of the PDF file. Can either be 'standard' or 'minimum'.
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
importxlwingsasxwsheet1=xw.Book("MyBook.xlsx").sheets[0]sheet1.range("A1")sheet1.range("A1:C3")sheet1.range((1,1))sheet1.range((1,1),(3,3))sheet1.range("NamedRange")# Or using index/slice notationsheet1["A1"]sheet1["A1:C3"]sheet1[0,0]sheet1[0:4,0:4]sheet1["NamedRange"]
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.’
Gets and sets the background color of the specified Range.
To set the color, either use an RGB tuple (0,0,0) or a hex string
like #efefef or an Excel color constant.
To remove the background, set the color to None, see Examples.
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.
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 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') –
shift (str, default None) – Use right or down. If omitted, Excel decides based on the shape of
the range.
copy_origin (str, default format_from_left_or_above) – Use format_from_left_or_above or format_from_right_or_below.
Note that this is not supported on macOS.
Returns a Range object that represents the merged Range containing the
specified cell. If the specified cell isn’t in a merged range, this property
returns the specified cell.
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
expand (str, default None) – One of 'table', 'down', 'right'
chunksize (int) – Use a chunksize, e.g. 10000 to prevent timeout or memory issues when
reading or writing large amounts of data. Works with all formats, including
DataFrames, NumPy arrays, and list of lists.
err_to_str (Boolean, default False) –
If True, will include cell errors such as #N/A as strings. By
default, they will be converted to None.
Gets and sets the values directly as delivered from/accepted by the engine that
s 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.
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.
path (str or path-like, default None) – Path where you want to store the pdf. Defaults to the address of the range
in the same directory as the Excel file if the Excel file is stored and to
the current working directory otherwise.
layout (str or path-like object, default None) –
This argument requires xlwings PRO.
Path to a PDF file on which the report will be printed. This is ideal for
headers and footers as well as borderless printing of graphics/artwork. The
PDF file either needs to have only 1 page (every report page uses the same
layout) or otherwise needs the same amount of pages as the report (each
report page is printed on the respective page in the layout PDF).
show (bool, default False) – Once created, open the PDF file with the default application.
quality (str, default 'standard') – Quality of the PDF file. Can either be 'standard' or 'minimum'.
path (str or path-like, default None) – Path where you want to store the picture. Defaults to the name of the range
in the same directory as the Excel file if the Excel file is stored and to
the current working directory otherwise.
Gets and sets the values for the given Range. See xlwings.Range.options()
about how to set options, e.g., to transform it into a DataFrame or how to set
a chunksize.
Returns True if the wrap_text property is enabled and False if it’s
disabled. If not all cells have the same value in a range, on Windows it returns
None and on macOS False.
Represents the rows of a range. Do not construct this class directly, use
Range.rows instead.
Example
importxlwingsasxwwb=xw.Book("MyBook.xlsx")sheet1=wb.sheets[0]myrange=sheet1.range('A1:C4')assertlen(myrange.rows)==4# or myrange.rows.countmyrange.rows[0].value='a'assertmyrange.rows[2]==sheet1.range('A3:C3')assertmyrange.rows(2)==sheet1.range('A2:C2')forrinmyrange.rows:print(r.address)
Represents the columns of a range. Do not construct this class directly, use
Range.columns instead.
Example
importxlwingsasxwwb=xw.Book("MyFile.xlsx")sheet1=wb.sheets[0]myrange=sheet1.range('A1:C4')assertlen(myrange.columns)==3# or myrange.columns.countmyrange.columns[0].value='a'assertmyrange.columns[2]==sheet1.range('C1:C4')assertmyrange.columns(2)==sheet1.range('B1:B4')forcinmyrange.columns:print(c.address)
path (str or path-like, default None) – Path where you want to store the pdf. Defaults to the name of the chart in
the same directory as the Excel file if the Excel file is stored and to the
current working directory otherwise.
show (bool, default False) – Once created, open the PDF file with the default application.
quality (str, default 'standard') – Quality of the PDF file. Can either be 'standard' or 'minimum'.
path (str or path-like, default None) – Path where you want to store the picture. Defaults to the name of the chart
in the same directory as the Excel file if the Excel file is stored and to
the current working directory otherwise.
image (str or path-like object or matplotlib.figure.Figure) – Either a filepath or a Matplotlib figure object.
left (float, default None) – Left position in points, defaults to 0. If you use top/left, you
must not provide a value for anchor.
top (float, default None) – Top position in points, defaults to 0. If you use top/left,
you must not provide a value for anchor.
width (float, default None) – Width in points. Defaults to original width.
height (float, default None) – Height in points. Defaults to original height.
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.
scale (float, default None) – Scales your picture by the provided factor.
format (str, default None) – Only used if image is a Matplotlib or Plotly plot. By default, the plot is
inserted in the “png” format, but you may want to change this to a
vector-based format like “svg” on Windows (may require Microsoft 365) or
“eps” on macOS for better print quality. If you use 'vector', it will be
using 'svg' on Windows and 'eps' on macOS. To find out which formats
your version of Excel supports, see:
https://support.microsoft.com/en-us/topic/support-for-eps-images-has-been-turned-off-in-office-a069d664-4bcf-415e-a1b5-cbb0c334a840
anchor (xw.Range, default None) –
The xlwings Range object of where you want to insert the picture. If you use
anchor, you must not provide values for top/left.
New in version 0.24.3.
export_options (dict, default None) –
For Matplotlib plots, this dictionary is passed on to image.savefig()
with the following defaults: {"bbox_inches":"tight","dpi":200}, so
if you want to leave the picture uncropped and increase dpi to 300, use:
export_options={"dpi":300}. For Plotly, the options are passed to
write_image().
>>> importxlwingsasxw>>> book=xw.books['Book1']# book scope and sheet scope>>> book.names[<Name 'MyName': =Sheet1!$A$3>]>>> book.sheets[0].names# sheet scope only
source (xlwings range, default None) – An xlwings range object, representing the data source.
name (str, default None) – The name of the Table. By default, it uses the autogenerated name that is
assigned by Excel.
source_type (str, default None) – This currently defaults to xlSrcRange, i.e. expects an xlwings range
object. No other options are allowed at the moment.
link_source (bool, default None) – Currently not implemented as this is only in case source_type is
xlSrcExternal.
has_headers (bool or str, default True) – Indicates whether the data being imported has column labels. Defaults to
True. Possible values: True, FAlse, 'guess'
destination (xlwings range, default None) – Currently not implemented as this is used in case source_type is
xlSrcExternal.
table_style_name (str, default 'TableStyleMedium2') – Possible strings: 'TableStyleLightN'' (where N is 1-21),
'TableStyleMediumN' (where N is 1-28),
‘TableStyleDarkN’` (where N is 1-11)
Returns an xlwings range object representing the row where data is going to
be inserted. This is only available for empty tables, otherwise it’ll return
None
importpandasaspdimportxlwingsasxwsheet=xw.Book('Book1.xlsx').sheets[0]table_name='mytable'# Sample DataFramenrows,ncols=3,3df=pd.DataFrame(data=nrows*[ncols*['test']],columns=['col '+str(i)foriinrange(ncols)])# Hide the index, then insert a new table if it doesn't exist yet,# otherwise update the existing onedf=df.set_index('col 0')iftable_namein[table.namefortableinsheet.tables]:sheet.tables[table_name].update(df)else:mytable=sheet.tables.add(source=sheet['A1'],name=table_name).update(df)
1-14 represent built-in categories, for user-defined categories use strings
New in version 0.10.3.
volatilebool, 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_wizardbool, default True
Set to False to suppress the function call in the function wizard.
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.
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:
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 pipinstallFlask):
xlwingsrestapirun
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):
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.
xlwingsrestapirun 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:
setFLASK_APP=xlwings.rest.apiflaskrun
If you are on Mac, use exportFLASK_APP=xlwings.rest.api instead of setFLASK_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: gunicornxlwings.rest.api:api. Or with waitress (adjust the host accordingly if
you want to make the api accessible from outside of localhost):
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.