about UDFS（@xw.arg : ndim=2 or expand='table')

lgxcxd commented 1 month ago

OS (e.g. Windows 10 or macOS Sierra)

windows 11

Versions of xlwings, Excel and Python (e.g. 0.11.8, Office 365, Python 3.7)

0.33

Describe your issue (incl. Traceback!)

# Your traceback here

Include a minimal code sample to reproduce the issue (and attach a sample workbook if required!)

# Your code here
import xlwings as xw
@xw.func
@xw.arg('data', ndim=2)
def add_one(data):
    return [[cell+1 for cell in row] for row in data]

@xw.func
@xw.arg('data', expand='table')
def add_two(data):
    return [[cell+2 for cell in row] for row in data]

These two definitions both work properly, but what are the deeper differences between them?

fzumstein commented 1 month ago

These options are explained here: https://docs.xlwings.org/en/latest/converters.html let me know if that doesn't clear things up.

lgxcxd commented 1 month ago

These options are explained here: https://docs.xlwings.org/en/latest/converters.html let me know if that doesn't clear things up.

I found that ndim=2 appears in three topics within the help documentation:

Data Structures Tutorial: lists, 1dlists, used to preserve the original row and column information from Excel when reading single rows or columns; (The section on Converters and Options—ndim is not detailed enough) myrange = sheet['a1:a5'].options(ndim=2).value
"Data Structures Tutorial: NumPy arrays" used in conjunction with the converter convert=np.array: mydata = sheet['A1:CE'].options(np.array, ndim=2);
"User Defined Functions (UDFs)—Array formulas: Get efficient—Number of array dimensions: ndim" used when defining custom functions: @xw.arg('x', ndim=2)

The option expand='table' is generally used for reading 2D cell ranges from Excel: myrange = sheet['A1'].options(expand = 'table').value; If the area starting from cell A1 in Excel is a single row or column and you need to retain the original row or column information, you should use it this way. Suppose the range A1:A5 in the sheet contains data: myrange = sheet['a1'].options(expand='table', ndim=2).value, or myrange = sheet['a1:a5'].options(ndim = 2).value.
Since range expansion generally involves reading 2D regions, using expand() or expand='table' for expanding reads can be associated with ndim=2, which literally means a 2D region, hence my confusion.

fzumstein commented 1 month ago

expand="table" autoexpands if there's more than 1 cell. It's basically ctrl+shift+down+right. If there's just one cell, you get a scalar, if there's a row or column array, you get a 1d list, only if there's a 2d source range, you get a 2d list/numpy array. If you'd always want to force the values to arrive as 2d list/array, even if it's just a single cell/1d array in Excel, you set ndim=2.

lgxcxd commented 1 month ago

expand="table" autoexpands if there's more than 1 cell. It's basically ctrl+shift+down+right. If there's just one cell, you get a scalar, if there's a row or column array, you get a 1d list, only if there's a 2d source range, you get a 2d list/numpy array. If you'd always want to force the values to arrive as 2d list/array, even if it's just a single cell/1d array in Excel, you set ndim=2.

Thank you!

xlwings / xlwings