Преобразование JSON в Excel: 5 методов для различных рабочих процессов

JSON — один из самых широко используемых форматов для хранения и обмена структурированными данными. API, файлы конфигурации и многие современные приложения используют JSON для представления наборов данных.

Однако JSON не всегда удобен для анализа, создания отчетов или обмена с нетехническими пользователями. Во многих случаях преобразование данных JSON в электронную таблицу Excel упрощает просмотр, фильтрацию, сортировку и представление информации.

В этом руководстве показаны пять практических методов преобразования JSON в Excel, от решений без кода до программных подходов.

Обзор рассматриваемых методов:

• Метод 1 — Excel Power Query (без кода)
• Метод 2 — Онлайн-конвертер JSON (для разовых задач)
• Метод 3 — Python с pandas (удобно для автоматизации)
• Метод 4 — Python с Spire.XLS (для форматированных отчетов Excel)
• Метод 5 — Node.js с SheetJS (рабочий процесс на JavaScript)

Подготовьте свою структуру JSON

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

Корневой массив и корневой объект

Многие инструменты для преобразования JSON в Excel ожидают, что данные будут представлять собой корневой массив объектов, например:

[
  {"id":1,"name":"Алиса"},
  {"id":2,"name":"Боб"}
]

Эта структура естественным образом сопоставляется с таблицей Excel:

Каждый объект становится строкой, а ключи — заголовками столбцов.

Однако многие реальные API и наборы данных заключают массив в корневой объект:

{
  "employees":[
    {"id":1,"name":"Алиса"},
    {"id":2,"name":"Боб"}
  ]
}

В этом случае фактический табличный набор данных хранится в свойстве "employees".

Почему некоторые инструменты не работают с корневыми объектами

Некоторые конвертеры не могут автоматически определить, какое свойство содержит табличный набор данных. Они ожидают, что JSON-файл будет начинаться непосредственно с массива.

Когда данные заключены в корневой объект, эти инструменты могут неверно проанализировать файл или выдать пустые результаты.

Поэтому вам может потребоваться извлечь соответствующий массив вручную перед выполнением преобразования. Например, вы можете реструктурировать JSON так, чтобы массив стал корневым элементом для онлайн-конвертера, или получить к нему доступ непосредственно в коде.

Доступ к массиву в Python

Например, если набор данных хранится в "employees", вы можете загрузить и извлечь его следующим образом:

data = json.load(f)["employees"]

После этого шага data становится списком объектов, которые можно легко записать в Excel в виде строк и столбцов.

Метод 1 — Excel Power Query (без кода)

Если у вас уже установлен Microsoft Excel, вы можете импортировать данные JSON напрямую, не написав ни строчки кода. Функция Power Query в Excel позволяет пользователям загружать файлы JSON и автоматически преобразовывать их в табличный формат. Этот подход идеален для аналитиков или бизнес-пользователей, которые хотят быстро просматривать данные JSON в среде электронных таблиц.

Шаги

1. Запустите Microsoft Excel на своем компьютере.
2. Перейдите на вкладку Данные, расположенную в верхнем меню Excel.
3. На вкладке "Данные" перейдите к Получить данные, затем выберите Из файла и выберите Из JSON в раскрывающемся меню.



4. Когда появится запрос, найдите и выберите свой JSON-файл, затем нажмите Импорт.
5. Запустится редактор Power Query.



6. Если файл открывается как список записей:

   • Нажмите В таблицу, чтобы преобразовать список в табличный формат.

   

   • Затем щелкните значок Развернуть (⇄) в заголовке столбца, чтобы отобразить имена столбцов. Если в столбцах по-прежнему отображается "Список" или "Запись," щелкните значок развертывания еще раз, чтобы продолжить сведение.

   

   • Появится диалоговое окно, в котором вы сможете выбрать, какие поля вы хотите включить в таблицу. Выберите необходимые поля, снимите флажок "Использовать исходное имя столбца как префикс" для более чистых заголовков, а затем нажмите ОК.

   

7. Нажмите Закрыть и загрузить, чтобы импортировать структурированные данные в лист Excel.



8. Сохраните файл Excel в формате .xlsx, чтобы ваши данные сохранились для будущего использования.

Когда использовать этот метод

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

Метод 2 — Онлайн-конвертер JSON (для разовых задач)

Онлайн-конвертеры JSON предоставляют один из самых быстрых способов превратить файлы JSON в электронные таблицы Excel. Эти инструменты обычно требуют только загрузки файла и автоматически создают загружаемый файл Excel. Платформы, подобные тем, что предлагает jsontoexcel.net, могут выполнить преобразование за считанные секунды без установки какого-либо программного обеспечения.

Шаги

1. Откройте веб-сайт конвертера JSON в Excel.
2. Скопируйте и вставьте данные JSON непосредственно в текстовый редактор или загрузите файл с вашего компьютера.



3. Нажмите кнопку Конвертировать в Excel, чтобы начать процесс преобразования.
4. Загрузите сгенерированный файл Excel.

Важное замечание

Большинство онлайн-конвертеров ожидают корневой массив объектов; в противном случае они могут молча завершиться неудачей или выдать неожиданные результаты. Этот формат наиболее надежно преобразуется в Excel.

