Как подключить api к excel

Обновлено: 08.07.2024

Ранее уже писал про получение данных с Московской биржи через формулы Google Таблиц. Однако остался вопрос — можно ли получать эти же данные при локальном использовании Microsoft Excel или его свободного аналога LibreOffice Calc? Без использования скриптов или ручного копирования.

Microsoft Excel с формулами получения данных с Мосбиржи

И на этот вопрос можно дать положительный ответ. Это даже более удобно, поскольку не приходится ожидать загрузки результатов работы функции IMPORTXML в Гугл Таблицах.

Аналогом этой функции в Excel и Calc выступает связка формул: WEBSERVICE (ВЕБСЛУЖБА) + FILTERXML (ФИЛЬТР.XML).

При работе с Microsoft Excel есть некоторые нюансы:

Эти функции доступны только в Excel 2013 и более поздних версиях для Windows.
Эти функции не будет возвращать результаты на компьютере Mac.

Требуется LibreOffice 4.2 и выше.
Нет ограничений на используемую ОС. Работает под:
Windows
Linux
Mac OS
Файл Excel .xlsx открывается и работоспособен, но визуально форматирование может быть нарушено.

Протестировал этот файл в Microsoft Excel 2019 под Windows 10 и в LibreOffice Calc 6.4 под Linux Mint 19.3. Под Mac OS у меня возможности протестировать не было.

Идентификатор режима торгов

В API Московской биржи очень многое зависит от параметра «Идентификатор режима торгов» (primary_boardid), который можно посмотреть прямо у них на сайте через форму поиска.

Идентификатор режима торгов для акций Тинькофф

Автоматическое получение имени акций, облигаций и ETF

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

Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc

Автоматическое получение текущих цен

На этой вкладке представлены актуальные примеры для получения цен акций, облигаций и ETF с Московской биржи.

Цена предыдущего дня берётся через PREVADMITTEDQUOTE, а не LAST с 15 минутной задержкой, поскольку по некоторым низко ликвидным инструментам через LAST цены может просто не быть.

Автоматическое получение дивидендных выплат для акций

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

Автоматическое получение облигационных выплат

По облигациям (не только корпоративным, но также ОФЗ и еврооблигациям) можно автоматически получать дату выплаты следующего купона и его значение.

Автоматическое получение дат оферт

Удобно планировать собственные финансы, получая даты оферт (дата, в которую инвестор или эмитент имеют право досрочно погасить облигацию по цене номинала) автоматически.

UPD. Пользователь mixei подсказывает, что автоматическое обновление настраивается через Параметры — Центр управления безопасностью — вкладка Внешнее содержимое — там надо поставить все флажки где не рекомендуется :) Но это на страх и риск пользователей.

API Московской биржи предоставляет широкие возможности, которые гораздо шире чем описанные в данной статье. Это статья своеобразная шпаргалка для долгосрочного частного инвестора, который ведёт учёт в локальном файле на собственном компьютере.

Также хочу отметить, что я никак не связан с Московской биржей и использую ИСС Мосбиржи только в личных интересах.

Вы можете использовать пакет средств разработки XLL для Microsoft Excel 2013 и API C, чтобы создавать высокопроизводительные функции обработки листов в Excel 2013. Обновления API C для Excel 2013 отражают постоянную поддержку пользователей, для которых производительность сторонних или внутренних функций играет важную роль.

Программные интерфейсы Excel

В программе Excel доступны несколько вариантов разработки приложений, которые с ней взаимодействуют. Программные интерфейсы Excel добавлялись в предыдущие версии в следующем порядке:

Макроязык XLM: первый доступный пользователям язык для расширения возможностей Excel и основа API C. Хотя язык XLM по-прежнему поддерживается в Excel 2010, ему на замену пришел язык Visual Basic для приложений (VBA).

API C и XLL: библиотеки DLL, интегрируемые с Excel. Они предоставляют интерфейс для непосредственного и самого быстрого добавления высокопроизводительных функций обработки листов, но использовать их сложнее, чем последующие технологии.

VBA: объекты кода Visual Basic, связанные с объектами книги Excel. VBA позволяет перехватывать события, настраивать и добавлять определяемые пользователем функции и команды. VBA — это наиболее распространенный и самый простой вариант расширения возможностей Excel.

Модель COM: стандарт взаимодействия для приложений Windows, с помощью которого Excel предоставляет свои события и объекты. VBA использует модель COM для взаимодействия с Excel. Excel экспортирует библиотеки типов COM, которые помогают создавать ресурсы кода COM и приложения на C++, управляющие Excel.

Причины для использования C API

Основная причина написания XLL и использования API C — создание высокопроизводительных функций для обработки листов. Хотя функции XLL часто называют определяемыми пользователем функциями, время для изучения и обретения необходимых навыков делает эту технологию непрактичной для большинства пользователей. Но возможность применять высокопроизводительные функции, а также создавать в Excel 2013 многопоточные интерфейсы для мощных серверных ресурсов делает ее очень важной частью вариантов расширения Excel.

Версия C API, представленная в Excel 2007, относится, в основном, к высокопроизводительным расчетам, а не пользовательскому интерфейсу.

Написание высокопроизводительных определяемых пользователем функций для работы с листами

API C для Excel — это идеальный выбор для создания высокопроизводительных функций обработки листов с помощью надстроек XLL. API C предоставляет прямой доступ к данным листов. XLL предоставляют Excel прямой доступ к ресурсам DLL. Производительность XLL повышена в Excel 2013 за счет добавления новых типов данных и, что еще важнее, за счет поддержки выполнения определяемых пользователем функций на кластерных серверах.

Доступ к многопоточным серверам с помощью функций XLL

Многопоточный пересчет (MTR), впервые появившийся в Excel 2007, позволяет создавать потокобезопасные функции листа XLL. Их можно использовать для доступа к многопоточным серверам. В следующих разделах более подробно описывается, как это может значительно увеличить наблюдаемую производительность. Для пользователей Excel, которым иногда требуется доступ к мощным вычислительным ресурсам, сочетание библиотеки XLL, использующей MTR, и мощного сервера обеспечивает высокую производительность решения.

Настройка пользовательского интерфейса Excel

Для многих версий Excel интерфейс C API не был лучшим выбором для настройки пользовательского интерфейса. VBA предоставляет эффективный доступ к объектам и событиям Excel. Пользовательский интерфейс, впервые представленный в Excel 2007, существенно отличается от предыдущих версий как внешним видом, так и базовой технологией. Этот интерфейс лучше всего можно настроить с помощью ресурсов управляемого кода.

Создание приложений, доступных в Интернете

Управление Excel из внешних приложений

Асинхронный вызов Excel

Excel позволяет библиотеке XLL вызывать C API, только если приложение Excel передало управление XLL. Функция листа, которую вызывает Excel, может осуществлять обратный вызов Excel с помощью C API. Команда XLL, которую вызывает приложение Excel, может вызывать C API. Функции и команды DLL и XLL, которые вызываются VBA после вызова VBA приложением Excel, могут вызывать C API. Невозможно, например, задать обратный вызов Windows с указанием времени в библиотеке XLL и вызвать из нее C API. Кроме того, невозможно вызвать C API из фонового потока, созданного библиотекой XLL. Не рекомендуется вызывать Excel асинхронно с помощью модели COM из DLL или XLL.

Это очень ограничивают ваши возможности, так как некоторые приложения должны реагировать на события асинхронно. Например, вам может потребоваться получить часть данных в Excel из Интернета и повторно проводить вычисления при каждом изменении данных. Или вам может понадобиться фоновый поток для вычислений и пересчитать данные в Excel после его завершения.

Для этого с помощью Excel можно активно запрашивать изменения, но это неэффективно и непрактично, так как Excel часто будет прерывать регулярные операции. Вы можете настроить повторяющиеся запланированные команды, используя C API или VBA, хотя это не идеальное решение.

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

Интерфейс C API и его связь с XLM

Макроязык Excel (XLM) был первой доступной пользователям средой программирования в Excel. Она позволяет создавать специальные команды и функции на специальных листах макросов, которые похожи на обычные листы. Листы макросов XLM по-прежнему поддерживаются в Excel 2013. На листе макросов можно использовать все обычные функции листов, такие как СУММ и LOG, а также следующие элементы, которые невозможно ввести на листе:

функции, предоставляющие сведения о рабочей области, такие как GET.CELL и GET.WORKBOOK;

эквивалентные командам функции, позволяющие автоматизировать обычные пользовательские операции, такие как DEFINE.NAME и PASTE;

функции, связанные с надстройками, такие как REGISTER;

эквивалентное командам перехватывание событий (например, с помощью ON.ENRTY и ON.TIME);

операции макросов, связанные с функциями, такие как ARGUMENT и VOLATILE;

операции управления потоком выполнения, такие как GOTO и RETURN.

Ограниченная версия API C существовала в Excel версии 3. Однако в Excel версии 4 язык XLM был сопоставлен с интерфейсом API C. С тех пор библиотеки DLL могут вызывать все функции листа, информационные функции листа макросов, команды, даже перехватывать события. DLL не могут вызывать функции управления потоком XLM из API C. Эти команды и функции листа макроса описаны в файле справки XLMacr8.hlp (прежнее название — Macrofun.hlp). Для получения этого файла справки перейдите в Центр загрузки Майкрософт и выполните поиск "XLMacr8.hlp".

Windows Vista и Windows 7 не поддерживают HLP-файлы напрямую, но вы можете скачать программу справки Windows (WinHlp32.exe) для Windows Vista или программу справки Windows (WinHlp32.exe) для Windows 7 от корпорации Майкрософт, позволяющие открыть эти файлы.

Библиотеки DLL вызывают эквиваленты этих функций и команд интерфейса API C, используя функции обратного вызова Excel4, Excel4v, Excel12 и Excel12v (последние две были представлены в Excel 2007). Перечисленные константы, соответствующие каждой функции и команде, определены в файле заголовка и передаются как один из аргументов функциям обратного вызова. Например, функция GET.CELL представлена xlfGetCell, REGISTER — xlfRegister, а DEFINE.NAME — xlcDefineName.

Помимо функций листа, а также команд и функций листа макросов, API C предоставляет перечисления функций и команд, которые можно вызывать только с помощью этих функций обратного вызова из DLL. Например, xlGetName позволяет DLL найти собственный полный путь и имя файла, что необходимо при регистрации функций и команд в Excel.

После представления листов Visual Basic для приложений (VBA) в Excel версии 5 и редактора Visual Basic (VBE) в версии 8 (Excel 97) самым простым способом настройки Excel стал язык VBA, а не XLM. Множество новых возможностей, представленных в последующих версиях Excel, доступны через VBA, но не через XLM или C API. Например, несколько команд, прерывания событий и расширенные возможности диалогового окна функции доступны в VBA, но не в XLM или C API.

В этой статье описано, как использовать модель API для создания надстроек в Excel, Word и OneNote. Здесь представлены основные понятия, лежащие в основе использования API на основе обещаний.

Эта модель не поддерживается клиентами Office 2013. Используйте общую модель API для работы с этими версиями Office. Полные сведения о доступности платформ см. в статье Доступность клиентских приложений и платформ Office для надстроек Office.

В примерах на этой странице используются API JavaScript для Excel, но эти понятия также относятся к API JavaScript для OneNote, Visio и Word.

Асинхронный характер API на основе обещаний

Надстройки Office — это веб-сайты, отображающиеся внутри контейнера браузера в приложениях Office, таких как Excel. Этот контейнер внедряется в приложение Office на платформах для классических ПК, например Office для Windows, и запускается в элементе iFrame HTML в Office для Интернета. Из-за соображений производительности интерфейсы API Office.js не могут синхронно взаимодействовать с приложениями Office на всех платформах. Таким образом, вызов API sync() в Office.js возвращает обещание, которое разрешается, когда приложение Office выполняет запрошенные действия чтения или записи. Кроме того, вы можете поместить в очередь несколько действий, например действия настройки свойств или вызова методов, а затем запустить их в виде пакета команд в одном вызове метода sync() , а не отправлять отдельные запросы для каждого действия. В разделах ниже описано, как сделать это, используя API run() и sync() .

