Веб-сервисы в Microsoft Excel

Как вы знаете, использовать веб-технологии в Microsoft Excel можно с помощью веб-запросов и импорта данных – функционала, заложенного в продукт изначально.

В данной статье мы рассмотрим использование веб-сервисов на базе табличного процессора. За базу возьмем сервисы, предоставленные Банком России. 

Веб-сервисы

Веб-сервис (веб-служба, web service) – это общедоступная программная система, определяемая строкой URI, взаимодействие с которой осуществляется посредством SOAP-сообщений, основанных на XML, и передаваемых по сети Интернет.

Интерфейс веб-сервисов описан в формате WSDL (Web Services Description Language).

Добавление инструментария

В данной статье речь пойдет о функционале, недоступном в стандартной комплектации. Нам придется установить дополнительные компоненты для работы с веб-сервисами. Ниже приведены ссылки на установочные файлы:

• Microsoft Office XP: Web Services Toolkit 2.0
• Microsoft Office 2003: Web Services Toolkit 2.01, а также необязательное дополнение для более глубокого изучения предмета –  Research Service Development Extras

Далее мы рассмотрим инструментарий под Micrisoft Office 2003.

Установка Web Services Toolkit

Запустим установщик и пройдем все этапы установки…

По окончании процесса во встроенный в Microsoft Excel редактор Visual Basic for Applications войдет функционал работы с веб-сервисами.

Работа в Excel

Перейдем в режим редактирования кода Visual Basic (Alt+F11).

Теперь в меню Tools появился пункт Web Service References… (Связи с веб-сервисами), в который мы и перейдем.

Создание связи с веб-сервисом

Создадим связь с веб-сервисом, предоставленным Банком России.

Перейдем в браузере в раздел Технические ресурсы сайта Банка России и найдем ссылку на интересующий нас сервис. Допустим, мы хотим знать официальный курс иностранной валюты к рублю: в этом случае ссылка на сервис будет http://cbr.ru/DailyInfoWebServ/DailyInfo.asmx.

Введем этот адрес в поле Web Service URL и нажмем Search:

По завершении поиска в правой части окна появится корневой элемент веб-сервиса DailyInfo.

Его мы можем раскрыть и увидеть весь набор вложенных функций. Для каждой из таких функций дается краткое описание в поле Description. Проверить работоспособность сервиса можно, нажав на кнопку Test.

Чтобы добавить связь необходимо выбрать родительский веб-сервис и нажать Add.

Excel автоматически сгенерирует класс clsws_DailyInfo на основании полученного от сервера описания:

Пример кода для функции, возвращающей значения курсов валют, которую мы используем далее:

Public Function wsm_GetCursOnDateXML(ByVal dtm_On_date As Date) As MSXML2.IXMLDOMNodeList
    '*****************************************************************
    'Proxy function created from http://cbr.ru/DailyInfoWebServ/DailyInfo.asmx?wsdl.
    '
    '"wsm_GetCursOnDateXML" is defined as XML. See Complex Types: XML Variables in
    'Microsoft Office 2003 Web Services Toolkit Help for details on implementing XML variables.
    '*****************************************************************

    'Error Trap
    On Error GoTo wsm_GetCursOnDateXMLTrap

    Set wsm_GetCursOnDateXML = sc_DailyInfo.GetCursOnDateXML(dtm_On_date)

    Exit Function
    wsm_GetCursOnDateXMLTrap:
    DailyInfoErrorHandler "wsm_GetCursOnDateXML"

End Function

Разработка фронт-офиса

Создадим простую таблицу, куда пользователь будет вводить дату и нажимать кнопку для получения курсов валют на заданную дату.

Для более простого использования в дальнейшем, присвоим имя ячейке, в которую пользователь вводит дату курсов (в нашем случае это ячейка C4), а также ячейке B6, которая будет отражать начало таблицы курсов. Наиболее простым способом присвоения имени является следующий:

• Выделяем ячейку,
• Переходим в поле Имя, расположенное слева от строки формул,
• Заменяем текущее имя “C4” на “date”.

