背景信息:
我正在构建一个基于VBA运行的SCADA系统,但我需要一些C#的功能。我使用C#构建了一个DLL库,并成功实现了DLL与VBA之间的数据传输。
[ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
public class BE_Log
{
public string DateTime
{
[return: MarshalAs(UnmanagedType.BStr)]
get;
[param: MarshalAs(UnmanagedType.BStr)]
set;
}
public string User
{
[return: MarshalAs(UnmanagedType.BStr)]
get;
[param: MarshalAs(UnmanagedType.BStr)]
set;
}
public string SCADA
{
[return: MarshalAs(UnmanagedType.BStr)]
get;
[param: MarshalAs(UnmanagedType.BStr)]
set;
}
public string Tag
{
[return: MarshalAs(UnmanagedType.BStr)]
get;
[param: MarshalAs(UnmanagedType.BStr)]
set;
}
public string Area1
{
[return: MarshalAs(UnmanagedType.BStr)]
get;
[param: MarshalAs(UnmanagedType.BStr)]
set;
}
public string Area2
{
[return: MarshalAs(UnmanagedType.BStr)]
get;
[param: MarshalAs(UnmanagedType.BStr)]
set;
}
public string Description
{
[return: MarshalAs(UnmanagedType.BStr)]
get;
[param: MarshalAs(UnmanagedType.BStr)]
set;
}
public string ValueOld
{
[return: MarshalAs(UnmanagedType.BStr)]
get;
[param: MarshalAs(UnmanagedType.BStr)]
set;
}
public string ValueNew
{
[return: MarshalAs(UnmanagedType.BStr)]
get;
[param: MarshalAs(UnmanagedType.BStr)]
set;
}
public BE_Log(string DataTime, string User, string SCADA, string Tag, string Area1, string Area2,string Description)
{
this.DateTime = DateTime;
this.User = User;
this.SCADA = SCADA;
this.Tag = Tag;
this.Area1 = Area1;
this.Area2 = Area2;
this.Description = Description;
}
public BE_Log(string DataTime, string User, string SCADA, string Tag, string Area1, string Area2, string Description, string ValueOld, string ValueNew)
{
this.DateTime = DateTime;
this.User = User;
this.SCADA = SCADA;
this.Tag = Tag;
this.Area1 = Area1;
this.Area2 = Area2;
this.Description = Description;
this.ValueOld = ValueOld;
this.ValueNew = ValueNew;
}
}
我把这个类返回了,代码如下:
[ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
public class TI
{
private BLL_LogBook bll;
public TI()
{
bll = new BLL_LogBook();
}
[return: MarshalAs(UnmanagedType.SafeArray, SafeArraySubType=VarEnum.VT_USERDEFINED)] // SafeArrayUserDefinedSubType = typeof(BE_Log)
public BE_Log[] CreateLogBook()
{
List<BE_Log> logs = bll.GetLogEntry();
return logs.ToArray();
}
}
我的数据层:
public class BLL_LogBook
{
public List<BE_Log> GetLogEntry()
{
List<BE_Log> logs = new List<BE_Log>();
logs.Add(new BE_Log("05-05-2015", "some user", "scada01", "LA010NDA10CU12XQ12", "Ribe", "Esbjerg", "Some short description"));
logs.Add(new BE_Log("06-05-2015", "test user", "scada01", "LA010NDA10CU12XB05", "Herning", "KBH", "Some long description"));
logs.Add(new BE_Log("07-05-2015", "normal user", "scada02", "LA010NDA10CU12YQ01", "Åhus", "Tønder", "Some test description"));
return logs;
}
}
被调用的静态方法是VBA:
static class UnmanagedExports
{
[DllExport]
[return: MarshalAs(UnmanagedType.IDispatch)]
static Object TI_Object()
{
return new TI();
}
}
在VBA中,我使用以下方法获取数据:
Declare Function TI_Object Lib "<path>\\TJI.dll" () As Object
Sub TestTheTestClass()
Dim TJI As Object
Set TJI = TI_Object()
Dim test As Variant
test = TJI.CreateLogBook()
Dim log As Variant
Set log = test(0)
Debug.Print log.User
End Sub
现在来到我的问题:
如何返回类“BE_Log”的数组或列表
编辑: 这就是我卡住的地方:http://puu.sh/hnPGe/472ff863d0.png 我一直在尝试阅读微软的一些文档,但没有太多的成功。
我最初遵循的指南是这个:
http://www.analystcave.com/excel-use-c-sharp-in-excel-vba/ 他说了以下内容,但我并不完全理解它:
我认为这与“MarshalAs”或我在VBA中读取数据的方式有关。如果您使用数组作为参数,请确保使用C#“ref”按引用获取选项,例如ref int[] ar
test(0)
和test(1)
的"<No Variables>"。如果BE_Log是一个集合类型,并且集合为空,我会期望看到这种情况。此外,你正在访问log.DateTime
,这不是你发布的Log类的有效属性。如果你发布一个完整、一致的示例,将会更有帮助-请参阅http://stackoverflow.com/help/mcve。 - Joe