Accessing Worksheet Data¶

Reading Excel Values from Python¶

Macros will often need to read values from Excel. We can do that using the Excel Object Model in the same way as you might have done from VBA previously.

For example, the following code gets a Range object and then gets the value using the Range.Value property.

from pyxll import xl_macro, xl_app

@xl_macro
def read_value_from_excel():
    # Get the Excel.Application COM object
    xl = xl_app()

    # Get a Range in the current active sheet
    xl_range = xl.ActiveSheet.Range("A1:D10")

    # Access the Value of the Range
    value = xl_range.Value

What you will notice when reading values this way is that you can’t control the type of the value you get. For example, in the code above we are using a range and the Python value obtained will be a list of lists. If we were to use a single cell, the Python value would be a single value.

You can use get_type_converter to access PyXLL’s type converters (including any custom type converters you may have written) and use that to convert the raw value into the type you require. However, there is a slightly easier way.

Using the PyXLL class XLCell we can access the value as a specified type as follows:

from pyxll import xl_macro, xl_app, XLCell

@xl_macro
def read_value_from_excel():
    # Get the Excel.Application COM object
    xl = xl_app()

    # Get a Range in the current active sheet
    xl_range = xl.ActiveSheet.Range("A1:D10")

    # Get an XLCell object from the Range object
    cell = XLCell.from_range(xl_range)

    # Get the value as a DataFrame
    df = cell.options(type="dataframe").value

Writing Python Values to Excel¶

Writing values to Excel from Python is very similar to reading values, as shown above.

While you can set the Range.Value property directly, using XLCell.value is often more convenient as it can do any type conversions necessary for you.

For example, to write a DataFrame to a range you can do the following:

from pyxll import xl_macro, xl_app, XLCell

@xl_macro
def write_value_to_excel():
    # Get the Excel.Application COM object
    xl = xl_app()

    # Get a Range in the current active sheet
    xl_range = xl.ActiveSheet.Range("A1")

    # Get an XLCell object from the Range object
    cell = XLCell.from_range(xl_range)

    # Create the DataFrame we want to write to Excel
    df = your_code_to_construct_the_dataframe()

    # Write the DataFrame to Excel, automatically resizing the range to fit the data.
    cell.options(auto_resize=True, type="dataframe").value = df

In the code above you can see that as well as converting the DataFrame to the type expected by Excel, XLCell can also automatically expand the range that’s being written to fit the entire DataFrame.

For more details about the options available, see the XLCell class reference.

Accessing Cached Objects¶

When writing an Excel macro, if you need to access a cached object from a cell or set a cell value and cache an object you can use the XLCell class. Using the XLCell.options method you can set the type to object before getting or setting the cell value. For example:

from pyxll import xl_macro, xl_app, XLCell

@xl_macro
def get_cached_object():
    """Get an object from the cache and print it to the log"""
    # Get the Excel.Application object
    xl = xl_app()

    # Get the current selection Range object
    selection = xl.Selection

    # Get the cached object stored at the selection
    cell = XLCell.from_range(selection)
    obj = cell.options(type="object").value

    # 'value' is the actual Python object, not the handle
    print(obj)


@xl_macro
def set_cached_object():
    """Cache a Python object by setting it on a cell"""
    # Get the Excel.Application object
    xl = xl_app()

    # Get the current selection Range object
    selection = xl.Selection

    # Create our Python object
    obj = object()

    # Cache the object in the selected cell
    cell = XLCell.from_range(selection)
    cell.options(type="object").value = obj