Introduction

Converting between Excel and JSON formats is a common requirement in data processing and system integration. The Excel-to-JSON add-in offers a powerful set of professional features to handle various complex data conversion scenarios. This article will detail these pro features, their use cases, and implementation methods.

Subscription Information

Before diving into the features, here’s a brief overview of the subscription details:

• 7-day free trial available
• Multiple currency options:
  • USD: $2.66/month
  • EUR: €2.36/month
  • CNY: ¥19.90/month
  • HKD: HK$21.80/month
• Each Pro Code supports up to 10 devices
• Valid for both Excel-to-JSON and JSON-to-Excel add-ins

Core Features

1. Nested JSON Structure Support

Use Cases

When dealing with complex data structures, you often need to convert Excel data into multi-level JSON objects. For example:

• User Information Management: Including basic info, contact details, address
• Product Catalogs: Categories, specifications, pricing
• Organizational Structure: Departments, teams, members

Implementation

The add-in supports three delimiters for defining JSON hierarchy:

• Dot (.) - Example: user.name, user.contact.phone
• Underscore (_) - Example: user_name, user_contact_phone
• Forward Slash (/) - Example: user/name, user/contact/phone

Practical Example

Consider building a JSON structure containing user information:

Resulting JSON:

1
2
3
4
5
6
7
8
9
10
11
12

[{
    "id": 1,
    "user": {
        "name": "John",
        "contact": {
            "phone": "13800138000"
        },
        "address": {
            "city": "New York"
        }
    }
}]

2. Empty Cell Handling

Use Cases

• Data Cleaning: Processing incomplete datasets
• System Integration: Meeting different systems’ empty value requirements
• Data Analysis: Controlling how null values affect analysis

Three Processing Options

1. Convert to empty string
2. Convert to null
3. Completely exclude from JSON

3. Boolean Format

Use Cases

• Configuration File Generation: Requiring strict true/false format
• Data Visualization: Using 1/0 for status representation
• System Integration: Meeting various system format requirements

Supported Formats

1. JSON boolean (true/false)
2. String (“true”/“false”)
3. Number (1/0)

4. Date Format Conversion

Use Cases

• Cross-system Data Migration: Unifying date formats
• International Applications: Handling different timezone dates
• Data Analysis: Ensuring date consistency

Conversion Options

1. Days Format: Number of days since 1900-01-01
2. ISO 8601 Format: Standard datetime string

Tips and Best Practices

1. Data Preparation

   • Check Excel data format consistency before conversion
   • Plan JSON structure with appropriate delimiters
   • Add $date$ suffix for date columns

2. Format Selection

   • Choose empty cell handling based on target system requirements
   • Consider data readability when selecting boolean format
   • Pay attention to date format compatibility

3. Efficiency Improvement

   • Save commonly used conversion configurations as templates
   • Batch process similar data conversions
   • Utilize preview feature to validate conversion results

Video Demonstration

Official Documentation

https://excel-to-json.wtsolutions.cn/en/latest/profeatures.html

Conclusion

The pro features of the Excel-to-JSON add-in provide a flexible and powerful solution for data conversion. By properly utilizing these features, you can significantly improve data processing efficiency and accuracy.

前言

在数据处理和系统集成过程中，Excel和JSON格式的转换是一个常见需求。Excel-to-JSON插件提供了一套强大的专业版功能，能够满足各种复杂的数据转换场景。本文将详细介绍这些专业版功能的应用场景和使用方法。

订阅说明

在介绍具体功能之前，先简单说明一下订阅相关信息：

• 提供7天免费试用
• 支持多种货币订阅：
  • 人民币：¥19.90/月
  • 美元：$2.66/月
  • 欧元：€2.36/月
  • 港币：HK$21.80/月
• 每个专业版代码可支持10台设备使用
• 同时支持WTSolutions提供的Excel-to-JSON和JSON-to-Excel两个插件

核心功能详解

1. 嵌套JSON结构支持

应用场景

在处理复杂的数据结构时，经常需要将Excel表格数据转换为多层级的JSON对象。例如：

• 用户信息管理：包含基本信息、联系方式、地址等多层级数据
• 产品目录：包含类别、规格、价格等层级化信息
• 组织架构：部门、团队、成员的层级关系

使用方法

插件支持三种分隔符来定义JSON的层级结构：

• 点号(.) - 例如：user.name, user.contact.phone
• 下划线(_) - 例如：user_name, user_contact_phone
• 斜杠(/) - 例如：user/name, user/contact/phone

实际示例

