PyXLL can read and write Excel tables, as well as plain ranges of data.
Please see Working with Tables for more details.
The Table class is used to write an Excel table from a macro function using
XLCell.value.
For example, the following will write a pandas DataFrame as a table in Excel.
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
The Table class can be used as a base class for custom table classes
with methods overriden to customize the writing and updating of Excel tables.
The methods available to override are documented in the base class, TableBase.
data – DataFrame or other type that can be converted to var[][].
name – Table name (optional)
type – Type signature for data. If None, data must be a pandas or polars DataFrame or a list of lists.
preserve_columns –
New in PyXLL 5.11
If True and there is an existing Excel table, the columns in the Excel table are not modified.
kwargs – Additional type parameters.
Base class of Table.
The TableBase class can be used as a base class for user defined table classes
to customize how tables get written to Excel.
When writing a table to Excel, for example:
cell = XLCell.from_range(rng)
cell.value = Table(...)
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.
Note
Knowledge of the Excel Object Model is required to write an implementation of this class.
Return the com_package to use when passing COM objects to methods of this class.
Can be one of pythoncom, win32com, comtypes or None to use the default set in the pyxll.cfg file.
Finds the ListObject for the table.
This is called when writing a table to a range to find an existing table to update, if there is one.
xl_range – Range where the table is being written to as a Range COM object.
ListObject COM object or None if not found.
Creates a new ListObject for the table.
This is called if find_table returns None.
xl_range – Range where the table should be placed as a Range COM object.
ListObject COM object.
Resizes the ListObject to match the new data.
xl_list_object – Existing table as a ListObject COM object.
rows – Number of rows to resize the table to (excluding header row).
columns – Number of columns to resize the table to.
ListObject COM object.
Update the ListObject by setting the data on it.
xl_list_object – Existing table as a ListObject COM object.
Apply any filters to the table object.
xl_list_object – Existing table as a ListObject COM object.