Контекст запроса

Прокси-объекты

Объекты JavaScript для Office, объявляемые и используемые с помощью API на основе обещаний, являются прокси-объектами. Все методы, которые вы вызываете, либо свойства, которые вы настраиваете либо загружаете, в прокси-объектах просто добавляются в очередь команд, ожидающих выполнения. Когда вы вызываете метод sync() в контексте запроса (например, context.sync() ), команды, помещенные в очередь, передаются в приложение Office и выполняются. По существу, эти API ориентированы на работу с пакетами. Вы можете поместить в очередь любое количество изменений в контексте запроса, а затем вызвать метод sync() , чтобы запустить пакет команд, помещенных в очередь.

Например, во фрагменте кода ниже показано, как объявить локальный объект JavaScript Excel.Range ( selectedRange ) для ссылки на выделенный диапазон в книге Excel, а затем задать ряд свойств для этого объекта. Объект selectedRange представляет собой прокси-объект, поэтому свойства, заданные в этом объекте, и метод, вызываемый в этом объекте, не будут отображены в документе Excel, пока надстройка не вызовет метод context.sync() .

Совет по производительности: минимизируйте количество созданных прокси-объектов

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

При вызове метода sync() в контексте запроса будет синхронизировано состояние прокси-объектов и объектов в документе Office. Метод sync() запускает любые команды, помещенные в очередь в контексте запроса, и получает значения для любых свойств, которые следует загрузить в прокси-объектах. Метод sync() выполняется асинхронно и возвращает обещание, которое разрешается по завершении работы метода sync() .

В примере ниже показана пакетная функция, которая определяет локальный прокси-объект JavaScript ( selectedRange ), загружает свойство этого объекта, а затем использует шаблон обещаний JavaScript для вызова метода context.sync() и, соответственно, синхронизации состояния прокси-объектов и объектов в документе Excel.

В предыдущем примере настроен параметр selectedRange , и его свойство address загружается при вызове context.sync() .

Так как sync() — это асинхронная операция, всегда следует возвращать объект Promise , чтобы завершить операцию sync() , прежде чем продолжить выполнение сценария. Если вы используете TypeScript или JavaScript ES6+, вы можете await вызов context.sync() вместо возврата обещания.

Совет по производительности: минимизируйте количество вызовов синхронизации

В API JavaScript для Excel sync() является единственной асинхронной операцией и в некоторых обстоятельствах может выполняться медленно, особенно в случае с Excel в Интернете. Для оптимизации производительности минимизируйте количество вызовов sync() , поставив в очередь максимально возможное количество изменений до ее вызова. Дополнительные сведения об оптимизации производительности с помощью sync() см. в статье Избегайте использования метода context.sync в циклах.

Чтобы можно было считывать свойства прокси-объекта, вам необходимо явно загрузить их и заполнить прокси-объект данными из документа Office, а затем вызвать метод context.sync() . Например, вы создали прокси-объект для ссылки на выделенный диапазон, а затем вам потребовалось считать свойство address выделенного диапазона. Прежде чем вы сможете считать свойство address , вам потребуется загрузить его. Чтобы запросить загрузку свойств прокси-объекта, вызовите метод load() в объекте и укажите свойства, которые необходимо загрузить. В следующем примере показана загрузка свойства Range.address для myRange .

Если вы вызываете методы или задаете свойства только в прокси-объекте, вам не нужно вызывать метод load() . Метод load() требуется только тогда, когда вам необходимо считать свойства в прокси-объекте.

Аналогично запросам для задания свойств или вызова методов в прокси-объектах, запросы на загрузку свойств в прокси-объектах добавляются в очередь команд, ожидающих выполнения, в контексте запроса, который будет запущен, когда вы в следующий раз вызовете метод sync() . В очередь можно поставить сколько угодно вызовов load() в контексте запроса.

Скалярные и навигационные свойства

Существует две категории свойств: скалярные и навигационные. К скалярным свойствам относятся назначаемые типы, такие как строки, целые числа и структуры JSON. Свойства навигации — это объекты и коллекции объектов только для чтения, которым назначаются поля вместо прямого назначения свойства. Например, элементы name и position объекта Excel.Worksheet являются скалярными свойствами, а protection и tables — свойствами навигации.

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

Вы также можете задавать скалярные свойства из свойства навигации по пути к ним. Например, вы можете задать размер шрифта для Excel.Range с помощью команды someRange.format.font.size = 10; . Чтобы задать свойство, необязательно загружать его.

Имейте в виду, что некоторые свойства объекта могут совпадать с именем другого объекта. Например, format — это свойство объекта Excel.Range , но также имеется и объект format . Поэтому если вы, например, вызываете range.load("format") , это эквивалентно range.format.load() (нежелательный пустой оператор load() ). Чтобы избежать этого, ваш код должен загружать только "конечные узлы" в дереве объектов.

Вызов метода load без параметров (не рекомендуется)

Если вызвать метод load() для объекта (или коллекции), не указывая параметры, будут загружены все скалярные свойства объекта или объектов в коллекции. Загрузка ненужных данных замедлит вашу надстройку. Необходимо всегда явным образом указывать свойства для загрузки.

Объем данных, возвращаемых оператором load без параметров, может превышать ограничения по размерам для службы. Чтобы сократить риски для старых надстроек, некоторые свойства не возвращаются методом load без их явного запроса. Следующие свойства исключаются из таких операций нагрузки.

ClientResult

Методы в API на основе обещаний, возвращающие примитивные типы, используют шаблон, похожий на парадигму load / sync . Например, Excel.TableCollection.getCount получает количество таблиц в коллекции. getCount возвращает ClientResult<number> . Это означает, что свойство value возвращаемого ClientResult выражено числом. Сценарий не может получить доступ к этому значению, пока не вызовет context.sync() .

Следующий код получает общее количество таблиц в книге Excel и записывает его в консоль.

Установка свойств объекта с вложенными свойствами навигации может быть трудоемкой задачей. Вместо того чтобы задавать отдельные свойства с помощью путей навигации, как описано выше, вы можете использовать метод object.set() , доступный для объектов в API JavaScript на основе обещаний. С помощью этого метода можно задать сразу несколько свойств объекта, передавая другой объект того же типа Office.js или объект JavaScript со свойствами, сходными по структуре со свойствами объекта, для которого вызывается метод.

В приведенном ниже примере кода показано, как задать несколько свойств формата диапазона, вызвав метод set() и передав в него объект JavaScript, имена и типы свойств которого повторяют структуру свойств объекта Range . В этом примере предполагается, что данные находятся в диапазоне B2:E2.

Некоторые свойства невозможно задать напрямую

Некоторые свойства невозможно задать, хотя они и поддерживают запись. Эти свойства являются частью родительского свойства, которое должно быть задано как один объект. Это связано с тем, что родительское свойство использует вложенные свойства с определенными логическими связями. Эти родительские свойства должны быть заданы с помощью нотации литерала объекта, чтобы задать весь объект, а не отдельные вложенные свойства этого объекта. Один из примеров доступен в разделе PageLayout. Свойство zoom должно быть установлено с помощью одного объекта PageLayoutZoomOptions, как показано здесь.

В предыдущем примере вы не сможете напрямую присвоить значение zoom : sheet.pageLayout.zoom.scale = 200; . Этот оператор выдает ошибку, так как zoom не загружен. Даже если zoom загружен, масштабный набор не будет работать. Все контекстные операции происходят в zoom , обновляя прокси-объект в надстройке и переписывая локально установленные значения.

Это поведение отличается от свойств навигации, например Range.format. Свойства объектов format можно установить с помощью объектной навигации, как показано здесь.

Вы можете определить свойство, для которого невозможно напрямую задать его вложенные свойства, путем проверки модификатора только для чтения. Для всех свойств, доступных только для чтения, можно напрямую задать их вложенные свойства, использующиеся не только для чтения. Записываемые свойства, например PageLayout.zoom , должны быть заданы на уровне объекта. Сводка:

Свойство только для чтения: вложенные свойства можно задать с помощью навигации.
Записываемое свойство: вложенные свойства нельзя задать с помощью навигации (необходимо установить их в рамках начального назначения родительского объекта).

Методы и свойства *OrNullObject

Некоторые методы и свойства доступа создают исключение, если нужный объект не существует. Например, если для получения листа Excel указать имя листа, не существующее в книге, метод getItem() создаст исключение ItemNotFound . Библиотеки конкретных приложений позволяют коду проверять наличие сущностей документа, не требуя кода обработки исключений. Это достигается с помощью вариантов методов и свойств *OrNullObject . Эти варианты вместо создания исключения возвращают объект, свойству isNullObject которого присвоено значение true , если указанный элемент не существует.

Например, вы можете вызвать метод getItemOrNullObject() для коллекции, такой как Worksheets, чтобы получить элемент из коллекции. Метод getItemOrNullObject() возвращает указанный элемент, если он существует. В противном случае возвращается объект, свойству isNullObject которого присвоено значение true . Затем код может оценить это свойство, чтобы определить, существует ли объект.

Варианты *OrNullObject никогда не возвращают значение JavaScript null . Они возвращают обычные прокси-объекты Office. Если сущность, представляемая объектом, не существует, свойству isNullObject объекта присваивается значение true . Не проверяйте возвращенный объект на нулевое значение или ложность. Для него никогда не используются значения null , false или undefined .

Работая в IoT-сфере и плотно взаимодействуя с одним из основных элементов данной концепции технологий – сетевым сервером, столкнулся вот с какой проблемой (задачей): необходимо отправлять много запросов для работы с умными устройствами на сетевой сервер. На сервере был реализован REST API с оболочкой Swagger UI, где из графической оболочки можно было отправлять только разовые запросы. Анализ сторонних клиентов, типа Postman или Insomnia показал, что простого визуального способа поместить в скрипт массив из необходимого перечня идентификаторов устройств (или любых других элементов сервера), для обращения к ним – не нашлось.

Так как большая часть работы с выгрузками и данными была в Excel, то решено было вспомнить навыки, полученные на учебе в университете, и написать скрипт на VBA, который бы мою задачку решал.

получать информацию по устройствам с различными параметрами фильтрации (GET);

применять изменения в конфигурации по устройствам: имя, профиль устройства, сетевые лицензии и пр. (PUT);

отправлять данные для конфигурации и взаимодействия с устройствами (POST).

И сегодня я расскажу вам про то, как с помощью Excel, пары формул и самописных функций на VBA можно реализовать алгоритм, отправляющий любое необходимое количество REST-API запросов с использованием авторизации Bearer Token.

Данная статья будет полезная тем, кто воспользуется данным решением под Windows, но еще больше она будет полезна тем людям, которые хотят использовать данное решение на MacOS (с Excel x64). Как вы уже догадались, ниже будут рассмотрены два варианта реализации под разные системы, так как с MacOS есть нюанс.

Часть 1. Реализация решения под Windows

GET

Начнем с самого простого: GET – запросов. В данном примере необходимо получить ответ (информацию) от сервера по заданному списку устройств.

Для реализации GET – запросов нам дано:

1) Ссылка, в которой указываются параметры запроса.

2) Заголовки запроса + Токен авторизации (Bearer Token)

--header 'Accept: application/json' --header 'Authorization: Bearer

3) Параметр, указываемый в ссылке (в данном примере это идентификаторы устройств – DevEUI):

Имея такие данные на входе, делаем в Excel лист-шаблон, который заполняем в соответствии с тем, что имеем:

