convert XLSX file to JSON

excel_to_json.py

"""Example of how to convert an xlsx file to JSON.

Requirements:
- Python 3.7 or higher
- openpyxl (pip install openpyxl)

Assumptions:
- the active worksheet contains a rectangular array of data,
  with column names in the first row
- the data fits in memory (makes the code below a bit simpler)

Usage:
py -3.7 excel_to_json.py filename
"""
import json
from pathlib import Path
import sys

from openpyxl import load_workbook

def main(xlsx_file):
    """Read XLSX file and write it to JSON file in same folder.
    """
    workbook = load_workbook(filename=xlsx_file, read_only=True)
    worksheet = workbook.active

    excel_data = list(worksheet.rows)
    column_names = [column.value for column in excel_data[0]]

    json_output = []
    for row in excel_data[1:]:
        values = [cell.value for cell in row]
        row_dict = {name: str(value) for name, value in zip(column_names, values)}
        json_output.append(row_dict)

    output_file = Path(xlsx_file).with_suffix(".json")
    with open(output_file, "w") as fhandle:
        fhandle.write(json.dumps(json_output))

if __name__ == "__main__":
    main(sys.argv[1])