Excel вебслужба не работает

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Автоматическое обновление курса валют в Excel с помощью функции ВЕБСЛУЖБА и ФИЛЬТР.XML

Хотели ли вы когда-нибудь включить данные из интернет-ресурсов в ваши таблицы Excel, таких как курсы валют, погоду, результаты выдачи поисковиков или даже сообщения Twitter. C новым Excel 2013 такая возможность появилась в виде функции ВЕБСЛУЖБА.

Инструменты импорта данных из интернет были реализованы в более ранних версиях и позволяли выгружать информацию с веб страниц. Функция ВЕБСЛУЖБА возвращает данные на лист в формате XML, из которого в последующем можно выбрать необходимые данные с помощью функции ФИЛЬТР.XML. В сегодняшней статье мы рассмотрим возможность импорта курса доллара за недельный период (думаю, на фоне текущей нестабильной ситуации на рынках, эта информация актуальна для многих), по полученным данным построим график для возможности отслеживания тренда изменений

Для начала, необходимо найти веб сервис, который возвращает данные в формате XML. Я воспользовался службами Центробанка, который любезно предоставляет свои API для выгрузки данных. По ссылке вы найдете все действительные на текущий момент запросы банка.

Так как нас интересует недельный тренд доллара США, ищем динамику котировок, она находится под заголовком Example 2. Если мы щелкнем по ссылке этого примера, сервис вернет нам данные по котировкам доллара за период 01/03/2001 по 14/03/2001 в формате XML. Обратите внимание на адресную строку в браузере, здесь находится строка запроса к сервису (она то нам и нужна. ) с тремя аргументами: Дата С, Дата ПО и идентификационный номер валюты.

Изменяя любой из них, сервис ЦБ будет возвращать нужную нам информацию.