Когда использовать этот метод

Лучше всего подходит для быстрых, одноразовых преобразований или тестирования образцов наборов данных.

Метод 3 — Python с pandas (удобно для автоматизации)

Для разработчиков и инженеров данных Python предоставляет мощный способ автоматизации преобразования JSON в Excel. Популярная библиотека для анализа данных pandas может легко загружать файлы JSON, преобразовывать их в структурированный DataFrame и экспортировать результаты в Excel. Этот метод особенно полезен, когда преобразование необходимо интегрировать в скрипты, рабочие процессы ETL или автоматизированные конвейеры отчетности.

Установка зависимостей

pip install pandas openpyxl

Преобразование JSON в Excel

import pandas as pd
import json

with open("employees.json") as f:
    data = json.load(f)["employees"]

df = pd.json_normalize(data)
df.to_excel("output.xlsx", index=False)

Вывод:

Когда использовать этот метод

Идеально подходит для автоматизированной обработки данных, аналитических рабочих процессов и больших наборов данных.

Метод 4 — Python с Spire.XLS (для форматированных отчетов Excel)

Если ваша цель — создавать хорошо отформатированные отчеты Excel, Python может работать с Spire.XLS для программного создания электронных таблиц. В отличие от простых библиотек экспорта данных, Spire.XLS предоставляет обширный контроль над форматированием Excel, включая шрифты, цвета, выравнивание и размеры столбцов. Это делает его подходящим для создания профессиональных электронных таблиц, готовых для обмена с заинтересованными сторонами.

Установите библиотеку

pip install spire.xls

Пример кода

Следующий скрипт считывает данные о сотрудниках в формате JSON, динамически генерирует заголовки столбцов, записывает строки в Excel и применяет форматирование, такое как стиль заголовка, чередующиеся цвета строк и автоматически подогнанные столбцы.

import json
from spire.xls import *

# Загрузка данных JSON
with open('C:/Users/Administrator/Desktop/employees.json') as f:
    data = json.load(f)["employees"]

if not data:
    raise ValueError("Данные JSON пусты!")

# Создание книги и листа
workbook = Workbook()
sheet = workbook.Worksheets[0]

# Динамическое извлечение заголовков
headers = list(data[0].keys())
num_cols = len(headers)

# Запись заголовков
for col, header in enumerate(headers, start=1):
    sheet.Range[1, col].Value = header

# Запись строк
for row_index, item in enumerate(data, start=2):
    for col_index, key in enumerate(headers, start=1):
        value = item.get(key, "")
        sheet.Range[row_index, col_index].Value = str(value) if value is not None else ""

# Форматирование заголовка
header_row = sheet.Range[1, 1, 1, num_cols]
header_row.RowHeight = 30
header_style = header_row.Style
header_style.Font.FontName = "Times New Roman"
header_style.Font.Size = 16
header_style.Font.Color = Color.get_White()
header_style.Color = Color.FromArgb(255, 128, 128, 128)
header_style.HorizontalAlignment = HorizontalAlignType.Center
header_style.VerticalAlignment = VerticalAlignType.Center

# Форматирование данных
locatedRange = sheet.AllocatedRange
for rowNum in range(2, locatedRange.RowCount + 1):
    data_row = sheet.Range[rowNum, 1, rowNum, num_cols]
    data_row.RowHeight = 20
    row_style = data_row.Style
    row_style.Font.FontName = "Times New Roman"
    row_style.Font.Size = 13
    row_style.HorizontalAlignment = HorizontalAlignType.Center
    row_style.VerticalAlignment = VerticalAlignType.Center
    row_style.Color = Color.get_White() if rowNum % 2 == 0 else Color.FromArgb(255, 245, 245, 245)

# Автоподбор ширины столбцов
extra_width = 3
for col in range(1, num_cols + 1):
    sheet.AutoFitColumn(col)
    current_width = sheet.Columns[col - 1].ColumnWidth
    sheet.Columns[col - 1].ColumnWidth = current_width + extra_width

workbook.SaveToFile("JsonToExcel.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Вывод:

Когда использовать этот метод

Лучше всего подходит для автоматического создания отчетов, приложений, требующих точного форматирования Excel, и рабочих процессов, где файлы Excel являются конечным результатом.

Вам также может понравиться: Преобразование JSON в/из Excel на Python — полное руководство с примерами

Метод 5 — Node.js с SheetJS (рабочий процесс на JavaScript)

Разработчики JavaScript могут преобразовывать JSON в Excel с помощью таких библиотек, как SheetJS. Эта библиотека предоставляет утилиты для преобразования объектов JSON в листы электронных таблиц и их записи в файлы .xlsx. Поскольку она хорошо работает в средах Node.js, она обычно используется в бэкэнд-сервисах и скриптах обработки данных.

Установка

npm install xlsx

Пример

const XLSX = require("xlsx");
const fs = require("fs");

const data = JSON.parse(fs.readFileSync("employees.json")).employees;

const worksheet = XLSX.utils.json_to_sheet(data);
const workbook = XLSX.utils.book_new();

XLSX.utils.book_append_sheet(workbook, worksheet, "Employees");
XLSX.writeFile(workbook, "output.xlsx");

Вывод:

Когда использовать этот метод

Лучше всего подходит для веб-приложений, бэкендов Node.js и рабочих процессов автоматизации на основе JavaScript.

Краткое сравнение 5 методов

Каждый метод преобразования JSON в Excel предназначен для разных типов пользователей и рабочих процессов. Некоторые подходы ориентированы на простоту и скорость, в то время как другие обеспечивают автоматизацию и контроль форматирования для более сложных сценариев. В таблице ниже приведены основные различия, которые помогут вам выбрать наиболее подходящий вариант.

Лучшие практики по преобразованию JSON в Excel

Преобразование JSON в Excel может показаться простым, но реальные наборы данных часто содержат вложенные структуры, несогласованные ключи или большие объемы данных. Соблюдение нескольких лучших практик поможет обеспечить надежные и читаемые результаты.

1. Сведение вложенных структур JSON

Многие файлы JSON содержат вложенные объекты или массивы. Хотя JSON поддерживает иерархические данные, Excel лучше всего работает с плоскими, табличными структурами.

Например, такой JSON:

{
  "id": 1,
  "name": "Алиса",
  "address": {
    "city": "Сан-Франциско",
    "zip": "94105"
  }
}

может потребоваться свести к:

id	имя	address.city	address.zip
1	Алиса	Сан-Франциско	94105

Библиотеки, такие как pandas, предоставляют инструменты, подобные json_normalize(), для автоматического сведения вложенных данных. При работе с более сложным JSON предварительная обработка структуры перед экспортом в Excel часто дает более чистые результаты.

2. Учитывайте размер файла и производительность

   Большие наборы данных JSON могут содержать тысячи или миллионы записей, что может вызвать проблемы с производительностью при прямом преобразовании в Excel. Несколько советов:

   • Используйте программные решения (Python или Node.js) для больших файлов.
   • При необходимости обрабатывайте данные пакетами.
   • Избегайте загрузки чрезвычайно больших наборов данных непосредственно в Excel.

   Сам Excel имеет ограничения (например, около 1 048 576 строк на лист), поэтому очень большие наборы данных может потребоваться разделить на несколько листов.

3. Используйте несколько листов для сложных данных

Некоторые API возвращают JSON с несколькими связанными массивами, например:

{
  "customers": [...],
  "orders": [...],
  "products": [...]
}

Вместо того, чтобы помещать все в один лист, рассмотрите возможность экспорта каждого набора данных в отдельные листы Excel. Это сохраняет логическую структуру исходных данных и упрощает анализ.

4. Применяйте форматирование для лучшей читаемости

   Если файл Excel будет передаваться коллегам или заинтересованным сторонам, форматирование может значительно улучшить читаемость.

   Полезные практики форматирования включают:

   • Выделение строк заголовков жирным шрифтом
   • Скорректированная ширина столбцов
   • Чередующиеся цвета строк
   • Единообразные шрифты и выравнивание

   Библиотеки, подобные Spire.XLS, позволяют программно управлять этими элементами, что дает возможность автоматически создавать готовые к презентации отчеты.

Заключение

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

Для простых, одноразовых преобразований часто достаточно таких инструментов, как Excel или онлайн-конвертеры. Однако разработчики, работающие с конвейерами данных или автоматизированными отчетами, получат выгоду от программных решений, таких как pandas, Spire.XLS или SheetJS, которые предлагают большую гибкость и контроль над выводом.

Часто задаваемые вопросы

В1. Почему некоторые онлайн-конвертеры отклоняют действительный JSON?

Многие онлайн-конвертеры ожидают корневой массив объектов в качестве набора данных. Если JSON-файл начинается с корневого объекта, содержащего вложенные массивы, инструмент может не знать, какое свойство представляет таблицу. Извлечение соответствующего массива перед загрузкой файла обычно решает эту проблему.

В2. В чем разница между массивом JSON и объектом JSON?

Массив JSON — это упорядоченный список значений, заключенный в квадратные скобки [], а объект JSON — это коллекция пар ключ-значение, заключенная в фигурные скобки {}.

В3. Как я могу преобразовать вложенный JSON в Excel?

Вложенный JSON часто требует сведения перед экспортом в Excel. Инструменты, такие как pandas, предоставляют функции, подобные json_normalize(), которые автоматически разворачивают вложенные поля в столбцы. В качестве альтернативы вы можете вручную извлечь вложенные объекты или массивы перед записью данных.

В4. Может ли Excel открывать файлы JSON напрямую?

Да. В Excel есть функция под названием Power Query , которая может импортировать файлы JSON и преобразовывать их в таблицы. Однако процесс может потребовать ручного развертывания вложенных структур для получения чистого табличного набора данных.

Также читайте

• JSON в PDF на Python: сведение и экспорт в виде таблиц
• Преобразование JSON в CSV: бесплатные онлайн-инструменты, Excel и скрипты Python
• Как скрыть линии сетки в Excel (просмотр, печать и экспорт в PDF)
• Как разделить листы Excel на несколько файлов (3 способа)