Excel vba json запрос
У меня такая же проблема, как в Excel VBA: Parsed JSON Object Loop, но не может найти никакого решения. У моего JSON есть вложенные объекты, поэтому предлагаемое решение вроде VBJSON и vba-json не работает для меня. Я также исправил один из них для правильной работы, но результатом было переполнение стека вызовов из-за многих рекурсий функции doProcess.
Проблема на этом этапе заключается в том, что я не могу определить, какова будет структура объектов, поэтому я заранее не знаю ключи, которые будут находиться в каждом родовом объекте. Мне нужно пропустить общий объект VBA для получения ключей/свойств.
Если моя функция разбора javascript может вызвать функцию или подпрограмму VBA, это будет отлично.
Я помню ваш предыдущий вопрос, поэтому интересно снова его увидеть. У меня есть один вопрос: допустим, вы преуспели в анализе вашего JSON в VBA - как бы вы тогда использовали этот «объект» в VBA? Обратите внимание, что структура JSON может быть любого типа, так как бы вы могли ориентироваться в конечном результате в VBA? Моей первой мыслью может быть создание JScript, который будет анализировать JSON (используя eval или даже одну из «лучших» существующих библиотек) и затем выполнять итерацию по структуре, чтобы создать вложенный объект на основе словаря сценариев для передачи обратно в VBA. Что вы делаете с вашим разобранным JSON? Я создам лист для каждого объекта и добавлю записи в каждую строку, создав столбец, если он еще не существует (добавляя в строку 1). Ваш предложенный asp-xtreme-evoluton кажется интересным. Был в процессе создания чего-то очень похожего. Мне предоставили исправленный и почти работающий (я исправил небольшую «проблему») класса vba-json. Мы будем использовать это на данный момент. Рабочую версию vba-json предоставил Рэндир, автор соответствующего вопроса. @tim, мой предыдущий комментарий может не ответить на ваш вопрос должным образом. Я знаю, что структура в основном представляет собой список таблиц с записями. Таким образом, у меня есть объект (ключ: значение), представляющий таблицы. «Ключ» - это имя таблицы, а значение - массив [] записей, которые являются объектами (ключ: значение). Я не знаю точно, какая таблица была предоставлена и какие столбцы (поля) доступны. Для людей, которые не могут обойтись без строгой структуры, это дикое общее программирование :-) Конечно, никому не в обиду. Проще следовать, если структуры похожи, но «ключи» разные. Из интереса откуда берутся данные?Если вы хотите построить поверх ScriptControl , вы можете добавить несколько вспомогательных методов, чтобы получить необходимую информацию. Объект JScriptTypeInfo немного неудачен: он содержит всю необходимую информацию (как вы можете видеть в окне "Смотреть" ), но, похоже, это невозможно с VBA. Однако механизм Javascript может помочь нам:
- Если экземпляр JScriptTypeInfo ссылается на объект Javascript, For Each . Next не будет работать. Однако он работает, если он ссылается на массив Javascript (см. Функцию GetKeys ).
- Свойства доступа, имя которых известно только во время выполнения, используют функции GetProperty и GetObjectProperty .
- Массив Javascript предоставляет свойства length , 0 , Item 0 , 1 , Item 1 и т.д. С нотной точкой VBA ( jsonObject.property ) доступно только свойство length, и только если вы объявляете переменную с именем length со всеми строчными буквами. В противном случае случай не соответствует, и он не найдет его. Другие свойства недействительны в VBA. Поэтому лучше используйте функцию GetProperty .
- В коде используется раннее связывание. Поэтому вам нужно добавить ссылку на "Microsoft Script Control 1.0".
- Вам нужно позвонить InitScriptEngine один раз, прежде чем использовать другие функции, чтобы выполнить некоторую базовую инициализацию.
Вот еще один метод для анализа JSON в VBA, основанный на ScriptControl ActiveX, без внешних библиотек:
ОБНОВИТЬ
Пытаясь избежать этого, я создал JSON-парсер на основе RegEx. Объекты <> представлены словарей, что делает возможным использование словаря свойства и методы: .Count , .Exists() , .Item() , .Items , .Keys . Массивы [] - это обычные VB-массивы на основе нуля, поэтому UBound() показывает количество элементов. Вот код с некоторыми примерами использования:
Проверьте последнюю версию VBA-JSON-parser на GitHub (импортируйте модуль JSON.bas в проект VBA для обработки JSON).
Еще одна возможность этого анализатора JSON RegEx заключается в том, что он работает в 64-разрядной версии Office, где ScriptControl недоступен.
UPDATE2
Однако, если вы хотите проанализировать JSON в 64-битном Office с помощью ScriptControl , этот ответ может помочь вам заставить его работать.
Excel таблицы могут быть представлены в виде массива объектов в виде JSON. Каждый объект представляет строку в таблице. Это помогает извлекать данные из Excel в согласованном формате, который виден пользователю. Затем данные могут быть переданы другим системам Power Automate потоками.
Данные таблицы ввода
Вариант этого примера также включает гиперссылки в одном из столбцов таблицы. Это позволяет всплыть в JSON дополнительные уровни данных ячейки.
Данные таблицы ввода, включаемой гиперссылки
Диалоговое окно для редактирования гиперссылки
Пример Excel файла
Скачайте файлtable-data-with-hyperlinks.xlsx для готовой к использованию книги. Добавьте следующий скрипт, чтобы попробовать пример самостоятельно!
Пример кода: данные таблицы возврата в качестве JSON
Вы можете изменить interface TableData структуру, чтобы соответствовать столбцам таблицы. Обратите внимание, что для имен столбцов с пробелами обязательно поместите ключ в кавычках, например в "Event ID" примере.
Пример вывода из таблицы "PlainTable"
Пример кода. Возвращаем данные таблицы как JSON с текстом гиперссылки
Сценарий всегда извлекает гиперссылки из 4-го столбца (индекс 0) таблицы. Вы можете изменить этот порядок или включить несколько столбцов в качестве данных гиперссылки, изменяя код под комментарием // For the 4th column (0 index), extract the hyperlink and use that instead of text.
Пример вывода из таблицы "WithHyperLink"
Использование в Power Automate
О том, как использовать такой сценарий в Power Automate, см. в Power Automate.
Цель книги – предоставить инструменты Excel для автоматизации повторяющихся задач извлечения данных из Интернета. Автор предлагает несколько десятков готового кода VBA и описывает приемы работы в Power Query.
Eduardo Sanchez. Excel and The World Wide Web. Straight to the Point. – Holy Macro! Books, 2021. – 58 p.
Глава 1. Приступая к работе
Что такое HTML?
HTML (Hyper Text Markup Language) – язык гипертекстовой разметки. Он используется для создания веб-сайтов. Гипертекст – это контент, который ведет себя нелинейным образом. Представьте себе веб-сайт, на каждой странице которого есть несколько ссылок на другие страницы, как того же самого сайта, так и других сайтов. Пользователь перемещается, переходя с одной страницы на другую; это гипертекстовое поведение. Обычная печатная книга – это контрпример, ее предполагается читать последовательно.
Информация в HTML помечена тегами; ниже мы поговорим об этом подробнее. Существуют и другие языки, используемые для создания веб-сайтов, такие как CSS и JavaScript, но мы не будем подробно рассматривать их здесь.
CSS (Cascading Style Sheet) – каскадная таблица стилей, язык описания внешнего вида документа. Он работает вместе с HTML, который отвечать за содержимое страницы. Каскадирование означает, что можно использовать несколько CSS-файлов для создания окончательного визуального стиля. Этот язык управляет такими элементами, как размер шрифта, фоновые изображения и цветовая палитра.
JavaScript – язык программирования для реализации динамического поведения на веб-сайтах. С его помощью разработчики могут манипулировать содержимым страницы, создавать диаграммы и взаимодействовать с API (Application Programming Interface, интерфейс прикладного программирования). Обратите внимание, что JavaScript и Java – это два разных языка. Говорят, что в будущем JavaScript может заменить VBA в качестве языка программирования Office.
Одна из замечательных особенностей современных браузеров заключается в том, что они предоставляют исходный код страниц. Если вы используете Google Chrome, просто щелкните правой кнопкой мыши любой элемент страницы и выберите пункт Просмотреть код; в правой части окна появится панель, аналогичная показанной ниже:
Рис. 1. Фрагмент кода HTML веб-страницы; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
- Ключевые слова header, div, aside, nav, h4, li и другие являются тегами.
- Когда маленькие черные треугольники указывают вправо, это означает, что их можно щелкнуть, чтобы развернуть и отобразить дополнительную информацию.
- Ключевые слова id, class и href являются атрибутами.
- <li – это открывающий тег, а </li> – закрывающий.
- Элемент может принадлежать к нескольким различным классам.
Если вы чувствуете себя подавленным всем этим, не паникуйте; не обязательно быть программистом HTML, чтобы работать с такого рода автоматизацией. Позже мы увидим, как читать код HTML и извлекать информацию с помощью VBA.
Хотя веб-дизайнеры используют профессиональные редакторы, можно создавать HTML-файлы в стандартных приложениях Windows, например, в Блокноте. Это особенно быть полезно, когда вы хотите протестировать код VBA, который будет взаимодействовать с веб-страницей, но по какой-то причине реальный сайт недоступен.
В этом посте я покажу, как с помощью VBA сделать, то, для чего VBA вроде бы как изначально не предназначен – как получить значения нужных переменных из структуры JSON.
Поэтому создаваемому на VBA паучку придется постараться, чтобы понять разметку «для людей».
Для работы паука необходимо дополнительно подключить три библиотеки:
- Microsoft XML parser (MSXML) – тот же, что использовался для получения курсов ЦБ с сайта Банка России.
- Библиотеку для работы с объектной моделью HTML.
- Библиотеку для использования возможностей JavaScript из VBA.
В результате выполнения нижепредставленного кода в переменной strJson должна оказаться структура с данными в JSON формате.
Теперь самое интересное – как распарсить эту JSON структуру? Чистый VBA это делать не умеет. Но с JSON прекрасно работает JavaScript.
А в VBA есть инструмент для использования возможностей JavaScript для пользователей MS Excel.
Мы можем в VBA получить уже распарсенную JSON переменную:
Проблема в том, что с объектом objJSON ничего нельзя сделать в рамках VBA – у него нет ни свойств, ни методов. Поэтому создаем эти методы на языке JavaScript. Нам нужно вытащить даты (xAxis) и количество (series->data):
Вот что пишем в VBA редакторе:
Загоняем данные в привычные VBA массивы:
Ну и раскатываем эти массивы по рабочему листу:
Вот, что получилось в результате на листе рабочей книги:
По этим данным легко построить график, например, такой:
Excel файл с кодом можно скачать здесь. Если будут вопросы – пишите их сюда.
Читайте также: