DEV Community

WTSolutions
WTSolutions

Posted on

Using the JSON to Excel API - Programmatic Access for Developers

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

Introduction to the JSON to Excel API

The JSON to Excel API provides a powerful, programmatic way to convert JSON data to CSV format (which can be easily imported into Excel). It's designed for developers who need to:

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

API Overview

Endpoint

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

POST https://mcp2.wtsolutions.cn/json-to-excel-api
Enter fullscreen mode Exit fullscreen mode

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 JSON data string to be converted. Must be valid JSON array or object
url string No URL pointing to a JSON file. Either 'data' or 'url' must be provided

Important: Provide either data or url, not both.

Request Examples

Example 1: Converting JSON Data

Request:

{
  "data": "[{\"name\": \"WTSolutions\", \"age\": 18},{\"name\": \"David\", \"age\": 20}]"
}
Enter fullscreen mode Exit fullscreen mode

Response:

{
  "data": "\"name,age\\nWTSolutions,18\\nDavid,20\"",
  "isError": false,
  "msg": "success"
}
Enter fullscreen mode Exit fullscreen mode

Example 2: Converting from URL

Request:

{
  "url": "https://tools.wtsolutions.cn/example.json"
}
Enter fullscreen mode Exit fullscreen mode

Response:

{
  "data": "\"name,age\\nWTSolutions,18\\nDavid,20\"",
  "isError": false,
  "msg": "success"
}
Enter fullscreen mode Exit fullscreen mode

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 CSV data string, '' if there was an error

Error Response Example 

{
  "isError": true,
  "msg": "Invalid JSON format",
  "data": ""
}
Enter fullscreen mode Exit fullscreen mode

Pro API Usage

Request Format

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

Parameter Type Required Description
data string No JSON data string to be converted. Must be valid JSON array or object
url string No URL pointing to a JSON file. Either 'data' or 'url' must be provided
options object Yes 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: "nested" or "flat"
delimiter string "." Delimiter for nested JSON keys when using jsonMode: "nested". Acceptable: ".", "", "_", "/"
maxDepth string "unlimited" Maximum depth for nested JSON objects when using jsonMode: "nested". Acceptable: "unlimited", "1" ~ "20"

Pro Request Example

Request:

{
  "data": "[{\"name\":\"John\",\"contact\":{\"email\":\"john@example.com\",\"phone\":\"1234567890\"}},{\"name\":\"Jane\",\"contact\":{\"email\":\"jane@example.com\",\"phone\":\"0987654321\"}}]",
  "options": {
    "proCode": "your-email@example.com",
    "jsonMode": "nested",
    "delimiter": ".",
    "maxDepth": "unlimited"
  }
}
Enter fullscreen mode Exit fullscreen mode

Response:

{
  "isError": false,
  "data": "name,contact.email,contact.phone\nJohn,john@example.com,1234567890\nJane,jane@example.com,0987654321",
  "msg": "success"
}
Enter fullscreen mode Exit fullscreen mode

Implementation Examples

Python Implementation

Standard Mode 

import requests
import json

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

# Prepare your JSON data
json_data = [
    {"name": "John", "age": 30},
    {"name": "Jane", "age": 25}
]

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

# Process the response
result = response.json()

if not result["isError"]:
    csv_data = result["data"]
    print("CSV Data:", csv_data)
    # Save to file
    with open("output.csv", "w") as f:
        f.write(csv_data)
else:
    print("Error:", result["msg"])
Enter fullscreen mode Exit fullscreen mode

Pro Mode 

import requests
import json

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

# Prepare your JSON data with nested structure
json_data = [
    {
        "name": "John",
        "contact": {
            "email": "john@example.com",
            "phone": "1234567890"
        }
    },
    {
        "name": "Jane",
        "contact": {
            "email": "jane@example.com",
            "phone": "0987654321"
        }
    }
]

# Make the request with Pro options
response = requests.post(
    url,
    json={
        "data": json.dumps(json_data),
        "options": {
            "proCode": "your-email@example.com",
            "jsonMode": "nested",
            "delimiter": ".",
            "maxDepth": "unlimited"
        }
    },
    headers={"Content-Type": "application/json"}
)

# Process the response
result = response.json()

if not result["isError"]:
    csv_data = result["data"]
    print("CSV Data:", csv_data)
    # Save to file
    with open("output.csv", "w") as f:
        f.write(csv_data)
else:
    print("Error:", result["msg"])
Enter fullscreen mode Exit fullscreen mode

JavaScript/Node.js Implementation

Standard Mode 

const axios = require('axios');

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

// Prepare your JSON data
const jsonData = [
  { name: "John", age: 30 },
  { name: "Jane", age: 25 }
];

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

Pro Mode 

const axios = require('axios');

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

// Prepare your JSON data with nested structure
const jsonData = [
  {
    name: "John",
    contact: {
      email: "john@example.com",
      phone: "1234567890"
    }
  },
  {
    name: "Jane",
    contact: {
      email: "jane@example.com",
      phone: "0987654321"
    }
  }
];

// Make the request with Pro options
axios.post(url, {
  data: JSON.stringify(jsonData),
  options: {
    proCode: 'your-email@example.com',
    jsonMode: 'nested',
    delimiter: '.',
    maxDepth: 'unlimited'
  }
}, {
  headers: {
    'Content-Type': 'application/json'
  }
})
.then(response => {
  const result = response.data;
  if (!result.isError) {
    console.log('CSV Data:', result.data);
    // Save to file (Node.js)
    const fs = require('fs');
    fs.writeFileSync('output.csv', result.data);
  } else {
    console.log('Error:', result.msg);
  }
})
.catch(error => {
  console.error('Request failed:', error);
});
Enter fullscreen mode Exit fullscreen mode

