I have a need to create
xls files from json files then take the xls file and convert them back into json
files.
The json files need to
have some consistent formatting so I can diff them between changes.
Simple test
Let me create a simple json file that represents a budget
|
{
"January":
{
"food":
240.5,
"heating":
89.2,
"rent":
1709.10
},
"February":
{
"food":
202.5,
"heating":
112.2,
"rent":
1709.10
},
"March":
{
"food":
320.5,
"heating":
45.2,
"rent":
1709.10
}
}
|
To confirm it is in the correct format.
Simple python script to create xls file from json
|
> vi createxls_from_json.py
|
And place the following in it
|
#!/usr/bin/env python3
import openpyxl
import json
from openpyxl import Workbook
#############################################
# MAIN
#############################################
if __name__ == '__main__':
json_data = {}
with
open("original.json") as json_file:
json_data =
json.load(json_file)
wb = Workbook()
# grab the active
worksheet
ws_01 = wb.active
#Set the title of
the worksheet
ws_01.title =
"First Sheet"
#Set first row
ws_01.cell(1,1,
"Month")
ws_01.cell(1,2,
"food")
ws_01.cell(1,3,
"heating")
ws_01.cell(1,4,
"rent")
row = 1;
for month in
json_data.keys():
row+=1
ws_01.cell(row,1, month)
ws_01.cell(row,2, float(json_data[month]["food"]))
ws_01.cell(row,3, float(json_data[month]["heating"]))
ws_01.cell(row,4, float(json_data[month]["rent"]))
#Save it in an
Excel fie
wb.save("test_json.xlsx")
|
Also placed as gist on https://gist.github.com/patmandenver/101ca2429a9abdbe8043eaa1d7afee3e
Now chmod it and run it
|
> chmod u+x createxls_from_json.py
> ./createxls_from_json.py
|
Now open it up
Boom
Now let me create another program to convert the xls file
into a json file.
|
> vi
createjson_from_xslx.py
|
And place the following in it
|
#!/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)
|
|
> chmod u+x createjson_from_xslx.py
> ./createjson_from_xslx.py
|
Now use the jq tool to see it
|
> jq . new.json
|
Use diff to see it is identical to the original json
|
> diff
original.json new.json
|
The files are not exactly identical…
But is the json itself a match we can use jq to help us figure this out.
But is the json itself a match we can use jq to help us figure this out.
|
> diff <(jq
-S . original.json) <(jq -S . new.json)
|
And if it returns nothing then it is a match.
Let me change one thing by hand in the new.json and see if
it catches it.
|
> diff <(jq
-S . original.json) <(jq -S . new.json)
|
Yep J
No comments:
Post a Comment