Как открыть json в excel
Обновлено: 07.07.2024
Как описано в статье обзор пользовательских функций, проект пользовательских функций должен включать как файл метаданных 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, если вы <Resources> не используете yo office генератор.
Авторство метаданных и подключение к манифесту
Создайте файл JSON в проекте и укажи все сведения о его функциях, таких как параметры функции. См. следующий пример метаданных и ссылку на метаданные для полного списка свойств функций.
Убедитесь, что файл манифеста XML ссылается на файл JSON в разделе, аналогично <Resources> следующему примеру.
Пример метаданных JSON
В примере кода ниже показано содержимое JSON-файла метаданных для надстройки, определяющей настраиваемые функции. В следующих за этим примером разделах приводятся подробные сведения об отдельных свойствах, представленных в этом примере JSON.
Полный пример JSON-файла доступен в истории фиксации OfficeDev/Excel-Custom-Functions GitHub репозитория. Поскольку проект был скорректирован для автоматического создания JSON, полный пример рукописного JSON доступен только в предыдущих версиях проекта.
Ссылка на метаданные
allowCustomDataForDataTypeAny (предварительная версия)
Свойство allowCustomDataForDataTypeAny в настоящее время доступно в общедоступных предварительных просмотрах и совместимо только с Office на Windows. Функции предварительного просмотра могут изменяться и не предназначены для использования в производственной среде. Рекомендуется использовать их только в тестовой среде и среде разработки. Не используйте функции предварительного просмотра в производственной среде или в критически важных для бизнеса документах.
Чтобы проверить это свойство в Office Windows, необходимо иметь номер сборки Excel больше или равен 16.0.14623.20002. Чтобы использовать эту функцию, необходимо присоединиться к программе Office инсайдерской, а затем выбрать уровень инсайдерской бета-версии канала. Подробнее см. в статью Join the Office Insider Program.
Свойство 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.
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!
Howdee! It’s becoming increasingly more common for data to be generated in a JSON format as opposed to XML. XML was widely used for years, but recently JSON has started to become the preferred method of data exchange. Many REST APIs have the option to return both but the majority that I interact with default to returning JSON formatted data. Therefore, as excel ninjas, it’s important to understand how to import JSON to Excel for analysis. Before we begin on that route, let’s take a quick moment to talk about what JSON is for those that aren’t familiar with it.
Curious how to do this in VSTO? Click Here!
What is JSON?
JSON stands for JavaScript Object Notation and is a lightweight data-interchange format. In layman’s terms, it is a string of text that represents a universal data structure. It is easy for humans to read (when formatted properly) and, because of the universal structure, it is very easy and fast for machines to parse and generate. It’s made simple because JSON, at it’s most basic, is only two data types. It is made up of objects, or arrays, or a combination of both. Objects are made up of key/value pairs (often called a dictionary) and arrays are simply collections of values or objects separated by a comma.
JSON Object
JSON Array
A JSON array, in its simplest form, is a list of values separated by a comma that begins with [ and ends with ]. In the below image, the JSON array is a list of string data points. We can see that they are colors but there is no key in this example explicitly telling that this string represents a color. Therefore, JSON is most commonly seen as a combination of arrays and objects.
JSON Objects & Arrays Together
Most of the time, JSON is returned as an array of objects. If you recall, an object always begins and ends with curly brackets. Therefore, the array of objects would have several objects enclosed in curly brackets that are separated by commas and all objects are enclosed in normal brackets (the array). That looks like this image:
In this example, each object represents a color and its corresponding hex code. In Excel, this would likely be two columns of data. One column for color, and the other for its hex code value. Now that you’re more familiar with JSON data structure, let’s learn how to import JSON to Excel!
Setup JSON to Excel
There are several libraries out there to help you import JSON to Excel. The most popular of these is VBA-JSON which is available here on GitHub. This library will do most of the hard work of parsing the JSON string to a dictionary for you and then you can write the values to an excel worksheet. When you download the latest release, you’ll need to import the “JsonConverter.bas” file to your VBA project. This GIF shows how that is accomplished. You will also need to add a reference to the “Microsoft Scripting Runtime” by clicking tools from the ribbon, selecting references and checking the box for Microsoft Scripting Runtime as shown in the screen grab.
Once you have completed both steps, insert a new module into your project and title it whatever you like. To get started, we need to dimension some variables to use later.
The ws variable will represent a worksheet in excel. In this example, we will use this worksheet to both read in our JSON string from and write the parsed JSON to Excel. To assign a worksheet to this variable, set it like this:
The jsonText variable will represent our string of valid JSON data. For this example, I’ve pasted that string of colors and hex codes we looked at earlier into cell A1 on the JSON to Excel Example tab. To assign that string to a variable, type this code:
Lastly, to put the JSON string into the jsonObject, we will use one of the methods contained in the JsonConverter file you imported to begin this example. Since the builder of this library made all of the subroutines public, it is callable from the module this code is in. That call looks like this:
This call instructs the JsonConverter module to parse the JSON string you’ve passed it using the jsonText variable to a collection of dictionaries that we can now access. Now that we’ve finished our setup, we can start learning to import JSON to Excel in the next section.
Import JSON to Excel
The code to import JSON to Excel might sound daunting, but it is much easier than it sounds. At a high level, all you have to do is loop through the jsonObject you’ve created, and write each dictionary value to the sheet. The wonderful thing about dictionaries, is this is easy to do with a for each loop. I’ve added a couple of variables and a for each loop to our code and the result is this:
I set a counter variable, i, that I can use to tell the loop which row to write the data to. Next, create my column headers for the data. Then, for each “item” in the jsonObject write that dictionaries values to the cell row I indicate and increment my counter variable. That’s it! The results of our import JSON to Excel code looks like this:
As you can see, we’ve turned that odd-looking string of text in cell A1 to an easy to understand table of data with just a few lines of code!
Understanding JSON to Excel (Advanced)
Now, I do want to point out that understanding the structure of the JSON you’ll be parsing is very important. Within the for each loop, the code can access the value of each object by referencing its key. This goes back to understanding the key/value pair relationship of JSON and the dictionary structure to which it is parsed. The reason I stress this so much is because, when you’re pulling back more complex looking JSON, you may need to have multiple levels of dictionary references to access the value you want. There may also be times when you have to nest for loops when you have an array of objects with arrays of objects nested inside each object.
For example, let’s look at an example where the JSON is structured as a single object with key “data” and the value is an array of data about employee’s and their organizations:
As you can see, we have an object with an array nested inside. Some objects in the array contains other nested objects as well. This multi-tiered data structure is very common when pulling data from a REST service and was very confusing to me when I first began trying to import JSON to Excel. Let’s examine what the code looks like for this scenario before we dive any deeper.
The code overall is the exact same, just referencing a different tab and added more fields to pull out. The change I want to point out is to the for loop itself. The beginning of the for loop now starts by referencing the overall object that contains the array. This is referred to as the “root” of the JSON and you’ll see it if you’re querying a REST API for JSON data. This moves the for each loop inside the “data” level of the JSON and will now loop through all objects in the highest-level array. Had I left this out, the loop would only occur once as the JSON is all contained within a single object at its top level.
The second piece I want to point out is the lines for address and company. These are both objects contained within the parent object. So, in order for me to reach to value I want, I have to reference both keys to get the specific value I need. Lastly, I want to call out that you do not need to write every value from the returned JSON to the sheet. I have skipped over several fields to get the end result I desired and it does not affect my code results, which coincidently look like this:
I hope this brief explanation of how to import JSON to Excel was useful for you! Please let me know if it was helpful or if you have any questions in the comments below. Also, if you want to download the file with the example JSON and VBA code, you can do so here if you’re a free subscriber to the site. It’s free, easy, and only takes a moment to sign up.
Читайте также: