Skip to content

Instantly share code, notes, and snippets.

@patmandenver
Created February 8, 2020 19:37
Show Gist options
  • Save patmandenver/b9412bfbcac7a899237e21a9a5844cc6 to your computer and use it in GitHub Desktop.
Save patmandenver/b9412bfbcac7a899237e21a9a5844cc6 to your computer and use it in GitHub Desktop.
#!/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")
@Waitak
Copy link

Waitak commented Nov 6, 2020

Thanks for the example. You may not have noticed that lines 72-86 are a duplication of lines 57-71.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment