Как вы знаете, использовать веб-технологии в Microsoft Excel можно с помощью веб-запросов и импорта данных – функционала, заложенного в продукт изначально.
В данной статье мы рассмотрим использование веб-сервисов на базе табличного процессора. За базу возьмем сервисы, предоставленные Банком России.
Веб-сервис (веб-служба, web service) – это общедоступная программная система, определяемая строкой URI, взаимодействие с которой осуществляется посредством SOAP-сообщений, основанных на XML, и передаваемых по сети Интернет.
Интерфейс веб-сервисов описан в формате WSDL (Web Services Description Language).
В данной статье речь пойдет о функционале, недоступном в стандартной комплектации. Нам придется установить дополнительные компоненты для работы с веб-сервисами. Ниже приведены ссылки на установочные файлы:
Далее мы рассмотрим инструментарий под Micrisoft Office 2003.
Запустим установщик и пройдем все этапы установки…
По окончании процесса во встроенный в Microsoft Excel редактор Visual Basic for Applications войдет функционал работы с веб-сервисами.
Перейдем в режим редактирования кода 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, которая будет отражать начало таблицы курсов. Наиболее простым способом присвоения имени является следующий:
Аналогично поступим и с ячейкой B6: присвоим ей имя table.
Для внедрения кнопки используем элемент управления Кнопка:
Пока мы в режиме конструктора, добавим для кнопки код – из контекстного меню выберем пункт Исходный текст:
Появится окно редактора 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 – она будет определять начало таблицы со значениями курсов валют. |
Dim objectPath As String Dim objectNodeList As IXMLDOMNodeList objectPath = "*" Set objectNodeList = rates.wsm_GetCursOnDateXML(rateDate). _ Item(0).selectNodes(objectPath) |
Автоматически сгенерированная функция wsm_GetCursOnDateXML определена в формате XML (MSXML2.IXMLDOMNodeList). |
If objectNodeList.Length > 0 Then colIndex = 0 rowIndex = 0 For Each objectNode In … For Each propertyNode In … Next … Next End If |
Если веб-сервис возвращает значения курсов (Length > 0), формируем таблицу. |
Dim objectNode As IXMLDOMElement Dim propertyPath As String |
Данный код перебирает последовательно каждый элемент возращенного XML документа (For Each objectNode In objectNodeList…). Пример элемента: <ValuteCursOnDate> Внутри данного элемента перебираем все вложенные элементы (For Each propertyNode In objectNode.selectNodes(propertyPath)…) и разносим их содержимое по столбцам:
|
Так как теперь мы знаем набор значений, который попадет в нашу таблицу, мы можем сформировать заголовки столбцов.
В коде выше мы указали ячейку B6 в качестве базовой для вставки курсов валют, так что на 5-ой строке мы можем без проблем разместить наши заголовки:
Настало время попробовать нашу таблицу в действии. Выйдем из режима конструктора на панели инструментов Visual Basic и нажмем кнопку Обновить.
В строках 6-23 формируется таблица курсов иностранных валют на основании полученных от веб-сервиса данных за заданную дату.
Осталось лишь придать таблице приличный вид. На этом этапе вы поймете всю прелесть использования имени ячейки, на которое мы ссылаемся в коде (date, table), вместо фактического адреса (C4, B6): можно без труда перенести эти ячейки в любое место таблицы – присвоенное имя сохранится – это поможет избежать лишнюю правку кода.
Допустим, финальный вариант будет выглядеть так:
В данной статье мы познакомились с функционалом работы с веб-сервисами в Microsoft Excel, работы с XML документами, именованными диапазонами. Если у вас возникли вопросы по использованию веб-сервисов - вы можете оставить комментарий внизу статьи и я постараюсь на него ответить.