createjson_from_xslx.py

#!/usr/bin/env python3

import openpyxl
import json
from openpyxl import load_workbook

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

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

  ws = wb["First Sheet"]

  budget_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):
    #Store months out
    month_cell = row[0]
    temp_cell_dict = {}
    #Skip the month cell
    for cell in row[1:]:
      #Store the data in dict with key as column name
      col_name = column_headers[cell.column]
      temp_cell_dict[col_name] = cell.internal_value

    #Set month key to be contents of row
    budget_dict[month_cell.internal_value] = temp_cell_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)