def get_xls_export(self, context):
datas = self._get_datas(context)
output = StringIO.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_unicode(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()
python类Workbook()的实例源码
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 __writexls__(self, filename="output.xlsx", outputdict={}):
if outputdict is None:
raise ValueError('An output argument was expected')
if not isinstance(outputdict, dict):
raise TypeError('Was expecting a dictionary for outputdict but got a %s' % type(dict))
extension = '.xlsx'
if not filename.endswith(extension):
filename += extension
#TODO check location and filename are valid
workbook = xlsxwriter.Workbook(filename)
for sheet, grid in sorted(outputdict.items()):
worksheet = workbook.add_worksheet(name=sheet[:32]) #worksheet name cannot be more than 32 chars long
if not isinstance(grid, Matrix):
raise TypeError('Expected a Matrix but got a %s' % type(grid))
for i, row in enumerate(grid):
worksheet.write_row(i, 0, row)
workbook.close()
return True
def ExportResults(data,cms,export):
#var's of excel
# Start from the first cell. Rows and columns are zero indexed.
row = 0
col = 0
i = 0
#json
if (export == 1):
print "Exporting the results in a json"
for domain in data:
for j in cms:
filename = "output_domains" + code + ".json"
with open(filename, 'w') as f:
json.dump(domain,j, f)
#excel
if (export ==2):
print "Exporting the results in an excel"
# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('Status_domains.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write(row, col, "Domain")
worksheet.write(row, col+1, "cms")
row += 1
# Iterate over the data and write it out row by row.
for domain in data:
col = 0
worksheet.write(row, col, domain)
worksheet.write(row, col+1, cms[i])
row += 1
i += 1
#close the excel
workbook.close()
def __init__(self, filename):
self.workbook = xlsxwriter.Workbook(filename)
def __init__(self, filename):
self.__fileName = filename
try:
xlrd.open_workbook(filename)
except:
work_book = xlwt.Workbook()
work_sheet = work_book.add_sheet("Sheet1")
work_book.save(filename)
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 save_contacts_info(self):
import xlsxwriter
from PIL import Image
from io import BytesIO
workbook = xlsxwriter.Workbook(os.path.join(self.temp_pwd,'contact_list.xlsx')) #?????
'''
?????sheet:
sheet1
'''
sheet1 = workbook.add_worksheet() #??sheet
row0 = [u'NickName', u'HeadImgUrl', u'RemarkName']
sheet1.set_column('A:C', 18)
#?????
for i in range(0,len(row0)):
sheet1.write(0,i,row0[i])
for i in range(0, len(self.contact_list)):
try:
sheet1.set_row(i+1, 100)
sheet1.write(i+1,0,self.contact_list[i]['NickName'])
# ???????https://wx2.qq.com
url ="https://wx.qq.com" + self.contact_list[i]['HeadImgUrl']
print(url)
r = self.session.get(url)
pic = Image.open(BytesIO(r.content))
pic.save(os.path.join(self.temp_pwd, 'img', self.contact_list[i]['NickName']+'.jpg'))
#sheet1.write(i+1,1,self.contact_list[i]['HeadImgUrl'])
sheet1.insert_image(i+1,1,os.path.join(self.temp_pwd, 'img', self.contact_list[i]['NickName']+'.jpg'))
sheet1.write(i+1,2,self.contact_list[i]['RemarkName'])
except Exception as e:
print(e)
workbook.close()
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()
def get_xls_export(self, context):
datas = self._get_datas(context)
output = StringIO.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_unicode(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 make_xlsx(rows):
tmp_path = tempfile.mktemp()
workbook = xlsxwriter.Workbook(tmp_path, {'strings_to_numbers': False,
'strings_to_formulas': False,
'strings_to_urls': False})
worksheet = workbook.add_worksheet()
worksheet.write_row(0, 0, rows.headers)
for i, row in enumerate(rows):
worksheet.write_row(i + 1, 0, row)
workbook.close()
contents = open(tmp_path, "rb").read()
os.remove(tmp_path)
return contents
def writeexcel(path, dealcontent):
workbook = wx.Workbook(path)
top = workbook.add_format(
{'border': 1, 'align': 'center', 'bg_color': 'white', 'font_size': 11, 'font_name': '????'})
red = workbook.add_format(
{'font_color': 'white', 'border': 1, 'align': 'center', 'bg_color': '800000', 'font_size': 11,
'font_name': '????', 'bold': True})
image = workbook.add_format(
{'border': 1, 'align': 'center', 'bg_color': 'white', 'font_size': 11, 'font_name': '????'})
formatt = top
formatt.set_align('vcenter') # ?????????
worksheet = workbook.add_worksheet() # ?????????
width = len(dealcontent[0])
worksheet.set_column(0, width, 38.5) # ???????22??
for i in range(0, len(dealcontent)):
if i == 0:
formatt = red
else:
formatt = top
for j in range(0, len(dealcontent[i])):
if i != 0 and j == len(dealcontent[i]) - 1:
if dealcontent[i][j] == '':
worksheet.write(i, j, ' ', formatt)
else:
try:
worksheet.insert_image(i, j, dealcontent[i][j])
except:
worksheet.write(i, j, ' ', formatt)
else:
if dealcontent[i][j]:
worksheet.write(i, j, dealcontent[i][j].replace(' ', ''), formatt)
else:
worksheet.write(i, j, '?', formatt)
workbook.close()
def writeexcel(path, dealcontent):
workbook = wx.Workbook(path)
top = workbook.add_format(
{'border': 1, 'align': 'center', 'bg_color': 'white', 'font_size': 11, 'font_name': '????'})
red = workbook.add_format(
{'font_color': 'white', 'border': 1, 'align': 'center', 'bg_color': '800000', 'font_size': 11,
'font_name': '????', 'bold': True})
image = workbook.add_format(
{'border': 1, 'align': 'center', 'bg_color': 'white', 'font_size': 11, 'font_name': '????'})
formatt = top
formatt.set_align('vcenter') # ?????????
worksheet = workbook.add_worksheet() # ?????????
width = len(dealcontent[0])
worksheet.set_column(0, width, 38.5) # ???????22??
for i in range(0, len(dealcontent)):
if i == 0:
formatt = red
else:
formatt = top
for j in range(0, len(dealcontent[i])):
if i != 0 and j == len(dealcontent[i]) - 1:
if dealcontent[i][j] == '':
worksheet.write(i, j, ' ', formatt)
else:
try:
worksheet.insert_image(i, j, dealcontent[i][j])
except:
worksheet.write(i, j, ' ', formatt)
else:
if dealcontent[i][j]:
worksheet.write(i, j, dealcontent[i][j].replace(' ', ''), formatt)
else:
worksheet.write(i, j, '?', formatt)
workbook.close()
def build_one_graph(source_file, build_file):
# ??EXCEL???
workbook = xlsxwriter.Workbook(build_file)
# ????????
time_set = database.get_time_set(source_file)
# ??
time_tuple = sorted(time_set.items(), key=lambda e: e[0], reverse=False)
# ?????
tools.add_sheet_type(workbook, u"?????", time_tuple, u"??", u"???")
# ?????
people_say_set = database.get_people_say_set(source_file)
# ??
people_say_tuple = sorted(people_say_set.items(), key=lambda e: e[1], reverse=True)
# ?????
tools.add_sheet_type(workbook, u"????", people_say_tuple, u"??", u"???")
# ????
word_tuple = database.get_hot_noun_counts(source_file)
# ?????
tools.add_sheet_type(workbook, u"????", word_tuple, u"??", u"????")
# ????
workbook.close()
def get_xlsx_export(self, context):
u'''
?? xlsx
'''
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()
def get_xls_export(self, context):
u'''
?? xls
'''
datas = self._get_datas(context)
output = StringIO.StringIO()
export_header = (
self.request.GET.get('export_xls_header', 'off') == 'on')
model_name = self.opts.verbose_name if self.opts else self.admin_view.verbose_name
book = xlwt.Workbook(encoding='utf8')
sheet = book.add_sheet(
u"%s %s" % (_(u'Sheet'), force_unicode(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 writeexcel(path, dealcontent=[]):
workbook = wx.Workbook(path)
top = workbook.add_format(
{'border': 1, 'align': 'center', 'bg_color': 'white', 'font_size': 11, 'font_name': '????'})
red = workbook.add_format(
{'font_color': 'white', 'border': 1, 'align': 'center', 'bg_color': '800000', 'font_size': 11,
'font_name': '????', 'bold': True})
image = workbook.add_format(
{'border': 1, 'align': 'center', 'bg_color': 'white', 'font_size': 11, 'font_name': '????'})
formatt = top
formatt.set_align('vcenter') # ?????????
worksheet = workbook.add_worksheet() # ?????????
width = len(dealcontent[0])
worksheet.set_column(0, width, 38.5) # ???????22??
for i in range(0, len(dealcontent)):
if i == 0:
formatt = red
else:
formatt = top
for j in range(0, len(dealcontent[i])):
if dealcontent[i][j]:
worksheet.write(i, j, dealcontent[i][j].replace(' ', ''), formatt)
else:
worksheet.write(i, j, '', formatt)
workbook.close()
# ?????????? (Windows)
def qidongceshi(cishu,start):
try:
workbook=xlsxwriter.Workbook('????????.xlsx')
worksheet=workbook.add_worksheet('time')
bold=workbook.add_format({'bold':1})
headings=['????','????']
data=[cishu,start]
worksheet.write_row('A1',headings,bold)
worksheet.write_column('A2',data[0])
worksheet.write_column('B2',data[1])
chart1 = workbook.add_chart({'type': 'scatter',
'subtype': 'straight_with_markers'})
chart1.add_series({
'name':'=time!$B$1',
'categories': '=time!$A$2:$A$%s'%(len(start)+1),
'values': '=time!$B$2:$B$%s'%(len(start)+1),
})
chart1.set_title({'name':'????'})
chart1.set_x_axis({'name':"????"})
chart1.set_y_axis({'name':'????:ms'})
chart1.set_style(11)
worksheet.insert_chart('D2', chart1, {'x_offset': 25, 'y_offset': 10})
workbook.close()
LOG.info('????????')
except:
LOG.info('???????????:%s'%Exception)
