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 to convert a JSON data file 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 31 · Answer 1 · answered Apr 12 '16 at 22:23

31

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,917
1
27
38

4

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 · edited Jan 19 '23 at 05:40

22

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

edited Jan 19 '23 at 05:40

Michael M.

10,486
9
18
34

answered Mar 13 '13 at 07:34

GodMan

2,561
2
24
40

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 6 · Answer 3 · answered Apr 03 '20 at 11:23

6

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

656
7
15

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

score 2 · 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

score 0 · Answer 5 · edited Jul 12 '23 at 23:27

0

If you want to convert any JSON (.json) file into Microsoft Excel you can try the below code snippet. And you are getting Value error: trailing data

Example: your file name is input.json.

import pandas as pd
pd.read_json(“input.json”, lines = True).to_excel(“output.xlsx”)

output.xlsx will be your required file

edited Jul 12 '23 at 23:27

Osadhi Virochana

1,294
2
11
21

answered Jul 11 '23 at 07:07

Fit_coder

1

How to convert JSON to XLS in Python

5 Answers5

Linked