Returning list of list results to Excel

[xlSlim Dev]

Hi,

I had some queries regarding returning list of lists to Excel.

Generally xlSlim handles list of list results well, with and without type hints.

I wrote these sample functions to illustrate how list of list results behave:

from typing import List, Any


def only_strings(n):
    result = []
    for i in range(int(n)):
        result.append(["col1", "col2"])
    return result


def only_strings_with_hints(n: int) -> List[List[str]]:
    result = []
    for i in range(n):
        result.append(["col1", "col2"])
    return result


def only_numbers(n):
    result = []
    for i in range(int(n)):
        result.append([i, i * 2.0])
    return result


def only_numbers_with_hints(n: int) -> List[List[float]]:
    result = []
    for i in range(n):
        result.append([i, i * 2.0])
    return result


def mixed_string_and_numbers(n):
    result = []
    for i in range(int(n)):
        result.append(["col1", i * 2.0])
    return result


def mixed_string_and_numbers_with_hints(n: int) -> List[List[Any]]:
    result = []
    for i in range(n):
        result.append(["col1", i * 2.0])
    return result


def inconsistent_columns(n):
    """After 5 rows the first col switches to a string"""
    result = []
    for i in range(int(n)):
        result.append([i if i < 5 else str(i), i * 2.0])
    return result


def inconsistent_columns_with_hints(n: int) -> List[List[float]]:
    """After 5 rows the first col switches to a string"""
    result = []
    for i in range(n):
        result.append([i if i < 5 else str(i), i * 2.0])
    return result


def some_null_values(n):
    """After 5 rows the first col switches to a None"""
    result = []
    for i in range(int(n)):
        result.append([i if i < 5 else None, i * 2.0])
    return result


def some_null_values_with_hints(n: int) -> List[List[Any]]:
    """After 5 rows the first col switches to a None"""
    result = []
    for i in range(n):
        result.append([i if i < 5 else None, i * 2.0])
    return result


def inconsistent_number_of_elements(n):
    """Even rows have 3 elements"""
    result = []
    for i in range(int(n)):
        if i % 2 == 0.0:
            result.append([i, i * 2.0, i * 3.0])
        else:
            result.append([i, i * 2.0])
    return result


def inconsistent_number_of_elements_with_hints(n: int) -> List[List[Any]]:
    """Even rows have 3 elements"""
    result = []
    for i in range(n):
        if i % 2 == 0.0:
            result.append([i, i * 2.0, i * 3.0])
        else:
            result.append([i, i * 2.0])
    return result


if __name__ == "__main__":
    print(only_strings(5))
    print(only_numbers(5))
    print(mixed_string_and_numbers(5))
    print(inconsistent_columns(10))
    print(some_null_values(10))
    print(inconsistent_number_of_elements(10))

The code is better formatted in this gist:

https://gist.github.com/RusselWebber/7f4e78e35258ecfbdfe0a9ff153ebb26

Then I created the attached Excel sheet TestingListOfLists.xlsx to show how the functions behave when called by xlSlim.

xlSlim supports returning these list of lists:

Lists of lists of strings:

[['col1', 'col2'], ['col1', 'col2'], ['col1', 'col2'], ['col1', 'col2'], ['col1', 'col2']]

List of lists of numbers:

[[0, 0.0], [1, 2.0], [2, 4.0], [3, 6.0], [4, 8.0]]

List of list of strings and numbers:

[['col1', 0.0], ['col1', 2.0], ['col1', 4.0], ['col1', 6.0], ['col1', 8.0]]

List of lists with inconsistent types:

[[0, 0.0], [1, 2.0], [2, 4.0], [3, 6.0], [4, 8.0], ['5', 10.0], ['6', 12.0], ['7', 14.0], ['8', 16.0], ['9', 18.0]]

List of lists with some None values

[[0, 0.0], [1, 2.0], [2, 4.0], [3, 6.0], [4, 8.0], [None, 10.0], [None, 12.0], [None, 14.0], [None, 16.0], [None, 18.0]]

The only case where xlSlim correctly fails is when the number of elements in each list changes.

Please let me know if that helps.

Regards,

Russel