DEV Community

WTSolutions
WTSolutions

Posted on

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
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 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"
}
Enter fullscreen mode Exit fullscreen mode

Response:

{
  "isError": false,
  "msg": "success",
  "data": "[{\"Name\":\"John Doe\",\"Age\":25,\"IsStudent\":false},{\"Name\":\"Jane Smith\",\"Age\":30,\"IsStudent\":true}]"
}
Enter fullscreen mode Exit fullscreen mode

Example 2: Converting from URL

Request:

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

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\"}]}]"
}
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 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": ""
}
Enter fullscreen mode Exit fullscreen mode

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": "."
  }
}
Enter fullscreen mode Exit fullscreen mode

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}}]"
}
Enter fullscreen mode Exit fullscreen mode

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"
  }
}
Enter fullscreen mode Exit fullscreen mode

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]]"
}
Enter fullscreen mode Exit fullscreen mode

Example 3: Single Object Output Format

Request:

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

Response:

{
  "isError": false,
  "msg": "success",
  "data": "{\"Name\":\"John\",\"Age\":20}"
}
Enter fullscreen mode Exit fullscreen mode

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"])
Enter fullscreen mode Exit fullscreen mode

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"])
Enter fullscreen mode Exit fullscreen mode

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);
});
Enter fullscreen mode Exit fullscreen mode

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);
});
Enter fullscreen mode Exit fullscreen mode

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"}'
Enter fullscreen mode Exit fullscreen mode

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"
    }
  }'
Enter fullscreen mode Exit fullscreen mode

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

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)
Enter fullscreen mode Exit fullscreen mode

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' });
  }
});
Enter fullscreen mode Exit fullscreen mode

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')
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 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
Enter fullscreen mode Exit fullscreen mode

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!

Need help with the API? Contact the team at he.yang@wtsolutions.cn for support.

Top comments (0)