def invalid_excel_file_path(tmpdir):
test_file_path = tmpdir.join("invalid.xlsx")
workbook = xlsxwriter.Workbook(str(test_file_path))
write_worksheet(workbook.add_worksheet("testsheet1"), table=[
["", "", "", ""],
["", "a", "", "c"],
["", "aa", "ab", ""],
["", "", 1.1, "a"],
])
worksheet = workbook.add_worksheet("testsheet2")
workbook.close()
return str(test_file_path)
python类Workbook()的实例源码
def execute(self, context, filepath, sheets=None, workbook_handler=None):
if filepath is None:
# ???????????
filepath = "/tmp/{}_{}.xlsx".format(
int(time.time()), random.randint(100, 999))
workbook = xlsxwriter.Workbook(filepath)
elif filepath.startswith("memory:"):
output = StringIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
context[filepath[len("memory:"):]] = output
else:
workbook = xlsxwriter.Workbook(filepath)
for sheet in sheets:
sheet(context, workbook)
if workbook_handler:
workbook_handler(workbook)
workbook.close()
return filepath
def open(self, file_name, **keywords):
"""
Open a file for writing
Please note that this writer configure xlsxwriter's BookWriter to use
constant_memory by default.
:param keywords: **default_date_format** control the date time format.
**constant_memory** if true, reduces the memory
footprint when writing large files. Other parameters
can be found in `xlsxwriter's documentation
<http://xlsxwriter.readthedocs.io/workbook.html>`_
"""
keywords.setdefault('default_date_format', 'dd/mm/yy')
keywords.setdefault('constant_memory', True)
BookWriter.open(self, file_name, **keywords)
self._native_book = xlsxwriter.Workbook(
file_name, keywords
)
def download_xlsx(filename, info):
'''Çreate xlsx file for given info and download it '''
output = StringIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
worksheet = workbook.add_worksheet()
row_num = 0
col_num = 0
for row in info:
for grid in row:
worksheet.write(row_num, col_num, grid)
col_num += 1
col_num = 0
row_num += 1
workbook.close()
output.seek(0)
headers = Headers()
headers.set('Content-Disposition', 'attachment', filename=filename)
return Response(output.read(), mimetype='application/vnd.openxmlformats-'
'officedocument.spreadsheetml.sheet', headers=headers)
def __init__(self, output_filename="output.xlsx"):
""" Generate XLSX """
self.workbook = xlsxwriter.Workbook(output_filename)
self.core_worksheet = self.workbook.add_worksheet("Core")
self.core_alteration_worksheet = self.workbook.add_worksheet("Core Alteration")
self.plugins_worksheet = self.workbook.add_worksheet("Plugins")
self.plugins_vulns_worksheet = self.workbook.add_worksheet("Plugins Vulns")
self.plugins_alteration_worksheet = self.workbook.add_worksheet("Plugins Alteration")
self.themes_worksheet = self.workbook.add_worksheet("Themes")
self.themes_vulns_worksheet = self.workbook.add_worksheet("Themes Vulns")
self.themes_alteration_worksheet = self.workbook.add_worksheet("Themes Alteration")
self.generate_heading()
self.generate_formatting(self.workbook)
def save_excel(count_dict, file_name):
book = xlsxwriter.Workbook(r'C:\Users\Administrator\Desktop\%s.xls' % file_name)
tmp = book.add_worksheet()
row_num = len(count_dict)
for i in range(1, row_num):
if i == 1:
tag_pos = 'A%s' % i
tmp.write_row(tag_pos, ['???', '??'])
else:
con_pos = 'A%s' % i
k_v = list(count_dict[i-2])
tmp.write_row(con_pos, k_v)
chart1 = book.add_chart({'type': 'area'})
chart1.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$80',
'values': '=Sheet1!$B$2:$B$80'
})
chart1.set_title({'name': '?????'})
chart1.set_x_axis({'name': '???'})
chart1.set_y_axis({'name': '???/??'})
tmp.insert_chart('C2', chart1, {'x_offset': 25, 'y_offset': 10})
######################################################################################
def module_run(self):
filename = self.options['filename']
# create an new xlsx file
with xlsxwriter.Workbook(filename, {'strings_to_urls': False}) as workbook:
tables = self.get_tables()
# loop through all tables in the database
for table in tables:
# create a worksheet for the table
worksheet = workbook.add_worksheet(table)
# build the data set
rows = [tuple([x[0] for x in self.get_columns(table)])]
rows.extend(self.query('SELECT * FROM "%s"' % (table)))
# write the rows of data to the xlsx file
for r in range(0, len(rows)):
for c in range(0, len(rows[r])):
worksheet.write(r, c, rows[r][c])
self.output('All data written to \'%s\'.' % (filename))
def xls(self):
from xlwt import Workbook
filename = self.filename_front + '.xls'
wb = Workbook(encoding='utf-8')
wb_sheet = wb.add_sheet("sheet1")
for title in self.title_list:
wb_sheet.write(0, self.title_list.index(title), title)
for line in self.query_set :
row_no = (self.query_set.index(line) + 1)
print(line)
col_no = 0
for column in line[1:] :
if col_no == len(line) - 2:
# 2016-10-17 15:21:33.348313+00:00
re.split('\.' , str(column))[0]
wb_sheet.write(row_no, col_no, column)
col_no += 1
# wb.write(?, ?, ??)
return filename
def valid_excel_file_1():
file_path = "valid_underscore.xlsx"
workbook = xlsxwriter.Workbook(str(file_path))
worksheet = workbook.add_worksheet("sheet_a")
table = [
["data", "_data", "da_ta", "data_"],
[1, 0.0, "a", "aaaa"],
[2, 0.1, "b", "bbbb"],
[3, 0.2, "c", "cccc"],
]
for row_idx, row in enumerate(table):
for col_idx, item in enumerate(row):
worksheet.write(row_idx, col_idx, item)
workbook.close()
return str(file_path)
def invalid_excel_file_1():
file_path = "invalid.xlsx"
workbook = xlsxwriter.Workbook(file_path)
worksheet = workbook.add_worksheet("testsheet1")
table = [
["", "", "", ""],
["", "a", "", "c"],
["", "aa", "ab", ""],
["", "", 1.1, "a"],
]
for row_idx, row in enumerate(table):
for col_idx, item in enumerate(row):
worksheet.write(row_idx, col_idx, item)
worksheet = workbook.add_worksheet("testsheet2")
workbook.close()
return file_path
def save_excel(count_dict, file_name):
book = xlsxwriter.Workbook(r'E:\positions\%s.xls' % file_name)
tmp = book.add_worksheet()
row_num = len(count_dict)
for i in range(1, row_num):
if i == 1:
tag_pos = 'A%s' % i
tmp.write_row(tag_pos, ['???', '??'])
else:
con_pos = 'A%s' % i
k_v = list(count_dict[i-2])
tmp.write_row(con_pos, k_v)
chart1 = book.add_chart({'type':'area'})
chart1.add_series({
'name' : '=Sheet1!$B$1',
'categories' : '=Sheet1!$A$2:$A$80',
'values' : '=Sheet1!$B$2:$B$80'
})
chart1.set_title({'name':'?????'})
chart1.set_x_axis({'name': '???'})
chart1.set_y_axis({'name': '??(/?)'})
tmp.insert_chart('C2', chart1, {'x_offset':15, 'y_offset':10})
book.close()
def createXcel():
workbook = xlsxwriter.Workbook('Data.xlsx')
title = workbook.add_format({"bold": True, "align": "center", "font_size": 14})
norm = workbook.add_format({"italic": True, "align": "center"})
worksheet = workbook.add_worksheet()
worksheet.set_column('B:B', 15)
worksheet.set_column('C:C', 15)
worksheet.set_column('D:D', 15)
worksheet.set_column('E:E', 15)
worksheet.write(1, 1, "User", title)
worksheet.write(1, 2, "Drinks Made", title)
worksheet.write(1, 3, "Drinks Drunk", title)
worksheet.write(1, 4, "Ratio", title)
sorteddb = sorted(database.data.items(), key=lambda x: tryDivide('(x[1]["drinks"]/x[1]["made"])', x))
for index, user in enumerate(sorteddb):
worksheet.write(index + 2, 0 , index + 1, norm)
worksheet.write(index + 2, 1 , userInfo(user[0])['name'], norm)
worksheet.write(index + 2, 2 , user[1]["made"], norm)
worksheet.write(index + 2, 3 , user[1]["drinks"], norm)
worksheet.write(index + 2, 4 , str(tryDivide('(x[1]["made"]/x[1]["drinks"])', user)), norm)
worksheet.insert_image('F3', 'KoalaTea.jpg')
workbook.close()
def createXcel():
workbook = xlsxwriter.Workbook('Data.xlsx')
title = workbook.add_format({"bold": True, "align": "center", "font_size": 14})
norm = workbook.add_format({"italic": True, "align": "center"})
worksheet = workbook.add_worksheet()
worksheet.set_column('B:B', 15)
worksheet.set_column('C:C', 15)
worksheet.set_column('D:D', 15)
worksheet.set_column('E:E', 15)
worksheet.write(1, 1, "User", title)
worksheet.write(1, 2, "Drinks Made", title)
worksheet.write(1, 3, "Drinks Drunk", title)
worksheet.write(1, 4, "Ratio", title)
sorteddb = sorted(database.data.items(), key=lambda x: tryDivide('(x[1]["drinks"]/x[1]["made"])', x))
for index, user in enumerate(sorteddb):
worksheet.write(index + 2, 0 , index + 1, norm)
worksheet.write(index + 2, 1 , userInfo(user[0])['name'], norm)
worksheet.write(index + 2, 2 , user[1]["made"], norm)
worksheet.write(index + 2, 3 , user[1]["drinks"], norm)
worksheet.write(index + 2, 4 , str(tryDivide('(x[1]["made"]/x[1]["drinks"])', user)), norm)
worksheet.insert_image('F3', 'KoalaTea.jpg')
workbook.close()
def _generate_xlsx(self):
stream = io.BytesIO()
workbook = xlsxwriter.Workbook(stream, {
'in_memory': True,
'strings_to_formulas': False
})
worksheet = workbook.add_worksheet()
worksheet.write(0, 0, _('Form Field'))
worksheet.write(0, 1, _('Response'))
field_values = self.kwargs.get('field_values')
for rownum, row in enumerate(field_values.items(), start=1):
worksheet.write(rownum, 0, row[0])
worksheet.write(rownum, 1, self._fix_newline_if_string(row[1]))
workbook.close()
return stream.getvalue()
def get(self, request, *args, **kwargs):
response = HttpResponse(
content_type='application/vnd.openxmlformats-officedocument'
'.spreadsheetml.sheet')
response['Content-Disposition'] = \
'attachment; filename="%s"' % self.get_filename()
workbook = xlsxwriter.Workbook(response, {
'in_memory': True,
'strings_to_formulas': False
})
worksheet = workbook.add_worksheet()
for colnum, field in enumerate(self.get_header()):
worksheet.write(0, colnum, field)
for rownum, row in enumerate(self.export_rows(), start=1):
for colnum, field in enumerate(row):
worksheet.write(rownum, colnum, self._clean_field(field))
workbook.close()
return response
def module_run(self):
filename = self.options['filename']
# create an new xlsx file
with xlsxwriter.Workbook(filename, {'strings_to_urls': False}) as workbook:
tables = self.get_tables()
# loop through all tables in the database
for table in tables:
# create a worksheet for the table
worksheet = workbook.add_worksheet(table)
# build the data set
rows = [tuple([x[0] for x in self.get_columns(table)])]
rows.extend(self.query('SELECT * FROM "%s"' % (table)))
# write the rows of data to the xlsx file
for r in range(0, len(rows)):
for c in range(0, len(rows[r])):
worksheet.write(r, c, rows[r][c])
self.output('All data written to \'%s\'.' % (filename))
def save_excel(count_dict, file_name):
book = xlsxwriter.Workbook(r'C:\Users\Administrator\Desktop\%s.xls' % file_name)
tmp = book.add_worksheet()
row_num = len(count_dict)
for i in range(1, row_num):
if i == 1:
tag_pos = 'A%s' % i
tmp.write_row(tag_pos, ['???', '??'])
else:
con_pos = 'A%s' % i
k_v = list(count_dict[i-2])
tmp.write_row(con_pos, k_v)
chart1 = book.add_chart({'type': 'area'})
chart1.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$80',
'values': '=Sheet1!$B$2:$B$80'
})
chart1.set_title({'name': '?????'})
chart1.set_x_axis({'name': '???'})
chart1.set_y_axis({'name': '???/??'})
tmp.insert_chart('C2', chart1, {'x_offset': 25, 'y_offset': 10})
######################################################################################
def dict_to_xlsx(d,fname,headers):
import xlsxwriter
xlsx_file = xlsxwriter.Workbook(fname)
xlsx_worksheet = xlsx_file.add_worksheet()
bold = xlsx_file.add_format({'bold': True})
for i,h in enumerate(headers):
xlsx_worksheet.write(0,i,h,bold)
keys = list(d.keys())
keys.sort()
for i, key in enumerate(keys):
xlsx_worksheet.write(i+1,0,key)
for j in range(len(d[key])):
xlsx_worksheet.write(i+1,j+1,d[key][j])
def get(self, request, *args, **kwargs):
response = HttpResponse(
content_type='application/vnd.openxmlformats-officedocument'
'.spreadsheetml.sheet')
response['Content-Disposition'] = \
'attachment; filename="%s"' % self.get_filename()
workbook = xlsxwriter.Workbook(response, {'in_memory': True})
worksheet = workbook.add_worksheet()
for col, field in enumerate(self.get_header()):
worksheet.write(0, col, field)
for rownum, row in enumerate(self.export_rows(), start=1):
for col, field in enumerate(row):
worksheet.write(rownum, col, field)
workbook.close()
return response
def __enter__(self):
"""Enter from a context manager."""
self.filepath = self.gen_tmpfilename()
self.workbook = xlsxwriter.Workbook(self.filepath)
self.worksheet = self.workbook.add_worksheet()
self.header_format = self.workbook.add_format({'bold': True, 'bg_color': '#C9C9C9'})
self.footer_format = self.workbook.add_format({'bold': True, 'bg_color': '#DDDDDD'})
self.current_row = 0
self.line_widths = {}
self.has_header = False
return self
def runnerCase():
starttime = datetime.now()
init()
suite = unittest.TestSuite()
suite.addTest(ParametrizedTestCase.parametrize(LoginTest))
suite.addTest(ParametrizedTestCase.parametrize(SubmitExecResultTest))
suite.addTest(ParametrizedTestCase.parametrize(SendEmailTest))
unittest.TextTestRunner(verbosity=2).run(suite)
endtime = datetime.now()
print(u"???"+str((endtime - starttime).seconds) + "?")
info = readInfo(PATH("../Log/info.log"))
workbook = xlsxwriter.Workbook('report.xlsx')
worksheet = workbook.add_worksheet("????")
re = OperateReport(wd=workbook)
re.report(worksheet, data=info)
destroy()
def randomxls (path) :
numxls = (randint(2000,5000))
for i in range(10):
name = path + ''.join([random.choice(string.ascii_letters + string.digits) for n in xrange(randint(5,15))]) + ".xlsx"
workbook = xlsxwriter.Workbook(name)
worksheet = workbook.add_worksheet()
numrows = (randint(100,500))
for i in range(numrows):
coord = 'A' + str(i)
textinrow = ''.join([random.choice(string.ascii_letters + string.digits) for n in xrange(randint(5,15))])
worksheet.write(coord , textinrow)
workbook.close()
for i in range(numxls):
dupli = path + ''.join([random.choice(string.ascii_letters + string.digits) for n in xrange(randint(5,15))]) + ".xlsx"
copyfile(name, dupli)
#PDF Files
def __init__(self, public_client, ticker):
self._client = public_client
self._ticker = ticker
self._workbook = xlsxwriter.Workbook(str(self._ticker) + '-HISTORICAL.xlsx')
self._worksheet = self._workbook.add_worksheet()
self._rowCount = 0
self._lastTick = ""
self._lastTickTime = 0
def saveContactFile(memberList):
_data = [(u'??', u'???', u'???', u'???', u'??', u'??', u'??', u'??', ' ')]
for contact in memberList:
if not isPerson(contact):
continue
info = (
removeEmoji(contact['NickName']),
removeEmoji(contact['RemarkName']),
pickScreenName(contact['NickName'], contact['RemarkName']),
contact['Alias'],
convertGender(contact['Sex']),
contact['Province'],
contact['City'],
removeEmoji(contact['Signature']),
formatQuanPin(contact['PYQuanPin'], contact['RemarkPYQuanPin'])
)
_data.append(info)
_data.sort(key=lambda x: x[-1])
_data = [e[:-1] for e in _data]
filename = u'%s_????_%s.xlsx' % (
removeEmoji(w.nickName),
strftime('%Y%m%d-%H%M%S', localtime()),
)
workbook = xlsxwriter.Workbook(filename)
worksheet = workbook.add_worksheet()
row = 0
col = 0
for nickName, remarkName, screenName, alias, gender, province, city, signature in OrderedSet(_data):
worksheet.write(row, col, nickName)
worksheet.write(row, col + 1, remarkName)
worksheet.write(row, col + 2, screenName)
worksheet.write(row, col + 3, alias)
worksheet.write(row, col + 4, gender)
worksheet.write(row, col + 5, province)
worksheet.write(row, col + 6, city)
worksheet.write(row, col + 7, signature)
row += 1
workbook.close()
print 'total: %d' % (row - 1)
return
def saveGroupFile(groups, firends):
friendsUserName = [contact['UserName'] for contact in firends]
for group in groups:
gMemberList = queryGMemberList(group['UserName'])
_data = [(u'??', u'???', u'???', u'????', u'???', u'??', u'??', u'??', u'??')]
for contact in gMemberList:
info = (
removeEmoji(contact['NickName']),
contact['Alias'],
removeEmoji(contact['DisplayName']),
isFriend(friendsUserName, contact['UserName']),
removeEmoji(contact['RemarkName']),
convertGender(contact['Sex']),
contact['Province'],
contact['City'],
removeEmoji(contact['Signature'])
)
_data.append(info)
filename = u'%s_???_%s.xlsx' % (
removeEmoji(group['NickName']),
strftime('%Y%m%d-%H%M%S', localtime()),
)
workbook = xlsxwriter.Workbook(filename)
worksheet = workbook.add_worksheet()
row = 0
col = 0
for nickName, alias, displayName, friend, remarkName, gender, province, city, signature in _data:
worksheet.write(row, col, nickName)
worksheet.write(row, col + 1, alias)
worksheet.write(row, col + 2, displayName)
worksheet.write(row, col + 3, friend)
worksheet.write(row, col + 4, remarkName)
worksheet.write(row, col + 5, gender)
worksheet.write(row, col + 6, province)
worksheet.write(row, col + 7, city)
worksheet.write(row, col + 8, signature)
row += 1
workbook.close()
print '%s: %d' % (removeEmoji(group['NickName']), row - 1)
return
def get_xlsx_export(self, context):
datas = self._get_datas(context)
output = io.StringIO()
export_header = (
self.request.GET.get('export_xlsx_header', 'off') == 'on')
model_name = self.opts.verbose_name
book = xlsxwriter.Workbook(output)
sheet = book.add_worksheet(
u"%s %s" % (_(u'Sheet'), force_text(model_name)))
styles = {'datetime': book.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'}),
'date': book.add_format({'num_format': 'yyyy-mm-dd'}),
'time': book.add_format({'num_format': 'hh:mm:ss'}),
'header': book.add_format({'font': 'name Times New Roman', 'color': 'red', 'bold': 'on', 'num_format': '#,##0.00'}),
'default': book.add_format()}
if not export_header:
datas = datas[1:]
for rowx, row in enumerate(datas):
for colx, value in enumerate(row):
if export_header and rowx == 0:
cell_style = styles['header']
else:
if isinstance(value, datetime.datetime):
cell_style = styles['datetime']
elif isinstance(value, datetime.date):
cell_style = styles['date']
elif isinstance(value, datetime.time):
cell_style = styles['time']
else:
cell_style = styles['default']
sheet.write(rowx, colx, value, cell_style)
book.close()
output.seek(0)
return output.getvalue()
def get_xls_export(self, context):
datas = self._get_datas(context)
output = io.StringIO()
export_header = (
self.request.GET.get('export_xls_header', 'off') == 'on')
model_name = self.opts.verbose_name
book = xlwt.Workbook(encoding='utf8')
sheet = book.add_sheet(
u"%s %s" % (_(u'Sheet'), force_text(model_name)))
styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
'header': xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00'),
'default': xlwt.Style.default_style}
if not export_header:
datas = datas[1:]
for rowx, row in enumerate(datas):
for colx, value in enumerate(row):
if export_header and rowx == 0:
cell_style = styles['header']
else:
if isinstance(value, datetime.datetime):
cell_style = styles['datetime']
elif isinstance(value, datetime.date):
cell_style = styles['date']
elif isinstance(value, datetime.time):
cell_style = styles['time']
else:
cell_style = styles['default']
sheet.write(rowx, colx, value, style=cell_style)
book.save(output)
output.seek(0)
return output.getvalue()
def get_xlsx_export(self, context):
datas = self._get_datas(context)
output = io.BytesIO()
export_header = (
self.request.GET.get('export_xlsx_header', 'off') == 'on')
model_name = self.opts.verbose_name
book = xlsxwriter.Workbook(output)
sheet = book.add_worksheet(
u"%s %s" % (_(u'Sheet'), force_text(model_name)))
styles = {'datetime': book.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'}),
'date': book.add_format({'num_format': 'yyyy-mm-dd'}),
'time': book.add_format({'num_format': 'hh:mm:ss'}),
'header': book.add_format({'font': 'name Times New Roman', 'color': 'red', 'bold': 'on', 'num_format': '#,##0.00'}),
'default': book.add_format()}
if not export_header:
datas = datas[1:]
for rowx, row in enumerate(datas):
for colx, value in enumerate(row):
if export_header and rowx == 0:
cell_style = styles['header']
else:
if isinstance(value, datetime.datetime):
cell_style = styles['datetime']
elif isinstance(value, datetime.date):
cell_style = styles['date']
elif isinstance(value, datetime.time):
cell_style = styles['time']
else:
cell_style = styles['default']
sheet.write(rowx, colx, value, cell_style)
book.close()
output.seek(0)
return output.getvalue()
def get_xls_export(self, context):
datas = self._get_datas(context)
output = io.BytesIO()
export_header = (
self.request.GET.get('export_xls_header', 'off') == 'on')
model_name = self.opts.verbose_name
book = xlwt.Workbook(encoding='utf8')
sheet = book.add_sheet(
u"%s %s" % (_(u'Sheet'), force_text(model_name)))
styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
'header': xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00'),
'default': xlwt.Style.default_style}
if not export_header:
datas = datas[1:]
for rowx, row in enumerate(datas):
for colx, value in enumerate(row):
if export_header and rowx == 0:
cell_style = styles['header']
else:
if isinstance(value, datetime.datetime):
cell_style = styles['datetime']
elif isinstance(value, datetime.date):
cell_style = styles['date']
elif isinstance(value, datetime.time):
cell_style = styles['time']
else:
cell_style = styles['default']
sheet.write(rowx, colx, value, style=cell_style)
book.save(output)
output.seek(0)
return output.getvalue()
def get_xlsx_export(self, context):
datas = self._get_datas(context)
output = StringIO.StringIO()
export_header = (
self.request.GET.get('export_xlsx_header', 'off') == 'on')
model_name = self.opts.verbose_name
book = xlsxwriter.Workbook(output)
sheet = book.add_worksheet(
u"%s %s" % (_(u'Sheet'), force_unicode(model_name)))
styles = {'datetime': book.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'}),
'date': book.add_format({'num_format': 'yyyy-mm-dd'}),
'time': book.add_format({'num_format': 'hh:mm:ss'}),
'header': book.add_format({'font': 'name Times New Roman', 'color': 'red', 'bold': 'on', 'num_format': '#,##0.00'}),
'default': book.add_format()}
if not export_header:
datas = datas[1:]
for rowx, row in enumerate(datas):
for colx, value in enumerate(row):
if export_header and rowx == 0:
cell_style = styles['header']
else:
if isinstance(value, datetime.datetime):
cell_style = styles['datetime']
elif isinstance(value, datetime.date):
cell_style = styles['date']
elif isinstance(value, datetime.time):
cell_style = styles['time']
else:
cell_style = styles['default']
sheet.write(rowx, colx, value, cell_style)
book.close()
output.seek(0)
return output.getvalue()