v0.6.4 (January 6, 2016)¶
Quickstart: It’s now easier than ever to start a new xlwings project, simply use the commmand line client (GH306):
xlwings quickstart myprojectwill produce a folder with the following files, ready to be used (see Command Line Client):
myproject |--myproject.xlsm |--myproject.py
New documentation about how to use xlwings with other languages like R and Julia, see xlwings with R and Julia.
- [Win]: Importing UDFs with the add-in was throwing an error if the filename was including characters like spaces or dashes (GH331).
To fix this, close Excel completely and run
xlwings addin update.
Workbook.caller()is now also accessible within functions that are decorated with
@xlfunc. Previously, it was only available with functions that used the
- Writing a Pandas DataFrame failed in case the index was named the same as a column (GH334).
v0.6.3 (December 18, 2015)¶
v0.6.2 (December 15, 2015)¶
- LOG_FILE: So far, the log file has been placed next to the Excel file per default (VBA settings). This has been changed as it was
causing issues for files on SharePoint/OneDrive and Mac Excel 2016: The place where
LOG_FILE = ""refers to depends on the OS and the Excel version, see LOG_FILE default locations.
- [Mac]: This version adds support for the VBA module on Mac Excel 2016 (i.e. the
RunPythoncommand) and is now feature equivalent with Mac Excel 2011 (GH206).
v0.6.1 (December 4, 2015)¶
v0.6.0 (November 30, 2015)¶
User Defined Functions (UDFs) - currently Windows only
The ExcelPython project has been fully merged into xlwings. This means that on Windows, UDF’s are now supported via decorator syntax. A simple example:
from xlwings import xlfunc @xlfunc def double_sum(x, y): """Returns twice the sum of the two arguments""" return 2 * (x + y)
For array formulas with or without NumPy, see the docs: User Defined Functions (UDFs)
Command Line Client
The new xlwings command line client makes it easy to work with the xlwings template and the developer add-in (the add-in is currently Windows-only). E.g. to create a new Excel spreadsheet from the template, run:
xlwings template open
For all commands, see the docs: Command Line Client
v0.5.0 (November 10, 2015)¶
This version adds support for Matplotlib! Matplotlib figures can be shown in Excel as pictures in just 2 lines of code:
- Get a matplotlib
via PyPlot interface:
import matplotlib.pyplot as plt fig = plt.figure() plt.plot([1, 2, 3, 4, 5])
via object oriented interface:
from matplotlib.figure import Figure fig = Figure(figsize=(8, 6)) ax = fig.add_subplot(111) ax.plot([1, 2, 3, 4, 5])
import pandas as pd import numpy as np df = pd.DataFrame(np.random.rand(10, 4), columns=['a', 'b', 'c', 'd']) ax = df.plot(kind='bar') fig = ax.get_figure()
Show it in Excel as picture:
plot = Plot(fig) plot.show('Plot1')
Sheet.addwas not always acting on the correct workbook (GH287)
- Iteration over a
Rangeonly 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)¶
This release makes it easier than ever to connect to Excel from Python! In addition to the existing ways, you can now connect to the active Workbook (on Windows across all instances) and if the Workbook is already open, it’s good enough to refer to it by name (instead of having to use the full path). Accordingly, this is how you make a connection to… (GH30 and GH226):
- a new workbook:
wb = Workbook()
- the active workbook [New!]:
wb = Workbook.active()
- an unsaved workbook:
wb = Workbook('Book1')
- a saved (open) workbook by name (incl. xlsx etc.) [New!]:
wb = Workbook('MyWorkbook.xlsx')
- a saved (open or closed) workbook by path:
wb = Workbook(r'C:\\path\\to\\file.xlsx')
Also, there are some new docs:
v0.4.0 (September 13, 2015)¶
The most important update with this release was made on Windows: The methodology used to make a connection
to Workbooks has been completely replaced. This finally allows xlwings to reliably connect to multiple instances of
Excel even if the Workbooks are opened from untrusted locations (network drives or files downloaded from the internet).
This gets rid of the dreaded
Filename is already open... error message that was sometimes shown in this
context. It also allows the VBA hooks (
RunPython) to work correctly if the very same file is opened in various instances of
Note that you will need to update the VBA module and that apart from
pywin32 there is now a new dependency for the
comtypes. It should be installed automatically though when installing/upgrading xlwings with
Added support to manipulate named Ranges (GH92):
>>> wb = Workbook() >>> Range('A1').name = 'Name1' >>> Range('A1').name >>> 'Name1' >>> del wb.names['Name1']
Rangenow also accepts
Sheetobjects, the following 3 ways are hence all valid (GH92):
r = Range(1, 'A1') r = Range('Sheet1', 'A1') sheet1 = Sheet(1) r = Range(sheet1, 'A1')
[Win]: Error pop-ups show now the full error message that can also be copied with
v0.3.6 (July 14, 2015)¶
Application as attribute of a
Workbook has been removed (
wb is a
|Correct Syntax (as before)||Removed|
Excel 2016 for Mac Support (GH170)
Excel 2016 for Mac is finally supported (Python side). The VBA hooks (
RunPython) are currently not yet supported.
In more details:
This release allows Excel 2011 and Excel 2016 to be installed in parallel.
Workbook()will open the default Excel installation (usually Excel 2016).
The new keyword argument
app_targetallows to connect to a different Excel installation, e.g.:
Workbook(app_target='/Applications/Microsoft Office 2011/Microsoft Excel')
app_targetis only available on Mac. On Windows, if you want to change the version of Excel that xlwings talks to, go to
Control Panel > Programs and Featuresand
Repairthe Office version that you want as default.
RunPythoncalls 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!
- [Win]: When using the
OPTIMIZED_CONNECTIONon 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)¶
Range.autofit(): The integer argument for the axis has been removed (GH186).
Use string arguments
r for autofitting rows and
c for autofitting columns
>>> rng = Range('A1').table >>> rng.row, rng.column (1, 1) >>> rng.last_cell.row, rng.last_cell.column (4, 5)
v0.3.3 (March 8, 2015)¶
quitmethod and properties
calculation(GH101, GH158, GH159). It can be conveniently accessed from within a Workbook (on Windows,
Applicationis instance dependent). A few examples:
>>> from xlwings import Workbook, Calculation >>> wb = Workbook() >>> wb.application.screen_updating = False >>> wb.application.calculation = Calculation.xlCalculationManual >>> wb.application.quit()
New headless mode: The Excel application can be hidden either during
Workbookinstantiation or through the
>>> wb = Workbook(app_visible=False) >>> wb.application.visible False >>> wb.application.visible = True
Newly included Excel template which includes the xlwings VBA module and boilerplate code. This is currently accessible from an interactive interpreter session only:
>>> from xlwings import Workbook >>> Workbook.open_template()
datetime.dateobjects were causing an error (GH44).
- Depending on how it was instantiated, Workbook was sometimes missing the
Range.hyperlinkwas 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)¶
v0.3.1 (January 16, 2015)¶
import os from xlwings import Workbook, Range def my_macro(): wb = Workbook.caller() Range('A1').value = 1 if __name__ == '__main__': # To run from Python, not needed when called from Excel. # Expects the Excel file next to this source file, adjust accordingly. path = os.path.abspath(os.path.join(os.path.dirname(__file__), 'myfile.xlsm')) Workbook.set_mock_caller(path) my_macro()
simulationexample on the homepage works now also on Mac.
- [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)¶
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.
This version adds two exciting but still experimental features from ExcelPython (Windows only!):
- Optimized connection: Set the
OPTIMIZED_CONNECTION = Truein 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.exeprocess is restarted by killing it manually in the Windows Task Manager. The suggested workflow is hence to set
OPTIMIZED_CONNECTION = Falsefor development and only set it to
Truefor 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.
Further enhancements include:
lenbuilt-in function can now be used on
>>> len(Range('A1:B5')) 5
Rangeobject is now iterable (GH108):
for cell in Range('A1:B2'): if cell.value < 2: cell.color = (255, 0, 0)
[Mac]: The VBA module finds now automatically the default Python installation as per
PYTHON_MAC = ""(the default in the VBA settings) (GH95).
The VBA error pop-up can now be muted by setting
SHOW_LOG = Falsein 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).
v0.2.3 (October 17, 2014)¶
>>> Sheet.add() # Place at end with default name >>> Sheet.add('NewSheet', before='Sheet1') # Include name and position >>> new_sheet = Sheet.add(after=3) >>> new_sheet.index 4
>>> Sheet.count() 3
autofit()works now also on
Sheetobjects, not only on
>>> Sheet(1).autofit() # autofit columns and rows >>> Sheet('Sheet1').autofit('c') # autofit columns
>>> Range('A1').number_format 'General' >>> Range('A1:C3').number_format = '0.00%' >>> Range('A1:C3').number_format '0.00%'
Works also with the
>>> Range('A1').value = [1,2,3,4,5] >>> Range('A1').table.number_format = '0.00%'
>>> Range((1,1)).get_address() '$A$1' >>> Range((1,1)).get_address(False, False) 'A1' >>> Range('Sheet1', (1,1), (3,3)).get_address(True, False, include_sheetname=True) 'Sheet1!A$1:C$3' >>> Range('Sheet1', (1,1), (3,3)).get_address(True, False, external=True) '[Workbook1]Sheet1!A$1:C$3'
Sheet.all()returning a list with all Sheet objects:
>>> Sheet.all() [<Sheet 'Sheet1' of Workbook 'Book1'>, <Sheet 'Sheet2' of Workbook 'Book1'>] >>> [i.name.lower() for i in Sheet.all()] ['sheet1', 'sheet2'] >>> [i.autofit() for i in Sheet.all()]
- xlwings works now also with NumPy < 1.7.0. Before, doing something like
Range('A1').value = 'Foo'was causing a
NotImplementedError: Not implemented for this typeerror 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)¶
Workbookqualification changed: It now has to be specified as keyword argument. Assume we have instantiated two Workbooks like so:
wb1 = Workbook()and
wb2 = Workbook().
Chartclasses will default to
wb2as it was instantiated last. To target
wb1, use the new
Alternatively, simply set the current Workbook before using the
Through the introduction of the
Sheetclass (see Enhancements), a few methods moved from the
Sheetclass. Assume the current Workbook is:
wb = Workbook():
The syntax to add a new Chart has been slightly changed (it is a class method now):
[Mac]: Python errors are now also shown in a Message Box. This makes the Mac version feature equivalent with the Windows version (GH57):
>>> Sheet(1).name 'Sheet1' >>> Sheet('Sheet1').clear_contents() >>> Sheet.active() <Sheet 'Sheet1' of Workbook 'Book1'>
Rangeclass has a new method
autofit()that autofits the width/height of either columns, rows or both (GH33).
axis : string or integer, default None - To autofit rows, use one of the following: 'rows' or 'r' - To autofit columns, use one of the following: 'columns' or 'c' - To autofit rows and columns, provide no arguments
# Autofit column A Range('A:A').autofit() # Autofit row 1 Range('1:1').autofit() # Autofit columns and rows, taking into account Range('A1:E4') Range('A1:E4').autofit() # AutoFit rows, taking into account Range('A1:E4') Range('A1:E4').autofit('rows')
Workbookclass has the following additional methods:
set_current(). They determine the default Workbook for
Chart. On Windows, in case there are various Excel instances, when creating new or opening existing Workbooks, they are being created in the same instance as the current Workbook.
>>> wb1 = Workbook() >>> wb2 = Workbook() >>> Workbook.current() <Workbook 'Book2'> >>> wb1.set_current() >>> Workbook.current() <Workbook 'Book1'>
Chartobject is instantiated without an existing
Workbookobject, a user-friendly error message is raised (GH58).
atleast_2dkeyword had no effect on Ranges consisting of a single cell and was raising an error when used in combination with the
asarraykeyword. Both have been fixed (GH53):
>>> Range('A1').value = 1 >>> Range('A1', atleast_2d=True).value [[1.0]] >>> Range('A1', atleast_2d=True, asarray=True).value array([[1.]])
[Mac]: After creating two new unsaved Workbooks with
Chartobject would always just access the latest one, even if the Workbook had been specified (GH63).
[Mac]: When xlwings was imported without ever instantiating a
Workbookobject, Excel would start upon quitting the Python interpreter (GH51).
[Mac]: When installing xlwings, it now requires
psutilto be at least version
v0.2.1 (August 7, 2014)¶
All VBA user settings have been reorganized into a section at the top of the VBA xlwings module:
PYTHON_WIN = "" PYTHON_MAC = GetMacDir("Home") & "/anaconda/bin" PYTHON_FROZEN = ThisWorkbook.Path & "\build\exe.win32-2.7" PYTHONPATH = ThisWorkbook.Path LOG_FILE = ThisWorkbook.Path & "\xlwings_log.txt"
Calling Python from within Excel VBA is now also supported on Mac, i.e. Python functions can be called like this:
RunPython("import bar; bar.foo()"). Running frozen executables (
RunFrozenPython) isn’t available yet on Mac though.
Note that there is a slight difference in the way that this functionality behaves on Windows and Mac:
- Windows: After calling the Macro (e.g. by pressing a button), Excel waits until Python is done. In case there’s an error in the Python code, a pop-up message is being shown with the traceback.
- Mac: After calling the Macro, the call returns instantly but Excel’s Status Bar turns into “Running…” during the duration of the Python call. Python errors are currently not shown as a pop-up, but need to be checked in the log file. I.e. if the Status Bar returns to its default (“Ready”) but nothing has happened, check out the log file for the Python traceback.
Special thanks go to Georgi Petrov for helping with this release.
v0.2.0 (July 29, 2014)¶
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.
clear_contentsmethods of the
Workbookobject now default to the active sheet (GH5):
wb = Workbook() wb.clear_contents() # Clears contents of the entire active sheet
v0.1.1 (June 27, 2014)¶
asarray=True, NumPy arrays are now always at least 1d arrays, even in the case of a single cell (GH14):
>>> Range('A1', asarray=True).value array([34.])
Similar to NumPy’s logic, 1d Ranges in Excel, i.e. rows or columns, are now being read in as flat lists or 1d arrays. If you want the same behavior as before, you can use the
tableproperty is also delivering a 1d array/list, if the table Range is really a column or row.
>>> Range('A1').vertical.value [1.0, 2.0, 3.0, 4.0] >>> Range('A1', atleast_2d=True).vertical.value [[1.0], [2.0], [3.0], [4.0]] >>> Range('C1').horizontal.value [1.0, 2.0, 3.0, 4.0] >>> Range('C1', atleast_2d=True).horizontal.value [[1.0, 2.0, 3.0, 4.0]] >>> Range('A1', asarray=True).table.value array([ 1., 2., 3., 4.]) >>> Range('A1', asarray=True, atleast_2d=True).table.value array([[ 1.], [ 2.], [ 3.], [ 4.]])
The single file approach has been dropped. xlwings is now a traditional Python package.
xlwings is now officially suppported on Python 2.6-2.7 and 3.1-3.4
Support for Pandas
Serieshas been added (GH24):
>>> import numpy as np >>> import pandas as pd >>> from xlwings import Workbook, Range >>> wb = Workbook() >>> s = pd.Series([1.1, 3.3, 5., np.nan, 6., 8.]) >>> s 0 1.1 1 3.3 2 5.0 3 NaN 4 6.0 5 8.0 dtype: float64 >>> Range('A1').value = s >>> Range('D1', index=False).value = s
Excel constants have been added under their original Excel name, but categorized under their enum (GH18), e.g.:
# Extra long version import xlwings as xl xl.constants.ChartType.xlArea # Long version from xlwings import constants constants.ChartType.xlArea # Short version from xlwings import ChartType ChartType.xlArea
Slightly enhanced Chart support to control the
>>> from xlwings import Workbook, Range, Chart, ChartType >>> wb = Workbook() >>> Range('A1').value = [['one', 'two'],[10, 20]] >>> my_chart = Chart().add(chart_type=ChartType.xlLine, name='My Chart', source_data=Range('A1').table)
alternatively, the properties can also be set like this:
>>> my_chart = Chart().add() # Existing Charts: my_chart = Chart('My Chart') >>> my_chart.name = 'My Chart' >>> my_chart.chart_type = ChartType.xlLine >>> my_chart.set_source_data(Range('A1').table)
pytzis no longer a dependency as
datetimeobject are now being read in from Excel as time-zone naive (Excel doesn’t know timezones). Before,
datetimeobjects got the UTC timezone attached.
Workbookclass has the following additional methods:
Rangeclass has the following additional methods:
np.nanto 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
nancorrectly in Excel or numbers were being truncated (GH31) & (GH35).
- Installation is now putting all files in the correct place (GH20).
v0.1.0 (March 19, 2014)¶
Initial release of xlwings.