Копируем строку с заголовком, открываем книгу Excel, в ячейку B1 вбиваем функцию =ВЕБСЛУЖБА(), где аргументом функции станет, скопированная нами, строка, т.е. =ВЕБСЛУЖБА(«https://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=07/08/2013&date_req2=13/08/2013&VAL_NM_RQ=R01235»)

Обратите внимание, функция ВЕБСЛУЖБА вернула ответ в формате 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 c интерактивным веб-приложением

Не секрет, что Excel довольно мощный инструмент для работы с числовыми табличными данными. Однако средства, которые предоставляет Microsoft для интеграции с ним, далеки от идеала. В частности, сложно интегрировать в Excel современные пользовательские интерфейсы. Нам нужно было дать пользователям Excel возможность работать с довольно насыщенным и функциональным интерфейсом. Мы пошли несколько другим путем, который в итоге показал хороший результат. В этой статье я расскажу, как можно организовать интерактивное взаимодействие Excel c веб-приложением на Angular и расшить Excel практически любым функционалом, который реализуем в современном веб-приложении.

Итак, меня зовут Михаил и я CTO в Exerica. Одна из проблем которые мы решаем — облегчение работы финансовых аналитиков с числовыми данными. Обычно они работают как с исходными документами финансовой и статистической отчетности, так и каким-либо инструментом для создания и поддержания аналитических моделей. Так сложилось, что 99% аналитиков работают в Microsoft Excel и делают там довольно сложные вещи. Поэтому перевести их с Excel на другие решения не эффективно и практически невозможно. Объективно, «облачные» сервисы электронных таблиц до функционала Excel пока не дотягивают. Но в современном мире инструменты должны быть удобны и соответствовать ожиданиям пользователей: открываться по клику мышки, иметь удобный поиск. А реализация в виде разных несвязанных приложений будет довольно далека от ожиданий пользователя.

То с чем работает аналитик выглядит примерно так:

Основные данные тут — это числовые «финансовые показатели», например, доход за 1 квартал 2020 года. Для простоты буду называть их дальше просто «числами». Как видно, почти никакой связи между числами в документе и в аналитической модели нет, все только в голове у аналитика. И работа с заполнением и поддержанием модели занимает часы поиска и перебивания чисел из документа в таблицы, а потом поиск ошибок ввода. Вместе этого мы хотели бы предложить пользователю привычные инструменты: «перетаскивание», вставка через клипборд и так далее, а также быстрый просмотр исходных данных.

Что у нас уже было

К моменту, когда мы начали реализацию интерактивного взаимодействия с Excel в виде, изложенном в этой статье, у нас уже была база данных на MongoDB, бэкэнд в виде REST API на .NET Core, фронтовое SPA на Angular и некоторые другие сервисы. Мы к этому моменту уже пробовали разные варианты интеграции в приложения электронных таблиц, в том числе и в Excel, и все они не пошли дальше MVP, но это тема отдельной статьи.

Связываем данные

В Excel существует два распространенных инструмента, с помощью которых можно решить задачу связывания данных в таблице с данными в системе: RTD (RealTimeData) и UDF (User-Defined Functions). Чистый RTD менее удобен для пользователя в плане синтаксиса и ограничивает гибкость решения. С помощью UDF можно создать кастомную функцию, которая будет работать привычным для Excel-пользователя образом. Ее можно использовать в других функциях, она понимает ссылки типа A1 или R1C1 и вообще ведет себя как надо. При этом никто не мешает использовать механизм RTD для обновления значения функции (что мы и сделали). UDF мы разрабатывали в виде Excel addin с использованием привычного нам C# и .NET Framework. Для ускорения разработки мы использовали библиотеку Excel DNA.

Кроме UDF наш addin реализует ribbon (панель инструментов) с настройками и некоторыми полезными функциями по работе с данными.

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

Для передачи данных в Excel и налаживания интерактива мы разработали отдельный сервис, который предоставляет подключение по Websocket при помощи библиотеки SignalR и фактически является брокером для сообщений о событиях, которыми должны обмениваться «фронтовые» части системы в реальном времени. Он у нас называется Notification Service.

Вставляем данные в Excel

В нашем SPA мы подсвечиваем все числа, которые обнаружила система. Пользователь может выделять их, навигировать по ним и т.п. Для вставки данных мы реализовали 3 механизма, чтобы закрыть различные варианты использования:

• Перетаскивание (drag-and-drop)
• Автоматическая вставка по клику в SPA
• Копирование и вставка через клипборд

Когда пользователь инициирует drag’n’drop некоторого числа из SPA, для перетаскивания формируется ссылка с идентификатором этого числа из нашей системы ( . /unifiedId/005F5549CDD04F8000010405FF06009EB57C0D985CD001 ). При вставке в Excel наш addin перехватывает событие вставки и парсит регэкспом вставляемый текст. При обнаружении валидной ссылки на лету подменяет ее на соответствующую формулу =ExrcP(. ) .

При клике на числе в SPA через Notification Service отправляется сообщение в addin, содержащее все необходимые данные для вставки формулы. Далее формула просто вставляется в текущую выделенную ячейку.

Эти способы хороши, когда пользователю нужно вставлять в свою модель по одному числу, но если надо перенести целую таблицу или ее часть, необходим другой механизм. Наиболее привычным для пользователей представляется копирование через клипборд. Однако этот способ оказался сложнее первых двух. Дело в том, что для удобства вставляемые данные должны быть представлены в нативном для Excel формате — OpenXML Spreadsheet. Наиболее просто это реализуется используя объектную модель Excel, то есть из addin’а. Поэтому процесс формирования клипборда у нас выглядит так:

• Пользователь выделяет область с числами в SPA
• Массив выделенных чисел передается на Notification Service
• Notification Service передает его в addin
• Addin формирует OpenXML и вставляет его в клипборд
• Пользователь может вставить данные из клипборда в любое место любой Excel-таблицы.

Несмотря на то, что данные проделывают довольно долгий путь, благодаря SignalR и RTD происходит это довольно быстро и абстрагированно от пользователя.

Распространяем данные

После того, как пользователь выбрал начальные данные для своей модели, их надо «распространить» все периоды (года, полугодия и кварталы), которые представляют интерес. Для этих целей одним из параметров нашей UDF является дата (период) данного числа (вспоминаем: «доход за 1 квартал 2020 года»). В Excel существует нативный механизм «распространения» формул, который позволяет заполнить ячейки той же формулой с учетом ссылок, заданных в параметрах. То есть вместо конкретной даты в формулу вставлена ссылка на нее, а далее пользователь «распространяет» ее на другие периоды, при этом в таблицу автоматически загружаются «те же» числа из других периодов.

А что это там за число?

Теперь у пользователя есть модель на несколько сотен строк и несколько десятков столбцов. И у него может возникнуть вопрос, что же там за число в ячейке L123? Чтобы получить ответ, у нас ему достаточно кликнуть на эту ячейку и в нашем SPA откроется тот самый отчет, на той самой странице, где записано кликнутое число, а число в отчете будет выделено. Вот так:

А если это не просто одно число из отчета, а результат некоторых вычислений на числах, взятых из отчета, то мы подсветим все числа, входящие в вычисляемое в Excel выражение. При этом не происходит загрузки всего приложения и подгрузки всех необходимых данных, как в случае перехода по ссылке.

В качестве заключения

Вот такая, на мой взгляд, нестандартная реализация взаимодействия между Excel и веб-приложением получилась довольно удобной для пользователя. Благодаря использованию Excel порог входа для пользователей из целевой аудитории довольно низкий. При этом мы получаем также всю мощь Excel по работе с числовыми данными. Сами данные всегда остаются связанными с источником, а также связанными во времени. Для целевых клиентов нет необходимости встраивать в бизнес-процесс работы с данными абсолютно новые внешние системы. Наше решение встраивается как дополнительный «инструмент» внутри Excel, что является стандартом де-факто для провайдеров финансовых данных.

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

Источник

Устранение неполадок веб-соединителя

Подключение к Microsoft Graph

подключение к Microsoft Graph интерфейсам api Power Query не рекомендуется или не поддерживается. Дополнительные сведения см. в этой статье .

Использование шлюза с веб-соединителем

при каждом использовании веб-соединителя для получения данных из страницы HTML необходимо использовать локальный шлюз данных при публикации в облачной службе, независимо от того, Power BI ли он служба для наборов данных или потокового Power Apps обмена. (в настоящее время Dynamics 365 Customer Insights не поддерживает использование шлюза.) **Options and settings** > **Options** > **Preview features**. The option is enabled by default. —>

если Please specify how to connect при попытке подключения к странице HTML с помощью веб-соединителя появляется сообщение об ошибке, убедитесь, что на компьютере, на котором размещен локальный шлюз данных, установлена Internet Explorer 10 или более поздней версии.

Сбор веб-запросов и отзыв сертификатов

Мы усиливается безопасность веб-подключений для защиты данных. Однако это означает, что определенные сценарии, такие как захват веб-запросов с помощью Fiddler, больше не будут работать по умолчанию. Чтобы включить эти сценарии, выполните следующие действия.

Откройте Power BI Desktop.

На вкладке файл выберите Параметры и > Параметры.

В области Параметры в разделе Глобальная > Безопасность снимите флажок включить проверку отзыва сертификатов.

Щелкните ОК.

Перезапустите Power BI Desktop.

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

Чтобы задать этот сценарий в групповая политика, используйте ключ «Дисаблецертификатеревокатиончекк» в пути реестра «Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Power BI Desktop». Если задать для параметра «Дисаблецертификатеревокатиончекк» значение 0, всегда будет включена проверка (остановка Fiddler и похожего программного обеспечения), а установка «Дисаблецертификатеревокатиончекк» в значение 1 всегда отключит проверку (включение Fiddler и аналогичного программного обеспечения).

Изменение метода проверки подлинности

В некоторых случаях может потребоваться изменить метод проверки подлинности, используемый для доступа к определенному сайту. Если это изменение необходимо, см. раздел изменение метода проверки подлинности.

Ограничения на проверку подлинности веб-соединителя для содержимого HTML

Ограничения, описанные в этом разделе, применимы только к веб-страницам HTML. При открытии других типов файлов из Интернета с помощью этого соединителя Эти ограничения не затрагиваются.

Устаревший веб-соединитель Power Query автоматически создает Web.Page запрос, поддерживающий проверку подлинности. единственное ограничение возникает, если в диалоговом окне метод проверки подлинности выбрано Windows проверка подлинности. В этом случае выбор » использовать мои текущие учетные данные » работает правильно, но использование альтернативных учетных данных не будет проходить проверку подлинности.

новая версия веб-соединителя (в настоящее время доступна в Power BI Desktop) автоматически создает Web.BrowserContents запрос. В настоящее время такие запросы поддерживают только анонимную проверку подлинности. Другими словами, новый веб-соединитель нельзя использовать для подключения к источнику, для которого требуется неанонимная проверка подлинности. Это ограничение применяется к Web.BrowserContents функции независимо от среды размещения.

в настоящее время Power BI Desktop автоматически использует Web.BrowserContents функцию. Web.Page функция по-прежнему используется автоматически Excel и Power Query в сети. Power Query Online поддерживает Web.BrowserContents использование локального шлюза данных, но в данный момент придется вводить такую формулу вручную. Когда веб-пример становится доступным в Power Query Online в середине октября 2020, этот компонент будет использовать Web.BrowserContents .

для Web.Page работы функции требуется, чтобы на компьютере был установлен Internet Explorer 10. при обновлении Web.Page запроса с помощью локального шлюза данных на компьютере, содержащем шлюз, должен быть установлен Internet Explorer 10. если вы используете только Web.BrowserContents функцию, вам не нужно устанавливать Internet Explorer 10 на компьютере или компьютере, содержащем локальный шлюз данных.

В случаях, когда необходимо использовать Web.Page вместо из Web.BrowserContents -за проблем с проверкой подлинности, вы все равно можете вручную использовать Web.Page .

в Power BI Desktop можно использовать более старую Web.Page функцию, сняв функцию предварительного просмотра вывода веб-таблицы .

На вкладке файл выберите Параметры и > Параметры.

В разделе глобальные выберите предварительные версии компонентов.

Очистите новую функцию предварительного просмотра вывода веб-таблицы и нажмите кнопку ОК.

Перезапустите Power BI Desktop.

в настоящее время нельзя отключить использование Web.BrowserContents в Power BI Desktop, оптимизированном для Сервер отчетов Power BI.

Можно также получить копию Web.Page запроса из Excel. Копирование кода из Excel:

1. Выберите из Интернета на вкладке данные .
2. Введите адрес в диалоговом окне из Интернета , а затем нажмите кнопку ОК.
3. В навигаторе выберите данные, которые требуется загрузить, а затем выберите преобразовать данные.
4. На вкладке Главная в Power Query выберите Расширенный редактор.
5. В Расширенный редактор скопируйте формулу M.
6. В приложении, которое использует Web.BrowserContents , выберите пустой соединитель запросов .
7. Если выполняется копирование в Power BI Desktop:
   1. На вкладке Главная выберите Расширенный редактор.
   2. Вставьте скопированный Web.Page запрос в редактор и нажмите кнопку Готово.
8. При копировании в Power Query в сети:
   1. В пустом запросе вставьте скопированный Web.Page запрос в пустой запрос.
   2. Выберите локальный шлюз данных для использования.
   3. Выберите Далее.

Можно также вручную ввести следующий код в пустой запрос. Убедитесь, что введен адрес веб-страницы, которую требуется загрузить.

Проверка подлинности в произвольных службах

Некоторые службы поддерживают возможность использования веб-соединителем проверки подлинности с помощью OAuth или AAD. Однако в большинстве случаев это не сработает.

Если при попытке проверки подлинности появится следующая ошибка:

«Не удалось подключиться, так как этот тип учетных данных не поддерживается для этого ресурса. Выберите другой тип учетных данных «.

Обратитесь к владельцу службы. Им потребуется либо изменить конфигурацию проверки подлинности, либо создать настраиваемый соединитель.

Веб-соединитель использует HTTP 1,1 для обмена данными

Веб-соединитель Power Query взаимодействует с источником данных по протоколу HTTP 1,1. Если источник данных ожидает обмена данными по протоколу HTTP 1,0, может появиться сообщение об ошибке, например 500 Internal Server Error .

Невозможно переключить Power Query на использование HTTP 1,0. Power Query всегда отправляет, Expect:100-continue когда есть текст, чтобы избежать передачи потенциально больших полезных данных, когда исходный вызов может завершиться неудачей (например, из-за отсутствия разрешений). В настоящее время это поведение нельзя изменить.

Источник