#!/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)]) | |
#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 | |
ws.column_dimensions[get_column_letter(col)].auto_size = True | |
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("col_width_row_height.xlsx") | |
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("col_width_row_height.xlsx") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
Thanks for the example. You may not have noticed that lines 72-86 are a duplication of lines 57-71.