I am trying to return dynamic array with help of Excel-DNA api. I am able to produce desired output. But I want to produce output in vertical .
here is the c# code for my UDF. i have used this link for producing my sample. http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/
Below code produces this output:
(0,0) (0,1) (0,2) (0,3)
I want output in this format:
(0,0)
(0,1)
(0,2)
(0,3)
Here is the code:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Threading;
using CometCollaborator;
using ExcelDna.Integration;
namespace ExcelUDFs
{
public class UDFs
{
[ExcelFunction(Description = "Make Array")]
public static object MakeArrayAndResize(int columns)
{
object result = MakeArray(columns);
// Call Resize via Excel - so if the Resize add-in is not part of this code, it should still work.
var a = XlCall.Excel(XlCall.xlUDF, "Resize", result);
return a;
}
[ExcelFunction(Description = "Make Array")]
public static object MakeArray( int columns)
{
object[,] result = new string[1, columns];
for (int i = 0; i < 1; i++)
{
for (int j = 0; j < columns; j++)
{
result[i, j] = string.Format("({0},{1})", i, j);
}
}
return result;
}
static Queue<ExcelReference> ResizeJobs = new Queue<ExcelReference>();
// This function will run in the UDF context.
// Needs extra protection to allow multithreaded use.
public static object Resize(object[,] array)
{
ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
if (caller == null)
return array;
int rows = array.GetLength(0);
int columns = array.GetLength(1);
if ((caller.RowLast - caller.RowFirst + 1 != rows) ||
(caller.ColumnLast - caller.ColumnFirst + 1 != columns))
{
// Size problem: enqueue job, call async update and return #N/A
// TODO: Add guard for ever-changing result?
EnqueueResize(caller, rows, columns);
AsyncRunMacro("DoResizing");
return ExcelError.ExcelErrorNA;
}
// Size is already OK - just return result
return array;
//object[,] retArray = new object[columns, rows];
//for(int i=0;i<array.Length;i++)
//{
// retArray[i, 0] = array[0, i];
//}
//return retArray;
}
static void EnqueueResize(ExcelReference caller, int rows, int columns)
{
ExcelReference target = new ExcelReference(caller.RowFirst, caller.RowFirst + rows - 1, caller.ColumnFirst, caller.ColumnFirst + columns - 1, caller.SheetId);
ResizeJobs.Enqueue(target);
}
public static void DoResizing()
{
while (ResizeJobs.Count > 0)
{
DoResize(ResizeJobs.Dequeue());
}
}
static void DoResize(ExcelReference target)
{
try
{
// Get the current state for reset later
XlCall.Excel(XlCall.xlcEcho, false);
// Get the formula in the first cell of the target
string formula = (string)XlCall.Excel(XlCall.xlfGetCell, 41, target);
ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId);
bool isFormulaArray = (bool)XlCall.Excel(XlCall.xlfGetCell, 49, target);
if (isFormulaArray)
{
object oldSelectionOnActiveSheet = XlCall.Excel(XlCall.xlfSelection);
object oldActiveCell = XlCall.Excel(XlCall.xlfActiveCell);
// Remember old selection and select the first cell of the target
string firstCellSheet = (string)XlCall.Excel(XlCall.xlSheetNm, firstCell);
XlCall.Excel(XlCall.xlcWorkbookSelect, new object[] { firstCellSheet });
object oldSelectionOnArraySheet = XlCall.Excel(XlCall.xlfSelection);
XlCall.Excel(XlCall.xlcFormulaGoto, firstCell);
// Extend the selection to the whole array and clear
XlCall.Excel(XlCall.xlcSelectSpecial, 6);
ExcelReference oldArray = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
oldArray.SetValue(ExcelEmpty.Value);
XlCall.Excel(XlCall.xlcSelect, oldSelectionOnArraySheet);
XlCall.Excel(XlCall.xlcFormulaGoto, oldSelectionOnActiveSheet);
}
// Get the formula and convert to R1C1 mode
bool isR1C1Mode = (bool)XlCall.Excel(XlCall.xlfGetWorkspace, 4);
string formulaR1C1 = formula;
if (!isR1C1Mode)
{
// Set the formula into the whole target
formulaR1C1 = (string)XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell);
}
// Must be R1C1-style references
object ignoredResult;
XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlcFormulaArray, out ignoredResult, formulaR1C1, target);
if (retval != XlCall.XlReturn.XlReturnSuccess)
{
// TODO: Consider what to do now!?
// Might have failed due to array in the way.
firstCell.SetValue("'" + formula);
}
}
finally
{
XlCall.Excel(XlCall.xlcEcho, true);
}
}
// Most of this from the newsgroup: http://groups.google.com/group/exceldna/browse_thread/thread/a72c9b9f49523fc9/4577cd6840c7f195
private static readonly TimeSpan BackoffTime = TimeSpan.FromSeconds(1);
static void AsyncRunMacro(string macroName)
{
// Do this on a new thread....
Thread newThread = new Thread(delegate()
{
while (true)
{
try
{
RunMacro(macroName);
break;
}
catch (COMException cex)
{
if (IsRetry(cex))
{
Thread.Sleep(BackoffTime);
continue;
}
// TODO: Handle unexpected error
return;
}
catch (Exception ex)
{
// TODO: Handle unexpected error
return;
}
}
});
newThread.Start();
}
static void RunMacro(string macroName)
{
object xlApp=null;
try
{
xlApp = ExcelDnaUtil.Application;
xlApp.GetType().InvokeMember("Run", BindingFlags.InvokeMethod, null, xlApp, new object[] { macroName });
}
catch (TargetInvocationException tie)
{
throw tie.InnerException;
}
finally
{
Marshal.ReleaseComObject(xlApp);
}
}
const uint RPC_E_SERVERCALL_RETRYLATER = 0x8001010A;
const uint VBA_E_IGNORE = 0x800AC472;
static bool IsRetry(COMException e)
{
uint errorCode = (uint)e.ErrorCode;
switch (errorCode)
{
case RPC_E_SERVERCALL_RETRYLATER:
case VBA_E_IGNORE:
return true;
default:
return false;
}
}
}
}
Convert your 1D array to 2D array and call resize method.
object[,] retArray = new object[a.Length, 1];
for (int i = 0; i < a.Length; i++)
{
retArray[i, 0] = a[i];
}
var ab = XlCall.Excel(XlCall.xlUDF, "Resize", retArray);
return ab;
User contributions licensed under CC BY-SA 3.0