xlslim - questions

[Peihong Ke]

Dear xlSlim supports,

 

I hope this email finds you well.

I am a new user of your product and is exploring the functionalities. May I know if you have answers/solutions to the following points please ?  

 

1. Return a list of strings
1. When I return a list of strings of different length, e.g., [‘abc’, ‘ef’], I have the error of “ERR  the function call failed – The nested Python sequence must have the same number of items in each nested list”. Is this expected please?
2. When the lengths of the strings are the same, e.g., [‘abc’, ‘efg’], I did get the call work successfully. However, the result I got is like a table as follows.

 , rather than the more natural format of . Is there a way to achieve the later please?

2. Echo list of lists does not work for me
1. I define the following function which takes a list of lists of integers and return it straight away.

def echo_list_of_lists(int_list_of_lists: List[List[int]]) -> List[List[int]]:

    return int_list_of_lists

2. I expected Excel to display both lists in the following example. However, only shows the first nested list.

 

Thank you very much.

 

Regards,

Peihong

Classification: Internal - ADIA Only

**************************************************************************************************************
PRIVATE & CONFIDENTIAL COMMUNICATION: This email is from the Abu Dhabi Investment Authority (ADIA). This email and its attachments (if any) are confidential and private business communication and are intended only for the named addressees. If you received this email in error, please notify the sender by reply email and promptly delete the original email from your system. The unauthorised use, disclosure or copying of this email, or any part of it, is strictly prohibited.

While ADIA takes care to protect its systems from viruses, it cannot guarantee that this message (including attachments) is free of any virus or other harmful matter, and accepts no responsibility for any loss or damage resulting from the recipient receiving, opening or using it, or for any damage or liability that may be caused by or result from the use of email or internet communications or the reliance thereon.

ADIA does not enter into contracts or undertakings, or provide binding assurances, by email.
**************************************************************************************************************

[xlSlim Dev]

Dear Peihong,

Thank you for your email, I am pleased you are considering xlSlim.

To address your questions:

1 a) Yes, this is intentional, if you are writing a list of lists back to Excel then each list must have the same number of elements

1 b) You can handle this by returning a list of lists

You may find this support post useful

2. That does look like a little bug on my side, only the first row is being passed from Excel. I would encourage you to use numpy arrays to pass matrix data between Excel and Python, they are far more efficient. This will echo the input back to Excel:

For optimal performance you usually want to cache the data within xlSlim and then pass cache handles around. For example, the create_ones_array() function below will create a cache handle in A7 of [Book1]Sheet1$A$7:

The handle can then be passed very efficiently into other xlSlim functions. For example we could sum the array like this:

sum_array() returns 4 as expected. ViewPyObject() shows the array is 2x2 of ones as expected.

I hope that helps. Please let me know if you have any other questions.

All the best,

Russel

[David Cua]

Thank you for your reply. 

1: Your proposed solution of returning a list of single element lists looks more a work around to me. I will use it if I just want to get something work on the excel level. However, we are trying to expose an existing library to EXCEL, I think you would agree that it would not be feasible to apply this work around to a whole existing library.  Would you consider supporting the more natural way please? 

2: thank you for your comments, please let me know when the bug is fixed. 

[Peihong Ke]

Thank you for your reply to my last email on your website. I just replied with follow up questions there.  Please check. Thank you.

 

I have more questions here:

 

1. Handling of NaN
1. Float(‘nan’) is represented as a big float 2.7E+308 in Excel. This apparently is not idea. Would you consider represent it as #QNAN or something more intuitive instead?
2. Handling of dates

1. It fails when a function returns python date – the error is #ERR The function call failed - Not a recognised Python datetime.
2. It fails when a function returns pandas.NaT, it returns #ERR The function call failed - No method matches given arguments for DateTime..ctor: (<class 'float'>, <class 'float'>, <class 'float'>, <class 'float'>, <class 'float'>, <class 'float'>)

It does support python time – however it returns a handle of the time object, and when I display the handle view , it shows  the following, which is not reusable immediately

Excel has direct support of type Time, would you consider supporting this in future releases please?

 

 

3. Failed to handle function with parameter of type “Optional[List[datetime]] = None” when the function is called from Excel without any value supplied.
1. It failed with the cell showing #VALUE! when calling the following function with no inputs provided from Excel.

 

Code:

def get_length_of_optional_list_of_datetime(list_of_dts: Optional[List[datetime]] = None) -> int:

    if not list_of_dts:

        return 0

    else:

        return len(list_of_dts)

                              Same error if the type is an optional list of user defined types.

4. Handling pandas’ dataframe
1. It cannot handle a dataframe column with a mixture types of datetime/pandas.NaT/None. It does return a handle, but failed when displaying it with error “#ERR The function call failed - Not a legal OleAut date.”

Code:

def df_datetime_mixing_NaT() -> pd.DataFrame:

    col1 = ['a', 'b', 'c']

    col2 = [datetime(2021, 1, 1), datetime(2022, 1, 1), pd.NaT]

return pd.DataFrame(zip(col1, col2), columns=['col1', 'col2'])

2. Inconsistency treatment of None with different primitive types

                                                    i.     None is treated as 0 when it is in a column of type int/bool

                                                   ii.     None is treated as 2.6965E+308 when it is in a column of type int/float

Code:

def df_primitive_types_mixing_None() -> pd.DataFrame:

    strs = ['a', 'b', None] # None displayed as 0

    ints = [1, 2, None] # None displayed as 2.6965E+308

    floats = [1.1, 2.2, None] # None displayed as 2.6965E+308

    bools = [True, False, None] # None displayed as 0

    return pd.DataFrame(zip(strs, ints, floats, bools), columns=['str', 'int', 'float', 'bool'])

3. When a column has various types, datetime handling failed.

Code:

def df_datetime_with_mixed_types() -> pd.DataFrame:

    col1 = ['abc', 1.2, 1, True, datetime(2027,1,1), float('nan'), None]

return pd.DataFrame(col1, columns=['mixed_types'], index = ['str', 'float', 'int', 'bool', 'datetime', 'nan', 'None'])

 

Regards,

Peihong

Classification: Internal - ADIA Only

From: Peihong Ke
Sent: Monday, July 10, 2023 4:24 PM
To: 'sup...@xlslim.com' <sup...@xlslim.com>
Subject: xlslim - questions

 

Dear xlSlim supports,

 

I hope this email finds you well.

I am a new user of your product and is exploring the functionalities. May I know if you have answers/solutions to the following points please ?  

 

1. Return a list of strings
1. When I return a list of strings of different length, e.g., [‘abc’, ‘ef’], I have the error of “ERR  the function call failed – The nested Python sequence must have the same number of items in each nested list”. Is this expected please?
2. When the lengths of the strings are the same, e.g., [‘abc’, ‘efg’], I did get the call work successfully. However, the result I got is like a table as follows.

 , rather than the more natural format of . Is there a way to achieve the later please?

2. Echo list of lists does not work for me
1. I define the following function which takes a list of lists of integers and return it straight away.

def echo_list_of_lists(int_list_of_lists: List[List[int]]) -> List[List[int]]:

    return int_list_of_lists

2. I expected Excel to display both lists in the following example. However, only shows the first nested list.

 

Thank you very much.

 

Regards,

Peihong

**************************************************************************************************************

[xlSlim Dev]

Hi David,

I will have a look at supporting lists of strings more naturally. What is happening at the moment is xlSlim is seeing a list of strings as a list of lists as strings are themselves iterable. I can detect strings and handle that better. Will be in the next release.

All the best,

Russel 

--
You received this message because you are subscribed to the Google Groups "xlSlim Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xlslim-suppor...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/xlslim-support/6f875cc0-6ec9-457c-b350-7774f594e3e5n%40googlegroups.com.

[xlSlim Dev]

Hi Peihong,

Thank you for such a detailed email.

1 a) A good idea, I will do so

2 a) Did the function have type hints of the form f()->datetime.datetime ? Could you send the test functions you used please?

3) I will investigate 

4) I will investigate. Note that some of these are limitations of the ViewPyObject() function which tries to convert the object into something Excel can represent, rather than an issue with object caching itself. If you the pass a handle into a function expecting one of these dataframes it will behave as expected.

Thanks again for the detailed email.

All the best,

Russel 

--

You received this message because you are subscribed to the Google Groups "xlSlim Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xlslim-suppor...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/xlslim-support/DU0PR01MB907130993DAE74BF570EB3A58F36A%40DU0PR01MB9071.eurprd01.prod.exchangelabs.com.

[David Cua]

code for 2 a)

from datetime import date

def output_date() -> date:
"""not supported, #ERR The function call failed - Not a recognised Python datetime."""
return date(2023, 7, 1)

The return type is date not datetime as it should be.

code for 2 b)

import pandas as pd

def output_pd_NaT() -> pd._libs.tslibs.nattype.NaTType:
    return pd.NaT

[xlSlim Dev]

Hi David,

Thank you for the test functions.

I have fixed a few items already:

* nan, inf and -inf will now be returned as Excel's #NUM error

* lists of strings can now be returned more naturally

* Python datetime.date results are correctly handled

I will continue looking at the other issues. Most should be fixed in the next release.

All the best,

Russel

To view this discussion on the web, visit https://groups.google.com/d/msgid/xlslim-support/bbaf334c-b1eb-4eb9-9911-964de362cf87n%40googlegroups.com.

[xlSlim Dev]

Hi Peihong,

Most of the issues you found are now fixed:

* I have corrected the handling of optional lists so the mapped function is called when no input is supplied.

* Python functions expecting lists of lists can now be passed an Excel range, the function receives all the rows, not just the first row.

* Pandas NaT values are recognised and Python datetime.time instances are recognised when returned from functions.

* nan, inf and -inf will now be returned as Excel's #NUM error

* lists of strings can now be returned more naturally

* Python datetime.date results are correctly handled

* The utility function ViewPyObject() has been improved

I'll be testing these changes over the next week and hope to get a release out soon thereafter. I'll be more than happy to extend your trial license.

Thanks again for all the feedback,

Russel

[David Cua]

thank you. please let me know when it is released

[xlSlim Dev]

Hi,

I have released a new version 3.1.0 that addresses the issues you have raised.

Please download the new version from https://delivery.shopifyapps.com/-/de75ae34178a40a5/bbc8da973f15c682

I have made the following changes:

Added

• Python functions expecting lists of lists can now be passed a range from Excel. Previously only the first row was being passed from Excel.
• Pandas NaT values are recognised and a #NA error is returned

Fixed

• Python datetime.date and datetime.time instances are correctly handled when returned from functions
• Lists of strings are not unpacked into list of lists of characters when returned to Excel
• Optional lists now work correctly when not passed from Excel, previously the mapped function was not being called
• Removed unused sections in the xlSlim License Manager application

• The utility function ViewPyObject() has been improved

Changed

• nan and inf doubles are now returned as #NUM errors
• nulls and None are now returned as #NA errors
• Lists of strings are not unpacked into list of lists of characters when returned to Excel

Please have a look. Note that the only recognised type hint for dates and times is still datetime, although xlSlim will recognise datetime.dates, datetime.times and pandas Timestamps when returned by functions.

All the best,

Russel