C# DLL COM，它调用 Web 服务在 Excel VB 中不起作用

Question

我目前有一个项目指南，试图使C# DLL作为COM在Excel和其他平台上使用。目前，它调用 Web 服务并执行计算，例如从 Web 服务获取重力预测。一切都运行良好，除了当我需要使用利用 Web 服务的类方法时。我在Excel中收到一个错误，指出它无法与服务建立SSL/TSL连接，我似乎找不到解决此问题的方法。任何帮助都将不胜感激，因为我是一个终身开发人员，但将其工作到 Excel 中对我来说是全新的。以下是我在代码中的处理方式。

重力结果对象.cs

public class GravityObjResult
{
public string lat { get; set; }
public string latDms { get; set; }
public string lon { get; set; }
public string lonDms { get; set; }
public string eht { get; set; }
public string predictedGravity { get; set; }
}
static void Main(string[] args)
{
}
public static async Task<string> TestGravApi(float lat, float lon, float eht)
{
GravityObjResult gResult = new GravityObjResult();
using (var httpClient = new HttpClient())
{
string url = String.Format("my-api-here-gp?lat={0}&lon={1}&eht={2}", lat, lon, eht);
var json = await httpClient.GetStringAsync(url);
if (json != null)
{
gResult = JsonConvert.DeserializeObject<GravityObjResult>(json);
// Now parse with JSON.Net
return gResult.predictedGravity;
}
}
return String.Empty;
}
}

GPredict.cs

public class GPredict
{
public float Lon { get; set; }
public float Lat { get; set; }
public float Elip { get; set; }
public float Grav { get; set; }
public GPredict(float lon, float lat, float elip)
{
Lon = lon;
Lat = lat;
Elip = elip;
}
public GPredict()
{
}
public float SetLON(float a)
{
Lon = a;
return Lon;
}
public float SetLAT(float a)
{
Lat = a;
return Lat;
}
public float SetELIP(float a)
{
Elip = a;
return Elip;
}
public string GetG()
{
string gReturn = String.Empty;
try
{
var task = WebRequestCall.TestGravApi(Lat, Lon, Elip);
gReturn = float.Parse(task.Result).ToString();
}
catch (Exception e)
{
gReturn = e.ToString();
}
return gReturn;
}
}

动态计算包装器.cs

public class DynamicCalcWrapper : DynamicObject
{
Calculator calc;
GPredict gPredict;
public DynamicCalcWrapper()
{
calc = new Calculator();
gPredict = new GPredict();
}
public override bool TryGetMember(GetMemberBinder binder, out object result)
{
result = null;
switch (binder.Name)
{
case "add":
result = (Func<double, double, double>)((double a, double b) => calc.add(a, b));
return true; 
case "sub":
result = (Func<double, double, double>)((double a, double b) => calc.sub(a, b));
return true; 
case "SetLON":
result = (Func<float, float>)((float a) => gPredict.SetLON(a));
return true;
case "SetLAT":
result = (Func<float, float>)((float a) => gPredict.SetLAT(a));
return true;
case "SetELIP":
result = (Func<float, float>)((float a) => gPredict.SetELIP(a));
return true;
case "GetG":
result = (Func<float, string>)((float a) => gPredict.GetG().ToString());
return true;
}
return false; 
}
}

在 Excel VB 中测试结果：

Sub T()
Dim getG As New GPredict
getG.Lon = 145#
getG.Lat = 41.11
getG.Elip = 33
Dim result As String
result = getG.getG()
MsgBox result
End Sub

请注意，我有属性的常规 getter/setter，但也有第二种方法来设置它，因为我必须允许基于 Fortran 的代码使用它，并且对参数设置的长度等有一些限制。

Answer 1

我想指出的是，在示例中发布上面的代码之前，我也先尝试过它，它首先直接指向上下文"https://"中的 url，直到抛出错误。我能够发现我必须添加以下设置才能通过添加下面的ServicePointManager的代码行来使其运行：

public static async Task<string> TestGravApi(float lat, float lon, float eht)
{
GravityObjResult gResult = new GravityObjResult();
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls
| SecurityProtocolType.Tls11
| SecurityProtocolType.Tls12
| SecurityProtocolType.Ssl3;
using (var httpClient = new HttpClient())
{
string url = String.Format("https://{my-url-here}/api/gravd/gp?lat={0}&lon={1}&eht={2}", lat, lon, eht);
var json = await httpClient.GetStringAsync(url);
//sends from object to string json
//string output = JsonConvert.SerializeObject(product);
if (json != null)
{
gResult = JsonConvert.DeserializeObject<GravityObjResult>(json);
// Now parse with JSON.Net
return gResult.predictedGravity;
}
}
return String.Empty;
}