JSON do Excel: cztery podejścia i gdzie każde traci wierność

Najważniejsze: płaska tablica obiektów albo obiekt opakowujący tablicę konwertują się najszybciej przez narzędzie online; użyj Power Query, gdy dane odświeżają się co tydzień, pandas gdy jest wiele pól do oczyszczenia, a skryptu Node tylko gdy konwersja musi wejść do potoku — ale przed dotknięciem zagnieżdżonych danych lub niespójnych kluczy zdecyduj, co znaczy „jeden wiersz”.

Sama konwersja nie jest trudna; trudne jest odkrycie po fakcie, że liczby stały się tekstem, a kilka poziomów zagnieżdżenia zapadło się w jeden. Przeczytaj strukturę danych przed wyborem metody, a zaoszczędzisz sobie poprawki.

1. Najpierw ustal, jakiego rodzaju JSON masz

Otwórz plik i spójrz na poziom zewnętrzny:

Dwie pierwsze konwertują się bezpośrednio z niemal 100% skutecznością; przy trzech ostatnich najpierw zdecyduj, co znaczy „jeden wiersz”, inaczej wynikowy arkusz jest bezużyteczny.

2. Podejście 1: narzędzie online (1 minuta)

Otwórz JSON do tabeli, wklej dane → wybierz separator i nagłówek → wyeksportuj CSV → otwórz w Excelu. Pasuje do od kilkudziesięciu do kilku tysięcy wierszy dobrze ustrukturyzowanych danych.

Trzy rzeczy, na które musisz uważać:

• Otrzymujesz CSV, a nie xlsx. Gdy Excel otwiera CSV, zgaduje kodowanie z ustawień regionalnych systemu, więc chiński tekst może stać się krzaczkami. Użycie „Dane → Z tekstu/CSV” i jawne wybranie UTF-8 jest bardziej niezawodne.
• Identyfikatory zaczynające się od 0 tracą zera. "007" w CSV jest tekstem, ale Excel automatycznie konwertuje je do liczby 7. Ustaw tę kolumnę jako tekst podczas importu albo dodaj prefiks w JSON-ie.
• Długie liczby stają się notacją naukową. Numery dowodów i numery zamówień dłuższe niż 15 cyfr tracą precyzję; takie pola powinny już być łańcuchami w JSON-ie i po imporcie muszą pozostać w formacie tekstowym.

3. Podejście 2: wbudowany Power Query w Excelu (wielokrotnego użytku)

Gdy dane odświeżają się co tydzień, użyj Power Query wbudowanego w Excel: skonfiguruj raz, a potem klikaj tylko „Odśwież”:

1. „Dane” → „Pobierz dane” → „Z pliku” → „Z JSON”;
2. Po wybraniu pliku trafiasz do edytora Power Query, gdzie widzisz struktury List / Record;
3. Kliknij ikonę rozwijania po prawej stronie nagłówka kolumny, aby rozwijać zagnieżdżone pola poziom po poziomie;
4. Gdy nazwy kolumn i typy wyglądają dobrze, wybierz „Zamknij i załaduj”.

Zaletą jest to, że zagnieżdżenie można rozwijać do wielu kolumn warstwa po warstwie, a typy można przypisywać ręcznie; ceną jest to, że pierwsza konfiguracja wymaga zrozumienia różnicy między List a Record — List to tablica, Record to obiekt, a rozwijają się w różnych kierunkach.

4. Podejście 3: Python + pandas (wiele pól, wymaga czyszczenia)

复制import pandas as pd

# Object wrapping an array: first reach the list level
rows = pd.read_json('data.json')['data']

# Expand nested fields into multiple columns
frame = pd.json_normalize(rows, sep='_')

frame.to_excel('out.xlsx', index=False)

Kluczowym parametrem json_normalize jest sep, który określa styl nazewnictwa kolumn po rozwinięciu zagnieżdżonych pól; user_name jest łatwiejsze w użyciu w Excelu niż user.name. Tablice tablic (takie jak tags) wymagają dodatkowego etapu explode:

复制frame = frame.explode('tags')

Ten etap dzieli jeden wiersz na kilka, co jest jedynym sposobem zachowania relacji jeden-do-wielu — kosztem duplikowania pozostałych pól.

5. Podejście 4: skrypt Node (gdy musi wejść do potoku)

复制import { readFileSync, writeFileSync } from 'node:fs';

const rows = JSON.parse(readFileSync('data.json', 'utf8'));
const headers = [...new Set(rows.flatMap((row) => Object.keys(row)))];
const escapeCell = (value) => `"${String(value ?? '').replaceAll('"', '""')}"`;
const lines = [
  headers.join(','),
  ...rows.map((row) => headers.map((key) => escapeCell(row[key])).join(',')),
];

writeFileSync('out.csv', '\uFEFF' + lines.join('\n'), 'utf8');

Dwa szczegóły, których nie powinieneś pomijać: zapisz \uFEFF (BOM) na początku, aby Excel rozpoznał UTF-8, i escape'uj podwójne cudzysłowy w komórkach jako "", inaczej kolumny się przesuną.

6. Trzy rzeczy, które konwersja zawsze gubi

Ponadto null i „brak pola” wyglądają w arkuszu tak samo (oba to puste komórki), ale w domenie biznesowej znaczą co innego. Gdy musisz je rozróżnić, jawnie zamień null na łańcuch "null" w JSON-ie albo dodaj kolumnę znacznika _has_field.

7. Weryfikacja po fakcie: nie patrz tylko na pierwszy wiersz

Po konwersji sprawdź wyrywkowo co najmniej trzy rzeczy:

1. Liczba rekordów: liczba wierszy CSV powinna równać się długości tablicy + 1 (nagłówek); brakujący wiersz zwykle oznacza, że końcowa nowa linia została pochłonięta;
2. Kolumna o najwyższym współczynniku null: wiele pustych komórek wskazuje na niespójne nazwy kluczy — wróć i sprawdź, czy oryginalny JSON używa dwóch pisowni (userId i user_id);
3. Najdłuższy wiersz: zagnieżdżone tablice zwykle tam trafiają; potwierdź, że nic nie zostało obcięte.

Gdy potrzebujesz operacji odwrotnej (Excel/CSV z powrotem do JSON), użyj CSV do JSON i pamiętaj, że nagłówek jest traktowany jako nazwy kluczy — zmiana nagłówka to zmiana nazw pól.

FAQ

Jak wybrać między narzędziem online, Power Query i pandas?

Użyj narzędzia online dla od kilkudziesięciu do kilku tysięcy wierszy dobrze ustrukturyzowanych danych, które konwertujesz raz. Użyj Power Query, gdy dane odświeżają się co tydzień, ponieważ konfiguruje się je raz i odświeża jednym kliknięciem. Użyj pandas z json_normalize, gdy jest wiele pól do oczyszczenia, a skryptu Node, gdy konwersja musi wejść do potoku.

Dlaczego chiński tekst zamienia się w krzaczki w CSV?

Eksport to CSV, a nie xlsx, a Excel zgaduje kodowanie z ustawień regionalnych systemu, więc UTF-8 bez BOM pokazuje krzaczki. Zapisz BOM \uFEFF na początku pliku albo otwórz go przez „Dane → Z tekstu/CSV” i jawnie wybierz UTF-8.

Czy zagnieżdżone obiekty spłaszczać, czy dzielić na dodatkowe wiersze?

Spłaszczaj je poziom po poziomie, zamiast wklejać jako łańcuchy: Power Query rozwija zagnieżdżone pola do kolumn, a json_normalize z sep robi to samo w pandas. Tablice tablic wymagają dodatkowego etapu explode, który dzieli jeden wiersz na kilka. Przekazanie głębokiego obiektu do String(obj) daje tylko [object Object].