[question] is there an option in ExcelDna to return Array "vertically"?

[akasolace]

Output of ExcelDna function returning a range are horizontal. Most of the time I need to have them vertically so I wrap my function call into excel transpose function. Is there an efficient way to do the transpose using ExcelDNA ?

Thank you

[keithalewis]

The data are stored in a 1-d array so just swap rows and columns members before returning.

[akasolace]

@keithalewis my function is returning an array double{], what you are suggesting is to return a matrix double[ , ] with only the first column being filled in, correct?

[govert]

I asked ChatGPT to help here (assuming object arrays, but double would be similar) :

Q: Write a C# function to convert an object[] array to the column of a 2D object[,] array.

A: Here is an example C# function that takes an object array as input and converts it to a single column in a 2D object array:

public static object[,] ConvertTo2DArray(object[] arr)
{
    int numRows = arr.Length;
    int numCols = 1;
    object[,] result = new object[numRows, numCols];

    for (int i = 0; i < numRows; i++)
    {
        result[i, 0] = arr[i];
    }

    return result;
}

This function takes an object array arr as input and creates a new 2D object array result with the same number of rows as the length of arr, and a single column. It then loops through each element in arr and sets the corresponding value in result. Finally, it returns the resulting 2D object array.

Here is an example usage of this function:

object[] arr = new object[] { "apple", "banana", "cherry" };
object[,] result = ConvertTo2DArray(arr);

// output the resulting 2D array
for (int i = 0; i < result.GetLength(0); i++)
{
    for (int j = 0; j < result.GetLength(1); j++)
    {
        Console.Write(result[i, j] + " ");
    }
    Console.WriteLine();
}

This will output:

apple 
banana 
cherry

which is a single column of the 2D object array.

[keithalewis]

The ChatGPT Oracle has pondered your question deeply. If you have a XLOPER12 o with o.val.array.rows == 1 you can achieve nirvana with std::swap(o.val.array.rows, o.val.array.columns). If you see sharp a similar technique achieves the same end without your training wheels touching the ground. You owe The Oracle (1 + o.val.array.rows * o.val.array.columns)*sizeof(XLOPER12) bytes.

[govert]

If you have a XLOPER12 o ...

Oh but in the Excel-DNA world you never have to face such an abomination.

[govert]

You could call

    double[] myArray = ...
    return XlCall.Excel(XlCall.xlfTranspose, myArray);

At the cost of marshaling the array back and forth an extra time.

[keithalewis]

If it is an array of double then the FP/FP12 abominations involve only passing a pointer. Transpose is expensive if the array has more than one row and column.

[akasolace]

Thank you @govert and @keithalewis. If I recap I have 3 options: 1) call transpose from Excel (but not what I want to do) 2) call XlCall.Excel(XlCall.xlfTranspose, myArray) (expecting worst performance than 1 because of extra marshaling) 3) transpose in C# and return object[, ] which force changing the signature but might have the best performance

I will test the difference of performance between 2 and 3 and I will share .... Thank you for your inputs !!

[keithalewis]

4) https://github.com/xlladdins/xll/blob/master/test/transpose.cpp If this is not 10x faster than the other methods, I'll eat my hat.

[akasolace]

4. https://github.com/xlladdins/xll/blob/master/test/transpose.cpp If this is not 10x faster than the other methods, I'll eat my hat.

@keithalewis sorry for my ignorance but you are pointing to another project that converts C code to Excel. It is kinf of an alternative project to ExcelDna, right? It is not really something I can use in my specific case where my full addin is based on ExcelDNA, or am I missing something?

[keithalewis]

AFAIK, ExcelDna does not expose the FP/FP12 datatype, so you will need to use C++ if performance is a concern.