Excel вебслужба не работает
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Инструменты импорта данных из интернет были реализованы в более ранних версиях и позволяли выгружать информацию с веб страниц. Функция ВЕБСЛУЖБА возвращает данные на лист в формате XML, из которого в последующем можно выбрать необходимые данные с помощью функции ФИЛЬТР.XML. В сегодняшней статье мы рассмотрим возможность импорта курса доллара за недельный период (думаю, на фоне текущей нестабильной ситуации на рынках, эта информация актуальна для многих), по полученным данным построим график для возможности отслеживания тренда изменений
Для начала, необходимо найти веб сервис, который возвращает данные в формате XML. Я воспользовался службами Центробанка, который любезно предоставляет свои API для выгрузки данных. По ссылке вы найдете все действительные на текущий момент запросы банка.
Так как нас интересует недельный тренд доллара США, ищем динамику котировок, она находится под заголовком Example 2. Если мы щелкнем по ссылке этого примера, сервис вернет нам данные по котировкам доллара за период 01/03/2001 по 14/03/2001 в формате XML. Обратите внимание на адресную строку в браузере, здесь находится строка запроса к сервису (она то нам и нужна. ) с тремя аргументами: Дата С, Дата ПО и идентификационный номер валюты.
Изменяя любой из них, сервис ЦБ будет возвращать нужную нам информацию.
Обратите внимание, функция ВЕБСЛУЖБА вернула ответ в формате XML, такой же как мы видели в браузере. Теперь с полученного ответа необходимо выбрать нужные нам данные. В этом нам поможет функция ФИЛЬТР.XML, которая использует в качестве аргументов два параметра: первый — XML содержимое, второй – объект Xpath — язык запросов к элементам XML-документа, другими словами, это текстовая строка, которая указывает системе, какие конкретные данные необходимо получить из XML содержимого. Вы можете более подробно прочитать о языке Xpath в статье.
Выделяем ячейки A4:A8, жмем F2, вставляем формулу =ФИЛЬТР.XML(B1; "//Record//@Date") и нажимаем сочетание клавиш Ctrl + Shift + Enter, таким образом у нас получилась формула массива, которая выбирает из веб службы даты изменений курсов валют Центробанком. Таким же образом выделяем соседний диапазон B4:B8 и вставляет туда формулу =ФИЛЬТР.XML(B1;»//Value»). У нас должна получиться следующая таблица:
Так как мой компьютер воспринимает в качестве разделителя дробной части только точку (.), немного подправим формулу, возвращающую курс валют.
Итак, у нас есть статичная таблица изменения курса доллара к рублю за период с 7/08/2013 по 13/08/2013. Так как наша первоначальная задача была – динамическое обновление данных, нам потребуется заменить жестко зафиксированные даты в строке запроса на формулу, которая бы изменяла свое значение в зависимости от текущей даты.
Установим в качестве второго аргумента значение сегодняшней даты плюс один день (т.к. Центробанк устанавливает курс на следующий день), первый аргумент будет равняться второму аргументу минус 6 дней. Зададим обе эти даты в качестве первого и второго аргументов в нашей строке запроса.
Добавим немного красоты для наглядности, теперь вы точно будете знать, когда начинать сливать валюту)
Стоит отметить, что данную функцию можно применять для импорта данных с любых веб служб, предоставляющих свои API в свободном доступе. Такими службами являются API Яндекс, Twitter, API Google и т.д.
Я уже неоднократно разбирал способы импорта данных в Excel из интернета с последующим автоматическим обновлением. В частности:
- В старых версиях Excel 2007-2013 это можно было сделать с помощью прямого веб-запроса.
- Начиная с 2010 года это можно очень удобно делать с помощью надстройки Power Query.
К этим способам в последних версиях Microsoft Excel теперь можно добавить ещё один - импорт данных из интернета в формате XML с помощью встроенных функций.
Начиная с версии 2013 в Excel появились две функции для прямой загрузки XML-данных из интернета в ячейки листа: ВЕБСЛУЖБА (WEBSERVICE) и ФИЛЬТР.XML (FILTERXML) . Работают они в паре - сначала функция ВЕБСЛУЖБА выполняет запрос к нужному сайту и возвращает его ответ в формате XML, а затем с помощью функции ФИЛЬТР.XML мы «разбираем» этот ответ на составляющие, извлекая из него нужные нам данные.
Давайте рассмотрим работу этих функций на классическом примере – импорте курса любой нужной нам валюты на заданный интервал дат с сайта Центробанка России. В качестве заготовки будем использовать вот такую конструкцию:
- В жёлтых ячейках находятся даты начала и окончания интересующего нас периода.
- В синей сделан выпадающий список валют при помощи команды Данные - Проверка - Список (Data - Validation - List) .
- В зеленых ячейках мы будем использовать наши функции, чтобы создать строку запроса и получить ответ сервера.
- Таблица справа - справочник по кодам валют (потребуется нам чуть позже).
Шаг 1. Формируем строку запроса
Как видно из примера, строка запроса должна содержать даты начала (date_req1) и окончания (date_req2) интересующего нас периода и код валюты (VAL_NM_RQ), курс которой мы хотим получить. Коды основных валют вы можете найти в таблице ниже:
Дата последнего изменения: 7 сентября 2011 г.
Применимо к: SharePoint Server 2010
В этой статье
Веб-служба Excel
Общие сведения о безопасности
Visual Studio
Пользовательские функции служб Excel
Общие рекомендации
В этой статье приводятся известные проблемы служб Excel и советы по работе с ними.
Веб-служба Excel
Просмотр расположения WSDL
Если специализированный сайт отсутствует, то можно просмотреть язык WSDL по следующему URL-адресу:
Дополнительные сведения см. в статье Доступ к API-интерфейсу SOAP.
Общие сведения о веб-службах и пространствах имен Excel
Ниже приводятся веб-службы и пространства имен Excel.
Один объект веб-службы, содержащий все методы API: ExcelService
Имя страницы веб-службы: ExcelService.asmx
Установка локальной связи или связи с веб-службой
Дополнительные сведения и рекомендации, касающиеся применимости прямого связывания в том или ином случае, см. в статье Вызовы SOAP с замыканием на себя и прямая привязка.
Общие сведения о недопустимых символах
Вызовы методов GetCell и GetRange могут завершиться неудачно, если в ячейках книги содержатся символы, недопустимые в ответе XML.
Это нормальное поведение. Спецификация XML, которая определяет, какие символы разрешены в допустимом ответе XML, указывает, что шестнадцатеричные значения (0x1, 0x2 . 0x8) являются недопустимыми символами XML:
Сохранение книги
При внесении изменений в книгу, например при задании значений диапазона с помощью веб-служб Excel, эти изменения сохраняются только для этого конкретного сеанса. Изменения не сохраняются и не вносятся в саму книгу. Если текущий сеанс работы с книгой завершается (например, при вызове метода CloseWorkbook, или по истечении времени ожидания сеанса), внесенные изменения будут потеряны.
Если требуется сохранять изменения в книге, можно использовать метод GetWorkbook, а затем сохранить книгу с помощью API конечного файлового хранилища. Дополнительные сведения см. в статьях Получение рабочей книги целиком или ее снимка и How to: Save a Workbook.
Общие сведения о свойстве Url прокси-класса веб-служб Excel
Не используйте свойство Url прокси веб-служб Excel для расположения книги, которую требуется открыть. Свойство Url прокси-класса веб-службы, созданное Visual Studio, задает или возвращает базовый URL-адрес XML-веб-службы, которую запрашивает клиент. В случае веб-служб Excel это обычно следующий адрес:
Чтобы указать расположение книги, используйте метод OpenWorkbook вместо свойства Url , как показано в следующем примере кода.
Общие сведения о безопасности
Использование разрешений книг
Остерегайтесь следующих проблем, связанных с разрешениями книг.
Веб-службы Excel используют схему авторизации Microsoft SharePoint Foundation для проверки, имеет ли вызывающая сторона право на удаленный вызов интерфейсов API (т. е. вызова веб-служб) на сайте SharePoint Foundation (т. е. на веб-сайте, на котором расположены веб-службы Excel). Если вызывающая сторона не имеет прав на использование удаленного API, то веб-службы Excel возвращают ошибку "HTTP 401 (не санкционировано)" и записывают в журнал событие "API authorization failed" ("Сбой авторизации API"). Веб-службы Excel выполняют эти проверки только для вызовов, которые создаются как вызовы SOAP. Вызовы от приложений, которые локально связываются с библиотекой Microsoft.Office.Excel.Server.WebServices.dll, не рассматриваются как удаленные. Следовательно, для них не выполняются проверки авторизации. Однако если приложение, локально связывающееся с библиотекой Microsoft.Office.Excel.Server.WebServices.dll, само является службой SOAP и обрабатывает вызовы SOAP службы, то вызов веб-служб Excel будет выглядеть как вызов SOAP (хотя приложение и связывается непосредственно с библиотекой Microsoft.Office.Excel.Server.WebServices.dll). В этом случае веб-службы Excel будут выполнять проверки авторизации.
Чтобы получить всю книгу (например, путем вызова метода GetWorkbook с аргументом WorkbookType.FullWorkbook), вызывающей стороне потребуется разрешение на открытие книги или разрешение на чтение в общей папке.
Для вызова метода GetApiVersion не требуется никаких разрешений.
Для остальных методов веб-служб Excel вызывающей стороне помимо учетных данных требуется разрешение на просмотр (в SharePoint Foundation) или на чтение (в общей папке) книги.
Надежное расположение
Книга, которую требуется открыть в службах Excel, должна находиться в надежном расположении. Если это не так, то вызовы веб-служб Excel для открытия книги будут завершаться с ошибками.
Visual Studio
Поведение прокси Microsoft Visual Studio
Когда Microsoft Visual Studio создает прокси-класс для клиентского проекта, который вызывает веб-службы Excel, этот прокси-класс ведет себя следующим образом:
Если у метода отсутствует возвращаемое значение, и имеется хотя бы один аргумент out, то первый аргумент out перемещается и становится возвращаемым значением. Таким образом, метод в прокси-классе будет иметь в сигнатуре метода на один аргумент out меньше. Но в сигнатуре будет возвращаемое значение с типом и контентом, которые использовались в первом аргументе out.
Ниже приведены известные проблемы служб Excel и советы по работе с ними.
Excel Web Service
Просмотр расположения WSDL
Если специализированный сайт отсутствует, то можно просмотреть язык WSDL по следующему URL-адресу:
Дополнительную информацию см. в статье Доступ к API SOAP.
О веб-службах и пространствах имен Excel
Ниже приведены веб-службы и пространства имен Excel:
Один объект веб-службы, содержащий все методы API: ExcelService
Имя страницы веб-службы: ExcelService.asmx
Ссылка на локальный файл или на веб-службу
Дополнительную информацию и рекомендации по использованию прямых ссылок см. в статье Кольцевые SOAP-вызовы и прямые ссылки.
О недопустимых символах
Вызовы методов GetCell и GetRange завершатся сбоем, если ячейки книги содержат символы, недопустимые в ответе XML.
System.InvalidOperationException: Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- ' ', hexadecimal value 0x01, is an invalid character.
Такое поведение ожидаемо. Спецификация XML, которая определяет допустимые символы в ответе XML, указывает, что шестнадцатеричные значения 0x1, 0x2. 0x8 являются недопустимыми символами XML:
Сохранение книги
Изменения, вносимые в книгу, например значения, присваиваемые диапазону с помощью веб-служб Excel, сохраняются только на время определенного сеанса. Изменения не сохраняются и не вносятся в исходную книгу. Когда текущий сеанс работы с книгой будет завершен (например, при вызове метода CloseWorkbook или по истечении времени сеанса), внесенные изменения будут утеряны.
Чтобы сохранить изменения в книге, используйте метод GetWorkbook и сохраните книгу с помощью API конечного хранилища файлов. Дополнительную информацию см. в статье Получение всей книги или снимка и Как сохранить книгу.
О свойстве Url прокси-класса веб-служб Excel
Не используйте свойство Url прокси веб-служб Excel для указания расположения книги, которую вы хотите открыть. Свойство Url прокси-класса веб-службы, созданное Visual Studio возвращает или задает базовый URL-адрес веб-службы XML, запрашиваемый клиентом. В случае веб-служб Excel обычно это:
Чтобы указать расположение книги, используйте метод OpenWorkbook, а не свойство Url, как показано в следующем примере кода.
О безопасности
Использование разрешений для книги
Обратите внимание на следующие проблемы, связанные с разрешениями для книги:
Веб-службы Excel используют схему авторизации Microsoft SharePoint Foundation для проверки права вызывающей стороны удаленно вызывать API на веб-сайте SharePoint Foundation. Если у вызывающей стороны нет права "использовать удаленный API", веб-службы Excel возвращают ошибку "HTTP 401 (Unauthorized)" и записывают в журнал событие "API authorization failed". Веб-службы Excel выполняют эти проверки только для вызовов по протоколу SOAP. Вызовы от приложений с локальной ссылкой на файл Microsoft.Office.Excel.Server.WebServices.dll не рассматриваются как удаленные. Однако, если приложение с локальной ссылкой на файл Microsoft.Office.Excel.Server.WebServices.dll само является службой SOAP и обрабатывает вызовы службы по протоколу SOAP, то вызов веб-служб Excel будет выглядеть как вызов по протоколу SOAP (хотя приложение использует прямую ссылку на файл Microsoft.Office.Excel.Server.WebServices.dll). В таком случае веб-службы Excel будут выполнять проверки авторизации.
Чтобы получить всю книгу (например, путем вызова метода GetWorkbook, используя аргумент WorkbookType.FullWorkbook ), вызывающей стороне потребуется разрешение на открытие книги или разрешение на чтение общей папки.
Для вызова метода GetApiVersion разрешения не нужны.
Для остальных методов веб-служб Excel вызывающей стороне, помимо учетных данных, требуется разрешение на просмотр (в SharePoint Foundation) или на чтение (в общей папке) книги.
Надежное расположение
Книга, которую нужно открыть в службах Excel, должна находиться в надежном расположении. В противном случае вызовы веб-служб Excel для открытия книги будут завершаться сбоем.
Информацию о том, как сделать расположение доверенным, см. здесь и здесь.
Visual Studio
Microsoft Visual Studio Proxy Behavior
Когда Microsoft Visual Studio создает прокси-класс для клиентского проекта, который вызывает веб-службы Excel, этот прокси-класс ведет себя следующим образом:
При отсутствии возвращаемого значения и наличии одного или нескольких аргументов out первый аргумент out метода становится его возвращаемым значением. То есть в сигнатуре метода в прокси-классе будет на один аргумент out меньше. Но в сигнатуре будет возвращаемое значение, наследующее тип и содержимое первого аргумента out.
Это поведение характерно для следующих методов веб-служб Excel:
Calculate
CalculateA1
CalculateWorkbook
CancelRequest
CloseWorkbook
GetSessionInformation
Refresh
SetCell
SetCellA1
SetRange
SetRangeA1
Пользовательские функции служб Excel (UDF)
В первую очередь проверяется глобальный кэш сборок, затем — локальная папка
Таким образом, если сборка установлена в глобальном кэше сборок и находится в списке UDF, но отключена (или полностью удалена из списка UDF), а идентичная сборка установлена в локальной папке и включена, все равно будет загружаться и использоваться сборка в глобальном кэше сборок.
Это никак не влияет на сценарии обновления, в которых изменяется версия сборки.
Общие
Порядок строк в файле Sharedstring.xml не сохраняется
Службы Excel не сохраняют исходный порядок строк в таблице общих строк книги (в части Sharedstrings.xml файла в формате Excel XML в Microsoft Office). Например, выполните следующие действия:
Откройте файл, используя Excel.
Сохраните файл в формате XLSX.
Отправьте файл в библиотеку документов, которая является надежным расположением.
Откройте файл в библиотеке документов, используя Excel Web Access.
Нажмите Открыть в Excel.
Сохраните файл в формате XLSX.
Если сравнить файл Sharedstrings.xml, созданный в действии 2, с созданным в действии 6, можно заметить, что порядок частей Sharedstrings.xml может быть другим.
Не следует писать приложение, которое считает порядок строк в таблице общих строк фиксированным. Например, таблицу общих строк невозможно заменить существующей локализованной таблицей. Необходимо внести поправки с учетом нового порядка строк в таблице общих строк.
Импорт курса заданной валюты из интернета с автоматическим обновлением - весьма частая задача для многих пользователей Microsoft Excel. Представьте, что у вас есть прайс-лист, который должен пересчитываться каждое утро в соответствии с курсом. Или бюджет проекта. Или стоимость договора, которую надо посчитать, используя курс доллара на дату заключения договора.
В подобных ситуациях можно решить проблему по разному - всё зависит от того, какая версия Excel у вас установлена и какие надстройки поверх неё стоят.
Способ 1. Простой веб-запрос для текущего курса валют
Этот способ подойдет тем, у кого на компьютере пока ещё старые версии Microsoft Office 2003-2007. Он не использует никаких сторонних надстроек или макросов и оперирует только встроенными функциями.
Когда страница загрузится, то на таблицах, которые Excel может импортировать, появятся черно-желтые стрелки. Щелчок по такой стрелке помечает таблицу для импорта.
Когда все необходимые таблицы помечены - нажмите кнопку Импорт (Import) внизу окна. Спустя некоторое время, нужное для загрузки данных, содержимое отмеченных таблиц появится в ячейках на листе:
Для дополнительной настройки можно щелкнуть по любой из этих ячеек правой кнопкой мыши и выбрать в контекстном меню команду Свойства диапазона (Data range properties) . В этом диалоговом окне, при желании, возможно настроить периодичность обновления и другие параметры:
Котировки акций, т.к. они меняются каждые несколько минут, можно обновлять почаще (флажок Обновлять каждые N мин.), а вот курсы валют, в большинстве случаев, достаточно обновлять раз в день (флажок Обновление при открытии файла).
Обратите внимание, что весь импортированный диапазон данных воспринимается Excel как единое целое и получает собственное имя, которое можно увидеть в Диспетчере имен на вкладке Формулы (Formulas - Name Manager) .
Способ 2. Параметрический веб-запрос для получения курса валют на заданный интервал дат
Этот способ представляет собой слегка модернизированный первый вариант и дает пользователю возможность получать курс нужной валюты не только на текущий день, но и на любую другую интересующую дату или интервал дат. Для этого наш веб-запрос надо превратить в параметрический, т.е. добавить к нему два уточняющих параметра (код нужной нам валюты и текущую дату). Для этого делаем следующее:
2. В форме слева выбираем нужную валюту и задаем начальную и конечную даты:
3. Жмем кнопку Получить данные и через пару секунд видим таблицу с нужными нам значениями курса на заданном интервале дат. Прокручиваем полученную таблицу вниз до упора и помечаем ее для импорта, щелкнув по черно-желтой стрелке в левом нижнем углу вебстраницы (только не спрашивайте почему эта стрелка находится там, а не рядом с таблицей - это вопрос к дизайнерам сайта).
Теперь ищем в правом верхнем углу окна кнопку с дискетой Сохранить запрос (Save Query) и сохраняем файл с параметрами нашего запроса в любую подходящую папку под любым удобным именем - например в Мои документы под именем cbr.iqy. После этого окно веб-запроса и весь Excel можно пока закрыть.
4. Открываем папку, куда сохранили запрос и ищем файл запроса cbr.iqy, затем щелкаем по нему правой кнопкой мыши - Открыть с помощью - Блокнот (или выбрать его из списка - обычно это файл Notepad.exe из папки C:\Windows). После открытия файла запроса в Блокноте должны увидеть примерно следующее:
Самое ценное здесь - строка с адресом и параметры запроса в ней, которые мы будем подставлять - код нужной нам валюты (выделено красным) и конечная дата, которую мы заменим на сегодняшнюю (выделено синим). Аккуратно редактируем строку, чтобы получилось следующее:
Все остальное оставляем как есть, сохраняем и закрываем файл.
5. Создаем новую книгу в Excel, открываем лист, куда хотим импортировать архив курсов ЦБ. В любую подходящую ячейку вводим формулу, которая даст нам текущую дату в текстовом формате для подстановки в запрос:
Читайте также: