def xlsx(self , request):
from xlsxwriter import Workbook
filename = self.filename_front + '.xlsx'
wb = Workbook(filename)
# wb.encoding = 'gbk'
wb_sheet = wb.add_worksheet()
wb_sheet.write_row('A1' , self.title_list)
for line in self.query_set :
row_no = (self.query_set.index(line) + 2)
wb_sheet.write_row('A' + str(row_no) , line[1:-1])
wb_sheet.save
wb_sheet.close()
return filename
class ExcelXWriter:
def __init__(self, path):
self.path = path
self.book = Workbook(path)
self.sheet = self.book.add_worksheet()
self.column_to_fill = 0
def write_col(self, column_name, column_content):
row = 0
self.sheet.write(row, self.column_to_fill, column_name)
row += 1
for x in column_content:
self.sheet.write(row, self.column_to_fill, x)
row += 1
self.column_to_fill += 1
# write_note(self, note):
def save(self):
self.book.close()
class Json2Xls(object):
"""Json2Xls API 接口
:param string xls_filename: 指定需要生成的的excel的文件名
:param string json_data: 指定json数据来源，
可以是一个返回json的url，
也可以是一行json字符串，
也可以是一个包含每行一个json的文本文件
:param string method: 当数据来源为url时，请求url的方法，
默认为get请求
:param dict params: get请求参数，默认为 :py:class:`None`
:param dict post_data: post请求参数，默认为 :py:class:`None`
:param dict headers: 请求url时的HTTP头信息 (字典、json或文件)
:param bool form_encoded: post请求时是否作为表单请求，默认为 :py:class:`False`
:param string sheet_name: Excel的sheet名称，默认为 sheet0
:param string title_style: Excel的表头样式，默认为 :py:class:`None`
:param function json_dumps: 带ensure_ascii参数的json.dumps()，
默认参数值为 :py:class:`False`
:param function json_loads: 带object_pairs_hook参数的json.loads()，默认为保持原始排序
:param bool dumps: 生成excel时对表格内容执行json_dumps，默认为 :py:class:`False`
"""
def __init__(self,
xls_filename,
json_data,
method='get',
params=None,
post_data=None,
headers=None,
form_encoded=False,
dumps=False,
sheet_name='sheet0',
title_style=None):
self.json_dumps = partial(json.dumps, ensure_ascii=False)
self.json_loads = partial(json.loads, object_pairs_hook=OrderedDict)
self.sheet_name = sheet_name
self.xls_filename = xls_filename
self.json_data = json_data
self.method = method
self.params = params
self.post_data = post_data
self.headers = headers
self.form_encoded = form_encoded
self.dumps = dumps
self.__check_file_suffix()
# self.book = Workbook(encoding='utf-8', style_compression=2)
# self.sheet = self.book.add_sheet(self.sheet_name)
self.start_row = 0
# self.title_style = xlwt.easyxf(
# title_style or 'font: name Arial, bold on;'
# 'align: vert centre, horiz center;'
# 'borders: top 1, bottom 1, left 1, right 1;'
# 'pattern: pattern solid, fore_colour lime;')
def __sheet_write(self, row, col, value):
pass
def __check_file_suffix(self):
suffix = self.xls_filename.split('.')[-1]
self.excel_type = suffix
if '.' not in self.xls_filename:
self.xls_filename += '.xls'
elif suffix == 'xls':
self.book = Workbook(encoding='utf-8', style_compression=2)
self.sheet = self.book.add_sheet(self.sheet_name)
self.start_row = 0
# self.title_style = xlwt.easyxf(
# title_style or 'font: name Arial, bold on;'
# 'align: vert centre, horiz center;'
# 'borders: top 1, bottom 1, left 1, right 1;'
# 'pattern: pattern solid, fore_colour lime;')
elif suffix == 'xlsx':
self.book = xlsxwriter.Workbook(self.xls_filename)
self.sheet = self.book.add_worksheet(self.sheet_name)
self.start_row = 0
# self.title_style = xlwt.easyxf(
# title_style or 'font: name Arial, bold on;'
# 'align: vert centre, horiz center;'
# 'borders: top 1, bottom 1, left 1, right 1;'
# 'pattern: pattern solid, fore_colour lime;')
else:
raise Exception('filename suffix must be .xls')
def __get_json(self):
data = None
try:
# with open(self.json_data, encoding='utf-8') as f:
data = self.json_loads(self.json_data)
except Exception:
if os.path.isfile(self.json_data):
with open(self.json_data, 'r', encoding='utf-8') as source:
try:
data = self.json_loads(source.read().replace('\n', ''))
except Exception:
source.seek(0)
data = [self.json_loads(line) for line in source]
else:
if self.headers and os.path.isfile(self.headers):
with open(self.headers) as headers_txt:
self.headers = self.json_loads(
headers_txt.read().decode('utf-8').replace(
'\n', ''))
elif isinstance(self.headers, ("".__class__, u"".__class__)):
self.headers = self.json_loads(self.headers)
try:
if self.method.lower() == 'get':
resp = requests.get(self.json_data,
params=self.params,
headers=self.headers)
data = resp.json()
else:
if isinstance(
self.post_data,
("".__class__, u"".__class__)) and os.path.isfile(
self.post_data):
with open(self.post_data, 'r',
encoding='utf-8') as source:
self.post_data = self.json_loads(
source.read().replace('\n', ''))
if not self.form_encoded:
self.post_data = self.json_dumps(self.post_data)
resp = requests.post(self.json_data,
data=self.post_data,
headers=self.headers)
data = resp.json()
except Exception as e:
print(e)
return data
def __fill_title(self, data):
'''生成默认title'''
data = self.flatten(data)
for index, key in enumerate(data.keys()):
if self.dumps:
key = self.json_dumps(key)
try:
self.sheet.col(index).width = (len(key) + 1) * 256
except Exception:
pass
if self.excel_type == "xls":
self.sheet.row(self.start_row).write(index,
key.decode('utf-8'),
self.title_style)
if self.excel_type == "xlsx":
self.sheet.write(self.start_row, index, key.decode('utf-8'))
self.start_row += 1
def __fill_data(self, data):
'''生成默认sheet'''
data = self.flatten(data)
for index, value in enumerate(data.values()):
if self.dumps:
value = self.json_dumps(value)
self.auto_width(self.start_row, index, value)
if self.excel_type == "xls":
self.sheet.row(self.start_row).write(index, value)
if self.excel_type == "xlsx":
self.sheet.write(self.start_row, index, value)
self.start_row += 1
def auto_width(self, row, col, value):
'''单元格宽度自动伸缩
:param int row: 单元格所在行下标
:param int col: 单元格所在列下标
:param int value: 单元格中的内容
'''
try:
self.sheet.row(row).height_mismatch = True
# self.sheet.row(row).height = 0
width = self.sheet.col(col).width
new_width = min((len(value) + 1) * 256, 256 * 50)
self.sheet.col(col).width = width \
if width > new_width else new_width
except Exception:
pass
def flatten(self, data_dict, parent_key='', sep='.'):
'''对套嵌的dict进行flatten处理为单层dict
:param dict data_dict: 需要处理的dict数据。
:param str parent_key: 上层字典的key，默认为空字符串。
:param str sep: 套嵌key flatten后的分割符， 默认为“.” 。
'''
out = {}
def _flatten(x, parent_key, sep):
if isinstance(x, collections.MutableMapping):
for a in x:
_flatten(x[a], parent_key + a + sep, sep)
elif isinstance(x, collections.MutableSequence):
i = 0
for a in x:
_flatten(a, parent_key + str(i) + sep, sep)
i += 1
else:
if not isinstance(x, ("".__class__, u"".__class__)):
x = str(x)
out[parent_key[:-1].encode('utf-8')] = x
_flatten(data_dict, parent_key, sep)
return OrderedDict(out)
def make(self, title_callback=None, body_callback=None):
'''生成Excel。
:param func title_callback: 自定义生成Execl表头的回调函数。
默认为 :py:class:`None`，即采用默认方法生成
:param func body_callback: 自定义生成Execl内容的回调函数。
默认为 :py:class:`None`，即采用默认方法生成
'''
data = self.__get_json()
if not isinstance(data, (dict, list)):
raise Exception('The %s is not a valid json format' % type(data))
if isinstance(data, dict):
data = [data]
if title_callback is not None:
title_callback(self, data[0])
else:
self.__fill_title(data[0])
if body_callback is not None:
for d in data:
body_callback(self, d)
else:
for d in data:
self.__fill_data(d)
if self.excel_type == "xls":
self.book.save(self.xls_filename)
if self.excel_type == "xlsx":
self.book.close()
