Skip to main content

Writing arrays back to Excel

Tony Roberts
  • Updated

PyXLL has support for array functions, but sometimes you might want to write array data back to Excel in a different way without using an array function. For example, from a macro, menu function, ribbon button, context menu, or even a worksheet function that's not an array function.

The Excel Object Model can be accessed from Python via COM, and that can be used to write directly to the Excel sheet in the same way as can be done in VBA. We'll cover more of this below, but also see Python as a VBA Replacement for more details.

The Excel.Application object it the main object we start with when using the Excel Object Model. PyXLL has a helper function for us to access this, pyxll.xl_app. PyXLL itself doesn't provide the Python COM wrapper classes, but it has support for the main third-party COM wrappers win32com (part of the pywin32 package) and comtypes. It uses win32com by default which you can install from pip or conda by doing

pip install pywin32

or

conda install pywin32

 

 

Getting a Range Object and Writing to it

If you have ever written any VBA you will be familiar with the Range class. This is what Excel uses to represent a cell or selection of cells, and it's this that we will use to set values in Excel. You can find the documentation for the Range class here https://docs.microsoft.com/en-us/office/vba/api/excel.range(object).

There are a few ways to get a Range object. The two most common ways are from the current selection, or from an address or named range. The following shows how to get a range using both methods. It's written in a menu function, and so the function can be run from the "Range Example" menu item added to the Add-Ins tab in Excel.

from pyxll import xl_app, xl_menu

@xl_menu("Range Example")
def menu_function():
    # Get the Excel.Application object
    xl = xl_app()

    # Get the current selection as a Range object
    selected_range = xl.Selection

    # Get a Range object for the cell A1 on the active sheet
    specific_range = xl.ActiveSheet.Range("A1")

Now we have  a Range object we can set the value of the cell using the "Value" property. The value that we set should be a basic type (e.g. a string or number) for a single cell, or a list of lists of basic types if the range covers multiple cells. We'll cover how to convert from other types to these basic types shortly.

To get a Range that covers multiple cells we can construct a Range object from two other Range objects that correspond to two opposite corners of the range we want. The GetOffset method can be used to get a cell relative to another cell. Alternatively, we can use an address like "A1:D5" to get a Range that covers multiple cells.

Continuing the example above, the following gets a range with a few cells in it and sets the value to an array.

    # Get a 3x3 range
    top_left = specific_range
    bottom_right = specific_range.GetOffset(RowOffset=2, ColumnOffset=2)
    array_range = xl.Range(top_left, bottom_right)

    # Set the value to 3 rows of numbers
    array_range.Value = [
        [1, 2, 3],
        [4, 5, 6]
        [7, 8, 9]
    ]

 

Automatically Resizing a Range

In the section above we've seen how we can get an Excel Range object and set its value using the Value property. This requires us to create a range of the correct size for the data we want to write, which can be slightly cumbersome.

An alternative is to use PyXLL's XLCell class. This can be used to write back to Excel in the same way as the Excel Range object, and in fact uses the same Excel Object Model underneath. It can be used as shortcut for resizing the range when writing data as it also supports auto-resizing, in the same way PyXLL supports auto-resizing array functions.

If we already have an Excel Range object we can get an XLCell instance using the XLCell.from_range static method. The XLCell.options method is used to tell the cell to auto-resize when writing the value back to Excel. Continuing on from the earlier example, we can write:

from pyxll import XLCell

# ... see earlier code ...

    # Get the XLCell from the Range
    cell = XLCell.from_range(specific_range)

    # Use XLCell.options to set the 'auto_resize' option
    # and set the cell value to an array
    cell.options(auto_resize=True).value = [
        [1, 2, 3],
        [4, 5, 6],
        [7, 8, 9]
    ]

