Tables¶

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.

__init__(data, name=None, type=None, **kwargs)

Parameters:

• 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.

• 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:

1. TableBase.find_table is called to see if there is an existing ListObject object.

2. If no existing ListObject is found, TableBase.create_table is called.

3. If the ListObject size is different from that returned by TableBase.rows and TableBase.columns, TableBase.resize_table is called.

4. TableBase.update_table is called to update the data in the ListObject table object.

5. Finally, TableBase.apply_filters and TableBase.apply_sorting are called to apply any filtering and sorting required to the table.

com_package(self)

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.

find_table(self, xl_range)

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.

Parameters:

xl_range – Range where the table is being written to as a Range COM object.

Returns:

ListObject COM object or None if not found.

create_table(self, xl_range)

Creates a new ListObject for the table.

This is called if find_table returns None.

Parameters:

xl_range – Range where the table should be placed as a Range COM object.

Returns:

ListObject COM object.

rows(self)

Return the number of rows in the table, excluding any header row.

columns(self)

Return the number of columns in the table.

resize_table(self, xl_list_object, rows, columns)

Resizes the ListObject to match the new data.

Parameters:

• 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.

Returns:

ListObject COM object.

update_table(self, xl_list_object)

Update the ListObject by setting the data on it.

Parameters:

xl_list_object – Existing table as a ListObject COM object.

apply_filters(self, xl_list_object)

Apply any filters to the table object.

Parameters:

xl_list_object – Existing table as a ListObject COM object.

apply_sorting(self, xl_list_object)

Apply any sorting to the table object.

Parameters:

xl_list_object – Existing table as a ListObject COM object.

on_error(self, exc_value, xl_range)

Called if an error occurs when creating or updating a table.

Parameters:

• exc_value – The exception object raised.

• xl_range – The range that was to be used for the table.