def _excel_of_histories(self, histories, filename):
headers = ('??', '???', '????', '??', '????', '????', '????', '????', '????', '????', '????',)
columns = (
lambda x: x.timeslot and x.timeslot.order.order_id or (x.comment or '?????'),
'timeslot.order.grade',
'timeslot.order.subject',
'timeslot.order.level',
lambda x: x.timeslot and ('%s-%s' % (x.timeslot.start.strftime('%H:%M'), x.timeslot.end.strftime('%H:%M'),)) or '',
lambda x: x.timeslot and (x.timeslot.order.price / 100) or '',
lambda x: x.timeslot and x.timeslot.duration_hours() or '',
lambda x: x.timeslot and ('%s%%' % (x.timeslot.order.commission_percentage),) or '',
lambda x: x.amount and (x.amount / 100) or '',
)
workbook = xlwt.Workbook()
sheet_name = 'Export {0}'.format(datetime.date.today().strftime('%Y-%m-%d'))
sheet = workbook.add_sheet(sheet_name)
for y, th in enumerate(headers):
sheet.write(0, y, th, excel.HEADER_STYLE)
x = 1
for history in histories:
y = 0
day_val = history['day'].date()
sheet.write_merge(x, x + history['count'] - 1, y, y, day_val, excel.get_style_by_value(day_val))
x_sub = x
records = history['records']
for record in records:
y_sub = y+1
for column in columns:
value = callable(column) and column(record) or excel.get_column_cell(record, column)
sheet.write(x_sub, y_sub, value, excel.get_style_by_value(value))
y_sub += 1
x_sub += 1
income = history['income'] / 100
y += len(columns) + 1
sheet.write_merge(x, x + history['count'] - 1, y, y, income, excel.get_style_by_value(income))
x += len(records)
return excel.wb_excel_response(workbook, filename)
python类Workbook()的实例源码
def queryset_to_workbook(queryset, columns, headers=None, header_style=HEADER_STYLE,
default_style=DEFAULT_STYLE, cell_style_map=CELL_STYLE_MAP):
'''
?django QuerySet??????excel?Workbook
:param queryset: django QuerySet??
:param columns: ? ('??', '???', '????')
:param headers: ? ('name', 'profile.phone', lambda x: (x.balance/100),)
:param header_style: ?????
:param default_style: ????
:param cell_style_map: (????,??)???
:return: xlwt.Workbook
'''
workbook = xlwt.Workbook()
report_date = datetime.date.today()
sheet_name = 'Export {0}'.format(report_date.strftime('%Y-%m-%d'))
sheet = workbook.add_sheet(sheet_name)
if headers:
for y, th in enumerate(headers):
sheet.write(0, y, th, header_style)
else:
for y, th in enumerate(columns):
value = get_column_head(th)
sheet.write(0, y, value, header_style)
for x, obj in enumerate(queryset, start=1):
for y, column in enumerate(columns):
if callable(column):
value = column(obj)
else:
value = get_column_cell(obj, column)
style = get_style_by_value(value, cell_style_map, default_style)
sheet.write(x, y, value, style)
return workbook
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()
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 __init__(self,key,city):
self.key = key
self.city = city
# ?????????????????????
self.T = datetime.datetime.strftime(datetime.datetime.now(),"%Y%m%d%H%M")
# ??????????sheet??
self.work = xlwt.Workbook(encoding="utf-8")
self.sheet = self.work.add_sheet("{}_{}".format(self.key,self.city))
# ??????
self.get_title()
# ?????????
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 __init__(self):
self.headers={'Host': 'api.k.sohu.com','User-Agent': 'SohuNews/5.9.3 BuildCode/126',
'Authorization': '990006203070023','Content-Encoding': 'UTF-8'}
self.new_url='http://api.k.sohu.com/api/channel/v6/news.go?p1=NjMwMjg4NTczMDc1OTEyNzA2OA%3D%3D&pid=-1&channelId=1&num=20&imgTag=1&showPic=1&picScale=11&rt=json&net=wifi&cdma_lat=22.553053&cdma_lng=113.902393&from=channel&mac=b4%3A0b%3A44%3A83%3A93%3A16&AndroidID=4dd00e258bbe295f&carrier=CMCC&imei=990006203070023&imsi=460020242631842&density=3.0&apiVersion=37&skd=9bf84c6c9d24711f43f7058db2d1ed5ba7c6a2fecca504d3f44839a8bf22b4521ff192a4ac2d77946d871706ceb89baa269d145d2f5a07fddb656d6417029bb04459d2a5aa0ca50764b2de62da32f9e5e6055efa78b93cafbd89ef0971a836d3542ce2065edff7017a28b164e4210fec&v=1502985600&t=1503044087&page=1&action=0&mode=0&cursor=0&mainFocalId=0&focusPosition=1&viceFocalId=0&lastUpdateTime=0&gbcode=440300&forceRefresh=0&apiVersion=37&u=1&source=0&isSupportRedPacket=0&t=1503044087'
self.old_url='http://api.k.sohu.com/api/channel/v6/news.go?p1=NjMwMjg4NTczMDc1OTEyNzA2OA%3D%3D&pid=-1&channelId=1&num=20&imgTag=1&showPic=1&picScale=11&rt=json&net=wifi&cdma_lat=22.568970&cdma_lng=113.948697&from=channel&mac=b4%3A0b%3A44%3A83%3A93%3A16&AndroidID=4dd00e258bbe295f&carrier=CMCC&imei=990006203070023&imsi=460020242631842&density=3.0&apiVersion=37&skd=9bf84c6c9d24711f43f7058db2d1ed5ba7c6a2fecca504d3f44839a8bf22b4521ff192a4ac2d77946d871706ceb89baa26d2a04c74e7f9a802366235a8d013a24459d2a5aa0ca50764b2de62da32f9e5e6055efa78b93cafbd89ef0971a836d3542ce2065edff7017a28b164e4210fec&v=1502985600&t=1502992147&page=2&action=2&mode=1&cursor=6817205&mainFocalId=6816008&focusPosition=2&viceFocalId=0&lastUpdateTime=0&gbcode=440300&forceRefresh=0&apiVersion=37&u=1&source=0&isSupportRedPacket=0&t=1502992147'
self.current=datetime.datetime.now().strftime('%Y-%m-%d_%H-%M-%M')+'.xls'
self.excelFile=xlwt.Workbook()
self.sheet=self.excelFile.add_sheet('sohu')
self.row = 0
def extract(filename, format='csv', sizefilter=0, singlefile=False):
tables = extract_docx_table(filename)
name = filename.rsplit('.', 1)[0]
format = format.lower()
n = 0
lfilter = int(sizefilter)
if singlefile:
workbook = xlwt.Workbook()
for t in tables:
if lfilter >= len(t):
print 'Table length %d instead of %d. Skipped' % (len(t), lfilter)
continue
n += 1
sheet = workbook.add_sheet(str(n))
rn = 0
for row in t:
cn = 0
for c in row:
sheet.write(rn, cn, c.decode('utf8'))
cn += 1
rn += 1
destname = name + '.%s' % (format)
workbook.save(destname)
print destname, 'saved'
else:
for t in tables:
if lfilter >= len(t):
print 'Table length %d instead of %d. Skipped' % (len(t), lfilter)
continue
n += 1
destname = name + '_%d.%s' % (n, format)
store_table(t, destname, format)
print destname, 'saved'
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 generate_xls(urls, keys, result,filename):
"""?????excel??"""
xls_file = xlwt.Workbook()
sheet = xls_file.add_sheet(unicode(filename), cell_overwrite_ok=True)
font0 = xlwt.Font()
font0.name = 'Times New Roman'
font0.colour_index = 2
font0.bold = True
style0 = xlwt.XFStyle()
style0.font = font0
row = 0
sheet.col(0).width = 256 * 20
sheet.col(1).width = 256 * 30
sheet.col(2).width = 256 * 20
sheet.write(0, 0, u"??", style0)
sheet.write(0, 1, u"???", style0)
sheet.write(0, 2, u"??", style0)
if urls != None and keys != None and result != None:
for url, key, res in zip(urls, keys, result):
row += 1
sheet.write(row, 0, url.decode("utf-8"))
sheet.write(row, 1, key.decode("utf-8"))
sheet.write(row, 2, res.decode("utf-8"))
fpath=os.path.join(os.path.dirname(sys.argv[0]),u"{}.xls".format(unicode(filename))) #??????
print u"[??]???????{}".format(fpath)
xls_file.save(fpath)
def __rep_xls(self):
wb = xlwt.Workbook(encoding = 'utf-8') #?????
summary_sheet = self.__rep_xls_summary(wb)
self.__rep_xls_details(summary_sheet, wb)
# ????????report_[?????]_[????].xls
self.__rep_xls_file = os.path.join(self.report_dir() ,"report_" + self.name().encode('utf-8') + "_" + self.start_time(format = "%Y%m%d%H%M%S") + ".xls")
wb.save(self.__rep_xls_file) #????
return self.__rep_xls_file
def write_excel(filename="demo.xlsx", sheets=None):
"""Write excel from data.
"""
file = xlwt.Workbook() # ?????
for sheet in sheets:
new_sheet = file.add_sheet(sheet["name"], cell_overwrite_ok=True) # ??sheet
# ????
new_sheet.write(0, 0, "version=1.0.0", set_excel_style('Arial Black', 220, True))
for col, key in enumerate(sheet["keys"]):
new_sheet.write(1, col, key, set_excel_style('Arial Black', 220, True))
# ????
for index, item in enumerate(sheet["items"]):
for col, key in enumerate(sheet["keys"]):
new_sheet.write(index+2, col, item['n'][key])
file.save(filename) # ????
