excel-to-json

Using the Excel to JSON API - Programmatic Access for Developers

Welcome to part 8 of our Excel to JSON series! We’ve covered user-facing tools: Web App, Excel Add-in, and WPS Add-in, along with Pro features. Today, we’re exploring the Excel to JSON API - the perfect solution for developers who need to integrate Excel to JSON functionality into their applications and workflows.

excel-to-json-toolkit

Introduction to Excel to JSON API

The Excel to JSON API provides a powerful, programmatic way to convert Excel data to JSON format. It’s designed for developers who need to:

  • Automate Excel to JSON conversions in their applications
  • Integrate conversion capabilities into existing workflows
  • Process Excel data from web services and APIs
  • Build custom solutions around Excel to JSON functionality

API Overview

Endpoint

The Excel to JSON API is accessible via a single endpoint:

1
POST https://mcp.wtsolutions.cn/excel-to-json-api

Two Usage Modes

The API offers two distinct usage modes:

  1. Standard Mode: Free of charge, with standard conversion rules
  2. Pro Mode: Requires valid subscription, with custom conversion rules

Standard API Usage

Request Format

The Standard API accepts POST requests with application/json content type containing one of two parameters:

Parameter Type Required Description
data string No Tab-separated or comma-separated text data with at least two rows (header row + data row). Either ‘data’ or ‘url’ must be provided
url string No URL pointing to an Excel or CSV file. Either ‘data’ or ‘url’ must be provided

Important: Provide either data or url, not both.

Request Examples

Example 1: Converting Tab-Separated Data

Request:

1
2
3
{
  "data": "Name\tAge\tIsStudent\nJohn Doe\t25\tfalse\nJane Smith\t30\ttrue"
}

Response:

1
2
3
4
5
{
  "isError": false,
  "msg": "success",
  "data": "[{\"Name\":\"John Doe\",\"Age\":25,\"IsStudent\":false},{\"Name\":\"Jane Smith\",\"Age\":30,\"IsStudent\":true}]"
}

Example 2: Converting from URL

Request:

1
2
3
{
  "url": "https://tools.wtsolutions.cn/example.xlsx"
}

Response:

1
2
3
4
5
{
  "isError": false,
  "msg": "success",
  "data": "[{\"sheetName\":\"Sheet1\",\"data\":[{\"Name\":\"John Doe\",\"Age\":25,\"IsStudent\":false},{\"Name\":\"Jane Smith\",\"Age\":30,\"IsStudent\":true}]},{\"sheetName\":\"Sheet2\",\"data\":[{\"ID\":1,\"Value\":\"Example\"}]}]"
}

Response Format

The API returns a JSON object with the following structure:

Field Type Description
isError boolean Indicates if there was an error processing the request
msg string ‘success’ or error description
data string Converted data as array of sheet objects if using URL, string if using direct data

Error Response Example

1
2
3
4
5
{
  "isError": true,
  "msg": "At least 2 rows are required in Excel Data",
  "data": ""
}

Data Type Handling

The Standard API automatically detects and converts different data types:

  • Numbers: Converted to numeric values
  • Booleans: Recognizes ‘true’/‘false’ (case-insensitive) and converts to boolean values
  • Dates: Detects various date formats and converts them appropriately
  • Strings: Treated as string values
  • Empty values: Represented as empty strings

Pro API Usage

Request Format

The Pro API accepts POST requests with application/json content type containing:

Parameter Type Required Description
data string No Tab-separated or comma-separated text data. Either ‘data’ or ‘url’ must be provided
url string No URL pointing to an Excel or CSV file. Either ‘data’ or ‘url’ must be provided
options object Yes Optional configuration object for customizing the conversion process

Important:

  • Provide either data or url, not both
  • options is mandatory for Pro mode
  • You must have a valid Pro Code to use Pro mode

Options Object

The options object can contain the following properties:

Property Type Default Description
proCode string “” Pro Code for custom conversion rules. This is mandatory.
jsonMode string “flat” Format mode for JSON output: “nested”, or “flat”
header string “row” Specifies which row/column to use as headers: “row” (first row) or “column” (first column)
delimiter string “.” Delimiter character for nested JSON keys when using jsonMode: “nested”, acceptable delimiters are “.”, “_”, “__”, “/“
emptyCell string “emptyString” Handling of empty cells: “emptyString”, “null”, or “exclude”
booleanFormat string “trueFalse” Format for boolean values: “trueFalse”, “10”, or “string”
jsonFormat string “arrayOfObject” Overall JSON output format: “arrayOfObject” or “2DArray”
singleObjectFormat string “array” Format when result has only one object: “array” (keep as array) or “object” (return as single object)

Important Notes:

  • delimiter works only when jsonMode is “nested”
  • singleObjectFormat works only when jsonFormat is “arrayOfObject”
  • jsonFormat as “2DArray” works only when jsonMode is “flat”
  • proCode is mandatory for Pro mode

Pro Request Examples

Example 1: Nested JSON with Custom Delimiter

Request:

1
2
3
4
5
6
7
8
{
  "data": "id\tstudent.name\tstudent.familyname\tstudent.age\n1\tMeimei\tHan\t12\n2\tLily\tJaskson\t15\n3\tElon\tMask\t18",
  "options": {
    "proCode": "your-email@example.com",
    "jsonMode": "nested",
    "delimiter": "."
  }
}

Response:

1
2
3
4
5
{
  "isError": false,
  "msg": "success",
  "data": "[{\"id\":1,\"student\":{\"name\":\"Meimei\",\"familyname\":\"Han\",\"age\":12}},{\"id\":2,\"student\":{\"name\":\"Lily\",\"familyname\":\"Jaskson\",\"age\":15}},{\"id\":3,\"student\":{\"name\":\"Elon\",\"familyname\":\"Mask\",\"age\":18}}]"
}

Example 2: 2D Array Output Format

Request:

1
2
3
4
5
6
7
8
{
  "data": "id\tstudent.name\tstudent.familyname\tstudent.age\n1\tMeimei\tHan\t12\n2\tLily\tJaskson\t15\n3\tElon\tMask\t18",
  "options": {
    "proCode": "your-email@example.com",
    "jsonMode": "flat",
    "jsonFormat": "2DArray"
  }
}

Response:

1
2
3
4
5
{
  "isError": false,
  "msg": "success",
  "data": "[[\"id\",\"student.name\",\"student.familyname\",\"student.age\"],[1,\"Meimei\",\"Han\",12],[2,\"Lily\",\"Jaskson\",15],[3,\"Elon\",\"Mask\",18]]"
}

Example 3: Single Object Output Format

Request:

1
2
3
4
5
6
7
8
{
  "data": "Name\tAge\nJohn\t20",
  "options": {
    "proCode": "your-email@example.com",
    "jsonFormat": "arrayOfObject",
    "singleObjectFormat": "object"
  }
}

Response:

1
2
3
4
5
{
  "isError": false,
  "msg": "success",
  "data": "{\"Name\":\"John\",\"Age\":20}"
}

Implementation Examples

Python Implementation

Standard Mode

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
import requests

# API endpoint
url = "https://mcp.wtsolutions.cn/excel-to-json-api"

# Prepare your Excel data (tab-separated)
excel_data = "Name\tAge\tCompany\nJohn Doe\t25\tWTSolutions\nJane Smith\t30\tMicrosoft"

# Make request
response = requests.post(
    url,
    json={"data": excel_data},
    headers={"Content-Type": "application/json"}
)

# Process response
result = response.json()

if not result["isError"]:
    json_data = result["data"]
    print("JSON Data:", json_data)
    # Save to file
    with open("output.json", "w") as f:
        f.write(json_data)
else:
    print("Error:", result["msg"])

Pro Mode

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
import requests

# API endpoint
url = "https://mcp.wtsolutions.cn/excel-to-json-api"

# Prepare your Excel data with nested structure
excel_data = "id\tstudent.name\tstudent.familyname\tstudent.age\n1\tMeimei\tHan\t12\n2\tLily\tJaskson\t15"

# Make request with Pro options
response = requests.post(
    url,
    json={
        "data": excel_data,
        "options": {
            "proCode": "your-email@example.com",
            "jsonMode": "nested",
            "delimiter": ".",
            "emptyCell": "null",
            "booleanFormat": "trueFalse"
        }
    },
    headers={"Content-Type": "application/json"}
)

# Process response
result = response.json()

if not result["isError"]:
    json_data = result["data"]
    print("JSON Data:", json_data)
    # Save to file
    with open("output.json", "w") as f:
        f.write(json_data)
else:
    print("Error:", result["msg"])

JavaScript/Node.js Implementation

Standard Mode

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
const axios = require('axios');

// API endpoint
const url = 'https://mcp.wtsolutions.cn/excel-to-json-api';

// Prepare your Excel data (tab-separated)
const excelData = 'Name\tAge\tCompany\nJohn Doe\t25\tWTSolutions\nJane Smith\t30\tMicrosoft';

// Make request
axios.post(url, {
  data: excelData
}, {
  headers: {
    'Content-Type': 'application/json'
  }
})
.then(response => {
  const result = response.data;
  if (!result.isError) {
    console.log('JSON Data:', result.data);
    // Save to file (Node.js)
    const fs = require('fs');
    fs.writeFileSync('output.json', result.data);
  } else {
    console.log('Error:', result.msg);
  }
})
.catch(error => {
  console.error('Request failed:', error);
});

Pro Mode

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
const axios = require('axios');

// API endpoint
const url = 'https://mcp.wtsolutions.cn/excel-to-json-api';

// Prepare your Excel data with nested structure
const excelData = 'id\tstudent.name\tstudent.familyname\tstudent.age\n1\tMeimei\tHan\t12\n2\tLily\tJaskson\t15';

// Make request with Pro options
axios.post(url, {
  data: excelData,
  options: {
    proCode: 'your-email@example.com',
    jsonMode: 'nested',
    delimiter: '.',
    emptyCell: 'null',
    booleanFormat: 'trueFalse'
  }
}, {
  headers: {
    'Content-Type': 'application/json'
  }
})
.then(response => {
  const result = response.data;
  if (!result.isError) {
    console.log('JSON Data:', result.data);
    // Save to file (Node.js)
    const fs = require('fs');
    fs.writeFileSync('output.json', result.data);
  } else {
    console.log('Error:', result.msg);
  }
})
.catch(error => {
  console.error('Request failed:', error);
});

cURL Implementation

Standard Mode

1
2
3
curl -X POST https://mcp.wtsolutions.cn/excel-to-json-api \
  -H "Content-Type: application/json" \
  -d '{"data": "Name\tAge\tCompany\nJohn Doe\t25\tWTSolutions\nJane Smith\t30\tMicrosoft"}'

Pro Mode

1
2
3
4
5
6
7
8
9
10
11
curl -X POST https://mcp.wtsolutions.cn/excel-to-json-api \
  -H "Content-Type: application/json" \
  -d '{
    "data": "id\tstudent.name\tstudent.familyname\tstudent.age\n1\tMeimei\tHan\t12\n2\tLily\tJaskson\t15",
    "options": {
      "proCode": "your-email@example.com",
      "jsonMode": "nested",
      "delimiter": ".",
      "emptyCell": "null"
    }
  }'

Error Handling

The API provides descriptive error messages for common issues:

Error Message Cause
Excel Data Format Invalid Input data is not tab-separated or comma-separated
At least 2 rows are required Input data has fewer than 2 rows
Both data and url received Both ‘data’ and ‘url’ parameters are provided
Network Error when fetching file Error downloading file from provided URL
File not found File at provided URL cannot be found
Blank/Null/Empty cells in first row not allowed Header row contains empty cells
Server Internal Error Unexpected server error

Best Practices for Error Handling

  1. Always Check isError Flag

    1
    2
    3
    4
    5
    6
    if result["isError"]:
        # Handle error
        print(f"Error: {result['msg']}")
    else:
        # Process successful response
        json_data = result["data"]

  2. Implement Retry Logic

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    import time
    max_retries = 3
    for attempt in range(max_retries):
        try:
            response = requests.post(url, json=payload)
            result = response.json()
            if not result["isError"]:
                break
        except Exception as e:
            if attempt < max_retries - 1:
                time.sleep(2 ** attempt)  # Exponential backoff
            else:
                raise

  3. Log Errors for Debugging

    1
    2
    3
    4
    5
    6
    import logging
    logging.basicConfig(level=logging.INFO)

    if result["isError"]:
        logging.error(f"API Error: {result['msg']}")
        logging.error(f"Request payload: {payload}")

Use Cases

Use Case 1: Automated Data Pipeline

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
import requests
import schedule
import time

def process_excel_to_json(excel_file_path):
    # Read Excel file
    with open(excel_file_path, 'r') as f:
        excel_data = f.read()
    
    # Convert to JSON using API
    response = requests.post(
        'https://mcp.wtsolutions.cn/excel-to-json-api',
        json={
            "data": excel_data,
            "options": {
                "proCode": "your-email@example.com",
                "jsonMode": "nested",
                "delimiter": "_"
            }
        }
    )
    )
    
    result = response.json()
    
    if not result["isError"]:
        # Save JSON file
        json_file_path = excel_file_path.replace('.xlsx', '.json')
        with open(json_file_path, 'w') as f:
            f.write(result["data"])
        print(f"Converted: {excel_file_path} -> {json_file_path}")
    else:
        print(f"Error: {result['msg']}")

# Schedule daily processing
schedule.every().day.at("09:00").do(process_excel_to_json, "daily_report.xlsx")

while True:
    schedule.run_pending()
    time.sleep(60)

Use Case 2: Web Service Integration

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
// Express.js endpoint that converts Excel to JSON
app.post('/convert-to-json', async (req, res) => {
  try {
    const excelData = req.body.data;
    
    // Call Excel to JSON API
    const response = await axios.post(
      'https://mcp.wtsolutions.cn/excel-to-json-api',
      {
        data: excelData,
        options: {
          proCode: process.env.PRO_CODE,
          jsonMode: 'nested',
          delimiter: '.'
        }
      }
    );
    
    const result = response.data;
    
    if (!result.isError) {
      // Send JSON back to client
      res.setHeader('Content-Type', 'application/json');
      res.send(result.data);
    } else {
      res.status(400).json({ error: result.msg });
    }
  } catch (error) {
    res.status(500).json({ error: 'Conversion failed' });
  }
});

Use Case 3: Batch Processing

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
import requests
import os

def batch_convert_excel_files(directory_path):
    excel_files = [f for f in os.listdir(directory_path) if f.endswith('.xlsx')]
    
    for excel_file in excel_files:
        file_path = os.path.join(directory_path, excel_file)
        
        with open(file_path, 'r') as f:
            excel_data = f.read()
        
        response = requests.post(
            'https://mcp.wtsolutions.cn/excel-to-json-api',
            json={
                "data": excel_data,
                "options": {
                    "proCode": "your-email@example.com",
                    "jsonMode": "flat",
                    "emptyCell": "null"
                }
            }
        )
        )
        
        result = response.json()
        
        if not result["isError"]:
            json_file = excel_file.replace('.xlsx', '.json')
            json_path = os.path.join(directory_path, json_file)
            
            with open(json_path, 'w') as f:
                f.write(result["data"])
            
            print(f"Converted: {excel_file} -> {json_file}")
        else:
            print(f"Error converting {excel_file}: {result['msg']}")

# Process all Excel files in directory
batch_convert_excel_files('/path/to/excel/files')

Performance Considerations

Rate Limiting

Be mindful of API rate limits:

  • Implement appropriate delays between requests
  • Use caching for repeated conversions
  • Batch requests when possible

Large Data Handling

For large Excel datasets:

  • Consider splitting data into smaller chunks
  • Process asynchronously to avoid blocking
  • Implement progress tracking for long-running conversions

Caching Strategy

Cache conversion results to avoid redundant API calls:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import hashlib
import json

def get_cache_key(excel_data):
    return hashlib.md5(excel_data.encode()).hexdigest()

cache = {}

def convert_with_cache(excel_data):
    cache_key = get_cache_key(excel_data)
    
    if cache_key in cache:
        return cache[cache_key]
    
    # Make API call
    response = requests.post(
        'https://mcp.wtsolutions.cn/excel-to-json-api',
        json={"data": excel_data}
    )
    result = response.json()
    
    # Cache result
    cache[cache_key] = result
    return result

Next Steps

Now that you understand how to use the Excel to JSON API programmatically, you’re ready to explore MCP Service integration. In our next post, we’ll cover MCP Service, which provides another way for developers to integrate Excel to JSON functionality into their workflows, particularly for those working with AI and automation tools.

Ready to integrate the API? Start building your Excel to JSON integration today!

微信二维码
Share