Как разобрать json в excel

Обновлено: 06.07.2024

У меня та же проблема, что и в Excel VBA: анализируемый цикл объектов JSON, но я не могу найти решение. Мой JSON имеет вложенные объекты, поэтому предлагаемые решения, такие как VBJSON и vba-json, не работают для меня. Я также исправил один из них для правильной работы, но в результате было переполнение стека вызовов из-за многих рекурсий функции doProcess.

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

Если бы моя функция синтаксического анализа javascript могла вызвать функцию VBA или подпрограмму, это было бы отлично.

Если вы хотите построить на вершине ScriptControl Вы можете добавить несколько вспомогательных методов, чтобы получить необходимую информацию. JScriptTypeInfo Объект немного неудачен: он содержит всю необходимую информацию (как вы можете видеть в окне Watch), но, кажется, невозможно достичь этого с помощью VBA. Однако движок Javascript может помочь нам:

Если JScriptTypeInfo экземпляр ссылается на объект Javascript, For Each . Next не сработает Тем не менее, он работает, если он ссылается на массив Javascript (см. GetKeys функция).
Свойства доступа, имя которых известно только во время выполнения, используют функции GetProperty а также GetObjectProperty ,
Массив Javascript предоставляет свойства length , 0 , Item 0 , 1 , Item 1 и т. д. с точечной нотацией VBA ( jsonObject.property ), доступно только свойство длины, и только если вы объявляете переменную 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 , тогда этот ответ может помочь вам заставить его работать.

Howdee! I had a lot of requests after my recent article on converting a JSON string to Excel, asking if it was possible to convert Excel to JSON. Fortunately, it is not only possible, but you can use the same library we previously covered to do so! In this article, we will go over how to convert your Excel data to a JSON string, and how to convert that same data to a JSON file. If you haven’t read the previous article linked above, I highly recommend you do so before reading on here. Beyond covering how to import JSON, it also covers the basics of JSON objects and arrays. With that said, let’s dive in!

Convert Excel to JSON String

The first variable, excelRange, will be used to define the range of data we want to convert to JSON. The second, jsonItems, will be the collection we ultimately store our data in. Collection might be a new term to some so, at a high level, a collection is just an object that we can use to group and manage related objects. The third variable, jsonDictionary, is the dictionary we will first store our Excel values in. A dictionary is a type of collection that you can access using a key. Again, check out the previous post on JSON to get a better understanding of this type of object. The last two variables, i & cell, will be used in our for loop to help store the values to a dictionary. The next bit of our code looks like this:

So what is happening here? Firstly, I start by setting the range where our data exists. Now that I know the size of the range, I can begin to convert Excel to JSON by looping through each row (skipping headers) and writing each cell’s value into the dictionary. At the end of each loop, I need to write the dictionary to my collection and then clear it for the next loop. Lastly, I’m printing this JSON string to a message box so we can view it. You can do whatever you wish with the JSON string at this point, I’m only printing to a message box to demonstrate how to convert Excel to JSON string.

Convert Excel to JSON File

Converting Excel data to a JSON file is almost the exact same process. The only difference is, instead of outputting the result to a string variable or writing it to a cell in the workbook, you’re going to use the FileSystemObject model to create a new file to convert & store Excel data to a JSON file. You’ll need these two new variables:

The first allows you to access the FileSystemObject model which will allow you to create new files and save them to a folder. The second allows you to write text, in this case the JSON string, to the file. Those two new lines of code look like this:

The first will create the new JSON file. Make sure you use the keyword “True” if you’re planning to reuse this code. This will allow the program to overwrite the file. However, if you’re having the user create the name of the file, it might be useful to use “False” which will not allow the program to create any new file that would overwrite a previous one.

The second line of code simply writes the converted JSON to the file. Unless you open the file in your code, there is no need to explicitly save or close the file in this example. The full code to convert Excel to JSON looks like this:

Here is the outputted JSON file displayed in Visual Studio Code:

I hope this tutorial was useful for those of you that need to write your Excel data to JSON either to a string or to a file. If you have questions, drop them in the comments below. As always, the example file is available for your download if you’re a registered member to this site. The file contains the examples I walked through today to convert Excel to JSON. Registering is free and only takes a minute. You’ll get notified as I release additional content but I won’t spam you with tons of unsolicited emails. Until next time!

У меня та же проблема, что и в Excel VBA: разобранный цикл объектов JSON, но не могу найти любое решение. В моем JSON есть вложенные объекты, поэтому предлагаемые решения, такие как VBJSON и vba-json, мне не подходят. Я также исправил один из них, чтобы он работал правильно, но результатом было переполнение стека вызовов из-за многих рекурсий функции doProcess.

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

Если бы моя функция синтаксического анализа javascript могла запускать функцию или подпрограмму VBA, это было бы отлично.

Если вы хотите построить на основе ScriptControl , вы можете добавить несколько вспомогательных методов для получения необходимой информации. С объектом JScriptTypeInfo немного прискорбно: он содержит всю необходимую информацию (как вы можете видеть в окне Watch ), но кажется невозможным получить ее с помощью VBA. Однако движок Javascript может нам помочь:

Если экземпляр JScriptTypeInfo ссылается на объект Javascript, For Each . Next работать не будет. Однако он работает, если ссылается на массив Javascript (см. Функцию GetKeys ).
Свойства доступа, имя которых известно только во время выполнения, используют функции GetProperty и GetObjectProperty .
Массив Javascript предоставляет свойства length , 0 , Item 0 , 1 , Item 1 и т. Д. С точечной нотацией VBA ( jsonObject.property ), доступно только свойство длины, и только если вы объявите переменную с именем length со всеми строчными буквами. В противном случае случай не совпадает, и он его не найдет. Остальные свойства недействительны в VBA. Так что лучше используйте функцию GetProperty .
В коде используется раннее связывание. Поэтому вам нужно добавить ссылку на «Microsoft Script Control 1.0».
Вы должны вызвать InitScriptEngine один раз перед использованием других функций для выполнения базовой инициализации.

Другой парсер JSON на основе Regex (только декодирование)

У меня было требование проанализировать очень конкретную строку JSON, представляющую результаты вызова веб-API. JSON описывает список объектов и выглядит примерно так:

По этому поводу следует отметить несколько моментов:

JSON всегда должен описывать список (даже если он пустой), который должен содержать только объекты.
Объекты в списке должны содержать только свойства простых типов (строка / дата / число / логическое значение или null ).
Значение свойства может содержать запятую, что несколько усложняет синтаксический анализ JSON, но может не содержать кавычки (потому что я слишком ленив, чтобы иметь дело с этим) .

Функция ParseListOfObjects в приведенном ниже коде принимает строку JSON в качестве входных данных и возвращает Collection , представляющий элементы в списке. Каждый элемент представлен как Dictionary , где ключи словаря соответствуют именам свойств объекта. Значения автоматически преобразуются в соответствующий тип ( String , Date , Double , Boolean - или Empty , если значение равно null ) .

Вашему проекту VBA потребуется ссылка на библиотеку Microsoft Scripting Runtime для использования объекта Dictionary - хотя удалить эту зависимость не составит труда, если вы используете другой способ кодирования результатов.

Вот мой JSON.bas :

Два небольших вклада в ответ Codo:

Итак, теперь я могу делать такие вещи, как:

Большое спасибо, Кодо.

Я только что обновил и завершил то, что вы сделали для:

сериализовать json (мне нужно, чтобы вставить json в текстовый документ)

добавить, удалить и обновить узел (кто знает)

Microsoft : поскольку VBScript является подмножеством Visual Basic для Приложения.

Это работает для меня в Excel и больших файлах JSON с использованием запроса JSON, переведенного в собственную форму. https://github.com/VBA-tools/VBA-JSON Я могу проанализировать узел типа "item.something" и получить значение с помощью простой команды:

Чтобы проанализировать JSON в VBA без добавления огромной библиотеки в проект книги, я создал следующее решение. Это очень быстро и сохраняет все ключи и значения в словаре для легкого доступа:

Приведенный выше код действительно использует несколько вспомогательных функций, но приведенный выше является его основой.

Используемая здесь стратегия заключается в использовании рекурсивного токенизатора. Мне было достаточно интересно написать статью об этом решении на Medium. Это объясняет детали.

Вот полный (но удивительно короткий) листинг кода, включая все вспомогательные функции:

Более простой способ использовать array.myitem (0) в коде VB

Используйте объект this в js

Затем вы можете перейти к array.myitem (0)

ОБНОВЛЕНИЕ 3 (24 сентября 2017 г.)

Проверьте VBA-JSON-parser на GitHub, чтобы получить последнюю версию и примеры. Импортируйте модуль JSON.bas в проект VBA для обработки JSON .

ОБНОВЛЕНИЕ 2 (1 октября 2016 г.)

Однако, если вы хотите проанализировать JSON в 64-разрядном Office с помощью ScriptControl , тогда этот ответ может вам помочь. чтобы заставить ScriptControl работать в 64-битной версии.

ОБНОВЛЕНИЕ (26 октября 2015 г.)

Пытаясь избежать этого, я создал парсер JSON на основе RegEx. Объекты <> представлены словарями, что позволяет использовать свойства и методы словаря: .Count , .Exists() , .Item() , .Items , .Keys . Массивы [] - это обычные VB-массивы с отсчетом от нуля, поэтому UBound() показывает количество элементов. Вот код с некоторыми примерами использования:

Еще одна возможность этого парсера JSON RegEx заключается в том, что он работает в 64-битном Office, где ScriptControl недоступен.

НАЧАЛЬНЫЙ (27 мая 2015 г.)

Вот еще один метод синтаксического анализа JSON в VBA, основанный на ScriptControl ActiveX, без внешних библиотек:

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

Однако я получаю ошибку ниже!

Почему я получаю эту ошибку? Добрый совет

2 ответа

Далее в массиве data есть объекты, каждый из которых содержит некоторые свойства, которые могут быть заполнены в строках на листе:

Вот пример VBA, показывающий, как можно получить эти значения. Импортируйте модуль JSON.bas в проект VBA для обработки JSON.

Вывод массива data для меня выглядит следующим образом:

Кстати, аналогичный подход применялся в других ответах.

Я вставил ваш код в тестовый модуль, а затем импортировал JsonConverter в качестве дополнительного модуля в свою пустую книгу. Вероятно, вы получаете ошибку, потому что вам нужно добавить библиотеку Microsoft Scripting Runtime в свою книгу. В VBE перейдите в меню Инструменты -> Ссылки . а затем прокрутите вниз и поставьте галочку рядом с библиотекой. После этого ваш код без проблем проанализировал JSON.

Однако это не удалось в вашем цикле.

Я настоятельно рекомендую использовать Option Explicit в верхней части модуля. Типы переменных, которые, по вашему мнению, вы используете (потому что я вижу, что вы пытаетесь использовать венгерскую нотацию), не обязательно являются типами фактических данных. Я предлагаю использовать описательные имена для переменных, чтобы избежать путаницы. Кроме того, вы должны зацикливаться на структуре oJSON("data") (которая, кстати, является Collection ). Вот мои предложения, воплощенные в жизнь:

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