添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
不爱学习的火车  ·  定安印象 -- 定安文化 -- 定安县人民政府·  5 月前    · 
礼貌的针织衫  ·  nifi/nifi-nar-bundles/ ...·  6 月前    · 
发财的脆皮肠  ·  【12G】初音奴隶家族EX V1.10 ...·  7 月前    · 
逃课的紫菜汤  ·  药学快讯 - ...·  7 月前    · 
重感情的黄花菜  ·  SQL 低调的触发器（TRIGGER） – ...·  8 月前    · 
link管理  ›  UDF Function return error code · Issue #776 · xlwings/xlwings · GitHub
https://github.com/xlwings/xlwings/issues/776
完美的莴苣
5 月前

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement . We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

I was also wondering how to achieve this. The above just makes the Excel cell literally contain the string in question (e.g. "#N/A"), so it appears to have worked but doesn't actually behave the same way as a proper #N/A. For example, a proper #N/A multiplied with a number gives another #N/A, but a string (like in this case) multiplied by a number gives #VALUE (etc).

When a proper #N/A is input to xlwings it becomes a float (-2146826246), but returning this value from xlwings also literally just makes the Excel cell contain that number. Maybe because some conversion gets in the way.

Not the solution yet, but outside of UDFs, you can use the following: 

import xlwings as xw
sheet = xw.books.active.sheets[0]
sheet.range("A5").formula = '#N/A'
sheet.range("A6").formula = '#DIV/0!'
sheet.range("A7").formula = '#NAME?'
sheet.range("A8").formula = '#NULL!'
sheet.range("A9").formula = '#NUM!'
sheet.range("A10").formula = '#REF!'
sheet.range("A11").formula = '#VALUE!'
          
When drawing charts using data return by UDF, it's friendly to see #N/A. (chart on the right is what I expect.)


# UDF 
@xw.func
@xw.ret(expand='table')
@xw.ret(index=False)
@xw.ret(header=False)
@xw.ret(empty='NA')
def test():
    df = pd.DataFrame([[1,3,None,2,1]])
    return df

But we can not manipulate value in array return by UDF. Is there any other ways?


You can't properly return #N/A via UDFs currently, but in your case it might be better to just leave away the @xw.ret(empty='NA').


Thanks very much. My temporary solution is to set up another excel native function to format UDFs data.

          
@fzumstein do you have already an idea on how to fix this (variant representing an error)?

I guess it falls in the C++ domain of the addin...

          
@fzumstein when I put a breakpoint on that line, I never reach it when running a python function from excel.

I would like to be able to introspect what is sent at COM level to see what the conversion from python to COM variant is doing (if this makes sense)

          
hi @fzumstein , based on an idea in https://stackoverflow.com/questions/45017673/how-to-filter-an-array-with-a-worksheet-function, I came up with the function ReplaceEmpty


Private arr_()
Public Function Values() As Variant()
  Values = arr_
End Function
Public Function ReplaceEmpty(arr(), expression As String) As Variant()
  arr_ = arr
  ReplaceEmpty = Application.Evaluate("IF(Values()=""""," & expression & ",Values())")
End Function


that can then wrap the output to replace the empty values by N/A

Py.CallUDF("the_module", "the_function", Array(param), ThisWorkbook, Application.Caller)

ReplaceEmpty(Py.CallUDF("the_module", "the_function", Array(param), ThisWorkbook, Application.Caller), "#N/A")


A bit of a hack (specially with the global variable arr_ and potential reentrancy issues) but could give you ideas for a clean fix

          
Hi @sdementen, thanks! Actually, you are right about the previous comment, but the conversion is still handled in pywin32 and from what I figure, while it delivers the different error types as integers when reading, it doesn't support them when writing so your solution sounds like the sensible way of doing it!

          



Adjusting my previous comment for https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/cell-error-values, I'd actually would expect the following to work (but it doesn't):


import xlwings as xw
import pythoncom
from win32com.client import VARIANT
@xw.func
def hello(name):
    return VARIANT(pythoncom.VT_ERROR, 2042)


So the question would probably be if there's an equivalent of CVErr(xlErrNA) in pywin32 given that  VARIANT(pythoncom.VT_ERROR, 2042) doesn't seem to work.

      NA/nan within returned DataFrame via UDF back to Python is parsed to empty string '' instead of NA/nan
      #1947

@talalvas could you show an example how to implement xloil.CellError.NA ?


You have to use the xloil library for that.


import xloil as xlo
@xlo.func
def xlo_df_rows(df):
    if type(df) is xlo.CellError:
        # lookup_df is #NULL! (empty dataframe)
        if df == xlo.CellError.NULL:
            return 0
    return len(df.index)


You can change the NULL to ant error supported in the CellError class.
 
推荐文章
不爱学习的火车  ·  定安印象 -- 定安文化 -- 定安县人民政府
5 月前
礼貌的针织衫  ·  nifi/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/pr
6 月前
发财的脆皮肠  ·  【12G】初音奴隶家族EX V1.10 更新特殊事件/增加新人物 12G_ACG游戏资源站
7 月前
逃课的紫菜汤  ·  药学快讯 - 澳大利亚警示普瑞巴林的自杀风险-北京大学第六医院
7 月前
重感情的黄花菜  ·  SQL 低调的触发器（TRIGGER） – SQL网 -SQL、MySQL、Hive、ClickHouse免费基础教程 - SQL数据库基础教程
8 月前
Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
link管理 - 链接快照平台