Working with Tables ¶

Writing a Table ¶

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 a Table ¶

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 Reading Excel Values from Python 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

Updating a Table ¶

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

Tables and Worksheet Functions ¶

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.

Advanced Features ¶

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.

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}")