Excel To JSON writed in Python 3

import json 

# importing openpyxl module 

#pip install openpyxl

import openpyxl 

  

# Give the location of the file 

path = 'C:\\Users\\<MyUser>\\Path\\TO\\TheFile\\example.xlsx'

# workbook object is created 

wb_obj = openpyxl.load_workbook(path) 

wb_obj.active = 0

sheet_obj = wb_obj.active 

max_col = sheet_obj.max_column 

max_row = sheet_obj.max_row 

#tableHeader = [""]

#print(max_col)

#https://www.w3schools.com/python/python_lists.asp

tableHeader = []

data = {}

# Loop will print all columns name 

for i in range(1, max_col + 1): 

    cell_obj = sheet_obj.cell(row = 1, column = i) 

    tableHeader.insert((i - 1), cell_obj.value)

    #print(cell_obj.value) 

    data[i] = cell_obj.value

    

print(data[1])

dataPosta = {}

#data['key'] = 'value'

dataMaster = {}

#max_row = 2

#max_col = 20

# new test

for y in range(1, max_row + 1): 

    for x in range(1, max_col + 1): 

        cell_obj = sheet_obj.cell(row = y, column = x) 

        #print(cell_obj.value)

        dataPosta[data[x]] = cell_obj.value

    #print("---")

    dataMaster[str(y)] = dataPosta

print(dataPosta)

print("---------------")

print(dataMaster)

"""

j = json.dumps(dataMaster)

# Write to file

with open((path.replace("xlsx", "json")).replace("xls", "json"), "w") as f:

    f.write(j)

"""

with open((path.replace("xlsx", "json")).replace("xls", "json"), "w") as f:

    f.write(json.dumps(dataMaster, sort_keys=True, indent=2,  separators=(',', ": "), ensure_ascii=False))

    ##https://stackoverflow.com/questions/25894039/python-dump-json-with-accents

    f.close()

    print("%s was created" %f.name)

"""

output = \

open((path.replace("xlsx", "json")).replace("xls", "json"), "w")

output.write(json.dumps(dataMaster, sort_keys=True, indent=2,  separators=(',', ": "), ensure_ascii=False))

##https://stackoverflow.com/questions/25894039/python-dump-json-with-accents

output.close()

print("%s was created" %output.name)

"""

Other Way:

path = 'C:\\Users\\<MyUser>\\Path\\TO\\TheFile\\example.xlsx'

import json

import sys

import xlrd

workbook = xlrd.open_workbook(path)

print(workbook.sheet_names())

worksheet = workbook.sheet_by_index(0)

data = []

keys = [v.value for v in worksheet.row(0)]

for row_number in range(worksheet.nrows):

    if row_number == 0:

        continue

    row_data = {}

    for col_number, cell in enumerate(worksheet.row(row_number)):

        row_data[keys[col_number]] = cell.value

    data.append(row_data)

def getTime():

    import datetime

    x = datetime.datetime.now()

    xs = x.strftime("%X %x")

    datestring = str(xs)

    datestring = datestring.replace(":","-")

    datestring = datestring.replace("/", "-")

    datestring = datestring.replace(" ", "--")

    print(datestring)

    return datestring

with open((path.replace(".xlsx", getTime()+".json")).replace(".xls", getTime()+".json"), 'w') as json_file:

    json_file.write(json.dumps({'data': data}, sort_keys=True, indent=2,  separators=(',', ": "), ensure_ascii=False))

    print("%s was created" %json_file.name)

    json_file.close()