Created
February 7, 2020 01:20
-
-
Save patmandenver/98dec9b0825fcafed1b0c8521bea0508 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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