Skip to main content

generate excel by json

Project description

json2xls:Generate Excel by JSON data
====================================

[![](https://badge.fury.io/py/json2xls.png)](http://badge.fury.io/py/json2xls)
[![](https://pypip.in/d/json2xls/badge.png)](https://pypi.python.org/pypi/json2xls)

_ ____ _
(_)___ ___ _ __ |___ \__ _| |___
| / __|/ _ \| '_ \ __) \ \/ / / __|
| \__ \ (_) | | | |/ __/ > <| \__ \
_/ |___/\___/|_| |_|_____/_/\_\_|___/
|__/

**docs**

<http://json2xls.readthedocs.org/en/latest/>

**install**

pip install json2xls

or

python setup.py install

**generate excel by json string or json file**

code:

:::python
from json2xls import Json2Xls

json_data = '{"name": "ashin", "age": 16, "sex": "male"}'
Json2Xls('test.xls', json_data).make()

command:

python json2xls.py test.xls '{"a":"a", "b":"b"}'
python json2xls.py test.xls '[{"a":"a", "b":"b"},{"a":1, "b":2}]'

# from file: whole file is a complete json data
python json2xls.py test.xls "`cat tests/data.json`"

# from file: each line is a json data
python json2xls.py test.xls tests/data2.json

excel:

age | name | sex
----|------|----
30 | John | male
18 | Alice| female


**generate excel by request the url which return json data**

default request method is `get`, request argument pass by `params`.
and the `post` method's request argument pass by `data`, you can use `-d` to pass request data in command line, the data should be json or file


code:

:::python
from json2xls import Json2Xls

url = 'http://api.bosonnlp.com/sentiment/analysis'
Json2Xls('test.xlsx', url, method='post').make()

command:

python json2xls.py test.xls http://api.bosonnlp.com/sentiment/analysis
python json2xls.py test.xls http://api.bosonnlp.com/ner/analysis -m post -d '"我是傻逼"' -h "{'X-Token': 'bosontokenheader'}"

excel:

status | message
-------|--------
403 | no token header

**Custom title and body**

default only support one layer json to generate the excel, the nested json will be flattened. if you want custom it,
you can write the `title_callback` function and `body_callback` function, the pass them in the `make` function.
for the `body_callback`, you just need to care one line data's write way.

example:

:::python
#!/usr/bin/env python
#-*- coding:utf-8 -*-
import json
import xlwt
from json2xls import Json2Xls


def title_callback(self, data):
titles = [
u'title', u'text', u'forum', u'author.model',
u'author.verified_owner', u'linked',
u'linked.title', u'linked.forum'
]
for col, name in enumerate(titles):
try:
width = self.sheet.col(col).width
new_width = (len(name) + 1) * 256
self.sheet.col(col).width = width if width > new_width else new_width
except:
pass
self.sheet.row(0).write(col, name, self.title_style)
self.start_row += 1

def body_callback(self, data):
values = []
values.append(data['input']['meta']['title']['text'])
values.append(data['input']['text'])
values.append(data['input']['meta']['forum']['text'])
values.append(data['input']['meta']['author']['model'])
values.append(str(data['input']['meta']['author']['verified_owner']))
values.append('\n'.join(json.dumps(x, ensure_ascii=False)
for x in data['output']['linked']))
values.append('\n'.join(json.dumps(x, ensure_ascii=False)
for x in data['output']['meta']['title']['linked']))
values.append('\n'.join(json.dumps(x, ensure_ascii=False)
for x in data['output']['meta']['forum']['linked']))

for col, value in enumerate(values):
try:
self.sheet.row(self.start_row).height_mismatch = True
self.sheet.row(self.start_row).height = 0
width = self.sheet.col(col).width
new_width = (len(value) + 1) * 256
self.sheet.col(col).width = width if width > new_width else new_width
except:
pass
self.sheet.row(self.start_row).write(col, value, xlwt.easyxf('align: wrap on;'))
self.start_row += 1

j = Json2Xls('tests/normalization.xls', 'tests/normalization.json')
j.make(title_callback=title_callback, body_callback=body_callback)

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Filename, size & hash SHA256 hash help File type Python version Upload date
json2xls-0.1.2-py2.7.egg (13.5 kB) Copy SHA256 hash SHA256 Egg 2.7
json2xls-0.1.2-py2.py3-none-any.whl (10.7 kB) Copy SHA256 hash SHA256 Wheel py2.py3
json2xls-0.1.2.tar.gz (10.9 kB) Copy SHA256 hash SHA256 Source None

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN SignalFx SignalFx Supporter DigiCert DigiCert EV certificate StatusPage StatusPage Status page