Как подключить api к excel
Ранее уже писал про получение данных с Московской биржи через формулы 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 цены может просто не быть.
Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc
Автоматическое получение дивидендных выплат для акций
Очень удобная функция Мосбиржи, которая позволяет получать не только значение текущей выплаты, но и историю выплат дивидендов вместе с датами и значениями.
Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc
Автоматическое получение облигационных выплат
По облигациям (не только корпоративным, но также ОФЗ и еврооблигациям) можно автоматически получать дату выплаты следующего купона и его значение.
Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc
Автоматическое получение дат оферт
Удобно планировать собственные финансы, получая даты оферт (дата, в которую инвестор или эмитент имеют право досрочно погасить облигацию по цене номинала) автоматически.
Файл « API Мосбиржи в Microsoft Excel.xlsx » с примерами автоматического получения имени для разных классов активов. Корректно работает и в LibreOffice Calc
UPD. Пользователь mixei подсказывает, что автоматическое обновление настраивается через Параметры — Центр управления безопасностью — вкладка Внешнее содержимое — там надо поставить все флажки где не рекомендуется :) Но это на страх и риск пользователей.
API Московской биржи предоставляет широкие возможности, которые гораздо шире чем описанные в данной статье. Это статья своеобразная шпаргалка для долгосрочного частного инвестора, который ведёт учёт в локальном файле на собственном компьютере.
Также хочу отметить, что я никак не связан с Московской биржей и использую ИСС Мосбиржи только в личных интересах.
В этой статье описывается использование модели API для создания надстроек в Excel, Word, PowerPoint и OneNote. Здесь представлены основные понятия, лежащие в основе использования API на основе обещаний.
Эта модель не поддерживается клиентами Office 2013. Используйте общую модель API для работы с этими версиями Office. Полные сведения о доступности платформ см. в статье Доступность клиентских приложений и платформ Office для надстроек Office.
В примерах на этой странице используются Excel API JavaScript, но эти понятия также применяются к API OneNote, PowerPoint, Visio и API JavaScript 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 . Это означает, что свойство 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 .
В следующем примере кода осуществляется попытка получить лист Excel с именем Data с помощью метода getItemOrNullObject() . Если лист с таким именем не существует, создается новый лист. Обратите внимание, что код не загружает свойство isNullObject . Office автоматически загружает это свойство, когда вызывается context.sync , поэтому вам не нужно явным образом загружать его с помощью dataSheet.load('isNullObject') .
Вы можете использовать пакет средств разработки 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 JavaScript для Excel. Полный список свойств Workbook и методов, поддерживаемых объектом, см. в книге Объект (API JavaScript для Excel). В этой статье также рассматриваются действия на уровне книги, выполняемые с помощью объекта Application.
Объект Workbook — это точка входа для вашей надстройки для взаимодействия с Excel. Он поддерживает коллекции листов, таблиц, сводных таблиц и других элементов, через которые выполняется доступ и изменение данных Excel. Объект WorksheetCollection предоставляет надстройке доступ ко всем данным книги с помощью отдельных листов. В частности, он позволяет надстройке добавлять листы, перемещаться между ними и назначать обработчиков событий листа. В статье Работа с листами с использованием API JavaScript для Excel описывается способ доступа к листам и их изменение.
Получение активной ячейки или выделенного диапазона
Объект Workbook содержит два метода для получения диапазона ячеек, выделенных пользователем или надстройкой: getActiveCell() и getSelectedRange() . getActiveCell() получает активную ячейку из книги в виде объекта Range. В приведенном ниже примере показан вызов getActiveCell() с последующей печатью адреса ячейки в консоль.
Метод getSelectedRange() возвращает один диапазон, выделенный в настоящее время. Если выделено несколько диапазонов, возникает ошибка InvalidSelection. В приведенном ниже примере показан вызов метода getSelectedRange() , который затем устанавливает желтый цвет заливки для диапазона.
Создание книги
Ваша надстройка может создать новую книгу, отдельную от экземпляра Excel, в котором в настоящее время работает надстройка. Для этой цели в объекте Excel имеется метод createWorkbook . При вызове этого метода сразу открывается и отображается новая книга в новом экземпляре программы Excel. Ваша надстройка остается открытой и запущенной в предыдущей книге.
С помощью метода createWorkbook также можно создать копию существующей книги. Метод принимает в качестве необязательного параметра строковое представление XLSX-файла в кодировке base64. Полученная книга будет копией этого файла, предполагая, что строковый аргумент является допустимым XLSX-файлом.
Текущую книгу надстройки можно получить в качестве строки с кодом base64 с помощью нарезки файлов. Преобразование файла в нужную строку в кодировке base64 можно выполнить с помощью класса FileReader, как показано в приведенном ниже примере.
Вставка копии существующей книги в текущую книгу.
В предыдущем примере показана новая книга, которая была создана из существующей книги. Вы также можете скопировать отдельные части или всю существующую книгу целиком в книгу, привязанную в настоящее время к вашей надстройке. В книге используется метод insertWorksheetsFromBase64 вставки копий таблиц целевой книги в себя. Файл другой книги передается как строка с кодом base64, как и вызов Excel.createWorkbook .
Метод insertWorksheetsFromBase64 поддерживается для Excel на Windows, Mac и в Интернете. Он не поддерживается для iOS. Кроме того, Excel в Интернете этот метод не поддерживает исходные таблицы с элементами PivotTable, Chart, Comment или Slicer. Если эти объекты присутствуют, insertWorksheetsFromBase64 UnsupportedFeature метод возвращает ошибку в Excel в Интернете.
В следующем примере кода показано, как вставить в текущую книгу таблицы из другой книги. Этот пример FileReader кода сначала обрабатывает файл книги с объектом и извлекает строку с кодом base64, а затем вставляет эту строку с кодом base64 в текущую книгу. Новые листы вставляются после листа с именем Sheet1. Обратите внимание [] , что он передается в качестве параметра свойства InsertWorksheetOptions.sheetNamesToInsert . Это означает, что все таблицы из целевой книги вставляются в текущую книгу.
Защита структуры книги
Надстройка может управлять возможностью пользователя по изменению структуры книги. Свойство protection объекта Workbook является объектом WorkbookProtection с методом protect() . В приведенном ниже примере показан основной сценарий переключения защиты структуры книги.
Метод protect принимает необязательный строковый параметр. Эта строка представляет пароль, необходимый пользователю для обхода защиты и изменения структуры книги.
Защиту также можно установить на уровне книги, чтобы предотвратить нежелательные изменения данных. Дополнительные сведения см. в разделе Защита данных статьи Работа с листами с использованием API JavaScript для Excel.
Дополнительные сведения о защите книги в Excel см. в статье Защита книги.
Доступ к свойствам документов
Объекты Workbook имеют доступ к метаданным файлов Office, называемым свойствами документов. Свойство properties объекта Workbook является объектом DocumentProperties, содержащим эти значения метаданных. В следующем примере показано, как установить author свойство.
Настраиваемые свойства
Также можно установить настраиваемые свойства. Объект DocumentProperties содержит свойство custom , представляющее коллекцию пар "ключ-значение" для свойств, определяемых пользователем. В приведенном ниже примере показано, как создать настраиваемое свойство с именем Introduction со значением "Hello", а затем вызвать его.
Настраиваемые свойства на уровне таблицы
Настраиваемые свойства также можно установить на уровне таблицы. Они похожи на настраиваемые свойства на уровне документов, за исключением того, что один и тот же ключ может повторяться в разных таблицах. В следующем примере показано, как создать настраиваемую свойство WorksheetGroup со значением "Альфа" на текущем таблице, а затем получить его.
Доступ к параметрам документа
Параметры книги похожи на коллекцию настраиваемых свойств. Различие заключается в том, что параметры уникальны для одного файла Excel и соединения надстройки, а свойства связаны только с файлом. В приведенном ниже примере показано, как создать параметр и получить к нему доступ.
Доступ к настройкам культуры приложений
В книге есть языковые и культурные параметры, влияющие на отображение определенных данных. Эти параметры могут помочь локализовать данные, когда пользователи надстройки делятся книгами на разных языках и культурах. Ваша надстройка может использовать анализ строк для локализации формата чисел, дат и времени в зависимости от параметров культуры системы, чтобы каждый пользователь видел данные в формате своей культуры.
Application.cultureInfo определяет параметры культуры системы как объект CultureInfo . Это содержит параметры, такие как числовой десятичной сепаратор или формат даты.
Некоторые параметры культуры можно изменить с помощью Excel пользовательского интерфейса. Параметры системы сохраняются в объекте CultureInfo . Любые локальные изменения хранятся в качестве свойств уровня приложений, например Application.decimalSeparator .
В следующем примере изменяется десятичное сепараторное течение числовой строки с "," на символ, используемый в параметрах системы.
Добавление настраиваемых XML-данных в книгу
Формат файла Excel Open XML (XLSX) позволяет надстройке внедрить настраиваемые XML-данные в книгу. Эти данные сохраняются с книгой независимо от надстройки.
Книга содержит объект CustomXmlPartCollection, являющийся списком объектов CustomXmlParts. Они предоставляют доступ к строкам XML и соответствующему уникальному идентификатору. Сохраняя эти идентификаторы как параметры, надстройка может сохранять ключи к частям XML между сеансами.
В приведенных ниже примерах показано, как использовать настраиваемые части XML. В первом блоке кода показано, как внедрять XML-данные в документ. Выполняется сохранение списка проверяющих, а затем используются параметры книги, чтобы сохранить параметр id XML для будущих извлечений. Во втором блоке показано, как получить доступ к этим XML-данным позднее. Параметр "ContosoReviewXmlPartId" загружается и передается объекту customXmlParts книги. Данные XML затем печатаются в консоль.
CustomXMLPart.namespaceUri заполняется только в том случае, если настраиваемый XML-элемент верхнего уровня содержит атрибут xmlns .
Управление режимом вычислений
Установка режима вычислений
По умолчанию Excel пересчитывает результаты формул при каждом изменении ячейки из ссылки. Производительность вашей надстройки можно улучшить путем изменения режима вычислений. У объекта Application есть свойство calculationMode типа CalculationMode . Его можно установить к следующим значениям.
- automatic : режим пересчета по умолчанию, при котором Excel вычисляет новые результаты формулы при каждом изменении соответствующих данных.
- automaticExceptTables : аналогично automatic , за исключением того, что игнорируются любые изменения значений таблиц.
- manual : вычисления выполняются только в том случае, если пользователь или надстройка запрашивает их.
Установка типа вычислений
Объект Application предоставляет метод применения немедленного пересчета. Метод Application.calculate(calculationType) запускает ручной пересчет с учетом указанного типа calculationType . Можно укаварить следующие значения.
- full : пересчет всех формул во всех открытых книгах независимо от их изменения с прошлого пересчета.
- fullRebuild : проверка зависимых формул с последующим пересчетом всех формул во всех открытых книгах независимо от их изменения с прошлого пересчета.
- recalculate : пересчет формул, которые были изменены (или помечены программным путем для пересчета) с момента последнего вычисления, и зависимых от них формул во всех активных книгах.
Временная приостановка вычисления
API Excel также позволяет надстройкам отключить вычисления до вызова RequestContext.sync() . Для этого используется suspendApiCalculationUntilNextSync() . Используйте этот метод, если ваша надстройка изменяет большие диапазоны без необходимости доступа к данным между изменениями.
Обнаружение активации книг
Ваша надстройка может обнаруживать при активации книги. Книга становится неактивной, когда пользователь переключает фокус на другую книгу, на другое приложение или (в Excel в Интернете) на другую вкладку веб-браузера. Книга активируется , когда пользователь возвращает фокус в книгу. Активация книги может вызвать функции вызова в надстройке, например освежающие данные книги.
Чтобы определить, когда книга активирована, зарегистрируйте обработник событий для события onActivated книги. Обработчики событий для onActivated события получают объект WorkbookActivatedEventArgs при пожаре события.
Событие onActivated не определяет, когда книга открывается. Это событие обнаруживает только тогда, когда пользователь переключается на уже открытую книгу.
В следующем примере кода показано, как onActivated зарегистрировать обработник событий и настроить функцию вызова.
Сохраните книгу.
Workbook.save сохраняет книгу в постоянное хранилище. Метод save принимает один необязательный saveBehavior параметр, который может быть одним из следующих значений.
- Excel.SaveBehavior.save (по умолчанию): файл будет сохранен без предварительного запроса имени файла, а также место для сохранения. Если файл не был сохранен ранее, он будет сохранен в папке по умолчанию. Если файл уже был сохранен ранее, он будет сохранен в той же папке.
- Excel.SaveBehavior.prompt : если файл не был сохранен ранее, будет предложено ввести имя файла и место для сохранения. Если файл уже был сохранен ранее, он будет сохраняться в той же папке, и никаких дополнительных действий не потребуется.
Если пользователь при запрос на сохранение отменяет операцию, save выдает исключение.
Закрытие книги.
Workbook.close закрывает книгу, а также надстройки, связанные с книгой, (приложение Excel остается открытым). Метод close принимает один необязательный closeBehavior параметр, который может быть одним из следующих значений.
Работая в 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 и массово их отправлять на сервер. Надеюсь, кому-то пригодится :)
Если такие сторонние решения есть, а я не в курсе, и все можно было сделать проще, быстрее и изящнее – делитесь опытом в комментариях.
Файл-пример, который можно потыкать, пока жив сервер и "бессрочный" токен:
Читайте также: