Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
#!/usr/bin/env python3
import openpyxl
import json
from openpyxl import load_workbook
def get_sheet_dict(ws):
year_dict = {}
column_headers = {}
#Returns a Tuple that are cell type
first_row = ws[1]
#Skip the first column
for cell in first_row[1:]:
column_headers[cell.column]=cell.internal_value
for row in ws.iter_rows(min_row=2):
month = row[0].internal_value
temp_cell_dict = {}
#Skip the month cell
for cell in row[1:]:
#store the data in a dict with keys as column names
col_name = column_headers[cell.column]
temp_cell_dict[col_name]=cell.internal_value
#Set the month key to be contents of row
year_dict[month] = temp_cell_dict
return year_dict
#############################################
# MAIN
#############################################
if __name__ == '__main__':
wb = load_workbook(filename="test_json.xlsx")
budget_dict = {}
for ws in wb:
year_dict = get_sheet_dict(ws)
budget_dict[ws.title] = year_dict
#Convert dict to JSON
data_json = json.dumps(budget_dict)
#Write json file
with open("new.json", "w") as f:
f.write(data_json)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.