createjson_from_xslx_multiple_sheet.py

#!/usr/bin/env python3

import openpyxl
import json
from openpyxl import load_workbook


def get_sheet_dict(ws):
  year_dict = {}
  column_headers = {}

  #Returns a Tuple that are cell type
  first_row = ws[1]

  #Skip the first column
  for cell in first_row[1:]:
    column_headers[cell.column]=cell.internal_value

  for row in ws.iter_rows(min_row=2):
    month = row[0].internal_value
    temp_cell_dict = {}
    #Skip the month cell
    for cell in row[1:]:
      #store the data in a dict with keys as column names
      col_name = column_headers[cell.column]
      temp_cell_dict[col_name]=cell.internal_value

    #Set the month key to be contents of row
    year_dict[month] = temp_cell_dict

  return year_dict

#############################################
#  MAIN
#############################################
if __name__ == '__main__':

   wb = load_workbook(filename="test_json.xlsx")

   budget_dict = {}

   for ws in wb:
     year_dict = get_sheet_dict(ws)
     budget_dict[ws.title] = year_dict




   #Convert dict to JSON
   data_json = json.dumps(budget_dict)

   #Write json file
   with open("new.json", "w") as f:
     f.write(data_json)