Formatters can also be used from macro functions, as well as from worksheet functions.
To apply a formatter in a macro function use the formatter option to when setting
XLCell.value.
For example, to use the standard DataFrameFormatter when setting a DataFrame
to a range from an Excel macro you would do the following:
from pyxll import xl_macro, xl_app, XLCell, DataFrameFormatter
import pandas as pd
@xl_macro
def set_dataframe():
# Get the current selected cell
xl = xl_app()
selection = xl.Selection
# Get an XLCell instance for the selection
cell = XLCell.from_range(selection)
# Create a DataFrame
df = pd.DataFrame({
"A": [1, 2, 3],
"B": [4, 5, 6],
"C": [0.3, 0.6, 0.9]
})
# Construct the formatter to be applied
formatter = DataFrameFormatter()
# Set the 'value' on the current cell with the formatter
# and using the auto-resize option
cell.options(type="dataframe<index=True>",
auto_resize=True,
formatter=formatter).value = df
The same method can be used from a menu function or ribbon action.
