Создание пользовательской функции, которая вызывает веб-службу
This example shows how to call an external Web service from a user-defined function (UDF). The Web service used in this example is:
http://webservices.imacination.com/distance/Distance.jws?wsdl You must use Microsoft Visual Studio 2005 or a similar Microsoft .NET Framework 2.0-compatible development tool to create this sample.
Примечание.
[!Примечание] Before testing the code, make sure that the Web service you are calling is available. The Web service server could be down or the Web service discontinued. If the Web service is unavailable, the calls you make to the Web service from your code will fail. > Вы можете проверить, доступна ли веб-служба, посетив ее сайт. В этом примере URL-адрес: >http://webservices.imacination.com/distance/Distance.jws?wsdl> Если веб-служба доступна, вы увидите язык описания веб-служб (WSDL). Если он недоступен, вы получите обычную ошибку "веб-страница не найдена".
Пример
You can learn more about the Web service used in this example by examining its WSDL.
One service it provides is to return geographical coordinates in decimal form. В этом примере добавлена ToDegreeNotation функция, которая показывает, как можно преобразовывать координаты в градусы/минуты/секунды, что лучше подходит для отображения координат.
[UdfMethod]
public string ToDegreeNotation(double angle)
{
int deg = (int)angle;
double minutesAndSeconds = Math.Abs(angle - deg) * 60;
int minutes = (int)minutesAndSeconds;
int seconds = (int)(Math.Abs(minutesAndSeconds - minutes) * 60);
return deg.ToString() + "°" + minutes.ToString() + "\\'" +
seconds.ToString() + "\\"";
}
<UdfMethod> _
Public Function ToDegreeNotation(ByVal angle As Double) As String
Dim deg As Integer = CInt(Fix(angle))
Dim minutesAndSeconds As Double = Math.Abs(angle - deg) * 60
Dim minutes As Integer = CInt(Fix(minutesAndSeconds))
Dim seconds As Integer = CInt(Fix(Math.Abs(minutesAndSeconds - minutes) * 60))
Return deg.ToString() & "°" & minutes.ToString() & "'" & seconds.ToString() & """"
End Function
If your Internet Explorer LAN setting is configured to use a proxy server, your code must explicitly make a call to set the proxy server. Otherwise, your Web service calls will fail. You can set the proxy server in the constructor as follows:
namespace ZipCodeUdfSample
{
[UdfClass]
public class ZipCodeUdfs
{
DistanceService distanceService = null;
public ZipCodeUdfs()
{
this.distanceService = new DistanceService();
this.distanceService.Proxy =
new WebProxy("http://myproxy:80", true);
}
Namespace ZipCodeUdfSample
<UdfClass> _
Public Class ZipCodeUdfs
Private distanceService As DistanceService = Nothing
Public Sub New()
Me.distanceService = New DistanceService()
'this.distanceService.Proxy = new WebProxy("http://myproxy:80", true);
End Sub
Дополнительные сведения о том, как тестировать и вызывать определяемые пользователем функции из ячеек, см. в разделе Пошаговое руководство. Разработка Managed-Code определяемой пользователем функции.
using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using Microsoft.Office.Excel.Server.Udf;
using ZipCodes.com.imacination.webservices;
namespace ZipCodeUdfSample
{
[UdfClass]
public class ZipCodeUdfs
{
DistanceService distanceService = null;
public ZipCodeUdfs()
{
this.distanceService = new DistanceService();
//this.distanceService.Proxy = new WebProxy("http://myproxy:80", true);
}
[UdfMethod]
public double GetDistanceBetweenTwoZipCodes(int zip1, int zip2)
{
string zip1String = Convert.ToString(zip1);
string zip2String = Convert.ToString(zip2);
return (distanceService.getDistance(zip1String, zip2String));
}
[UdfMethod]
public string GetCityFromZip(int zip)
{
string zipString = Convert.ToString(zip);
return (distanceService.getCity(zipString));
}
[UdfMethod]
public string GetStateFromZip(int zip)
{
string zipString = Convert.ToString(zip);
return (distanceService.getState(zipString));
}
[UdfMethod]
public string GetLocationFromZip(int zip)
{
string zipString = Convert.ToString(zip);
return (distanceService.getLocation(zipString));
}
[UdfMethod]
public double GetLatitudeFromZip(int zip)
{
string zipString = Convert.ToString(zip);
return (distanceService.getLatitude(zipString));
}
[UdfMethod]
public double GetLongitudeFromZip(int zip)
{
string zipString = Convert.ToString(zip);
return (distanceService.getLongitude(zipString));
}
[UdfMethod]
public string ToDegreeNotation(double angle)
{
int deg = (int)angle;
double minutesAndSeconds = Math.Abs(angle - deg) * 60;
int minutes = (int)minutesAndSeconds;
int seconds = (int)(Math.Abs(minutesAndSeconds - minutes) * 60);
return deg.ToString() + "°" + minutes.ToString() + "\\'" + seconds.ToString() + "\"";
}
}
}
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Net
Imports Microsoft.Office.Excel.Server.Udf
Imports ZipCodes.com.imacination.webservices
Namespace ZipCodeUdfSample
<UdfClass> _
Public Class ZipCodeUdfs
Private distanceService As DistanceService = Nothing
Public Sub New()
Me.distanceService = New DistanceService()
'this.distanceService.Proxy = new WebProxy("http://myproxy:80", true);
End Sub
<UdfMethod> _
Public Function GetDistanceBetweenTwoZipCodes(ByVal zip1 As Integer, ByVal zip2 As Integer) As Double
Dim zip1String As String = Convert.ToString(zip1)
Dim zip2String As String = Convert.ToString(zip2)
Return (distanceService.getDistance(zip1String, zip2String))
End Function
<UdfMethod> _
Public Function GetCityFromZip(ByVal zip As Integer) As String
Dim zipString As String = Convert.ToString(zip)
Return (distanceService.getCity(zipString))
End Function
<UdfMethod> _
Public Function GetStateFromZip(ByVal zip As Integer) As String
Dim zipString As String = Convert.ToString(zip)
Return (distanceService.getState(zipString))
End Function
<UdfMethod> _
Public Function GetLocationFromZip(ByVal zip As Integer) As String
Dim zipString As String = Convert.ToString(zip)
Return (distanceService.getLocation(zipString))
End Function
<UdfMethod> _
Public Function GetLatitudeFromZip(ByVal zip As Integer) As Double
Dim zipString As String = Convert.ToString(zip)
Return (distanceService.getLatitude(zipString))
End Function
<UdfMethod> _
Public Function GetLongitudeFromZip(ByVal zip As Integer) As Double
Dim zipString As String = Convert.ToString(zip)
Return (distanceService.getLongitude(zipString))
End Function
<UdfMethod> _
Public Function ToDegreeNotation(ByVal angle As Double) As String
Dim deg As Integer = CInt(Fix(angle))
Dim minutesAndSeconds As Double = Math.Abs(angle - deg) * 60
Dim minutes As Integer = CInt(Fix(minutesAndSeconds))
Dim seconds As Integer = CInt(Fix(Math.Abs(minutesAndSeconds - minutes) * 60))
Return deg.ToString() & "°" & minutes.ToString() & "'" & seconds.ToString() & """"
End Function
End Class
End Namespace
См. также
Задачи
Step 1: Creating a Project and Adding a UDF Reference
Step 2: Creating a Managed-Code UDF
Step 3: Deploying and Enabling UDFs
Step 4: Testing and Calling UDFs from Cells
Понятия
Другие ресурсы
Этап 3. Получение доступа к веб-службе
Пошаговое руководство. Разработка настраиваемого приложения с помощью веб-служб Excel