столбец А уходит вот значения параметров

столбец F уходит под ссылку-родителя

столбец H уходит под заголовки, где в ячейке H1 единоразово для текущего листа указывается токен:

=СЦЕП("--header 'Accept: application/json' --header 'Authorization: Bearer ";$H$1;"'")

столбец I уходит под URL (ссылки-дети, на основе ссылки-родителя)

столбец J уходит под результат (ответ от сервера)

Далее, нам необходимо реализовать подпрограмму(макрос) отправки GET-запросов. Состоит она из четырех частей:

цикла, который считает количество строк для работы по листу, пробегая по столбцу А с 2 по первую пустую ячейку, чтобы у цикла был конец.

временной задержки, в случае если нужно отправлять запросы не сразу, после получения ответа, а задав время ожидания

таймером, который показывает время выполнения всего макроса после завершения

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

Таким образом, скрипт проходит по столбцу I, забирая из значения каждой ячейки URL, для тех строк, где в столбце А есть значения (которые и подставляются в URL). Для удобства также сделаны кнопки очистки полей и подсветка запросов условным форматированием, в случае успешного ответа на запрос.

PUT

Чуть-чуть усложним задачу и перейдем к PUT-запросам. В данном примере необходимо изменить профиль устройства, чтобы сервер по-другому с ним взаимодействовал.

К исходным данным для GET – запроса добавляется тело запроса с ключем-значением (п4). Итого дано:

1) Ссылка, в которой указываются параметры запроса.

2) Заголовки запроса + Токен авторизации (Bearer Token)

--header 'Content-Type: application/json' --header 'Accept: application/json' --header 'Authorization: Bearer

3) Параметр, указываемый в ссылке (в данном примере это внутренние идентификаторы устройств – hRef):

4) Тело запроса, с ключом и значением:

Немного дополняем новый PUT-лист в Excel по сравнению с GET (остальное без изменений):

новый столбец B теперь отвечает за ключ deviceProfileId (ячейка B1), а все значения ниже за его возможные значения)

Немного поменяем макрос и вынесем его в отдельную подпрограмму:

Привяжем макрос к кнопке и выполним.

Логика абсолютно аналогична GET запросу.

POST

Для POST запросов все аналогично PUT. Только немного меняется код в части типа запроса. В данном примере на устройство отправляется команда-конфигурация с указанием тела посылки (payload_hex) и порта (fport) для конкретного устройства.

Получившаяся таблица выглядит следующим образом:

На этом часть для Windows заканчивается. Здесь все оказалось довольно просто: стандартная библиотека, простенький алгоритм перебора значений в цикле.

Часть 2. Реализация решения под MacOS и Excel 64-bit

Чтобы обойти данное ограничение, был выбран единственный рабочий подход через cUrl, exec и функции. Данное решение точно работает под версией MacOS 10.14 и Excel 16.51. Функция ниже, в том или ином виде, встречается на различных форумах, однако на текущих версиях софта – не работает. В итоге, после небольших правок получили рабочий вариант:

Была отлажена функция вызова ExecShell:

И написаны отдельные функции для работы с различным методами GET / PUT / POST, которые на входе принимают URL и параметры):

В итоге, у меня получилось аналогичное windows по работе и функционалу решение для MacOS c использованием Excel 64-bit.

На просторах интернета я не нашел какого-то сборного и единого описания, только фрагменты кода и подходов, которые в большинстве случаев не работали полностью или частично. Поэтому решил объединить все в рабочее решение и выложить на хабр для истории.

На текущий момент я все еще не встретил иного решения, которое бы позволяло в пару кликов копипастить тысячи идентификаторов и параметров из excel и массово их отправлять на сервер. Надеюсь, кому-то пригодится :)

Если такие сторонние решения есть, а я не в курсе, и все можно было сделать проще, быстрее и изящнее – делитесь опытом в комментариях.

Файл-пример, который можно потыкать, пока жив сервер и "бессрочный" токен:

Читайте также: