Display array Data Excel Dna

Question

I am having trouble displaying data using Excel-Dna with C#. I have a function which takes in data and processes it to make a table, so I wrote a test function just to display the data, and I am unable to get a value out. The error is #VALUE.

public class Functions : IExcelAddIn
{
    public static String Username { get; set; }
    public static String Password { get; set; }
    public static Random rnd = new Random();

    public void AutoOpen()
    {
        ExcelAsyncUtil.Initialize();
    }

    public void AutoClose()
    {
        ExcelAsyncUtil.Uninitialize();
    }

    [ExcelFunction(Description="My first Excel-DNA function")]
    public static string MyFirstFunction(string name)
    {
        return "Hello, " + name + ".";
    }

    public static string ShowCurrentUser()
    {
        return (String.IsNullOrWhiteSpace(Username)) ? "Noone is logged in." : Username;
    }
    public static string LogIn(string user, string password)
    {
        const string connectionString = "server=localhost;userid={0};password={1};";
        MySqlConnection connection = new MySqlConnection(String.Format(connectionString, user, password));
        string output = "";
        try
        {
            connection.Open();
            Username = user;
            Password = password;
            output = "Successfully logged in!";
        }
        catch (Exception e)
        {
            output = "Errors: " + e.ToString();
        }
        finally
        {
            connection.Close();
        }

        return output;
    }
    public static object QMRTable(int SynNum, int YoA, int qtr, int TabNum)
    {
        object[,] response = new object[16, 3];

        for (int r = 0; r < response.GetLength(0); r++)
            for (int c = 0; c < response.GetLength(1); c++)
                response[r, c] = String.Format("Synd: {0}, YoA: {1}, Qtr: {2}, ({3},{4})", SynNum, YoA, qtr, r, c);

        return XlCall.Excel(XlCall.xlUDF, "Resize", response);
        //return response;
    }
    public static object QMRItem(int SynNum, int YoA, string qtr, string item)
    {
        return (rnd.NextDouble() * (100.0 - 0.0) + 0.0) + " GBP (M)";
    }
}

It seems what I am not understanding is how to set up my add in to have these methods be called correctly.

Does it work without the Resize call? If you are using the Resize from the latest distribution, do you have an AsyncUtil.Initialize() call in an AutoOpen handler? — Govert, Mar 20 '13 at 18:28
Okay, I didn't realize that i needed that, I will try it out and see if that works — Blaze Phoenix, Mar 20 '13 at 18:48
What was happening is that I didn't have the ArrayResizer class available to me so my calls were failing there. By including the ArrayResizer class I was able to make it work, the only thing to do now would be to figure out how to implement it using the dll from source rather than copying his code into mine. — Blaze Phoenix, Mar 20 '13 at 19:20

score 1 · Accepted Answer · answered Mar 21 '13 at 12:58

1

So, the answer is to include the AsyncFunctions.dll as well as include the ExcelAsyncUtil.Initialize(). You need to change your .dna file to look like this:

<DnaLibrary Name="MyExcel Add-In" RuntimeVersion="v4.0">
    <ExternalLibrary Path="MyExcelLibrary.dll" />
    <ExternalLibrary Path="AsyncFunctions.dll" />
</DnaLibrary>

To compile the library for external use, you will have to go into his Distribution folder and find the Async folder [Excel-Dna\Distribution\Samples\Async\AsyncFunctions] and open this solution and build the library. You may need to get the Reactive Extensions Library. You can get it via NuGet with the command, Install-Pakcage Rx-Main.

answered Mar 21 '13 at 12:58

Blaze Phoenix

859
2
14
33

Instead of including the whole AsyncFunction example, you can also just put the ArrayResizer.cs code in your own library. – Govert Mar 21 '13 at 18:00
I compiled the library than added the code to keep the creators code separate from mine both for development purposes and to be sure to give credit to the original writer. – Blaze Phoenix Mar 21 '13 at 22:41

Display array Data Excel Dna

1 Answers1