使用 GetObject() 方法从 Excel VBA 调用 WCF Webservice
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12745115/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling WCF Webservice from Excel VBA using GetObject() method
提问by Anish V
Possible Duplicate:
How do I call WCF client from Excel 2003 VBA?
I want to call a web service developed using WCF from the Excel using VBA code. How can we do this ? I have tried the GetObject()method but I'm getting syntax error while using this. I want to display the data that I'm getting from the web service in Excel. Please help.
The DataContract is as follows:
`
我想使用 VBA 代码从 Excel 调用使用 WCF 开发的 Web 服务。我们应该怎么做 ？我已尝试过该GetObject()方法，但在使用该方法时出现语法错误。我想在 Excel 中显示我从 Web 服务获取的数据。请帮忙。数据契约如下：`
[DataContract]
public class Data
{
[DataMember]
public int Id;
[DataMember]
public DateTime LockTime;
[DataMember]
public DateTime LoginTime;
[DataMember]
public DateTime LastDefenitionDate;
[DataMember]
public string NTLogin;
[DataMember]
public string SystemName;
}
ServiceContract is as follows:
服务合同如下：
`
`
[ServiceContract]
interface IDataService
{
[OperationContract]
List<Data> GetData();
[OperationContract]
void SubmitData(Data data);
}
`
`
DataService for accessing database is as follows:
访问数据库的DataService如下：
`
`
[ServiceBehavior(InstanceContextMode=InstanceContextMode.Single)]
[ServiceBehavior(InstanceContextMode=InstanceContextMode.Single)]
public class DataService : IDataService { public static SQLConnection SQLDBConnection = new SQLConnection();
公共类数据服务：IDataService { public static SQLConnection SQLDBConnection = new SQLConnection();
#region IDataService Members
public List<Data> GetData()
{
List<Data> datas = new List<Data>();
try
{
if (SQLDBConnection.con.State == ConnectionState.Closed) SQLDBConnection.con.Open();
datas.Clear();
SqlCommand sqlcommand = new SqlCommand();
sqlcommand.Connection = SQLDBConnection.con;
sqlcommand.CommandText = "select * from tblData";
sqlcommand.CommandType = CommandType.Text;
SqlDataAdapter sqladapter = new SqlDataAdapter(sqlcommand);
DataTable dt = new DataTable();
sqladapter.Fill(dt);
Data data = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
data = new Data();
data.Id = Convert.ToInt32(dt.Rows[i]["id"]);
data.NTLogin = dt.Rows[i]["NTLogin"].ToString();
data.SystemName = dt.Rows[i]["SystemName"].ToString();
data.LockTime = Convert.ToDateTime(dt.Rows[i]["LockTime"]);
data.LoginTime = Convert.ToDateTime(dt.Rows[i]["LoginTime"]);
data.LastDefenitionDate = Convert.ToDateTime(dt.Rows[i]["LastDefenitionDate"]);
datas.Add(data);
}
}
catch (Exception ex)
{ }
return datas;
}
public void SubmitData(Data data)
{
if (SQLDBConnection.con.State == ConnectionState.Closed) SQLDBConnection.con.Open();
SqlCommand sqlcommand = new SqlCommand();
sqlcommand.Connection = SQLDBConnection.con;
sqlcommand.CommandText = "Insert into dbo.tblData(NTLogin, SystemName, LockTime, LoginTime, LastDefenitionDate) values ('" + data.NTLogin + "','" + data.SystemName + "','" + data.LockTime + "' , '" + data.LoginTime + "', '" + data.LastDefenitionDate + "')";
sqlcommand.CommandType = CommandType.Text;
int RowsAffected = sqlcommand.ExecuteNonQuery();
}
#endregion
}
`
`
EDIT:
编辑：
The possible duplicatesuggestion's answer didn't worked out for me. My entire code is given here. I have even checked that post, before posting my question. Please check my code.
在可能重复的建议的答复并没有制定出适合我。我的整个代码在这里给出。在发布我的问题之前，我什至检查了该帖子。请检查我的代码。
回答by Regfor
Read following article, it describes in details how to call WCF service from VBA code using GetObject:
阅读以下文章，它详细介绍了如何使用 GetObject 从 VBA 代码调用 WCF 服务：
Calling WCF Services from Excel VBA clients using the WCF Service Moniker
http://damianblog.com/2009/07/05/excel-wcf/
使用 WCF 服务名称从 Excel VBA 客户端调用 WCF 服务
http://damianblog.com/2009/07/05/excel-wcf/
But it works only for simple WCF service contracts. For more complex things you need to use VSTO
但它仅适用于简单的 WCF 服务合同。对于更复杂的事情，您需要使用 VSTO
UPD:In such case, when using Moniker with MEX contracts, you should use only primitive types, and arrays of primitive types. When you need to use complex type, either e.g. try to pack them into string or use more advanced techniques like WCF Moniker with COM Clients ( http://msdn.microsoft.com/en-us/library/ms752245.aspx) or VSTO.
UPD：在这种情况下，将 Moniker 与 MEX 合约一起使用时，您应该仅使用原始类型和原始类型数组。当您需要使用复杂类型时，例如尝试将它们打包成字符串或使用更高级的技术，如带有 COM 客户端的 WCF Moniker ( http://msdn.microsoft.com/en-us/library/ms752245.aspx) 或 VSTO .
