JSON Toolkit

JSON kepada Excel: Empat Pendekatan dan Di Mana Setiap Satu Kehilangan Ketepatan

Inti pati: Struktur bersarang, medan tidak konsisten dan tatasusunan bertatasusunan ialah tempat penukaran JSON kepada hamparan rosak. Empat pendekatan disusun mengikut masa untuk hasil, masing-masing dengan pertukarannya.

Pemula~11 min bacaanDiterbitkan 2026-10-30Dikemas kini 2026-10-30
Kulit untuk panduan praktik JSON kepada Excel

Kesimpulan utama: tatasusunan objek rata atau objek yang membaluti tatasusunan ditukar paling pantas melalui alat dalam talian; gunakan Power Query apabila data disegarkan setiap minggu, pandas apabila banyak medan perlu dibersihkan, dan skrip Node hanya apabila penukaran mesti menyertai saluran — tetapi tentukan maksud "satu baris" sebelum menyentuh data bersarang atau kunci tidak konsisten.

Penukaran itu sendiri tidak sukar; yang sukar ialah menemui selepas itu bahawa nombor menjadi teks dan beberapa tahap sarang runtuh menjadi satu. Baca struktur data sebelum memilih kaedah dan anda menyelamatkan diri daripada kerja semula.

1. Pertama, kenal pasti jenis JSON yang anda ada

Buka fail dan lihat peringkat paling luar:

Struktur Bentuk Kesukaran penukaran
Tatasusunan objek rata [{"a":1,"b":2},{...}] Paling mudah — satu baris setiap rekod
Objek membaluti tatasusunan {"data":[{...}],"total":100} Mudah — ambil peringkat data
Objek bersarang {"user":{"name":"x"}} Sederhana — tentukan sama ada untuk meratakan atau menggabungkan menjadi rentetan
Tatasusunan bertatasusunan {"tags":["a","b"]} Menyusahkan — tidak boleh dimuatkan dalam satu baris, perlu pemecahan jadual
Kunci tidak konsisten Setiap rekod mempunyai medan berbeza Menyusahkan — lajur berakhir kebanyakannya kosong

Dua yang pertama ditukar terus dengan kadar kejayaan hampir 100%; bagi tiga yang terakhir, tentukan maksud "satu baris" dahulu, jika tidak hamparan yang terhasil tidak boleh digunakan.

2. Pendekatan 1: alat dalam talian (1 minit)

Buka JSON kepada jadual, tampal data → pilih pemisah dan pengepala → eksport CSV → buka dalam Excel. Sesuai untuk data berstruktur baik daripada beberapa dozen hingga beberapa ribu baris.

Tiga perkara yang mesti anda berhati-hati:

  • Anda mendapat CSV, bukan xlsx. Apabila Excel membuka CSV ia meneka pengekodan daripada lokasi sistem, jadi teks Cina mungkin menjadi mojibake. Menggunakan "Data → From Text/CSV" dan memilih UTF-8 secara eksplisit lebih boleh dipercayai.
  • Pengecam yang bermula dengan 0 kehilangan sifarnya. "007" ialah teks dalam CSV, tetapi Excel menukarnya kepada nombor 7 secara automatik. Tetapkan lajur itu sebagai teks semasa import, atau tambah awalan padanya dalam JSON.
  • Nombor panjang menjadi notasi saintifik. ID negara dan nombor pesanan yang lebih panjang daripada 15 digit kehilangan ketepatan; medan seperti itu sepatutnya sudah menjadi rentetan dalam JSON, dan mesti kekal dalam format teks selepas import.

3. Pendekatan 2: Power Query terbina dalam Excel (boleh diguna semula)

Apabila data anda disegarkan setiap minggu, gunakan Power Query yang terbina dalam Excel: konfigurasikannya sekali, kemudian hanya klik "Refresh":

  1. "Data" → "Get Data" → "From File" → "From JSON";
  2. Selepas memilih fail anda masuk ke editor Power Query, di mana anda melihat struktur List / Record;
  3. Klik ikon kembangkan di sebelah kanan pengepala lajur untuk mengembangkan medan bersarang tahap demi tahap;
  4. Sebaik sahaja nama dan jenis lajur kelihatan betul, "Close & Load".

Kelebihannya ialah sarang boleh dikembangkan menjadi berbilang lajur lapisan demi lapisan, dan jenis boleh ditetapkan secara manual; harganya ialah persediaan pertama memerlukan pemahaman perbezaan antara List dan Record — List ialah tatasusunan, Record ialah objek, dan ia mengembang dalam arah yang berbeza.

4. Pendekatan 3: Python + pandas (banyak medan, perlu pembersihan)

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)

Parameter utama json_normalize ialah sep, yang menentukan gaya penamaan lajur selepas mengembangkan medan bersarang; user_name lebih mudah digunakan dalam Excel daripada user.name. Tatasusunan bertatasusunan (seperti tags) memerlukan langkah explode tambahan:

frame = frame.explode('tags')

Langkah ini memecahkan satu baris menjadi beberapa, satu-satunya cara untuk mengekalkan hubungan satu-ke-banyak — dengan kos menggandakan medan yang tinggal.

5. Pendekatan 4: skrip Node (apabila ia mesti menyertai saluran)

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');

Dua perincian yang tidak boleh dilangkau: tulis \uFEFF (BOM) pada permulaan supaya Excel mengenali UTF-8, dan escape tanda petikan berganda di dalam sel sebagai "", jika tidak lajur akan beralih.

6. Tiga perkara yang sentiasa hilang dalam penukaran

Item yang hilang Gejala Penyelesaian
Maklumat jenis Nombor dan boolean menjadi teks atau TRUE/FALSE Tetapkan jenis lajur secara manual selepas import, atau nyatakannya dalam Power Query
Tahap sarang Medan dalam runtuh menjadi [object Object] Gunakan json_normalize atau kembangkan tahap demi tahap — jangan sekali-kali String(obj)
Hubungan satu-ke-banyak Tatasusunan dimasukkan ke dalam satu sel Pecahkan baris dengan explode, atau bina jadual berkaitan yang berasingan

Selain itu, null dan "medan tiada" kelihatan sama dalam hamparan (kedua-duanya sel kosong) tetapi bermakna perkara berbeza dalam domain perniagaan. Apabila anda perlu membezakannya, tukar null secara eksplisit kepada rentetan "null" dalam JSON, atau tambah lajur penanda _has_field.

7. Mengesahkan selepas itu: jangan hanya lihat baris pertama

Selepas penukaran, semak sekurang-kurangnya tiga perkara:

  1. Bilangan rekod: bilangan baris CSV harus sama dengan panjang tatasusunan + 1 (pengepala); baris yang hilang biasanya bermaksud baris baharu di hujung telah ditelan;
  2. Lajur dengan kadar null tertinggi: banyak sel kosong menunjukkan nama kunci tidak konsisten — kembali dan semak sama ada JSON asal menggunakan dua ejaan (userId dan user_id);
  3. Baris terpanjang: tatasusunan bersarang biasanya mendarat di sana; sahkan tiada apa yang dipotong.

Apabila anda memerlukan operasi sebaliknya (Excel/CSV kembali kepada JSON), gunakan CSV kepada JSON, dan ingat bahawa pengepala dilayan sebagai nama kunci — menukar pengepala bermaksud menukar nama medan.

Soalan Lazim

Bagaimana saya memilih antara alat dalam talian, Power Query, dan pandas?

Gunakan alat dalam talian untuk data berstruktur baik daripada beberapa dozen hingga beberapa ribu baris yang anda tukar sekali. Gunakan Power Query apabila data disegarkan setiap minggu, kerana ia dikonfigurasikan sekali dan disegarkan dengan satu klik. Gunakan pandas dengan json_normalize apabila banyak medan perlu dibersihkan, dan skrip Node apabila penukaran mesti menyertai saluran.

Mengapa teks Cina bertukar menjadi mojibake dalam CSV?

Eksportnya ialah CSV, bukan xlsx, dan Excel meneka pengekodan daripada lokasi sistem, jadi UTF-8 tanpa BOM menunjukkan mojibake. Tulis BOM \uFEFF pada permulaan fail, atau bukanya melalui "Data → From Text/CSV" dan pilih UTF-8 secara eksplisit.

Patutkah objek bersarang diratakan atau dipecahkan menjadi baris tambahan?

Ratakannya tahap demi tahap dan jangan tampalnya sebagai rentetan: Power Query mengembangkan medan bersarang menjadi lajur, dan json_normalize dengan sep melakukan perkara yang sama dalam pandas. Tatasusunan bertatasusunan memerlukan langkah explode tambahan yang memecahkan satu baris menjadi beberapa. Memberikan objek dalam kepada String(obj) hanya menghasilkan [object Object].