Аналогично поступим и с ячейкой B6: присвоим ей имя table.

Для внедрения кнопки используем элемент управления Кнопка:

• на панели инструментов Visual Basic нажмем кнопку Элементы управления
• на появившейся панели выберем элемент Кнопка
• в любом месте на таблице рисуем кнопку, при этом Excel входит в режим конструктора. В нашем случае это означает, что мы сможем взаимодействовать с элементом: изменять размеры, свойства, перемещать по таблице; однако, не сможем выполнить действие – нажать на кнопку.

Пока мы в режиме конструктора, добавим для кнопки код – из контекстного меню выберем пункт Исходный текст:

Появится окно редактора Visual Basic.

Работа с веб-сервисом

Ниже приведен финальный код, который мы и разберем.

Private Sub CommandButton1_Click()

    Dim rates As clsws_DailyInfo
    Set rates = New clsws_DailyInfo

    Dim rateDate As String
    Dim objectPath As String
    Dim propertyPath As String
    Dim objectNodeList As IXMLDOMNodeList
    Dim objectNode As IXMLDOMElement
    Dim propertyNode As IXMLDOMElement
    Dim baseRow&, baseCol&, rowIndex&, colIndex&
    Dim baseCell As Range

    rateDate = Range("date").Text
    objectPath = "*"
    propertyPath = "*"

    Set baseCell = Range("table")
    baseRow = baseCell.Row
    baseCol = baseCell.Column

    Set objectNodeList = rates.wsm_GetCursOnDateXML(rateDate).Item(0).selectNodes(objectPath)
    If objectNodeList.Length > 0 Then
        colIndex = 0
        rowIndex = 0
        For Each objectNode In objectNodeList
            colIndex = 0
            For Each propertyNode In objectNode.selectNodes(propertyPath)
                ActiveSheet.Cells(baseRow + rowIndex, baseCol + colIndex).Value = propertyNode.Text
                colIndex = colIndex + 1
            Next
            rowIndex = rowIndex + 1
        Next
    End If

End Sub

Dim rates As clsws_DailyInfo
Set rates = New clsws_DailyInfo

Dim rateDate As String

rateDate = Range("date").Text

Dim baseCell As Range

Set baseCell = Range("table")
baseRow = baseCell.Row
baseCol = baseCell.Column

Dim objectPath As String

Dim objectNodeList As IXMLDOMNodeList

objectPath = "*"

Set objectNodeList = rates.wsm_GetCursOnDateXML(rateDate). _ Item(0).selectNodes(objectPath)

If objectNodeList.Length > 0 Then
    colIndex = 0
    rowIndex = 0
    For Each objectNode In

        …

        For Each propertyNode In

            …

        Next

    …

    Next
End If

Доработка фронт-офиса

Так как теперь мы знаем набор значений, который попадет в нашу таблицу, мы можем сформировать заголовки столбцов.

В коде выше мы указали ячейку B6 в качестве базовой для вставки курсов валют, так что на 5-ой строке мы можем без проблем разместить наши заголовки:

• Наименование валюты,
• Номинал,
• Курс,
• Цифровой код валюты,
• Буквенный код валюты.

В действии

Настало время попробовать нашу таблицу в действии. Выйдем из режима конструктора на панели инструментов Visual Basic и нажмем кнопку Обновить.

В строках 6-23 формируется таблица курсов иностранных валют на основании полученных от веб-сервиса данных за заданную дату.

Осталось лишь придать таблице приличный вид. На этом этапе вы поймете всю прелесть использования имени ячейки, на которое мы ссылаемся в коде (date, table), вместо фактического адреса (C4, B6): можно без труда перенести эти ячейки в любое место таблицы – присвоенное имя сохранится – это поможет избежать лишнюю правку кода.

Допустим, финальный вариант будет выглядеть так:

Заключение

В данной статье мы познакомились с функционалом работы с веб-сервисами в Microsoft Excel, работы с XML документами, именованными диапазонами. Если у вас возникли вопросы по использованию веб-сервисов - вы можете оставить комментарий внизу статьи и я постараюсь на него ответить.

• VBA
• Web
• XML