Как открыть json в excel
Как описано в статье обзор пользовательских функций, проект пользовательских функций должен включать как файл метаданных JSON, так и файл скрипта (JavaScript или TypeScript), чтобы зарегистрировать функцию, что делает ее доступной для использования. Настраиваемые функции регистрируются, когда пользователь запускает надстройки в первый раз и после этого доступен одному пользователю во всех книгах.
Обратите внимание, что настраиваемые функции доступны в Excel на следующих платформах.
- Office для Windows
- Office для Mac
- Office в Интернете
Сейчас настраиваемые функции Excel не поддерживаются на iPad или в версиях Office 2019 (или более ранних), предлагаемых в виде единовременных покупок.
По возможности рекомендуется использовать автогенерацию JSON вместо создания собственного JSON-файла. Автогенерация менее подвержена ошибкам пользователей, yo office и в них уже содержатся файлы с подмостки. Дополнительные сведения о тегах JSDoc и процессе автогенерации JSON см. в рублях Autogenerate JSON metadata for custom functions.
Однако проект настраиваемой функции можно сделать с нуля. Этот процесс требует:
- Напишите файл JSON.
- Убедитесь, что файл манифеста подключен к файлу JSON.
- Связать функции и id свойства name в файле скрипта для регистрации функций.
На следующем изображении объясняются различия между использованием yo office файлов леса и написанием JSON с нуля.
Не забудьте подключить манифест к файлу JSON, который вы создаете, через раздел в XML-файле манифеста, если вы не используете генератор Yeoman для Office надстройки.
Авторство метаданных и подключение к манифесту
Создайте файл JSON в проекте и укажи все сведения о его функциях, таких как параметры функции. См . следующий пример метаданных и ссылку на метаданные для полного списка свойств функций.
Убедитесь, что файл манифеста XML ссылается на файл JSON в разделе, аналогично следующему примеру.
Пример метаданных JSON
В примере кода ниже показано содержимое JSON-файла метаданных для надстройки, определяющей настраиваемые функции. В следующих за этим примером разделах приводятся подробные сведения об отдельных свойствах, представленных в этом примере JSON.
Полный пример JSON-файла доступен в истории фиксации OfficeDev/Excel-Custom-Functions GitHub репозитория. Поскольку проект был скорректирован для автоматического создания JSON, полный пример рукописного JSON доступен только в предыдущих версиях проекта.
Ссылка на метаданные
allowCustomDataForDataTypeAny (предварительная версия)
Свойство allowCustomDataForDataTypeAny в настоящее время доступно в общедоступных предварительных просмотрах и совместимо только с Office на Windows. Функции предварительной версии могут быть изменены и не предназначены для использования в рабочей среде. Рекомендуется использовать их только в тестовой среде и среде разработки. Не используйте функции предварительной версии в рабочей среде или в важных деловых документах.
Чтобы проверить это свойство в Office Windows, необходимо иметь номер сборки Excel больше или равен 16.0.14623.20002. Чтобы использовать эту функцию, необходимо присоединиться к программе предварительной оценки Office, а затем выбрать уровень участия в программе Бета-канал. Дополнительные сведения см. в статье Участие в программе предварительной оценки Office.
Свойство allowCustomDataForDataTypeAny — это тип данных boolean. Настройка этого значения позволяет true настраиваемой функции принимать типы данных в качестве параметров и значений возврата. Дополнительные сведения см. в пользовательских функциях и типах данных.
В отличие от большинства других свойств метаданных JSON, allowCustomDataForDataTypeAny это свойство верхнего уровня и не содержит под-свойств. Пример кода кода метаданных JSON см. в примере формата этого свойства.
allowErrorForDataTypeAny
Свойство allowErrorForDataTypeAny — это тип данных boolean. Настройка значения позволяет true настраиваемой функции обрабатывать ошибки в качестве значений ввода. Все параметры с типом или any[][] any могут принимать ошибки в качестве значений ввода, allowErrorForDataTypeAny когда установлено значение true . Значение по умолчанию allowErrorForDataTypeAny . false
В отличие от других свойств метаданных JSON, allowErrorForDataTypeAny это свойство верхнего уровня и не содержит под-свойств. Пример кода кода метаданных JSON см. в примере формата этого свойства.
functions
Свойство functions представляет собой массив объектов настраиваемых функций. В таблице ниже приведены свойства каждого объекта.
Свойство | Тип данных | Обязательный | Описание |
---|---|---|---|
description | string | Нет | Описание функции, которое отображается пользователям в Excel (например, преобразует значение по шкале Цельсия в температуру по шкале Фаренгейта). |
helpUrl | string | Нет | URL-адрес, по которому можно получить сведения о функции (отображается в области задач). Пример: http://contoso.com/help/convertcelsiustofahrenheit.html . |
id | string | Да | Уникальный идентификатор для функции. Этот идентификатор может содержать только буквы, цифры и точки и не может изменяться после настройки. |
name | string | Да | Имя функции, которое отображается пользователям в Excel. В Excel это имя функции префиксировали в настраиваемом пространстве имен функций, указанном в файле манифеста XML. |
options | object | Нет | Позволяет настроить некоторые аспекты того, как и когда Excel выполняет функцию. Дополнительные сведения см. в разделе options. |
parameters | array | Да | Массив, который определяет входные параметры для функции. Сведения см. в параметрах . |
result | object | Да | Объект, который определяет тип информации, возвращаемый функцией. Дополнительные сведения см. в разделе result. |
options
Объект options позволяет настроить некоторые аспекты того, как и когда Excel выполняет функцию. В таблице ниже приведены свойства объекта options .
parameters
Свойство parameters представляет собой массив объектов параметров. В таблице ниже приведены свойства каждого объекта.
Свойство | Тип данных | Обязательный | Описание |
---|---|---|---|
description | string | Нет | Описание параметра. Это отображается в Excel IntelliSense. |
dimensionality | string | Нет | Должно быть либо scalar (не массивное значение), matrix либо (двухмерный массив). |
name | string | Да | Имя параметра. Это имя отображается в Excel IntelliSense. |
type | string | Нет | Тип данных параметра. Может быть boolean , number или string``any , что позволяет использовать любой из предыдущих трех типов. Если это свойство не указано, тип данных по умолчанию . any |
optional | boolean | Нет | Если присвоено значение true , параметр не обязателен. |
repeating | boolean | Нет | Если true параметры заполняются из указанного массива. Обратите внимание, что все повторяющие параметры по определению считаются необязательными. |
result
Объект result определяет тип информации, возвращаемый функцией. В таблице ниже приведены свойства объекта result .
Свойство | Тип данных | Обязательный | Описание |
---|---|---|---|
dimensionality | string | Нет | Должно быть либо scalar (не массивное значение), matrix либо (двухмерный массив). |
type | string | Нет | Тип данных результата. Может быть boolean , number или string``any (что позволяет использовать любой из предыдущих трех типов). Если это свойство не указано, тип данных по умолчанию . any |
Сопоставление имен функций с метаданными JSON
В следующем JSON показаны метаданные JSON, связанные с предыдущим пользовательским кодом JavaScript функции.
Имейте в виду приведенные ниже рекомендации при создании пользовательских функций в файле JavaScript и указании соответствующих сведений в файле метаданных JSON.
Убедитесь, что в файле метаданных JSON значение каждого свойства id содержит только буквы, цифры и точки.
Убедитесь, что в файле метаданных JSON значение каждого свойства id уникально в пределах файла. То есть никакие два объекта функций в файле метаданных не должны иметь одинаковое значение id .
Не изменяйте значение свойства id в файле метаданных JSON после его сопоставления с соответствующим именем функции JavaScript. Вы можете изменить имя функции, которое отображается для конечных пользователей в Excel, путем обновления свойства name в файле метаданных JSON, но никогда не следует изменять значение свойства id после его установления.
В файле JavaScript укажите настраиваемую ассоциацию функций с использованием после CustomFunctions.associate каждой функции.
В следующем примере показаны метаданные JSON, соответствующие функциям, определенным в предыдущем примере кода JavaScript. Значения id свойства name и свойства находятся в верхнем шкафу, что является наилучшей практикой при описании пользовательских функций. Этот JSON необходимо добавить только в том случае, если вы готовите собственный JSON-файл вручную и не используете автогенерацию. Дополнительные сведения об автогенерации см. в метаданных Autogenerate JSON для пользовательских функций.
Дальнейшие действия
Узнайте о лучших методах для именования функции или узнайте, как локализовать функцию с помощью описанного ранее рукописного метода JSON.
Некоторые возможности могут присутствовать в одном продукте, но не в других из-за расписаний развертывания и возможностей для конкретного узла.
Поддерживаемые возможности
Загрузка локального JSON-файла из Power Query Desktop
Чтобы загрузить локальный JSON-файл, выполните приведенные далее действия.
Выберите параметр JSON в выборе "Получить данные ". Этот выбор запускает локальный браузер файлов, в котором можно выбрать JSON-файл.
Чтобы открыть файл, выберите Открыть.
Загрузка JSON-файла автоматически запустит Редактор Power Query. Power Query использует автоматическое обнаружение таблиц, чтобы легко преобразовать данные JSON в таблицу. В редакторе можно продолжить преобразование данных, если хотите, или просто закрыть и применить. Дополнительные сведения: автоматическое обнаружение таблиц из JSON-файлов
Загрузка локального JSON-файла из Power Query Online
Чтобы загрузить локальный JSON-файл, выполните приведенные далее действия.
На странице источников данных выберите JSON.
Введите путь к локальному JSON-файлу.
Выберите локальный шлюз данных из шлюза данных.
Если требуется проверка подлинности, введите учетные данные.
Выберите Далее.
Загрузка JSON-файла автоматически запустит Редактор Power Query. Power Query использует автоматическое обнаружение таблиц, чтобы легко преобразовать данные JSON в таблицу. В редакторе можно продолжить преобразование данных, если хотите, или просто сохранить и закрыть для загрузки данных. Дополнительные сведения: автоматическое обнаружение таблиц из JSON-файлов
Загрузка из Интернета
Чтобы загрузить JSON-файл из Интернета, выберите веб-соединитель, введите веб-адрес файла и следуйте инструкциям по учетным данным.
Автоматическое обнаружение таблиц из JSON-файлов
Импорт данных из JSON-файлов (или веб-API) может оказаться сложной задачей для конечных пользователей. Ниже приведен пример JSON-файла с несколькими уровнями вложенных данных.
При добавлении возможностей автоматического обнаружения таблиц с помощью соединителя JSON в Power Query автоматически применяются шаги преобразования, чтобы преобразовать данные JSON в таблицу. Ранее пользователям приходилось добавлять в плоскую структуру записи и списки вручную.
Устранение неполадок
Если вы пытаетесь загрузить json Lines-файл, следующий пример кода M преобразует все входные данные строк JSON в одну плоскую таблицу автоматически:
Затем необходимо использовать операцию развертывания для объединения строк.
If you have data stored in JSON format that you would like to import into Excel, it’s now very easy and doesn’t require any VBA to import data locally from the disk or from a web API. Watch the video or read the steps below!
Step 1: Open The Data in the Query Editor
When clicking “From JSON”, you will be presented with a file browser. Find the file on your disk and click Import. This should bring you into the query editor. If you are using a web API, you may choose instead to import the data directly from the web. To do this, instead of clicking “From JSON”, click “From Web” and enter the URL. This should fetch the file and bring you into the query editor.
Step 2: Craft the Query
Start by finding the list in the data. Click through any single records until you find a list. Click it to open the list of records.
Click through the record to see what’s inside Once you find the list, go ahead and click it to open it.
One you have found the list you are looking for, the list needs to be converted to a table for Excel to use. Click “To Table” to convert it.
Click “To Table” to convert the list to a table.
Once you have the list as a table, you need to specify which columns you want excel to make use of as well as have the opportunity to specify how any inner lists (array fields) may be dealt with. Click the icon with two arrows pointing away from one another to expand the table. You may want to rename the table before doing this. To do so, double click the name and type a new one.
Click the arrow icon to expand the table. Select the columns you want Excel to make use of and then click OK.
Step 3: Bring the Table Back Into Excel
Click Close and Load to bring the data back into Excel as a Table. A new sheet may be created.
Click Close & Load. The query editor will close and the data will appear as a table in Excel.
Enjoy the finished product!
Finished Product
Файлы нотации объектов JSON или Javascript широко используются для хранения различных типов данных. Помимо хранения, они также используются для передачи данных между двумя серверами, и вы часто обнаружите это при общении с веб-сервером через API или что-то еще. Файлы JSON имеют довольно простую структуру, т. е. содержат массивы и объекты. В типичном файле JSON вы найдете пары ключ-значение, которые хранят данные внутри различных объектов, хранящихся в массиве. Доступ к данным можно получить, указав индекс вместе с ключом значения.
Как оказалось, структура файла JSON, хотя и является базовой для программиста или разработчика, обычному пользователю может быть утомительной для чтения. К счастью, в таком сценарии вы можете легко преобразовать файл JSON в лист Microsoft Excel, по которому может легко перемещаться и обычный пользователь. Это связано с тем, что листы Excel обеспечивают лучшую читаемость, а если у вас есть большой файл JSON, преобразование его в лист Excel может быть подходящим способом, если вы хотите быстрее переварить данные внутри.
Чтобы преобразовать файл JSON в лист Excel, вы можете использовать функцию «Получить и преобразовать» Microsoft Excel. В более старых версиях Microsoft Excel это может называться Power Query. Как оказалось, помимо ручного преобразования файла JSON в Excel, вы также можете использовать онлайн-инструмент, который сделает эту работу за вас. С учетом сказанного давайте начнем и покажем вам, как довольно легко преобразовать JSON в Excel.
Конвертировать JSON в Excel
Преобразование файла JSON в рабочий лист Excel довольно просто, и процесс довольно прост. Вы начинаете с импорта нужного файла JSON в Microsoft Excel. После импорта файла различные функции приложения Excel используются для преобразования данных внутри файла JSON в таблицу. Как оказалось, Excel поставляется с множеством различных функций, которые позволяют вам манипулировать данными, которые у вас есть внутри файла Excel. Кроме того, вы можете защитить свои данные, защитив файлы Excel паролем.
После преобразования данных в формат таблицы вы можете выбрать, какие данные вас интересуют. Это позволит вам фильтровать данные внутри файла JSON и перечислять только те значения в столбцах, которые необходимы. Все это делается в окне редактора Power Query. Следуйте инструкциям, приведенным ниже, чтобы сделать все это:
- Прежде всего, откройте файл Excel на своем компьютере.
- Открыв файл Excel, щелкните параметр «Данные» на ленте меню.Опция данных в ленте меню
- После этого под лентой меню щелкните раскрывающееся меню «Получить данные», расположенное в самом левом углу.Параметр получения данных Excel
- В раскрывающемся меню выберите параметр «Из файла» > «Из JSON».Загрузка файла JSON в Excel
- В появившемся окне «Импорт данных» перейдите туда, где хранится файл JSON, и откройте его. Это позволит импортировать файл JSON на лист Microsoft Excel.
- После импорта файла JSON откроется окно редактора Power Query.Редактор Power Query
- В левом верхнем углу нажмите на предоставленную опцию «К таблице».К столу
- В следующем диалоговом окне оставьте параметры по умолчанию и нажмите кнопку ОК.Преобразование в формат таблицы
- После этого данные были преобразованы в таблицу. Тем не менее, вы по-прежнему не можете видеть данные.
- Чтобы увидеть данные, вам придется развернуть значки. Для этого щелкните предоставленный значок «Развернуть столбец» (с двойными стрелками, направленными друг от друга). Щелчок по значку расширения столбца
- В появившемся диалоговом окне выберите столбцы, которые вы хотите видеть, и снимите флажки с тех, которые вы хотите исключить. Сделав это, нажмите кнопку ОК.Выбор столбцов для отображения
- Наконец, вы должны увидеть данные, перечисленные в отдельных столбцах.
- Для вашего удобства вы можете перемещать различные столбцы на листе. Для этого щелкните правой кнопкой мыши заголовок столбца и в раскрывающемся меню выберите параметр «Переместить».Перемещение столбца
- Оттуда вы можете переместить столбец влево, вправо, в начало или в конец.
- Когда вы будете удовлетворены компоновкой листа Excel, щелкните предоставленную опцию «Закрыть и загрузить».Закрыть и загрузить вариант
- Когда вы это сделаете, данные будут импортированы на ваш лист Excel из редактора Power Query, и вы сможете просмотреть таблицу.Преобразование JSON в Excel
- Сделав это, вы успешно преобразовали файл JSON в рабочий лист Excel.
Конвертируйте файл JSON с помощью онлайн-инструмента
Наконец, как оказалось, в дополнение к руководству, о котором мы упоминали выше, вы также можете избавить себя от всех проблем с преобразованием файла JSON вручную и вместо этого выбрать онлайн-инструмент. Использование онлайн-инструмента может быть намного проще и быстрее, так как вам нужно всего лишь загрузить файл JSON, который вы хотите преобразовать в рабочий лист Excel. Есть несколько веб-сайтов, которые предлагают эту функцию, и вы можете легко найти их с помощью одного веб-поиска. Итак, если вам не хочется решать все проблемы с преобразованием файла JSON вручную, у вас всегда есть возможность воспользоваться онлайн-инструментом, который сделает работу за вас намного быстрее.
To process json data in VBA, there are very few choices. They are scant, really. And there is nothing built into VBA, like the Javascript function, JSON.parse(). So how do we process json data in VBA?
A makeshift tactic that many have used is to fire up the Script Control or the Webbrowser control or automate a silent instantiation of Internet Explorer with the aim of having Javascript executed in those environments evaluate the json string and through a convoluted process make key/value pairs of data available to VBA. This can work, but this method has drawbacks. Evaluation of a json string can be a security liability because Javascript’s eval() function has access to the hard drive. This liability can be mostly limited by utilizing Crockford’s JSON2.js, but now the process of having Javascript parse the json string is even more convoluted and in the end eval() is still used to create actual Javascript objects from the json, but only after a bunch of checks to ensure that the json will not do anything untoward.
There are indeed a few great VBA libraries, VBA-JSON for example, that can be loaded into your project that will parse json, allowing easy access to the data. But these are all huge code bases that can seem like overkill.
And of course, there is PowerQuery. But if you don’t know PQ, this can be quite intimidating.
The only other option is to write your own VBA routine to parse out the values from a json string or file. However, this gets tricky, especially for generic json that you are not looking at when you craft your VBA routine, and especially for deeply nested json.
But we can do it in VBA and it can be small, fast, and easy to use:
Of course, the above uses a few helper routines (included below), but I wanted to show how concise the logic is. There will be unusual edge cases where this breaks, but I’ve tested it on hundreds of json files, from simple to deeply nested and it has never failed yet.
It is extremely fast. It’s instantaneous on typical web API json response strings. It takes less than a second to process a megabyte-sized file.
It produces a dictionary where the dictionary keys are full textual paths to every data value in the source json string, making access a breeze.
Take the following tiny json string as an example:
We can list every path, key and data item like so:
The above uses a helper function, ListPaths(). It is included below, but the simple code here prints the following in the VBEditor Immediate Window:
And you can get at any item by using the full path:
Or you can filter items to an array:
You might be wondering how this is even possible. At first glance it does not appear that the three main functions listed at the top actually do much. But they do! They work because ParseObj() and ParseArr() are recursive… which means they can and do call themselves and each other, over and over, as they work through the entire json string.
Some real magic happens in a support function called, Tokenize(). It processes the source json string into a list of tokens and that list of tokens is what is actually parsed by the recursive functions above. After tokenizing the source json string, Tokenize() returns an array that has one token in each element. The above json looks like the following after being tokenized (every line in the following listing is an element in the array returned):
And then from that array, the recursive functions are able to compile the paths, keys and data values and add them to the dictionary.
So the Tokenize() function eliminates all nonessential white space, removes quotes from quoted items (but leaves all escaped characters in place) and isolates every token. This can easily be done in VBA code, but it is more than 100 times quicker to have a RegEx pattern match what we need. The following Tokenize() function uses VBScript’s regex engine to do the heavy lifting:
Granted, the regex matching pattern looks intimidating, but it is actually extremely efficient. When given a source json string (and it does not matter if the string is pretty-printed, normal, or minified), it instantly (less than a millisecond) matches the bits we want and transforms the entire string into the tokenized array listing shown above.
So here is the full listing of our VBA JSON Parser including support functions:
This code makes no attempt to verify if the source json is valid. It will raise a runtime error if the json is invalid. But virtually all json data I work with is valid from the get go.
This code also does not attempt to convert values into data types. So that no false assumptions are introduced, all values remain string values. The data types can be converted by your code easily if you need or want that.
In a similar vein, all escaped characters are untouched.
Also, the code does not attempt to build complex objects in VBA. Instead every single full path including the final key is stored AS TEXT in the dictionary as a dictionary key to the actual data item. This can simplify programmatic access to the data… a lot.
And finally, this code just parses JSON. There are no routines included here to create a json string from your data. This article is about reading json data from VBA.
Читайте также: