createxls_from_json_multiple_sheets.py

#!/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 row height
    ws.row_dimensions[row_num].height = 35.5

    #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)].width = 16.1
      #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")