Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
#!/usr/bin/env python3
import openpyxl
import json
from openpyxl import Workbook
from openpyxl.styles import numbers
from openpyxl.utils import get_column_letter
from openpyxl.styles import colors
from openpyxl.styles import Font, Color
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment, NamedStyle
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_);_(@'
#Setting styles
header_style = NamedStyle(name="hearder_style")
header_style.font = Font(name="Times New Roman", bold=True, size=20, color='FF0000')
header_style.alignment = Alignment(horizontal="center", vertical="center")
double = Side(border_style="thick", color="000000")
header_style.border = Border(bottom=double)
#header_style.border = Border(top=double, left=double, right=double, bottom=double)
#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)]).style = header_style
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) + ")")
avg_func = ("=AVERAGE(" + get_column_letter(2) + str(row_num)
+ ":" + get_column_letter(4) + str(row_num) + ")")
ws.cell(row_num, 6, avg_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("format_test.xlsx")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.