当前位置：首页 > news >正文

Understanding JSON Formats - What JSON to Excel Supports - 教程

news 2026/5/12 3:07:52

Welcome to part 5 of our JSON to Excel series! So far, we’ve covered the various tools available: Web App, Excel Add-in, and WPS Add-in. Today, we’re diving into the technical side by exploring exactly what JSON formats JSON to Excel supports and how to ensure your data is ready for conversion.

The JSON Format Requirements

JSON to Excel is designed to handle a wide variety of JSON structures, but it does have specific requirements. Understanding these requirements will help you prepare your data and avoid conversion errors.

Core Requirements

At its core, JSON to Excel requires:

Valid JSON: The input must be syntactically correct JSON
Array or Object: The top-level structure must be either an array of objects or a single object
Object Properties: Each object should contain key-value pairs
Data Types: Values can be strings, numbers, booleans, null, arrays, or nested objects

Supported JSON Structures

Structure 1: Array of Objects (Most Common)

This is the most common and recommended format for JSON to Excel:

[
{
"name": "John Doe",
"age": 30,
"email": "john@example.com"
},
{
"name": "Jane Smith",
"age": 25,
"email": "jane@example.com"
}
]

Characteristics:

Wrapped in square brackets []
Contains 1 to 1000 objects
Each object represents one row in Excel
Each property becomes a column in Excel

Requirements:

Minimum: 1 object
Maximum: 1000 objects
Each object: 1 to 100 properties
Objects cannot be empty

Structure 2: Single Object

JSON to Excel also supports a single object:

{
"name": "John Doe",
"age": 30,
"email": "john@example.com"
}

Characteristics:

Wrapped in curly braces {}
Contains 1 to 100 properties
Converted to a single row in Excel

Requirements:

Minimum: 1 property
Maximum: 100 properties
Object cannot be empty

Supported Data Types

JSON to Excel handles all standard JSON data types:

1. Strings

{
"name": "John Doe",
"city": "New York"
}

Converted to text cells in Excel
Preserves formatting and special characters

2. Numbers

{
"age": 30,
"price": 99.99,
"quantity": 5
}

Converted to numeric cells in Excel
Integers and decimals are both supported
Can be used in Excel calculations

3. Booleans

{
"active": true,
"verified": false
}

Converted to TRUE/FALSE in Excel
Can be used in logical formulas

4. Null Values

{
"middleName": null,
"phone": null
}

Converted to blank cells in Excel
Useful for missing or optional data

5. Arrays

{
"tags": ["javascript", "python", "sql"],
"scores": [85, 92, 78]
}

Converted to string representation: ["javascript","python","sql"]
Not automatically expanded (use Nested JSON Mode for complex structures)

6. Nested Objects

{
"user": {
"name": "John",
"contact": {
"email": "john@example.com",
"phone": "555-1234"
}
}
}

In Flat Mode: Converted to string: {"name":"John","contact":{"email":"john@example.com","phone":"555-1234"}}
In Nested Mode: Flattened with delimiters: user.name, user.contact.email, user.contact.phone

Valid JSON Examples

Example 1: Simple Flat Array

[
{"id": 1, "name": "Product A", "price": 19.99},
{"id": 2, "name": "Product B", "price": 29.99},
{"id": 3, "name": "Product C", "price": 39.99}
]

✅ Valid - Simple array of objects with consistent properties

Example 2: Objects with Different Properties

[
{"id": 1, "name": "John", "age": 30},
{"id": 2, "name": "Jane", "city": "New York"}
]

✅ Valid - Objects can have different properties; Excel will have all possible columns

Example 3: Nested Structures

[
{
"id": 1,
"customer": {
"name": "John",
"address": {
"street": "123 Main St",
"city": "New York"
}
}
}
]

✅ Valid - Nested objects work with Nested JSON Mode

Example 4: Mixed Data Types

[
{
"id": 1,
"name": "John",
"active": true,
"score": 95.5,
"tags": ["vip", "premium"],
"metadata": null
}
]

✅ Valid - All data types are supported

Invalid JSON Examples

Example 1: Not Wrapped in Array

{"name": "John"},{"name": "Jane"}

❌ Invalid - Must be wrapped in array [] or be a single object

Example 2: Empty Array

[]

❌ Invalid - Array must contain at least one object

Example 3: Array with Non-Object Elements

[1, 2, 3]

❌ Invalid - Array elements must be objects

["a", "b", "c"]

❌ Invalid - Array elements must be objects

Example 4: Array with Empty Object

[{}]

❌ Invalid - Objects cannot be empty

Example 5: Array with Null

[null]

❌ Invalid - Array elements must be objects

Example 6: Mixed Types in Array

[{"name": "John"}, "text"]

❌ Invalid - All array elements must be objects

JSON Schema

For developers who want to validate their JSON programmatically, here’s the JSON Schema that JSON to Excel follows:

{
"$schema": "http://json-schema.org/draft-07/schema#",
"oneOf": [
{
"type": "array",
"minItems": 1,
"maxItems": 1000,
"items": {
"type": "object",
"minProperties": 1,
"maxProperties": 100,
"additionalProperties": true
},
"not": {
"contains": {
"anyOf": [
{"type": "array"},
{"type": "null"},
{"type": "string"},
{"type": "number"},
{"type": "boolean"},
{"type": "object", "maxProperties": 0}
]
}
}
},
{
"type": "object",
"minProperties": 1,
"maxProperties": 100,
"additionalProperties": true,
"not": {
"anyOf": [
{"type": "array"},
{"type": "null"},
{"type": "string"},
{"type": "number"},
{"type": "boolean"},
{"type": "object", "maxProperties": 0}
]
}
}
]
}