When returning values from a Python function in Excel, Excel will always display NULL values as zero (0). That's why even when you return None you see 0. Internally, PyXLL converts None to an Excel NULL value, but Excel still displays that value as 0.
Use PyXLL's "none_value" or "nan_value" option
A common case is where you are returning a numpy array, pandas, or polars DataFrame to Excel and want to display missing or NaN values as a blank cell.
Polars null values and None values will be displayed in Excel as 0. Similarly, if you replace NaN values with None, Excel will display those values as zero.
Instead, use either the "none_value" or "nan_value" option to the @xl_func decorator and have PyXLL converts NaN values to an empty string. If using polars and you want to replace null values, use the "none_value" option. For example:
from pyxll import xl_func
import numpy as np
@xl_func(": numpy_array", none_value="")
def none_test():
return np.array([[1, None], [None, 2]])
@xl_func(": numpy_array", nan_value="")
def nan_test():
return np.array([[1, np.nan], [np.nan, 2]])
This will automatically replace any None or nan values returned by your Python function to the specified "none_value" and "nan_value".
These can be set to an Exception instance to return an error to Excel. For example, "none_value=RuntimeError()" would result in Excel displaying #N/A instead of 0 for None values.
See this page in the docs for more information about these options https://www.pyxll.com/docs/api/udfs.html#pyxll.xl_func.
You can also set the default for these options in the pyxll.cfg file. See https://www.pyxll.com/docs/userguide/config/pyxll.html#nan-config for details.
Disable "Show a zero in cells that have zero value"
If instead you want zero values to be displayed as an empty cell you can change the display settings for the sheet to display a blank cell instead of 0, but that will affect all cells with zero value in the sheet.
To show a blank cell instead of zero go to Options -> Advanced and scroll down to "Display options for this worksheet" and uncheck "Show a zero in cells that have zero value".
