Excel ExcelDNA C # / Try to copy the behavior of Bloomberg BDH () (write array after web request)

Question

I want to copy the behavior of Bloomberg BDH.

BDH makes a web request and writes an array (but does not return an array style). During this web request, the function returns "# N / A Requesting". When the web request is complete, the BDH () function writes the result of the array to the worksheet.

For example, in ExcelDNA, I manage to write on a worksheet with a stream.

Result, if you use the code below in the DNA file, the result

= WriteArray (2; 2)

will be

Line 1> #N/A Requesting Data (0,1)

Row 2> (1,0) (1,1)

The latest release is to replace #N/A Requesting Data with a value and copy the formula. When you uncomment //xlActiveCellType.InvokeMember ("FormulaR1C1Local", you are close to the result, but you do not have the correct behavior

.Dna file

  <DnaLibrary Language="CS" RuntimeVersion="v4.0"> <![CDATA[ using System; using System.Collections.Generic; using System.Reflection; using System.Runtime.InteropServices; using System.Threading; using ExcelDna.Integration; public static class WriteForXL { public static object[,] MakeArray(int rows, int columns) { if (rows == 0 && columns == 0) { rows = 1; columns = 1; } object[,] result = new string[rows, columns]; for (int i = 0; i < rows; i++) { for (int j = 0; j < columns; j++) { result[i, j] = string.Format("({0},{1})", i, j); } } return result; } public static object WriteArray(int rows, int columns) { if (ExcelDnaUtil.IsInFunctionWizard()) return "Waiting for click on wizard ok button to calculate."; object[,] result = MakeArray(rows, columns); var xlApp = ExcelDnaUtil.Application; Type xlAppType = xlApp.GetType(); object caller = xlAppType.InvokeMember("ActiveCell", BindingFlags.GetProperty, null, xlApp, null); object formula = xlAppType.InvokeMember("FormulaR1C1Local", BindingFlags.GetProperty, null, caller, null); ObjectForThread q = new ObjectForThread() { xlRef = caller, value = result, FormulaR1C1Local = formula }; Thread t = new Thread(WriteFromThread); t.Start(q); return "#N/A Requesting Data"; } private static void WriteFromThread(Object o) { ObjectForThread q = (ObjectForThread) o; Type xlActiveCellType = q.xlRef.GetType(); try { for (int i = 0; i < q.value.GetLength(0); i++) { for (int j = 0; j < q.value.GetLength(1); j++) { if (i == 0 && j == 0) continue; Object cellBelow = xlActiveCellType.InvokeMember("Offset", BindingFlags.GetProperty, null, q.xlRef, new object[] { i, j }); xlActiveCellType.InvokeMember("Value", BindingFlags.SetProperty, null, cellBelow, new[] { Type.Missing, q.value[i, j] }); } } } catch(Exception e) { } finally { //xlActiveCellType.InvokeMember("Value", BindingFlags.SetProperty, null, q.xlRef, new[] { Type.Missing, q.value[0, 0] }); //xlActiveCellType.InvokeMember("FormulaR1C1Local", BindingFlags.SetProperty, null, q.xlRef, new [] { q.FormulaR1C1Local }); } } public class ObjectForThread { public object xlRef { get; set; } public object[,] value { get; set; } public object FormulaR1C1Local { get; set; } } } ]]> </DnaLibrary> 

@To Govert

BDH has become the standard in the financial industry. People do not know how to manipulate an array (even Ctrl + Shift + Enter).

BDH is the feature that made Bloomberg so popular (to the detriment of Reuters).

However, I will think about using your method or RTD.

Thank you for your work in Excel DNA.

Answer 1

My problem:

• dynamic array record

• data is retrieved asynchronously via webservice

After discussing with Govert, I decided to take the result as an array and not copy the Bloomberg functions (write an array, but return a single value).

Finally, to solve my problem, I used http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/ and change the resize() form.

This code is not RTD.

The code supports .dna format

 <DnaLibrary RuntimeVersion="v4.0" Language="C#"> <![CDATA[ using System; using System.Collections.Generic; using System.Reflection; using System.Runtime.InteropServices; using System.Threading; using System.ComponentModel; using ExcelDna.Integration; public static class ResizeTest { public static object[,] MakeArray(int rows, int columns) { object[,] result = new string[rows, columns]; for (int i = 0; i < rows; i++) { for (int j = 0; j < columns; j++) { result[i,j] = string.Format("({0},{1})", i, j); } } return result; } public static object MakeArrayAndResize() { // Call Resize via Excel - so if the Resize add-in is not part of this code, it should still work. return XlCall.Excel(XlCall.xlUDF, "Resize", null); } } public class Resizer { static Queue<ExcelReference> ResizeJobs = new Queue<ExcelReference>(); static Dictionary<string, object> JobIsDone = new Dictionary<string, object>(); // This function will run in the UDF context. // Needs extra protection to allow multithreaded use. public static object Resize(object args) { ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference; if (caller == null) return ExcelError.ExcelErrorNA; if (!JobIsDone.ContainsKey(GetHashcode(caller))) { BackgroundWorker(caller); return ExcelError.ExcelErrorNA; } else { // Size is already OK - just return result object[,] array = (object[,])JobIsDone[GetHashcode(caller)]; JobIsDone.Remove(GetHashcode(caller)); return array; } } /// <summary> /// Simulate WebServiceRequest /// </summary> /// <param name="caller"></param> /// <param name="rows"></param> /// <param name="columns"></param> static void BackgroundWorker(ExcelReference caller) { BackgroundWorker bw = new BackgroundWorker(); bw.DoWork += (sender, args) => { Thread.Sleep(3000); }; bw.RunWorkerCompleted += (sender, args) => { // La requete Random r = new Random(); object[,] array = ResizeTest.MakeArray(r.Next(10), r.Next(10)); JobIsDone[GetHashcode(caller)] = array; int rows = array.GetLength(0); int columns = array.GetLength(1); EnqueueResize(caller, rows, columns); AsyncRunMacro("DoResizing"); }; bw.RunWorkerAsync(); } static string GetHashcode(ExcelReference caller) { return caller.SheetId + ":L" + caller.RowFirst + "C" + caller.ColumnFirst; } 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; } } } ]]> </DnaLibrary> 

Answer 2

I assume that you tried the Excel-DNA ArrayResizer sample , which carefully avoids many of the problems you encounter. I would like to understand what you see as flaws in the approach to writing array formulas.

Now about your function:

First, you cannot safely transfer the caller’s Range COM object to another thread, but simply pass the address string and get the COM object from another thread (using the call to ExcelDnaUtil.Application in the workflow). Although in most cases you are lucky. The best way to do this from a workflow is to force Excel to run the macro in the main thread - by calling Application.Run. The Excel-DNA ArrayResizer sample shows how this can be done.

Secondly, you almost certainly don't need ActiveCell, but rather Application.Caller. ActiveCell may well have nothing to do with the cell from which the formula is launched.

Next, Excel will recount your function every time you set the formula again - therefore, you will be in an infinite loop when you include the set of formulas in the finally clause. You cannot set a value and a formula for a cell - if the cell has a formula, then Excel will use the formula to calculate the value. If you set the value, the formula will be deleted. It's unclear what you really want to leave in the [0,0] cell - Bloomberg IIRC changes the formula there so that it remembers how large the range was recorded. You can try adding some parameters to your function that tell your function whether to recalculate or return the actual value as a result.

Finally, you can reconsider whether BDH Bloomberg is a good example of what you want to do. This disrupts the calculation of the dependencies of your worksheet, which affects both performance and maintaining the consistency of the spreadsheet model.

Answer 3

I think you need to implement the request as an RTD server. Normal user functions will not be updated asynchronously.
You can then hide the call to the RTD server using a custom function that can be executed through Excel-DNA.

Answer 4

So finally you use the Array formula, right? As you said, users are not familiar with the array formula, they do not know ctrl + shift + enter. I think the array formula is a big problem for them.

I have the same problem for me. I am trying to create a prototype for him. see https://github.com/kchen0723/ExcelAsync.git