Created
February 8, 2020 21:04
-
-
Save patmandenver/0bf2247916e3cde9e19d7e8511675a62 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 Workbook | |
from openpyxl.styles import numbers | |
from openpyxl.utils import get_column_letter | |
header_dict = { "1": "Month", | |
"2": "food", | |
"3": "heating", | |
"4": "rent", | |
} | |
#Accounting format | |
fmt_acct = u'_($* #,##0.00_);[Red]_($* (#,##0.00);_($* -_0_0_);_(@' | |
#Populate the headers | |
def populate_header_row(ws): | |
for col in range(1, len(header_dict)+1): | |
ws.cell(1,col, header_dict[str(col)]) | |
ws.column_dimensions[get_column_letter(col)].auto_size = True | |
#Populate the rows | |
def populate_row(row_num, data_dict, ws): | |
#Set month column | |
ws.cell(row_num,1,data_dict["Month"]) | |
#Set the number format for each $ cell | |
for col in range(2,len(data_dict)+1): | |
col_value = data_dict[header_dict[str(col)]] | |
ws.cell(row_num, col, col_value).number_format = fmt_acct | |
def populate_sheet(json_data, ws): | |
populate_header_row(ws) | |
row_num = 1 | |
for month in json_data.keys(): | |
row_num+=1 | |
data_dict = json_data[month] | |
data_dict["Month"] = month | |
populate_row(row_num, data_dict, ws) | |
############################################# | |
# MAIN | |
############################################# | |
if __name__ == '__main__': | |
json_data = {} | |
with open("original.json") as json_file: | |
json_data = json.load(json_file) | |
wb = Workbook() | |
#When you make a new workbook you get a new blank active sheet | |
#We need to delete it since we do not want it | |
wb.remove(wb.active) | |
for year in json_data.keys(): | |
sheet = wb.create_sheet(title=year) | |
populate_sheet(json_data[year], sheet) | |
#Save it to excel | |
wb.save("functions_test.xlsx") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment