New in PyXLL 5.8
In the section Introduction it is explained how Python functions can be exposed a Excel macros, and how these macros can read and write Excel worksheet values.
Excel Tables can be used to make managing and analyzing a group of related data easier. In Excel, you can turn a range of cells into an Excel Table (previously known as an Excel list).
PyXLL can read and write Excel Tables in a similar way to how ranges can be read and written. Tables can be created and updated from Python data using macro functions.
Writing an Excel Table from a macro works in a very similar way to writing an array of data as a range.
If you’re not already familiar with writing a range of Python data from a macro function please see Writing Python Values to Excel for a full explaination.
To write an Excel Table we use the XLCell class, and set the XLCell.value
property.
As previously, we specify the data type using XLCell.options, but this time we use the special
table type to tell PyXLL to write the data as a Table instead of a range.
The table type takes one type parameter, which is the data type we want PyXLL to use when converting
the Python type to Excel values. To write a DataFrame we would use table<dataframe>. The inner
type can also be parameterized, for example, to include the index of the DataFrame we would use
table<dataframe<index=True>>.
from pyxll import xl_macro, XLCell
@xl_macro
def write_excel_table():
# Get an XLCell object for the cell 'A1' in the active sheet.
# We could fully specify the range, for example "[Book1]Sheet1!A1" if
# needed, or use a COM Range object instead of the address string.
# The table will be written with this cell as the top left of the table.
cell = XLCell.from_range("A1")
# Create the DataFrame we want to write to Excel as a table
df = your_code_to_construct_the_dataframe()
# Write the DataFrame to Excel as a Table
cell.options(type="table<dataframe>").value = df
The cell we use when writing the Table is the top left cell where we want the table to be written to.
If there is not enough space to write the table, a #SPILL! error will be written to Excel and
a SpillError Python exception will be raised.
When writing a table, we do not need to pass auto_resize=True to XLCell.options, the
size of the table written will always match the same of the data even if this is not specified.
Reading the data from an Excel Table is no different from reading it from a Range. A table is really just a range with some formatting added!
If you’re not already familiar with reading an Excel range into Python from a macro function please see macros-read-from-excel for a full explaination.
When getting the XLCell instance in order to read the data, you can pass the Range corresponding
to the entire table. Or, to make things simpler, you can pass any cell from within the table and use the
auto_resize=True option to XLCell.options. When this is used on a table, the entire table
will be used automatically.
from pyxll import xl_macro, XLCell
@xl_macro
def read_excel_table():
# Get the XLCell object for the top left of the existing table
cell = XLCell.from_range("A1")
# Read the entire table into a DataFrame by using the `auto_resize=True`
# option to XLCell.options.
df = cell.options(auto_resize=True, type="dataframe").value
To update a table written previously all that is required is to write to one of the cells in the table using the same method explained above. The existing table will be updated instead of creating a new table.
If the new data is larger than the current table the table will be expanded, or if the new data
is smaller the table will be contracted. If there is not enough space to expand the table a
#SPILL! error will be written to Excel and a SpillError Python exception will be
raised.
from pyxll import xl_macro, XLCell
@xl_macro
def update_excel_table():
# Get the XLCell object for the top left of the existing table
cell = XLCell.from_range("A1")
# Read the entire table into a DataFrame
df = cell.options(auto_resize=True, type="dataframe").value
# Make some changes to the DataFrame
new_df = your_code_to_update_the_dataframe(df)
# Update the table in Excel
cell.options(type="table<dataframe>").value = new_df
New in PyXLL 5.11
When updating an existing table, by default, the table columns in Excel will be updated to match the columns of the DataFrame.
If you want to keep the columns as they are in Excel and only update the columns where the headers match the
Python DataFrame columns you can specify preserve_columns=True as a type paramter to the table type.
This is useful where you wish to allow the Excel user to add calculated columns to the table, or remove columns
they don’t wish to include. Without specifying preserve_columns=True changes to the columns in the Excel table
would be lost when updating, but with preserve_columns=True the columns are preserved.
from pyxll import xl_macro, XLCell
@xl_macro
def update_excel_table():
# Get the XLCell object for the top left of the existing table
cell = XLCell.from_range("A1")
# Get the updated DataFrame
df = ...
# Update the table in Excel without modifying the columns
cell.options(type="table<dataframe, preserve_columns=True>").value = new_df
In all of the code above we have used xl_macro to read and write Excel Tables.
This is because reading and writing values from Excel using the XLCell class
must always be done from an Excel macro.
Sometimes, it is desirable to be able to return data from a worksheet function. We can
return DataFrames from worksheet functions using xl_func as dynamic arrays,
but what about using tables?
To do that we have to use schedule_call. This schedules a Python function to
be run in such a way that it is safe to do what we can otherwise only do in a macro.
Using schedule_call we can schedule a function that will write Python data
as a table in Excel. We would like to write the table near to where the worksheet
function was called from, and to do that we use xlfCaller.
xlfCaller returns the XLCell of the function’s calling cell. Using
XLCell.offset we can get one cell below the calling cell and use that as the
top left corner of our table.
For example:
from pyxll import xl_func, schedule_call, xlfCaller
@xl_func
def table_from_function():
"""A worksheet function that writes to a table."""
# Get the XLCell this function was called from
cell = xlfCaller()
# Create the DataFrame we want to write to Excel as a table
df = your_code_to_construct_the_dataframe()
# Get the top left cell of the table we're going to write,
# one row below the calling cell.
top_left = cell.offset(rows=1, columns=0)
# An inner function that will be called in the future and will
# write the DataFrame to a table below the calling cell.
def write_table():
top_left.options(type="table<dataframe>").value = df
# Schedule the call to 'write_table' that could otherwise
# only be called as part of an Excel macro.
schedule_call(write_table)
return "[OK]"
The above method using schedule_call works around the fact that we can only write
tables from a macro. It schedules a function to write the table after Excel has
finished calculating, when it is possible to do so.
Above we’ve seen how the table type can be used with XLCell.options to write
Python data to an Excel table.
The same can be acheived using the Table class, and using the table type
is really just shorthand for this.
The below example shows how to construct a Table instance and set that as
the XLCell.value. This has the same effect as using the table<dataframe>
type.
from pyxll import xl_macro, XLCell, Table
@xl_macro
def write_excel_table():
# Get an XLCell object for the cell 'A1' in the active sheet.
# We could fully specify the range, for example "[Book1]Sheet1!A1" if
# needed, or use a COM Range object instead of the address string.
# The table will be written with this cell as the top left of the table.
cell = XLCell.from_range("A1")
# Create the DataFrame we want to write to Excel as a table
df = your_code_to_construct_the_dataframe()
# Construct a Table instance, wrapping our DataFrame
table = Table(df, type="dataframe")
# Write the table to Excel
cell.value = table
This is a small change but it allows us to access some of the more advanced features of PyXLL’s table capabilites.
When setting XLCell.value using an instance of the Table class, you
can provide the name to use when creating the Excel Table.
The Table class constructor takes a kwargs name. It also has the
Table.name attribute, allowing you to query the table named used after writing
the table to Excel.
from pyxll import xl_macro, XLCell, Table
@xl_macro
def write_named_excel_table():
# Get an XLCell object for the cell 'A1' in the active sheet.
# We could fully specify the range, for example "[Book1]Sheet1!A1" if
# needed, or use a COM Range object instead of the address string.
# The table will be written with this cell as the top left of the table.
cell = XLCell.from_range("A1")
# Create the DataFrame we want to write to Excel as a table
df = your_code_to_construct_the_dataframe()
# Construct a named Table instance, wrapping our DataFrame
table = Table(df, type="dataframe", name="MyNamedTable")
# Write the table to Excel.
# When creating a new table the table name will be used.
cell.value = table
# The table name is accessible from the table.name attribute.
# You can use this to get the auto-generated name if no name
# was specified.
name = table.name
print(f"Table name = {name}")
Full control over how tables are written to Excel is possible by implementing
a class derived from Table or TableBase.
The TableBase class defines the methods required for tables to be
written to Excel. These can be used as escape hatches, allowing your own code
to function differently to the default Table class.
Any class implemented with TableBase as a base class can be used
when setting XLCell.value.
Note
Knowledge of the Excel Object Model is required to write an class derived
from Table or TableBase.
When writing a table to Excel, the following happens:
TableBase.find_table is called to see if there is an existing ListObject object.
If no existing ListObject is found, TableBase.create_table is called.
If the ListObject size is different from that returned by TableBase.rows and
TableBase.columns, TableBase.resize_table is called.
TableBase.update_table is called to update the data in the
ListObject table object.
Finally, TableBase.apply_filters and TableBase.apply_sorting are called to apply
any filtering and sorting required to the table.
Table provides the default implementation for these methods.
See the Tables API Reference for details of the Table
and TableBase classes, including the methods that need to be implemented.