假设我们要构建一个包含用户信息的JSON结构：

转换后的JSON结果：

1
2
3
4
5
6
7
8
9
10
11
12

[{
    "id": 1,
    "user": {
        "name": "张三",
        "contact": {
            "phone": "13800138000"
        },
        "address": {
            "city": "北京"
        }
    }
}]

2. 空值处理策略

应用场景

• 数据清洗：处理不完整的数据集
• 系统集成：满足不同系统对空值的处理要求
• 数据分析：控制空值对分析结果的影响

三种处理方式

1. 转换为空字符串
2. 转换为null
3. 在JSON中完全忽略该字段

3. 布尔值格式化

应用场景

• 配置文件生成：需要严格的true/false格式
• 数据可视化：使用1/0表示状态
• 系统集成：满足不同系统的数据格式要求

支持的格式

1. JSON布尔值（true/false）
2. 字符串（”true”/“false”）
3. 数字（1/0）

4. 日期格式转换

应用场景

• 跨系统数据迁移：统一日期格式
• 国际化应用：处理不同时区的日期
• 数据分析：确保日期数据的一致性

转换选项

1. 天数格式：从1900-01-01开始的天数
2. ISO 8601格式：标准的日期时间字符串

使用技巧与建议

1. 数据预处理

   • 在转换前检查Excel数据格式的一致性
   • 使用合适的分隔符规划JSON结构
   • 为日期列添加$date$后缀

2. 格式选择

   • 根据目标系统的要求选择合适的空值处理方式
   • 考虑数据可读性选择布尔值格式
   • 注意日期格式的兼容性

3. 效率提升

   • 使用模板保存常用的转换配置
   • 批量处理同类型的数据转换
   • 利用预览功能验证转换结果

插件获取

官方指南

https://excel-to-json.wtsolutions.cn/zh-cn/latest/profeatures.html

结语

Excel-to-JSON插件的专业版功能为数据转换提供了灵活而强大的解决方案。通过合理使用这些功能，可以大大提高数据处理的效率和准确性。

软件介绍

照片to谷歌地球/奥维地图是一款由WTSolutions开发的跨平台图片处理工具，能够将带有GPS信息的照片导入Google Earth（谷歌地球）或奥维地图。该软件支持Windows、Mac、iOS、Linux和Android系统，无需下载安装，直接在浏览器中即可使用。

主要功能

• 读取照片中的GPS信息
• 生成谷歌地球/奥维地图可读取的KMZ文件
• 在地图软件中根据照片的拍摄位置显示图片
• 支持批量处理照片
• 提供高级功能（支持自定义图片显示尺寸、导出GPS数据到Excel等）

使用方法

基本步骤

1. 访问在线工具：https://s.wtsolutions.cn/gps.html
2. 选择地图类型（谷歌地球或奥维地图）
3. 选择图标样式（默认、相机、圆圈等多种样式可选）
4. 将照片拖拽到指定区域
5. 等待工具处理完成，下载生成的KMZ文件

使用奥维地图的特别说明

如果选择奥维地图，需要额外设置：

• 提供图片在本地的完整绝对路径
• 路径格式示例：
  • MacOS：file:///Users/wtsolutions/Pictures/
  • Windows：file:///E:/wtsolutions/
• 注意：路径中不能包含特殊字符（*?”<>;/@&=+$,#）

高级功能

高级功能需要使用功能码激活，包括：

1. 单次处理照片数量无限制（普通用户限制6张）
2. 自定义图片显示尺寸（20-3000像素之间）
3. 导出所有图片的GPS数据到CSV文件（可用Excel打开）

应用场景

• 旅行照片整理：将旅行途中拍摄的照片在地图上直观展示
• 地理位置记录：记录和分享特定地点的图片信息
• 摄影作品展示：在地图上展示不同地点拍摄的作品
• 地理信息采集：收集和整理带有地理位置信息的图片数据

注意事项

1. 确保上传的照片包含GPS信息
2. 使用奥维地图时，正确设置本地图片路径
3. 遵循文件路径命名规范，避免使用特殊字符
4. 建议使用现代浏览器访问工具网站

Software Introduction

Images to Google Earth/OviMap is a cross-platform image processing tool developed by WTSolutions that allows you to import photos with GPS information into Google Earth or OviMap. The software supports Windows, Mac, iOS, Linux, and Android systems, requiring no download or installation - it runs directly in your browser.

Key Features

• Extract GPS information from photos
• Generate KMZ files compatible with Google Earth/OviMap
• Display photos at their captured locations on mapping software
• Support batch processing of multiple photos
• Advanced features (customize image display size, export GPS data to Excel, etc.)

How to Use

Basic Steps

1. Access the online tool: https://s.wtsolutions.cn/gps.html
2. Select map type (Google Earth or OviMap)
3. Choose icon style (default, camera, circle, and more options available)
4. Drag and drop photos to the designated area
5. Wait for processing to complete, then download the generated KMZ file

Special Instructions for OviMap

When using OviMap, additional setup is required:

• Provide the complete absolute path of images on your local system
• Path format examples:
  • MacOS: file:///Users/wtsolutions/Pictures/
  • Windows: file:///E:/wtsolutions/
• Note: Path must not contain special characters (*?”<>;/@&=+$,#)

Advanced Features

Advanced features require activation with a feature code, including:

1. Unlimited batch processing (standard users limited to 6 photos)
2. Customize image display size (between 20-3000 pixels)
3. Export all photo GPS data to CSV file (Excel compatible)

Use Cases

• Travel Photo Organization: Visualize travel photos on a map
• Location Documentation: Record and share location-specific images
• Photography Portfolio: Display photographs from different locations on a map
• Geographic Data Collection: Collect and organize images with geographical information

Important Notes

1. Ensure uploaded photos contain GPS information
2. Set correct local image path when using OviMap
3. Follow file path naming conventions, avoid special characters
4. Recommended to use modern browsers to access the tool

Introduction

In our daily data processing work, we often encounter scenarios where we need to handle nested JSON data, especially when dealing with API responses that have complex hierarchical structures. Today, I’d like to share a very useful tool - WTSolutions’ json-to-excel plugin, which helps us easily process such data in Excel.

Plugin Overview

json-to-excel is a plugin specifically designed for Microsoft Excel that can convert JSON data into Excel spreadsheet format. The most notable feature of this plugin is its support for handling nested JSON data, capable of flattening complex JSON structures into an easy-to-understand and manageable tabular format.

Key Features

1. Supports two conversion modes:

   • Flat JSON Mode
   • Nested JSON Mode

2. Pro Features:

   • Custom nested delimiter (choose from . _ /)
   • Configurable nesting depth limit (0-20 levels)
   • 7-day free trial

Usage Steps

1. Install the Plugin

   • Visit the official website
   • Follow the installation guide to complete the setup

2. Choose Conversion Mode

   • Select “Flat JSON Mode” for simple JSON data
   • Select “Nested JSON Mode” for data with nested structures

3. Data Preparation

   • Ensure JSON data format is correct
   • Data must be in array format, e.g.: [{"name":"John","age":25},{"name":"Jane","age":30}]
   • Each object in the array must contain at least one property

4. Pro Features Setup (Optional)

   • Click “Pro Features” to expand advanced settings
   • Choose appropriate nested delimiter
   • Set desired maximum nesting depth

Usage Examples

Example 1: Processing Simple JSON Data

1
2
3
4

[
  {"name":"John","age":25},
  {"name":"Jane","age":30}
]

Example 2: Processing Nested JSON Data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

[
  {
    "user": {
      "name": "John",
      "contact": {
        "email": "[email protected]",
        "phone": "1234567890"
      }
    },
    "orders": [
      {
        "id": "001",
        "amount": 100
      }
    ]
  }
]

The converted Excel spreadsheet will contain the following columns:

• user.name
• user.contact.email
• user.contact.phone
• orders.0.id
• orders.0.amount

Best Practices

1. Data Preprocessing

   • Check JSON format correctness before conversion
   • Use online JSON validation tools for format verification

2. Choose Appropriate Conversion Mode

   • Use Flat JSON Mode for simple data structures
   • Use Nested JSON Mode for multi-level nested data

3. Pro Features Usage Tips

   • Choose delimiter based on data structure
   • Set reasonable nesting depth to avoid over-complexity

4. Performance Optimization

   • Avoid processing too much data at once
   • Process large datasets in batches

Common Issues and Solutions

1. Data Format Errors

   • Ensure JSON data is in valid array format
   • Check for illegal characters

2. Incomplete Conversion Results

   • Verify nesting depth settings
   • Confirm all necessary data fields are present

Conclusion

The json-to-excel plugin greatly simplifies the task of handling JSON data in Excel. By properly utilizing its features, we can more efficiently process various complex data structures. I hope this experience sharing helps those who need to handle JSON data in Excel.

Related Links

• Official Documentation
• Pro Features Guide
• Feedback Form