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.

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:

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:

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

Response:

{
  "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:

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

Response:

{
  "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

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "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:

{
  "data": "Name\tAge\nJohn\t20",
  "options": {
    "proCode": "your-email@example.com",
    "jsonFormat": "arrayOfObject",
    "singleObjectFormat": "object"
  }
}

Response:

{
  "isError": false,
  "msg": "success",
  "data": "{\"Name\":\"John\",\"Age\":20}"
}

Implementation Examples

Python Implementation

Standard Mode

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

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

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

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

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

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

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

2. 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
   

3. 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}")
   

Use Cases

Use Case 1: Automated Data Pipeline

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

// 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

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:

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!