Unlike before we don't need the Range to be the correct size before writing the value as the XLCell class takes care of that for us.

 Note that unlike array function resizing this will not clear any previous values. So, if you write a large array and then subsequently write a smaller array to the same area, the previous array will not be cleared automatically and you will have to clear that previous range in your own code. The Excel Range object has a Clear method that can be used to clear a range, but you will have to determine exactly what to clear.

Using Other Types (eg DataFrames)

The Range.Value property only works with basic types, not all the types that PyXLL can work with. You can convert between types using PyXLL's get_type_converter function.

For example, to convert a pandas DataFrame to a basic list of lists type that Excel can understand you would do the following:

from pyxll import get_type_converter

# Get a function to convert a DataFrame to a basic Excel type
df_to_var = get_type_converter("dataframe", "var")

# Convert a DataFrame 'df' to an array (list of lists) of basic types
xl_value = df_to_var(df)

# 'xl_value' can now safely be used with Range
array_range.Value = xl_value

As above in the previous section this can be simplified by using XLCell.

With XLCell we can set the data type and have PyXLL do the conversion for us, along with auto-resizing, in a single step. For example,

# ... see earlier code ...

    # Get the XLCell from the Range
    cell = XLCell.from_range(specific_range)

    # Use XLCell.options to set the 'auto_resize' option
    # and set the cell value to an array
    cell.options(type="dataframe", auto_resize=True).value = df

The type that you specify in the cell.options is the same as you would use in a worksheet function signature. If there are additional type arguments that you want to pass then you can use the type_kwargs argument to the options method.

 

Writing Back to Excel from a Worksheet Function

The above example code was written inside the menu function. It would work equally well from a ribbon button, context menu or macro. Worksheet functions are a bit different however.

Worksheet functions are called by Excel when it is calculating a range, sheet or workbook. During Excel's calculation phase, it is not possible to make updates to Excel since doing so would break Excel's calculation graph.

Typically arrays are returned from worksheet functions using array functions and this is what we advise in nearly all cases. By using array functions, Excel can keep track of the inputs and outputs of functions and so it knows what needs to be called as cells change - this is what's know as the Excel calculation dependency graph.

If you do need to write back to Excel from a worksheet function then it is possible by using PyXLL's async_call function. This schedules a function call to be called after Excel has finished calculating, and therefore gets around the problem of not being able to write back to Excel during Excel's calculation phase.

This needs to be done with care since it is easy to create a circular dependency that Excel cannot detect and will cause Excel to keep recalculating indefinitely (so Excel hangs). For example, if you had a function that writes an array back to Excel but one of the arguments to that function is one of the cells you write to, that would cause an infinite loop and hang Excel. Similarly, if your function was volatile or one of its inputs was volatile then it would be recalculated whenever any change was made to the sheet - so if that function itself sets values on the sheet then it would recalculate and keep setting values on the sheet; another infinite loop.

The following shows how pyxll.async_call can be used to schedule a call from a worksheet function to set some values below the cell calling the function. It uses pyxll.xlfCaller to get the address of the calling cell, which requires "macro=True" to be set in the @xl_func decorator.

from pyxll import xl_func, xl_app, xlfCaller, async_call, XLCell

@xl_func(macro=True)
def write_dataframe():
    """Writes a dataframe below this function call"""

    # The update is done asynchronously so as not to block Excel
    # by updating the worksheet from a worksheet function
    def write_to_excel(address, df):
        xl = xl_app()
        xl_range = xl.Range(address)

        # Get the cell below and construct an XLCell instance
        one_below = xl_range.GetOffset(RowOffset=1, ColumnOffset=0)
        cell = XLCell.from_range(one_below)

        # Set the value
        cell.options(type="dataframe", auto_resize=True).value = df

    # Do some work to get the dataframe
    df = make_dataframe()

    # Get the address of the calling cell
    caller = xlfCaller()
    address = caller.address

    # Schedule the asynchronous call to the write function
    async_call(write_to_excel, address, df)

    return address

 

Related articles

Powered by Zendesk