How to convert JSON to XLS in Python

Question

Does anyone know how can I convert JSON to XLS in Python?

I know that it is possible to create xls files using the package xlwt in Python.

What if I want convert a JSON data convert to XLS file directly?

Is there a way to archive this?

You need a programming language to map your hierarchical json structure to flat spreadsheet structure. You may want to convert it to csv instead of xls directly as there is plenty examples of that on the web (all spreadsheet editors read csv). — Jarosław Jaryszew, Mar 13 '13 at 07:30
Another answer here: https://stackoverflow.com/a/56315305/1437254 `pip install hfexcel` — Emin Bugra Saral, May 26 '19 at 19:29

score 28 · Answer 1 · answered Apr 12 '16 at 22:23

28

Using pandas (0.15.1) and openpyxl (1.8.6):

import pandas
pandas.read_json("input.json").to_excel("output.xlsx")

answered Apr 12 '16 at 22:23

Bruno Lopes

2,847
1
26
38

3

this command puts all the key:value pairs of a particulat {set} in the same row. – Dr. Essen Jul 16 '20 at 14:40

score 22 · Accepted Answer · answered Mar 13 '13 at 07:34

22

I usually use tablib for this use. Its pretty simple to use: https://pypi.python.org/pypi/tablib/0.9.3

answered Mar 13 '13 at 07:34

GodMan

2,509
2
22
40

Thanks a lot!This is what I looking for! – fakelbst Mar 13 '13 at 07:53
1

please note that this link is to a very old version of tablib (probably newest at the time) and it causes problems with newest versions of python, instead install tablib 3.0.0 version – Silidrone Apr 28 '21 at 11:25

score 3 · Answer 3 · answered Apr 03 '20 at 11:23

3

If your json file is stored in some directory then,

import pandas as pd
pd.read_json("/path/to/json/file").to_excel("output.xlsx")

If you have your json within the code then, you can simply use DataFrame

json_file = {'name':["aparna", "pankaj", "sudhir", "Geeku"],'degree': ["MBA", "BCA", "M.Tech", "MBA"],'score':[90, 40, 80, 98]}
df = pd.DataFrame(json_file).to_excel("excel.xlsx")

answered Apr 03 '20 at 11:23

laplace

488
5
13

I am getting this error ' If using all scalar values, you must pass an index' – Jinna Balu Aug 01 '20 at 17:29

score 0 · Answer 4 · answered Jun 22 '20 at 22:39

In case someone wants to do output to Excel as a stream using Flask-REST

Pandas versions:

json_payload = request.get_json()

with NamedTemporaryFile(suffix='.xlsx') as tmp:

    pandas.DataFrame(json_payload).to_excel(tmp.name)

    buf = BytesIO(tmp.read())

    response = app.make_response(buf.getvalue())
    response.headers['content-type'] = 'application/octet-stream'

    return response

and OpenPyXL version:

keys = []
wb = Workbook()
ws = wb.active

json_data = request.get_json()

with NamedTemporaryFile() as tmp:

    for i in range(len(json_data)):
        sub_obj = json_data[i]
        if i == 0:
            keys = list(sub_obj.keys())
            for k in range(len(keys)):
                ws.cell(row=(i + 1), column=(k + 1), value=keys[k]);
        for j in range(len(keys)):
            ws.cell(row=(i + 2), column=(j + 1), value=sub_obj[keys[j]]);
    wb.save(tmp.name)

    buf = BytesIO(tmp.read())

    response = app.make_response(buf.getvalue())
    response.headers['content-type'] = 'application/octet-stream'

    return response

How to convert JSON to XLS in Python

4 Answers4

Linked