xlOil Python Excel.Application object¶
The Excel.Application object is the root of Excel’s COM interface. If you have used VBA you
will likely have come across it. In xlOil, you can get a reference to this object with
xloil.app
. From there the comtypes or
pywin32
libraries provides syntax similar to VBA to call methods on the object.
The available methods are documented extensively at Excel object model overview and Application Object
COM support can be provided by ‘comtypes’, a newer pure python package or ‘win32com’
a well-established more C++ based library. xloil.app
or the xloil.Worksheet.to_com
method accept a ‘lib’ argument If omitted, the default is ‘comtypes’. The default can
be changed in the XLL’s ini file.
Calling Worksheet Functions and Application.Run¶
In VBA, Application.Run
takes a function name and a variable argument list and attempts
to call the specified user-defined function. In xlOil, use xloil.run
to make the same
call or go via the COM library with xloil.app().Run(...)
. Like all COM calls, they must be
invoked on the main thread.
To call a worksheet function, use xloil.call
. This can also invoke old-style
macro sheet commands.
It must be called from a non-local worksheet function on the main thread. To access a worksheet
function from COM use ``xloil.app().WorksheetFunction.Sum(…)`.
xloil.run
and xloil.call
have async flavours xloil.run_async
and
xloil.call_async
which return a future and can be called from any thread.
Function |
Use |
Call from |
---|---|---|
Calls user-defined functions as per Application.Run |
Main thread |
|
Anywhere |
||
Calls worksheet functions, UDFs or macro sheet commands |
Non-local worksheet function |
|
Anywhere |
Accessing Sheets and Ranges¶
xlOil mirrors a small part of the Excel.Application object model to provide easier access to sheets and ranges. We compare the comtypes syntax with the xlOil syntax.
Reading from a range:
xl = xloil.app()
# Using COM to access a range with empty index
X = xl.Range["A1", "C1"].Value[:]
# X now contains a tuple like (10, "20", 31.4)
# COM alternative syntax, gives Y == X
Y = xl.Range["A1", "C1"].Value[()]
# Using xlOil functions, gives Z == X
Z = xloil.Range("A1:C1").value
Writing to a range:
xl.Range["A1", "C1"].Value[:] = (3, 2, 1)
xl.Range["A1", "C1"].Value[()] = (1, 2, 3)
# Using xlOil syntax
xloil.Range("A1:C1").value = (1, 2, 3)
xlOil supports several other functions to access ranges. The three examples below all refer to the same range.
wb = xloil.active_workbook()
# Specify normal Excel range address
r1 = wb['Sheet1']['B2:D3']
# The range function, like in Excel includes right and left hand ends
r2 = wb['Sheet1'].range(from_row=1, from_col=1, to_row=3, to_col=4)
# The python slice synax follows python conventions so only the
# left hand end is included
r3 = wb['Sheet1'][1:3, 1:4]
The square bracket operator for ranges behaves like numpy arrays in that if
the tuple specifies a single cell, it returns the value in that cell, otherwise
it returns a Range object. To create a range consisting of a single cell
use the xloil.Range.cells
method.
Troubleshooting¶
Both comtypes and win32com have caches for the python code backing the Excel object model. If these caches somehow become corrupted, it can result in strange COM errors. It is safe to delete these caches and let the library regenerate them. The caches are at:
comtypes: …/site-packages/comtypes/gen
win32com: run
import win32com; print(win32com.__gen_path__)
See for example