UDF function that takes XDocument and writes it to Excel

Aug 2, 2013 at 8:14 PM
Hi,

I am writing an ExcelDna Excel AddIn and know how to create a simple UDF. I wish to have a UDF that can take a complex structure (such as an XDocument or a List) and writes the data to Excel. I have a sample of the call below. I retrieve the XDocument, but have no idea how to complete the task.

Thanks,

Lee
        [ComVisible(true)]
        [ExcelFunction(Description = "Retrieve Loans")]
        public static object RetrieveLoans(string szPortfolioId, string szDealId, DateTime dtDateLimit)
        {
            XDocument XDocResult = null;

            XDocResult = RestWSRetrieval.RetrieveLoan(szPortfolioId, szDealId, dtDateLimit);

            return XDocResult;
        }
Coordinator
Aug 3, 2013 at 12:46 PM
Hi Lee,

Excel only allow a limited number of types to be returned from a UDF. Excel won't recognize an "XDocument" directly. You need to convert it to a string or something that Excel can understand.

The types supported by Excel-DNA are listed here: https://exceldna.codeplex.com/wikipage?title=Reference&referringTitle=Documentation

Regards,
Govert
Aug 7, 2013 at 3:38 PM
Hi Govert,

Thanks for the reply. I am deserializing the XDocument into a DataSet and then converting the DataSet into an object array. My UDF is returning object[,]. My problem is that only the 1st cell in the object is being returned. How do I get the entire object to be returned?
        [ComVisible(true)]
        [ExcelFunction(Description = "Retrieve Loans")]
        public static object RetrieveLoans(string szPortfolioId, string szDealId, DateTime dtDateLimit)
        {
            DataSet ds = new DataSet();
            XDocument XDocResult = null;

            XDocResult = RestWSRetrieval.RetrieveLoan(szPortfolioId, szDealId, dtDateLimit);

            using (XmlReader reader = XDocResult.CreateReader())
            {
                ds.ReadXml(reader);
            }

            int nRowCount = ds.Tables[0].Rows.Count;
            int nColumnCount = ds.Tables[0].Columns.Count;

            var result = new object[nRowCount, nColumnCount];
            for (int i = 0; i < nRowCount; i++)
            {
                DataRow dr = ds.Tables[0].Rows[i];
                for (int j = 0; j < nColumnCount; j++)
                {
                    result[i, j] = dr[j];
                }
            }

            return result;
        }
Coordinator
Aug 7, 2013 at 3:53 PM
Hi Lee,

If your function is returning an array, you need to enter it into Excel as an 'Array Formula'. This is done by selected the result region, then typing in the formula and pressing Ctrl+Shift+Enter. The resulting array formula will be displayed with extra curly brackets, as {=RetrieveLoads(...)}.

For an example of a built-in function that works like this, see the FREQUENCY function and its help.

As an advanced feature, there are some (dangerous) hacks that allow the formula to expand itself to the right size. The Excel-DNA sample Distribution\Samples\ArraResizer.dna shows how this can be done. But it goes against Excel's built in features and is not foolproof.

Regards,
Govert
Aug 7, 2013 at 6:15 PM
Edited Aug 8, 2013 at 1:37 PM
Hi Govert,

I got that to work.

I have seen some examples where the function is scalar (i.e. it spans only a single cell), but outside of the function the rest of the data is written. How can I write data from within a function to a single cell, and write the remainder of the data using the Interop or ExcelDNA methods? In this scenario, the active cell where the formula was typed will have a return and the other cells will have the value (not formula) set. I have seen some threads that say this not possible, but have also seen it implemented in practice on a blank sheet and was wondering how to do it. If attempting via the interop, I get a COM error and if attempting by creating an ExcelReference and using SetValue, the value false is returned.

This is also similar to the post http://stackoverflow.com/questions/9668649/excel-exceldna-c-sharp-try-to-copy-bloomberg-bdh-behavior-writing-array-aft.



Thanks,

Lee
Coordinator
Aug 8, 2013 at 8:36 PM
Hi Lee,

The closest implementation to what you describe that I can support is the ArrayResizer sample I mentioned, where the calling range is automatically extended to contain an array formula of the right size.

Anything closer in behaviour to the Bloomberg BDH function, where your function just writes to other parts of the sheet, is in my opinion very dangerous, and I am not willing to help with that. As described in my StackOverflow answer, this breaks the Excel calculation model, and inevitably leads to trouble later on. Excel-DNA does allow you to schedule a macro to run after calculations with the ExcelAsyncUtil.QueueAsMacro support, which is the only way to implement such things. The macro or delegate you enqueue can then do anything you want since it is not running in the constrained calculation context. But I can give no further help if you choose to go down that route. Others on the Google group might be more willing to assist.

Regards,
Govert