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

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

Веб-сервисы

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

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

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

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

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

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

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

Установка Web Services Toolkit - этап 1

Установка Web Services Toolkit - этап 2

Установка Web Services Toolkit - этап 3

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

Работа в Excel

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

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

Microsoft Excel - Связи с веб-верисами

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

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

Перейдем в браузере в раздел Технические ресурсы сайта Банка России и найдем ссылку на интересующий нас сервис. Допустим, мы хотим знать официальный курс иностранной валюты к рублю: в этом случае ссылка на сервис будет 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

Описываем переменную rates как сформированный автоматически класс clsws_DailyInfo, создаем новый экземпляр класса.

Dim rateDate As String
rateDate = Range("date").Text

Создаем текстовую переменную rateDate, присваиваем ей значение, введенное пользователем в ячейку с именем date, (ранее – ячейка C4).

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

Создаем переменную типа Range, которой присваиваем значение области на таблице. Теоретически – это может быть любая область, выраженная в текстовом виде: например, “A1:A25”, “A1:D15”, но в нашем случае это будет одна ячейка с именем table – она будет определять начало таблицы со значениями курсов валют.
Переменные baseRow и baseCol определяют номер строки и столбца ячейки baseCell, соответственно.

Dim objectPath As String
Dim objectNodeList As IXMLDOMNodeList
objectPath = "*"
Set objectNodeList = rates.wsm_GetCursOnDateXML(rateDate). _ Item(0).selectNodes(objectPath)

Автоматически сгенерированная функция wsm_GetCursOnDateXML определена в формате XML (MSXML2.IXMLDOMNodeList).
Вернем результат функции в переменную objectNodeList.
Используя синтаксис XPath мы можем выбрать из XML любое множество значений, подставив запрос в переменную objectPath.

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

Если веб-сервис возвращает значения курсов (Length > 0), формируем таблицу.
Переменные colIndex и rowIndex отражают отклонение от ячейки, которую мы взяли за базу (baseCell). Соответственно, каждая новая строка (objectNode) будет прибавлять 1 к rowIndex, каждый новый столбец (propertyNode) – 1 к colIndex.

Dim objectNode As IXMLDOMElement
Dim propertyNode As IXMLDOMElement

Dim propertyPath As String
propertyPath = "*"

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

Данный код перебирает последовательно каждый элемент возращенного XML документа (For Each objectNode In objectNodeList…).
Пример элемента:

<ValuteCursOnDate>
  <Vname>Австралийский доллар</Vname>
  <Vnom>1</Vnom>
  <Vcurs>26.7258</Vcurs>
  <Vcode>36</Vcode>
  <VchCode>AUD</VchCode>
</ValuteCursOnDate>

Внутри данного элемента перебираем все вложенные элементы (For Each propertyNode In objectNode.selectNodes(propertyPath)…) и разносим их содержимое по столбцам:

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

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

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

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

Заголовки формы

В действии

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

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

Загрузка данных

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

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

Финальный вид

Заключение

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

Скачать для Excel 2003, Excel 2007+

Добавить комментарий


Защитный код
Обновить

Похожие статьи

Вверх