_ ____ _
(_)___ ___ _ __ |___ \__ _| |___
| / __|/ _ \| '_ \ __) \ \/ / / __|
| \__ \ (_) | | | |/ __/ > <| \__ \
_/ |___/\___/|_| |_|_____/_/\_\_|___/
|__/
generate excel by json string or json file or url which return a json
testing on python2.7 and python3.6.0
docs http://json2xls.readthedocs.org/en/latest/
install
pip install json2xls
or
python setup.py install
json2xls cmd_str_test.xls '{"a":"a", "b":"b"}'
json2xls cmd_str_test1.xls '[{"a":"a", "b":"b"},{"a":1, "b":2}]'
json2xls cmd_list_test.xls "`cat tests/list_data.json`"
json2xls cmd_line_test.xls tests/line_data.json
json2xls cmd_get_test.xls http://httpbin.org/get
json2xls cmd_post_test.xls http://httpbin.org/post -m post -d '"hello json2xls"' -h "{'X-Token': 'bolobolomi'}"
#!/usr/bin/env python
#-*- coding:utf-8 -*-
from json2xls.json2xls import Json2Xls
json_data = u'''[
{"姓名": "John", "年龄": 30, "性别": "男"},
{"姓名": "Alice", "年龄": 18, "性别": "女"}
]'''
obj = Json2Xls('json_strlist_test.xls', json_data)
obj.make()
params = {
'location': u'上海',
'output': 'json',
'ak': '5slgyqGDENN7Sy7pw29IUvrZ'
}
Json2Xls('url_get_test.xls',
"http://httpbin.org/get",
params=params).make()
post_data = {
'location': u'上海',
'output': 'json',
'ak': '5slgyqGDENN7Sy7pw29IUvrZ'
}
Json2Xls('url_post_test1.xls',
"http://httpbin.org/post",
method='post',
post_data=post_data,
form_encoded=True).make()
post_data = 'tests/post_data.json'
Json2Xls('url_post_test2.xls',
"http://httpbin.org/post",
method='post',
post_data=post_data,
form_encoded=True).make()
Json2Xls('json_list_test.xls', json_data='tests/list_data.json').make()
obj = Json2Xls('json_line_test.xls', json_data='tests/line_data.json')
obj.make()
def title_callback(self, data):
'''use one of data record to generate excel title'''
self.sheet.write_merge(0, 0, 0, 3, 'title', self.title_style)
self.sheet.write_merge(1, 2, 0, 0, 'tag', self.title_style)
self.sheet.write_merge(1, 2, 1, 1, 'ner', self.title_style)
self.sheet.write_merge(1, 1, 2, 3, 'comment', self.title_style)
self.sheet.row(2).write(2, 'x', self.title_style)
self.sheet.row(2).write(3, 'y', self.title_style)
self.sheet.write_merge(0, 0, 4, 7, 'body', self.title_style)
self.sheet.write_merge(1, 2, 4, 4, 'tag', self.title_style)
self.sheet.write_merge(1, 2, 5, 5, 'ner', self.title_style)
self.sheet.write_merge(1, 1, 6, 7, 'comment', self.title_style)
self.sheet.row(2).write(6, 'x', self.title_style)
self.sheet.row(2).write(7, 'y', self.title_style)
self.start_row = 3
def body_callback(self, data):
key1 = ['title', 'body']
key2 = ['tag', 'ner', 'comment']
col = 0
for ii, i in enumerate(key1):
for ij, j in enumerate(key2):
if j != 'comment':
value = ', '.join(data[ii][i][j])
self.sheet.row(self.start_row).write(col, value)
col = 1
else:
for x in data[ii][i][j].values():
self.sheet.row(self.start_row).write(col, x)
col = 1
self.start_row = 1
data = 'tests/callback_data.json'
j = Json2Xls('tests/callback.xls', data)
j.make(title_callback=title_callback, body_callback=body_callback)
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
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, json2xls default think the data are all the same.
The test demo data file is in tests dir. and demo.py is all coding example to gen xls
