Created
February 8, 2020 19:37
-
-
Save patmandenver/b9412bfbcac7a899237e21a9a5844cc6 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)]) | |
#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
Thanks for the example. You may not have noticed that lines 72-86 are a duplication of lines 57-71.