Skip to content

Instantly share code, notes, and snippets.

@msuryaprakash
Forked from dmahugh/excel_to_json.py
Created September 13, 2019 11:42
Show Gist options
  • Save msuryaprakash/de2d9c0ac2253b06fdeba551ecbe185f to your computer and use it in GitHub Desktop.
Save msuryaprakash/de2d9c0ac2253b06fdeba551ecbe185f to your computer and use it in GitHub Desktop.
convert XLSX file to JSON
"""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])
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment