JSON to Excel (XLS/XLSX) refers to the process of converting data from a JSON (JavaScript Object Notation) format into an Excel spreadsheet format (XLS or XLSX). Excel is one of the most widely used applications for data analysis and manipulation, and converting JSON to Excel can make it easier for users to work with and analyze data in a more structured, tabular form.
Why Convert JSON to Excel?
Data Analysis in Excel: Excel provides a user-friendly environment for analyzing data, making charts, performing calculations, and applying various data analysis functions. Converting JSON to Excel allows you to take advantage of these powerful features.
Data Presentation: Excel is often used for reporting purposes. Converting JSON to Excel can make data more accessible for stakeholders, especially in business or project management.
Interoperability: Many systems or tools may support Excel as the preferred file format for data exchange. Converting JSON to Excel ensures compatibility across different platforms.
Ease of Use: Most users are familiar with Excel, and it's easier to navigate and manipulate tabular data in Excel compared to raw JSON.
How to Convert JSON to Excel (XLS/XLSX)?
There are several ways to convert JSON data into an Excel file, depending on your preferences. You can do this programmatically using Python or JavaScript, or use online tools for quick conversion.
1. Using Python (with pandas and openpyxl Libraries)
Python makes it very easy to convert JSON to Excel using the pandas library, which can handle data manipulation, and openpyxl, which is required for writing to XLSX files.
Step-by-Step Conversion Using pandas:
Install pandas and openpyxl:
bash
pip install pandas openpyxl
Example Python Code:
python
import pandas as pd
import json
# Sample JSON data
json_data = '''
[
{"id": 1, "name": "John Doe", "department": "Engineering"},
{"id": 2, "name": "Jane Smith", "department": "HR"}
]
'''
# Load the JSON data
data = json.loads(json_data)
# Convert JSON data to DataFrame (tabular form)
df = pd.DataFrame(data)
# Write the DataFrame to an Excel file
df.to_excel('output.xlsx', index=False, engine='openpyxl')
print("Excel file has been created.")
This will generate an output.xlsx Excel file with the following content:
id name department
1 John Doe Engineering
2 Jane Smith HR
Explanation:
json.loads(): Converts the JSON string to a Python dictionary.
pd.DataFrame(data): Converts the Python dictionary to a DataFrame, which is a table-like structure.
df.to_excel('output.xlsx', index=False): Writes the DataFrame to an Excel file (without writing row indices).
2. Using JavaScript (Node.js)
In JavaScript (Node.js), you can use the json2xls library to convert JSON to Excel files.
Install json2xls:
bash
npm install json2xls
Example Code:
javascript
const json2xls = require('json2xls');
const fs = require('fs');
// Sample JSON data
const jsonData = [
{ "id": 1, "name": "John Doe", "department": "Engineering" },
{ "id": 2, "name": "Jane Smith", "department": "HR" }
];
// Convert JSON to Excel
const xls = json2xls(jsonData);
// Write to Excel file
fs.writeFileSync('output.xlsx', xls, 'binary');
console.log("Excel file has been created.");
This will generate an output.xlsx Excel file similar to the Python example.
3. Using Online Tools
If you prefer not to write code, you can use online tools to convert JSON to Excel quickly:
ConvertCSV: This website allows you to upload your JSON file and download it as an Excel (XLS or XLSX) file.
JSON to Excel Converter: Various online converters provide a simple way to convert JSON to Excel with a few clicks.
Handling Nested JSON Data
If your JSON data contains nested structures (objects or arrays within objects), you will need to flatten it before converting it to Excel. Both Python and JavaScript offer ways to flatten nested JSON.
For example, consider this nested JSON:
json
[
{
"id": 1,
"name": "John Doe",
"department": {
"name": "Engineering",
"location": "Building 1"
}
},
{
"id": 2,
"name": "Jane Smith",
"department": {
"name": "HR",
"location": "Building 2"
}
}
]
In this case, you may want to flatten the department object so that it appears as separate columns.
Flattening JSON in Python:
python
import pandas as pd
import json
# Sample nested JSON data
json_data = '''
[
{"id": 1, "name": "John Doe", "department": {"name": "Engineering", "location": "Building 1"}},
{"id": 2, "name": "Jane Smith", "department": {"name": "HR", "location": "Building 2"}}
]
'''
# Load the JSON data
data = json.loads(json_data)
# Flatten the JSON structure
flattened_data = []
for item in data:
flat_item = {
"id": item["id"],
"name": item["name"],
"department_name": item["department"]["name"],
"department_location": item["department"]["location"]
}
flattened_data.append(flat_item)
# Convert to DataFrame
df = pd.DataFrame(flattened_data)
# Write to Excel
df.to_excel('flattened_output.xlsx', index=False, engine='openpyxl')
print("Flattened Excel file has been created.")
This will generate an Excel file where the nested department data is flattened into separate columns:
id name department_name department_location
1 John Doe Engineering Building 1
2 Jane Smith HR Building 2
4. Excel Formatting and Customization
You can also customize the Excel file further by using the openpyxl library in Python to set styles, add sheets, or perform other tasks. For example, you could:
Set column widths
Add headers with different styles
Add multiple sheets
Here's a simple example of setting the header color:
python
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Color, PatternFill
# Convert JSON to DataFrame and save to Excel
df = pd.DataFrame(data)
df.to_excel('formatted_output.xlsx', index=False, engine='openpyxl')
# Load the workbook and worksheet
wb = load_workbook('formatted_output.xlsx')
ws = wb.active
# Style the header row
header_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
for cell in ws[1]:
cell.fill = header_fill
cell.font = Font(bold=True)
# Save the styled workbook
wb.save('formatted_output.xlsx')
print("Styled Excel file has been created.")
JSON to Excel Example
Here's an example of a simple JSON structure and its converted Excel output:
JSON Input:
json
[
{"id": 1, "name": "John Doe", "department": "Engineering"},
{"id": 2, "name": "Jane Smith", "department": "HR"}
]
Converted Excel Output (Excel Table Format):
id name department
1 John Doe Engineering
2 Jane Smith HR
In Summary:
Converting JSON to Excel (XLS/XLSX) can be done easily using programming languages like Python with libraries such as pandas and openpyxl, or by using JavaScript with libraries like json2xls. You can also use online tools for a quick conversion. If the JSON is nested, you may need to flatten it before conversion. The resulting Excel file is ready for analysis, presentation, and further processing in Excel.