Excel ExcelDNA C#/尝试复制Bloomberg BDH()行为(在web请求后写入Array)
本文关键字：请求 web Array 行为 ExcelDNA 复制 Bloomberg Excel BDH | 更新日期: 2023-09-27 18:27:25
我想复制Bloomberg BDH行为。
BDH发出一个web请求并编写一个数组（但不返回数组样式）。在此web请求期间，函数返回"#N/A正在请求"。web请求完成后，BDH（）函数将数组结果写入工作表。
例如，在ExcelDNA中，我成功地用线程在工作表中进行了写入。
如果你在DNA文件中使用下面的代码，的结果
=WriteArray（2；2）
将是
第1行>#N/A Requesting Data (0,1)
第2行>(1,0) (1,1)
最后一个问题是用值替换#N/A Requesting Data并复制公式。当您取消注释//xlActiveCellType.InvokeMember（"FormulaR1C1Local"）时，您接近结果，但没有正确的行为
文件.dna
<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>
@致政府
BDH已成为金融业的一种标准。人们不知道如何操作数组（即使是Ctrl+Shift+Enter）。
BDH是彭博社如此受欢迎的功能（对路透社不利）。
然而，我会考虑使用您的方法或RTD。
感谢您在Excel DNA 中所做的所有工作
我想您已经尝试过Excel DNA ArrayResizer样本，它小心地避免了您遇到的许多问题。我想了解您认为的数组公式编写方法的缺点。
现在，关于你的功能：
首先，您无法安全地将"caller"Range COM对象传递给另一个线程，而是传递一个带有地址的字符串，然后从另一个螺纹获取COM对象（在工作线程上使用对ExcelDnaUtil.Application的调用）。不过，大多数时候你会很幸运。更好的方法是从工作线程调用Application.run，让Excel在主线程上运行宏。Excel DNA ArrayResizer示例显示了如何做到这一点。
其次，你几乎肯定不想要ActiveCell，而是想要Application.Caller。ActiveCell很可能与公式运行的单元格无关。
下一步-每次你再次设置公式时，Excel都会重新计算你的函数，因此当你在finally子句中启用公式集时，你会陷入一个无休止的循环不能同时设置单元格的"值"answers"公式"-如果单元格有"公式"，Excel将使用该公式计算"值"。如果设置"值"，则"公式"将被删除。目前还不清楚你想在[0,0]单元格中实际保留什么-IIRC Bloomberg修改了那里的公式，使其记住写入的范围有多大。你可以尝试在函数中添加一些参数，告诉函数是否重新计算或是否返回实际值作为结果。
最后，您可能需要重新考虑Bloomberg BDH函数是否是您想要做的事情的一个好例子。它打破了工作表的依赖性计算，这对性能和保持电子表格模型的一致性都有影响。
我的问题是：
-
写入动态阵列
-
通过Web服务异步检索数据
在与Govert讨论后，我选择将结果作为数组，而不是复制Bloomberg函数（编写一个数组，但返回一个值）。
最后，为了解决我的问题，我使用http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/并重塑CCD_ 4函数。
此代码不是RTD。
下面的代码在.dna文件中工作
<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>
我认为您需要将请求实现为RTD服务器。正常的用户定义函数不会异步更新
然后，您可以通过用户定义的函数隐藏RTD服务器的调用，该函数可以通过Excel DNA完成。
所以最后使用数组公式，对吧？正如您所说，用户不熟悉数组公式，他们不知道ctrl+shift+enter。我认为数组公式对他们来说是个大问题。
对我来说，我也有同样的问题。我正试图为它建立一个原型https://github.com/kchen0723/ExcelAsync.git