cURL Implementation

Standard Mode 

curl -X POST https://mcp2.wtsolutions.cn/json-to-excel-api \
  -H "Content-Type: application/json" \
  -d '{"data": "[{\"name\": \"John\", \"age\": 30},{\"name\": \"Jane\", \"age\": 25}]"}'
Enter fullscreen mode Exit fullscreen mode

Pro Mode 

curl -X POST https://mcp2.wtsolutions.cn/json-to-excel-api \
  -H "Content-Type: application/json" \
  -d '{
    "data": "[{\"name\":\"John\",\"contact\":{\"email\":\"john@example.com\",\"phone\":\"1234567890\"}}]",
    "options": {
      "proCode": "your-email@example.com",
      "jsonMode": "nested",
      "delimiter": ".",
      "maxDepth": "unlimited"
    }
  }'
Enter fullscreen mode Exit fullscreen mode

Data Type Handling

The API automatically handles different JSON data types:

JSON Type CSV Representation
Numbers Numeric values in CSV
Booleans 'true'/'false' strings
Strings Escaped and quoted if necessary
Arrays JSON.stringify array string
Objects JSON.stringify object string (unless using nested mode)

Error Handling

The API provides descriptive error messages for common issues:

Error Message Cause
Invalid JSON format Input data is not valid JSON
Empty JSON data Input data is an empty JSON string
Network Error when fetching file Error downloading file from URL
File not found File at provided URL cannot be found
Server Internal Error Unexpected server error
Invalid Pro Code Pro Code is not valid or expired

Best Practices for Error Handling

  1. Always Check isError Flag 
   if result["isError"]:
       # Handle error
       print(f"Error: {result['msg']}")
   else:
       # Process successful response
       csv_data = result["data"]
Enter fullscreen mode Exit fullscreen mode
  1. Implement Retry Logic 
   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
Enter fullscreen mode Exit fullscreen mode
  1. Log Errors for Debugging 
   import logging
   logging.basicConfig(level=logging.INFO)

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

CORS Considerations

When making requests from a web browser, you may encounter CORS (Cross-Origin Resource Sharing) issues. To handle CORS:

  1. Use a Server-Side Proxy

    • Make API calls from your server, not directly from the browser
    • Your server forwards requests to JSON to Excel API
    • Client communicates with your server

  2. Configure CORS Headers

    • Ensure your server properly handles CORS
    • Set appropriate headers for cross-origin requests

Use Cases

Use Case 1: Automated Report Generation 

import requests
import schedule
import time

def generate_daily_report():
    # Fetch data from your API
    api_response = requests.get('https://your-api.com/data')
    json_data = api_response.json()

    # Convert to CSV using JSON to Excel API
    conversion_response = requests.post(
        'https://mcp2.wtsolutions.cn/json-to-excel-api',
        json={"data": json.dumps(json_data)}
    )

    result = conversion_response.json()

    if not result["isError"]:
        # Save CSV file
        with open(f"daily_report_{time.strftime('%Y%m%d')}.csv", "w") as f:
            f.write(result["data"])
        print("Report generated successfully")

# Schedule daily report generation
schedule.every().day.at("09:00").do(generate_daily_report)

while True:
    schedule.run_pending()
    time.sleep(60)
Enter fullscreen mode Exit fullscreen mode

Use Case 2: Web Service Integration 

// Express.js endpoint that converts JSON to Excel
app.post('/convert-to-excel', async (req, res) => {
  try {
    const jsonData = req.body.data;

    // Call JSON to Excel API
    const response = await axios.post(
      'https://mcp2.wtsolutions.cn/json-to-excel-api',
      {
        data: JSON.stringify(jsonData),
        options: {
          proCode: process.env.PRO_CODE,
          jsonMode: 'nested',
          delimiter: '.'
        }
      }
    );

    const result = response.data;

    if (!result.isError) {
      // Send CSV back to client
      res.setHeader('Content-Type', 'text/csv');
      res.send(result.data);
    } else {
      res.status(400).json({ error: result.msg });
    }
  } catch (error) {
    res.status(500).json({ error: 'Conversion failed' });
  }
});
Enter fullscreen mode Exit fullscreen mode

Use Case 3: Data Pipeline Integration 

import requests
import pandas as pd

def process_api_data(api_url):
    # Fetch data from external API
    response = requests.get(api_url)
    json_data = response.json()

    # Convert to CSV using JSON to Excel API
    conversion_response = requests.post(
        'https://mcp2.wtsolutions.cn/json-to-excel-api',
        json={
            "data": json.dumps(json_data),
            "options": {
                "proCode": "your-email@example.com",
                "jsonMode": "nested",
                "delimiter": "_"
            }
        }
    )

    result = conversion_response.json()

    if not result["isError"]:
        # Load CSV into pandas for further processing
        from io import StringIO
        df = pd.read_csv(StringIO(result["data"]))

        # Perform additional analysis
        print(df.describe())

        return df
    else:
        print(f"Error: {result['msg']}")
        return None

# Usage
df = process_api_data('https://api.example.com/data')
Enter fullscreen mode Exit fullscreen mode

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 JSON 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:

import hashlib
import json

def get_cache_key(json_data):
    return hashlib.md5(json.dumps(json_data).encode()).hexdigest()

cache = {}

def convert_with_cache(json_data):
    cache_key = get_cache_key(json_data)

    if cache_key in cache:
        return cache[cache_key]

    # Make API call
    response = requests.post(
        'https://mcp2.wtsolutions.cn/json-to-excel-api',
        json={"data": json.dumps(json_data)}
    )
    result = response.json()

    # Cache the result
    cache[cache_key] = result
    return result
Enter fullscreen mode Exit fullscreen mode

Next Steps

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

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

Top comments (0)