#!/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", | |
"5": "", | |
"6": "Total" | |
} | |
#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): | |
if header_dict[str(col)]: | |
col_value = data_dict[header_dict[str(col)]] | |
ws.cell(row_num, col, col_value).number_format = fmt_acct | |
#Add the sum cell | |
sum_func = ("=sum(" + get_column_letter(2) + str(row_num) | |
+ ":" + get_column_letter(4) + str(row_num) + ")") | |
ws.cell(row_num, 6, sum_func).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