def export_as_xls(modeladmin, request, queryset):
"""
Generic xls export admin action.
"""
if not request.user.is_staff:
raise PermissionDenied
opts = modeladmin.model._meta
wb = Workbook()
ws0 = wb.add_sheet('0')
col = 0
field_names = []
# write header row
for field in opts.fields:
ws0.write(0, col, field.name)
field_names.append(field.name)
col = col + 1
row = 1
# Write data rows
for obj in queryset:
col = 0
for field in field_names:
val = unicode(getattr(obj, field)).strip()
ws0.write(row, col, val)
col = col + 1
row = row + 1
f = StringIO()
wb.save(f)
f.seek(0)
response = HttpResponse(f.read(), content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename=%s.xls' % unicode(opts).replace('.', '_')
return response
def __init__(self, fileName):
if os.path.exists(fileName) == False:
# 判断文件是否存在，存在则跳过，不存在则新建
self.book = Workbook(encoding='utf-8', style_compression=0)
self.sheet = self.book.add_sheet('豆瓣标签结果', cell_overwrite_ok=True)
## Column Width Determine Begin
for A in range(100):
self.sheet.col(A).width = 4800
## Column Width Determine Begin
self.sheet.write(0, 0, '编号', tittle_style)
self.sheet.write(0, 1, '用户', tittle_style)
self.sheet.write(0, 2, '标签', tittle_style)
self.sheet.write(0, 3, '次数', tittle_style)
self.sheet.write(0, 4, '标注过', tittle_style)
self.rowx = 1
self.index = 1
# sheet.write_merge(0, 5, 0, 1, "哈哈哈", normal_style)
else:
rb = open_workbook(fileName,
formatting_info=True) # 注意这里的workbook首字母是小写
r_sheet = rb.sheet_by_index(0)
self.rowx = r_sheet.nrows
firstColsValue = r_sheet.col_values(0)[::-1]
for index in firstColsValue:
if str(index).__eq__("") == False:
self.index = int(index)
break
self.index = self.index + 1
# 管道作用
self.book = copy(rb)
# 通过get_sheet()获取的sheet有write()方法
self.sheet = self.book.get_sheet(0)
self.sheet.write(0, 7, 'lalal', tittle_style)
return
def export_investdata_excel(self, request):
item_list = self.filter_queryset(self.get_queryset())
data = []
for con in item_list:
project = con.project
id = con.id
project_id = project.id
project_name = project.name
if con.is_futou:
is_futou = "复投"
else:
is_futou = "首投"
invest_time = con.invest_time
invest_mobile = con.invest_mobile
invest_amount = con.invest_amount
invest_term = con.invest_term
settle_amount = con.settle_amount
return_amount = ''
result = ''
if con.state == '0':
result = '否'
#return_amount = con.return_amount
elif con.state == '1':
result = '是'
return_amount = con.return_amount
source = con.get_source_display()
remark = con.remark
data.append([
id, project_id, project_name, is_futou, invest_time,
invest_mobile, invest_amount, invest_term, settle_amount,
result, return_amount, source, remark
])
w = Workbook() # 创建一个工作簿
ws = w.add_sheet(u'待审核记录') # 创建一个工作表
title_row = [
u'记录ID', u'项目编号', u'项目名称', u'首投/复投', u'投资日期', u'提交手机号', u'投资金额',
u'投资标期', u'预估消耗', u'审核状态', u'返现金额', u'投资来源', u'备注'
]
for i in range(len(title_row)):
ws.write(0, i, title_row[i])
row = len(data)
style1 = easyxf(num_format_str='YY/MM/DD')
for i in range(row):
lis = data[i]
col = len(lis)
for j in range(col):
if j == 4:
ws.write(i + 1, j, lis[j], style1)
else:
ws.write(i + 1, j, lis[j])
sio = BytesIO()
w.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(),
content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=导出表格.xls'
response.write(sio.getvalue())
return response
def export_project_statis(request):
item_list = []
item_list = ProjectStatis.objects.all()
project_state = request.GET.get("project_state", None)
if project_state == 'start' or project_state == 'finish':
item_list = item_list.filter(project__state=project_state)
data = []
for con in item_list:
id = con.project_id
time = con.project.time.strftime("%Y-%m-%d")
finish_time = con.project.finish_time.strftime(
"%Y-%m-%d") if con.project.finish_time else ''
title = con.project.name
topay_amount = con.project.topay_amount
consume = con.consume()
ret = con.ret()
site_consume = con.site_consume
site_ret = con.site_return
channel_consume = con.channel_consume
channel_ret = con.channel_return
state = con.project.get_state_display()
data.append([
id, time, finish_time, title, topay_amount, consume, ret,
channel_consume, channel_ret, site_consume, site_ret, state
])
w = Workbook() #创建一个工作簿
ws = w.add_sheet(u'账目明细') #创建一个工作表
title_row = [
u'项目编号', u'立项日期', u'结项日期', u'项目名称', u'预计待收/待消耗', u'预计总消耗', u'总返现金额',
u'预估渠道消耗', u'渠道返现金额', u'预估网站消耗', u'网站返现金额', u'项目状态'
]
for i in range(len(title_row)):
ws.write(0, i, title_row[i])
row = len(data)
style1 = easyxf(num_format_str='YY/MM/DD')
for i in range(row):
lis = data[i]
col = len(lis)
for j in range(col):
if j == 1:
ws.write(i + 1, j, lis[j], style1)
elif j == 2:
if lis[j]:
ws.write(i + 1, j, lis[j], style1)
else:
ws.write(i + 1, j, lis[j])
else:
ws.write(i + 1, j, lis[j])
sio = StringIO.StringIO()
w.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(),
content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=导出表格.xls'
response.write(sio.getvalue())
return response
def export_audit_result(request):
user = request.user
fid = request.GET.get("fid")
if fid == '0': #jzy
finance = Finance.objects.all()
item_list = UserEvent.objects.filter(user=user, finance=finance).order_by("-time")
else:
finance = Finance.objects.get(id=fid)
item_list = UserEvent.objects.filter(user=user, finance=finance).order_by("-time")
data = []
for con in item_list:
# project_name=finance.title
project = con.content_object #jzy
project_name=project.title #jzy
mobile_sub=con.invest_account
# time_sub=con.time
time_sub=con.invest_time #jzy
id=con.id
remark= con.remark
invest_amount= con.invest_amount
term=con.invest_term
result = con.get_audit_state_display(),
return_amount = '' if con.audit_state!='0' or not con.translist.exists() else str(con.translist.first().transAmount/100.0),
reason = '' if con.audit_state!='2' or not con.audited_logs.exists() else con.audited_logs.first().reason,
data.append([id, project_name, time_sub, mobile_sub, term, invest_amount, remark, result, return_amount, reason])
w = Workbook() #创建一个工作簿
ws = w.add_sheet(u'待审核记录') #创建一个工作表
title_row = [u'记录ID',u'项目名称',u'投资日期', u'注册手机号' ,u'投资期限' ,u'投资金额', u'备注', u'审核结果',u'返现金额',u'拒绝原因']
for i in range(len(title_row)):
ws.write(0,i,title_row[i])
row = len(data)
style1 = easyxf(num_format_str='YY/MM/DD')
for i in range(row):
lis = data[i]
col = len(lis)
for j in range(col):
if j==2:
ws.write(i+1,j,lis[j],style1)
else:
ws.write(i+1,j,lis[j])
sio = StringIO.StringIO()
w.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=审核结果.xls'
response.write(sio.getvalue())
return response
def create_and_write(self, sheet_name, types, row, col, text, savepath):
wb = Workbook('utf-8')
sheet = wb.add_sheet(sheet_name)
if types == 'one':
sheet.write(row, col, text)
elif types == 'row':
for i in range(len(self.content)):
sheet.write(row, col + i, self.content[i])
elif types == 'list':
for j in range(len(self.content)):
for k in range(len(self.content[j])):
sheet.write(5 + j, 7 + k, self.content[j][k])
else:
raise print('对不起,只能输入one,row,list,否则无法写入任何数据')
wb.save(savepath)
return self.content
def start(self):
book = Workbook()
sheet1 = book.add_sheet('Sheet1')
i = 0
while self.p <= 5:
zipd = self.getData()
#print(type(list(zipd)))
for gsmc, zwmc, zwyx, gzdd, gxsj in zipd:
sheet1.write(i, 0, gsmc.get_text())
sheet1.write(i, 1, zwmc.get_text())
sheet1.write(i, 2, zwyx.get_text())
sheet1.write(i, 3, gzdd.get_text())
sheet1.write(i, 4, gxsj.get_text())
sheet1.write(i, 5, zwmc.get('href'))
#self.sub_url.append(zwmc.get('href'))
#zwms = self.getSubdata(zwmc.get('href'))
#sheet1.write(i,5,self.getSubdata(zwmc.get('href'))) #太慢
i += 1
#print(type(zwmc))
#return
sheet1.col(0).width = 256 * 40
sheet1.col(1).width = 256 * 25
sheet1.col(5).width = 256 * 60
book.save('d:/gup/danwei.xls')
else:
iccpu = '/'
compn = temp2[1]
compqty = temp2[4]
lists.append(sdpn)
lists.append(descript)
lists.append(wo)
lists.append(iccpu)
lists.append(compn)
lists.append(po)
lists.append(orderqty)
lists.append(compqty)
lists.append(unit + price)
excellist.append(lists)
#d = {'SDPN':sdpn,'Descript':descript,'WO':wo,'iccpu':iccpu,'component':compn,'PO':po,'Orderqty':orderqty,'unitprice':unit+price+''}
#lists.append(d)
#print(excellist)
book = Workbook()
sheet1 = book.add_sheet('Sheet1')
row0 = [
'SD P/N', 'Description', 'WO#', 'IC CPU', 'Component', 'PO#', 'Order Qty',
'Comp qty', 'Unit price'
]
for i in range(len(row0)):
sheet1.write(0, i, row0[i])
for i, li in enumerate(excellist):
#print(li,'------')
for j, lj in enumerate(li):
sheet1.write(i + 1, j, lj)
book.save('d:/alltxt/result.xls')
#print('生成excel,目录为:',mypath)
# -*-coding:UTF-8 -*-
from xlwt.Workbook import Workbook
wb = Workbook("utf-8")
sheet = wb.add_sheet("测试报告")
sheet.write(1, 1, '客户无忧自动化测试报告')
wb.save(r'd:\自动化测试.xls')
def handle(self, *args, **kwargs):
# Save a file with summary statistics
self.log('Saving summary stats...')
percent_correct = 1.0 * F('correct_images') / F('user__num_images')
percentages = LoginAttempt.objects.filter(
right_password=True).annotate(
percent_correct=percent_correct).order_by(
'percent_correct').values_list('percent_correct',
flat=True)
total = len(percentages)
middle = total / 2 - 1
if total % 2 == 0:
median = sum(percentages[middle:middle + 2]) / 2
else:
median = percentages[middle]
with default_storage.open('summary.txt', 'w') as f:
f.write('\n'.join([
'Total accounts: %d' % User.objects.count(),
'Total login attempts: %d' % LoginAttempt.objects.count(),
'Total login attempts with invalid passwords: %d' %
LoginAttempt.objects.filter(right_password=False).count(),
'Min percentage: %f' % percentages[0],
'Max percentage: %f' % percentages[total - 1],
'Average percentage: %f' % (sum(percentages) / float(total)),
'Median percentage: %f' % median,
]))
# Save a file with all the data as an Excel file
self.log('Saving all data...')
workbook = Workbook()
users_sheet = ExcelSheet(workbook, 'Users')
fields = ['timestamp', 'username', 'email', 'num_images']
values = User.objects.values_list(*fields)
users_sheet.write_headers(fields)
users_sheet.add_rows(values)
logins_sheet = ExcelSheet(workbook, 'Login Attempts')
logins_sheet.write_headers([
'Timestamp', 'Username', 'Right password?', '# correct images',
'# total images', 'Percent correct'
])
images_accuracy = ExcelSheet(workbook, 'NumImages_Accuracy')
images_accuracy.write_headers(['Number of Images', 'Percent Correct'])
check_sheet = ExcelSheet(workbook, 'Check')
check_sheet.write_headers([
'Number of Images', 'Accuracy Threshold', 'Time', 'Right password'
])
crack_sheet = ExcelSheet(workbook, 'Crack')
crack_sheet.write_headers(
['Number of Images', 'Accuracy Threshold', 'Time'])
for login in LoginAttempt.objects.annotate(
percent_correct=percent_correct):
num_images = login.user.num_images
logins_sheet.add_row([
login.timestamp, login.user.username, login.right_password,
login.correct_images, num_images, login.percent_correct
])
if login.right_password:
# only run accuracy for logins with the right password
images_accuracy.add_row([num_images, login.percent_correct])
try:
benchmarks = login.get_benchmarks()
except:
continue
for threshold, time in enumerate(benchmarks['check']):
check_sheet.add_row(
[num_images, threshold, time, login.right_password])
for threshold, time in enumerate(benchmarks['crack']):
crack_sheet.add_row([num_images, threshold, time])
with default_storage.open('data.xls', 'w+') as f:
workbook.save(f)
# -*- coding: utf-8 -*-
from xlwt.Formatting import Borders, Font, Alignment, Pattern
from xlwt.Style import XFStyle
from xlwt.Workbook import Workbook
wb = Workbook('utf-8')
sheet = wb.add_sheet('测试报告')
title = ['编号', '姓名', '职业', '上级', '入职日期']
title_style = XFStyle()
border = Borders()
border.left = border.THIN
border.right = border.THIN
border.top = border.THIN
border.bottom = border.THIN
font = Font()
font.bold = True
alignment = Alignment()
alignment.horz = alignment.HORZ_CENTER
pattern = Pattern()
pattern.pattern = pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x35
title_style.borders = border
title_style.font = font
title_style.alignment = alignment
title_style.pattern = pattern
def _new_workbook(self):
return Workbook()
def save_excel(city, dist_dict):
book = Workbook()
for dist in dist_dict:
sheet = book.add_sheet(dist)
excel_file = 'd://danxia_house//' + city + '.xls'
sheet.write(0, 0, '小区'.decode('utf-8'))
sheet.write(0, 1, '总价'.decode('utf-8'))
sheet.write(0, 2, '单价'.decode('utf-8'))
sheet.write(0, 3, '户型'.decode('utf-8'))
sheet.write(0, 4, '面积'.decode('utf-8'))
sheet.write(0, 5, '朝向'.decode('utf-8'))
sheet.write(0, 6, '楼层'.decode('utf-8'))
sheet.write(0, 7, '位置'.decode('utf-8'))
sheet.write(0, 8, '年限'.decode('utf-8'))
sheet.write(0, 9, '看房次数'.decode('utf-8'))
sheet.write(0, 10, '其他'.decode('utf-8'))
i = 1
for page_num in range(1, int(dist_dict[dist]) + 1):
file = 'd://danxia_' + city + '//' + city + '_' + dist + '_' + str(
page_num) + '.html'
#print file
if os.path.exists(file):
#房子house_herf
re_house_herf = r'<a.*?class="img".*?href="(.*?)".*?target="_blank">'
#小区正则规则
re_house_regoin = r'<p.*?class="moudle">.*?<a.*?target="_blank">(.*?)</a>'
#房型信息正则规则
re_module = r'<p.*?class="moudle">.*?<a.*?</a>(.*?)</p>'
re_sub_module = r'<span>(.*?)</span>'
#房屋总价正则规则
re_total_price = r'<div.*?class="percent">.*?<b>(.*?)</b>'
#房屋单价正则规则
re_per_price = r'<div.*?class="percent">.*?<span>(.*?)</span>'
#看房次数正则规则
re_look = r'<p.*?class="text">.*?<span>(.*?)</span>'
#房屋区域正则规则
re_zone = r'<p.*?class="moudle">.*?</p>.*?<p.*?class="msg">.*?<a.*?target="_blank">(.*?)</a>'
#房屋楼层、年限
re_msg = r'<p.*?class="moudle">.*?</p>.*?<p.*?class="msg">.*?<a.*?</a>(.*?)</p>'
re_sub_msg = r'<span>(.*?)</span>'
re_fix = r'<p.*?class="moudle">.*?</p>.*?<p.*?class="msg">.*?<p.*?class=".*?">(.*?)</p>'
re_sub_fix = r'<i.*?class=".*?">(.*?)</i>'
#房子house_href连接
house_index_ids = house_info(file, re_house_herf)
#当前页面房屋所在小区列表
house_regoin = house_info(file, re_house_regoin)
#print '-----', house_regoin
#当前页户型、面积、朝向信息列表
house_module = house_info(file, re_module)
#当前页面房屋总价列表
house_totalprice = house_info(file, re_total_price)
#当前页房屋单价列表
house_perprice = house_info(file, re_per_price)
#当前页房屋看房次数列表
house_look = house_info(file, re_look)
#当前页房屋所在区域列表
house_zone = house_info(file, re_zone)
#当前页房屋楼层、年限类别
house_msg = house_info(file, re_msg)
#当前页房屋其他信息（有否钥匙等）
house_fix = house_info(file, re_fix)
#当前页面房屋数
house_length = len(house_regoin)
print 'per page house nums: %s' % house_length
for seq in range(house_length):
house_href = 'http://danxia.com' + house_index_ids[seq]
house_sub_module = house_info(house_module[seq],
re_sub_module)
house_sub_module_length = len(house_sub_module)
if len(house_sub_module) == 3:
#户型
house_hx = house_sub_module[0]
#面积
house_area = house_sub_module[1].strip('平米')
#朝向
house_faceto = house_sub_module[2]
#若没有朝向信息，则输入为空
if len(house_sub_module) == 2:
house_hx = house_sub_module[0]
house_area = house_sub_module[1].strip('平米')
house_faceto = ' '
#拆分成楼层、年限信息三个列表
house_sub_msg = house_info(house_msg[seq], re_sub_msg)
house_sub_msg_length = len(house_sub_msg)
if house_sub_msg_length == 2:
#楼层
house_floor = house_sub_msg[0]
#年限
house_year = house_sub_msg[1]
if house_sub_msg_length == 1:
#楼层
house_floor = house_sub_msg[0]
#年限
house_year = ' '
house_others = ''
house_sub_fix = house_info(house_fix[seq], re_sub_fix)
for each_house_sub_fix in house_sub_fix:
house_others = house_others + each_house_sub_fix + ','
house_others
#house_z = house_zone[seq]
#print type(house_z.strip('二手房').decode('utf-8'))
print '房源链接： %s, 小区：%s, 总价：%s, 单价：%s, 户型：%s, 面积：%s, 朝向：%s, 楼层：%s, 区域：%s, 年限：%s, 看房次数：%s, 其他：%s' \
% (house_href, house_regoin[seq], house_totalprice[seq], house_perprice[seq].strip('元/㎡'), house_hx, house_area, house_faceto,
house_floor, house_zone[seq][:-9], house_year.strip('年建'), house_look[seq], house_others)
'''
sheet.write(i, 0 ,house_regoin[seq].decode('utf-8'))
sheet.write(i, 1 ,house_totalprice[seq].decode('utf-8'))
sheet.write(i, 2 ,house_perprice[seq].strip('元/㎡').decode('utf-8'))
sheet.write(i, 3 ,house_hx.decode('utf-8'))
sheet.write(i, 4 ,house_area.decode('utf-8'))
sheet.write(i, 5 ,house_faceto.decode('utf-8'))
sheet.write(i, 6 ,house_floor.decode('utf-8'))
sheet.write(i, 7 ,house_zone[seq][:-9].decode('utf-8'))
sheet.write(i, 8 ,house_year.strip('年建').decode('utf-8'))
sheet.write(i, 9 ,house_look[seq].decode('utf-8'))
sheet.write(i, 10 ,house_others.decode('utf-8'))
i=i+1
'''
print 'page %s-------' % page_num
#my_logger(page_num)
book.save(excel_file)
print 'save'
def save_excel(city, dist_dict):
book = Workbook()
for dist in dist_dict:
sheet = book.add_sheet(dist)
excel_file = 'd://lianjia_house//' + city + '.xls'
sheet.write(0, 0, '小区'.decode('utf-8'))
sheet.write(0, 1, '总价'.decode('utf-8'))
sheet.write(0, 2, '单价'.decode('utf-8'))
sheet.write(0, 3, '户型'.decode('utf-8'))
sheet.write(0, 4, '面积'.decode('utf-8'))
sheet.write(0, 5, '装修'.decode('utf-8'))
sheet.write(0, 6, '楼层'.decode('utf-8'))
sheet.write(0, 7, '位置'.decode('utf-8'))
sheet.write(0, 8, '关注人数'.decode('utf-8'))
sheet.write(0, 9, '看房次数'.decode('utf-8'))
sheet.write(0, 10, '发布时间'.decode('utf-8'))
sheet.write(0, 11, '税费'.decode('utf-8'))
i = 1
for page_num in range(1, int(dist_dict[dist]) + 1):
file = 'd://lianjia_' + city + '//' + city + '_' + dist + '_' + str(
page_num) + '.html'
if os.path.exists(file):
re_house_regoin = r'"region">(.*?)</a>'
re_house_detail = r'"region">.*?</a>(.*?)</div>'
re_house_totalprice = r'totalPrice">.*?<span>(.*?)</span>'
re_house_perprice = r'data-price="(.*?)">'
re_house_poistion = r'class="positionInfo">.*?<a.*?target="_blank">(.*?)</a>'
re_house_floor = r'class="positionIcon"></span>(.*?)<a'
re_house_publish = r'class="starIcon"></span>(.*?)</div>'
re_house_tag = r'<li.*?class="tag".*?>(.*?)</div>'
re_sub_house_tag = r'<span.*?class=.*?">(.*?)</span>'
house_regoin = house_info(file, re_house_regoin)
house_detail = house_info(file, re_house_detail)
house_total_price = house_info(file, re_house_totalprice)
house_per_price = house_info(file, re_house_perprice)
house_position = house_info(file, re_house_poistion)
house_floor = house_info(file, re_house_floor)
house_publish = house_info(file, re_house_publish)
house_tag = house_info(file, re_house_tag)
#print house_regoin[1]
house_sum = len(house_regoin)
print 'per page house nums: %s' % house_sum
for seq in range(0, house_sum):
house_focus = house_publish[seq].split(
'/')[0].strip().strip('人关注')
house_look = house_publish[seq].split(
'/')[1].strip().strip('共').strip('次带看')
house_push_time = house_publish[seq].split('/')[2].strip()
house_layout = house_detail[seq].split('|')[1].strip()
house_area = house_detail[seq].split('|')[2].strip().strip(
'平米')
house_fixed = ''
for each_house_fixed in house_detail[seq].split('|')[3:]:
house_fixed = house_fixed + '' + each_house_fixed
house_tax = ''
if house_tag[seq] == '':
house_tax = house_tag[seq]
else:
house_tax_sub = house_info(house_tag[seq],
re_sub_house_tag)
for each_house_tax in house_tax_sub:
house_tax = house_tax + each_house_tax + ' '
print '小区:%s 总价:%s 单价:%s 户型:%s 面积: %s 装修: %s 楼层:%s 位置:%s 关注人数: %s 看房次数:%s 发布时间: %s 税费: %s' \
%(house_regoin[seq], house_total_price[seq], house_per_price[seq],
house_layout, house_area, house_fixed, house_floor[seq], house_position[seq],
house_focus, house_look, house_push_time, house_tax)
sheet.write(i, 0, house_regoin[seq].decode('utf-8'))
sheet.write(i, 1, house_total_price[seq].decode('utf-8'))
sheet.write(i, 2, house_per_price[seq].decode('utf-8'))
sheet.write(i, 3, house_layout.decode('utf-8'))
sheet.write(i, 4, house_area.decode('utf-8'))
sheet.write(i, 5, house_fixed.decode('utf-8'))
sheet.write(i, 6, house_floor[seq].decode('utf-8'))
sheet.write(i, 7, house_position[seq].decode('utf-8'))
sheet.write(i, 8, house_focus.decode('utf-8'))
sheet.write(i, 9, house_look.decode('utf-8'))
sheet.write(i, 10, house_push_time.decode('utf-8'))
sheet.write(i, 11, house_tax.decode('utf-8'))
i = i + 1
print 'page %s-------' % page_num
#my_logger(page_num)
book.save(excel_file)
print 'save'
def export_account_bill_excel(request):
user = request.user
item_list = []
item_list = AccountBill.objects.all()
name = request.GET.get("name", None)
if name:
item_list = item_list.filter(account__name=name)
account = request.GET.get("account", None)
if account:
item_list = item_list.filter(account_id=account)
account_type = request.GET.get("account_type", None)
if account_type:
item_list = item_list.filter(account__type=account_type)
type = request.GET.get("type", None)
if type:
item_list = item_list.filter(type=type)
subtype = request.GET.get("subtype", None)
if subtype:
item_list = item_list.filter(subtype=subtype)
target = request.GET.get("target", None)
if target:
item_list = item_list.filter(target__contains=target)
timeft_0 = request.GET.get("timeft_0", None)
timeft_1 = request.GET.get("timeft_1", None)
if timeft_0 and timeft_1:
s = datetime.datetime.strptime(timeft_0, '%Y-%m-%d')
e = datetime.datetime.strptime(timeft_1, '%Y-%m-%d')
e += timedelta(days=1)
item_list = item_list.filter(time__range=(s, e))
data = []
for con in item_list:
time = con.time.strftime("%Y-%m-%d %H:%M")
account_id = con.account_id
account_type = con.account.get_type_display()
account_name = con.account.name
bill_type = con.get_type_display()
subtype = con.get_subtype_display()
target = con.target
amount = con.amount
balance = con.account.balance
remark = con.remark
data.append([
time, account_id, account_type, account_name, bill_type, subtype,
target, amount, balance, remark
])
w = Workbook() #创建一个工作簿
ws = w.add_sheet(u'账目明细') #创建一个工作表
title_row = [
u'账单时间', u'账户ID', u'账户类型', u'账户名称', u'账单类型', u'收/支类型', u'交易对象',
u'交易金额', u'账户余额', u'备注'
]
for i in range(len(title_row)):
ws.write(0, i, title_row[i])
row = len(data)
style1 = easyxf(num_format_str='YY/MM/DD HH:mm')
for i in range(row):
lis = data[i]
col = len(lis)
for j in range(col):
if j == 0:
ws.write(i + 1, j, lis[j], style1)
else:
ws.write(i + 1, j, lis[j])
sio = StringIO.StringIO()
w.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(),
content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=导出表格.xls'
response.write(sio.getvalue())
return response
def export_investdata_excel(request):
user = request.user
item_list = ProjectInvestData.objects
is_futou = request.GET.get("is_futou", None)
if is_futou == "true":
item_list = item_list.filter(is_futou=True)
elif is_futou == "false":
item_list = item_list.filter(is_futou=False)
state = request.GET.get("state", None)
if state:
item_list = item_list.filter(state=state)
source = request.GET.get("source", None)
if source:
item_list = item_list.filter(source=source)
invest_mobile = request.GET.get("invest_mobile", None)
if invest_mobile:
item_list = item_list.filter(invest_mobile=invest_mobile)
name__contains = request.GET.get("name__contains", None)
if name__contains:
item_list = item_list.filter(project__name__contains=name__contains)
project = request.GET.get("project_id", None)
if project:
item_list = item_list.filter(project_id=project)
investtime_0 = request.GET.get("investtime_0", None)
investtime_1 = request.GET.get("investtime_1", None)
audittime_0 = request.GET.get("audittime_0", None)
audittime_1 = request.GET.get("audittime_1", None)
if investtime_0 and investtime_1:
s = datetime.datetime.strptime(investtime_0, '%Y-%m-%d')
e = datetime.datetime.strptime(investtime_1, '%Y-%m-%d')
item_list = item_list.filter(invest_time__range=(s, e))
if audittime_0 and audittime_1:
s = datetime.datetime.strptime(audittime_0, '%Y-%m-%d')
e = datetime.datetime.strptime(audittime_1, '%Y-%m-%d')
item_list = item_list.filter(audit_time__range=(s, e))
item_list = item_list.select_related('project').order_by('invest_time')
data = []
for con in item_list:
project = con.project
id = con.id
project_id = project.id
project_name = project.name
if con.is_futou:
is_futou = u"复投"
else:
is_futou = u"首投"
invest_time = con.invest_time
invest_mobile = con.invest_mobile
invest_amount = con.invest_amount
invest_term = con.invest_term
settle_amount = con.settle_amount
return_amount = ''
result = ''
if con.state == '0':
result = u'是'
return_amount = con.return_amount
elif con.state == '2':
result = u'否'
source = con.get_source_display()
remark = con.remark
data.append([
id, project_id, project_name, is_futou, invest_time, invest_mobile,
invest_amount, invest_term, settle_amount, result, return_amount,
source, remark
])
w = Workbook() #创建一个工作簿
ws = w.add_sheet(u'待审核记录') #创建一个工作表
title_row = [
u'记录ID', u'项目编号', u'项目名称', u'首投/复投', u'投资日期', u'提交手机号', u'投资金额',
u'投资标期', u'预估消耗', u'审核状态', u'返现金额', u'投资来源', u'备注'
]
for i in range(len(title_row)):
ws.write(0, i, title_row[i])
row = len(data)
style1 = easyxf(num_format_str='YY/MM/DD')
for i in range(row):
lis = data[i]
col = len(lis)
for j in range(col):
if j == 4:
ws.write(i + 1, j, lis[j], style1)
else:
ws.write(i + 1, j, lis[j])
sio = StringIO.StringIO()
w.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(),
content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=导出表格.xls'
response.write(sio.getvalue())
return response