Отображает>255 символов в ячейке Excel

Question

Excel показывает #VALUE! когда my UDF возвращает более 255 символов строки.

xlwings - 0.7.1, а excel - 2007, который, согласно Microsoft, может содержать до 32767 символов в ячейке.

Где может быть проблема?

Answer 1

Как я могу сказать, Py.CallUDF (используется xlwings udfs) возвращает массив 2D Variant.

Также кажется, что по какой-то причине возврат массива Variant с длинами строк больше 255 из UDF с чистым VBA приводит к ошибке #VALUE при вызове в excel. Помещение часов в массив в редакторе VBA показывает, что данные не повреждены, но просто не могут быть правильно переданы. Небольшой поиск вернул несколько вопросов о максимальной длине строки в VBA, но ничего, что конкретно решало эту проблему.

Возвращение массивов String или отдельных строк с> 255 символами, похоже, работает нормально.

Вот несколько простых примеров VBA, показывающих проблему:

Возвращаем вариант массива:

Function variant_long_string(n)
    Dim temp(0 To 0, 0 To 0) As Variant
    temp(0, 0) = String(n, "a")
    variant_long_string = temp
End Function

Вызов из Excel, возвращает (сбой при N > 255):

255 aaaaaaaaaaaaa....aaaaaaaaa
256 #VALUE!

Возвращаем элемент вариативного массива:

Function variant_long_string_element(n)
    Dim temp(0 To 0, 0 To 0) As Variant
    temp(0, 0) = String(n, "a")
    variant_long_string_element = temp(0, 0)
End Function

Вызов из Excel возвращает (успешно для N > 255):

255 aaaaaaaaaaaaa....aaaaaaaaa
256 aaaaaaaaaaaaa....aaaaaaaaaa

Возвращаемый массив строк:

Function string_long_string(n)
    Dim temp(0 To 0, 0 To 0) As String
    temp(0, 0) = String(n, "a")
    string_long_string = temp
End Function

Вызов из Excel возвращает (успешно для N > 255):

255 aaaaaaaaaaaaa....aaaaaaaaa
256 aaaaaaaaaaaaa....aaaaaaaaaa

Временное решение

Если ваш Python UDF возвращает только одно строковое значение, например:

@xw.func    
def build_long_string(n):
    res = 'a'*int(n)
    return res 

xlwings автоматически сгенерирует следующий макрос VBA в модуле xlwings_udfs:

Function build_long_string(n)
        If TypeOf Application.Caller Is Range Then On Error GoTo failed
        build_long_string = Py.CallUDF(PyScriptPath, "build_long_string", Array(n), ThisWorkbook)
        Exit Function
failed:
        build_long_string = Err.Description
End Function

В качестве быстрого патча, чтобы ваш UDF заработал, слегка изменив этот макрос:

Function build_long_string(n)
        If TypeOf Application.Caller Is Range Then On Error GoTo failed
        temp = Py.CallUDF(PyScriptPath, "build_long_string", Array(n), ThisWorkbook)
        build_long_string = temp(0, 0)
        Exit Function
failed:
        build_long_string = Err.Description
End Function

позволяет строку> 255 длины, чтобы сделать это в Excel успешно. Вы можете сделать что-то похожее для результата массива, вам просто нужно преобразовать массив Variant в массив String путем зацикливания / переназначения всех значений из temp в результат.

Answer 2

Основываясь на предложении @schoolie относительно преобразования массива 2D Variant в массив 2D String, я изменил источник логики генерации функции VBA в моих локальных файлах:

В udfs.generate_vba_wrapper()

заменить:

vba.write('{fname} = Py.CallUDF("{module_name}", "{fname}", {args_vba}, ThisWorkbook)\n',
                    module_name=module_name,
                    fname=fname,
                    args_vba=args_vba,
                )

с:

vba.write('r = Py.CallUDF("{module_name}", "{fname}", {args_vba}, ThisWorkbook)\n',
                                        module_name=module_name,
                                        fname=fname,
                                        args_vba=args_vba,
                                    )                    
                vba.write('ReDim strarray(UBound(r, 1), UBound(r, 2)) As String\n')
                vba.write('For i = 0 To UBound(r, 1)\n')
                vba.write('  For j = 0 To UBound(r, 2)\n')
                vba.write('    strarray(i, j) = CStr(r(i, j))\n')
                vba.write('  Next\n')
                vba.write('Next\n')
                vba.write('{fname} = strarray\n', fname=fname)

Другой вариант заключается в исправлении сгенерированного макроса VB в редакторе VB после выполнения "Импорт пользовательских функций Python". Однако это изменение будет потеряно при повторном импорте. Код уже указан выше @schoolie