Python program to convert an .xlsx file to JSON using the openpyxl library

Here’s a Python program to convert an .xlsx file to JSON using the openpyxl library. Make sure you have the openpyxl library installed. If you don't have it yet, install it using:

pip install openpyxl

Program to Convert XLSX to JSON

import openpyxl
import json

def xlsx_to_json(file_path, sheet_name=None):
    try:
        # Load the workbook and select a sheet
        workbook = openpyxl.load_workbook(file_path)
        sheet = workbook[sheet_name] if sheet_name else workbook.active

        # Extract data from the sheet
        data = []
        headers = [cell.value for cell in next(sheet.iter_rows(min_row=1, max_row=1))]

        for row in sheet.iter_rows(min_row=2, values_only=True):
            entry = {headers[i]: row[i] for i in range(len(headers))}
            data.append(entry)

        # Convert the data to JSON format
        json_data = json.dumps(data, indent=4)

        # Save the JSON to a file
        json_file = file_path.replace('.xlsx', '.json')
        with open(json_file, 'w') as f:
            f.write(json_data)

        print(f"Successfully converted '{file_path}' to '{json_file}'")

    except Exception as e:
        print(f"Error: {e}")

# Example usage
xlsx_to_json('example.xlsx')

Explanation

1. Loading the Workbook:
   We load the .xlsx file using openpyxl.load_workbook.

2. Selecting the Sheet:

   • If a sheet_name is provided, it selects that sheet.
   • If not, it selects the active sheet by default.

3. Reading the Data:

   • It extracts the first row as headers.
   • It iterates over the rest of the rows, creating dictionaries with headers as keys and cell values as values.

4. Converting to JSON:
   It uses json.dumps() to convert the list of dictionaries into a JSON string.

5. Saving the JSON:
   The JSON is saved to a file with the same name but with a .json extension.

Example Input and Output

Input (example.xlsx):

Name	Age	City
Alice	25	New York
Bob	30	San Diego

Output (example.json):

[
    {
        "Name": "Alice",
        "Age": 25,
        "City": "New York"
    },
    {
        "Name": "Bob",
        "Age": 30,
        "City": "San Diego"
    }
]

Run the Program

Make sure to replace 'example.xlsx' with the path to your .xlsx file. This program will convert it to a JSON file in the same directory.

Let me know if you encounter any issues!