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_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 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
class HtmlOutputer(object):
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 writeData(self, rowx, colx, data):
self.sheet.write(rowx, colx, '标注过', tittle_style)
def writeDataDefault(self, username, allTags, allBooks):
self.sheet.write(self.rowx, 0, self.index, normal_style)
self.sheet.write(self.rowx, 1, username, normal_style)
for tag in allTags:
self.sheet.write(self.rowx, 2, tag, normal_style)
self.sheet.write(self.rowx, 3, allBooks[tag].__len__(),
normal_style)
self.sheet.write(self.rowx, 4, ",".join(allBooks[tag]),
normal_style)
self.rowx = self.rowx + 1
def save(self, name):
self.book.save(name)
class HtmlOutputer(object):
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 writeData(self, rowx, colx, data):
self.sheet.write(rowx, colx, '标注过', tittle_style)
def writeDataDefault(self, username, allTags, allBooks):
self.sheet.write(self.rowx, 0, self.index, normal_style)
self.sheet.write(self.rowx, 1, username, normal_style)
for tag in allTags:
self.sheet.write(self.rowx, 2, tag, normal_style)
self.sheet.write(self.rowx, 3, allBooks[tag].__len__(), normal_style)
self.sheet.write(self.rowx, 4, ",".join(allBooks[tag]), normal_style)
self.rowx = self.rowx + 1
def save(self, name):
self.book.save(name)
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 __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 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 __init__(self):
self.sudoku=[]
self.sudokuCheck=[]
self.elements=[]
self.elementInit()
self.wb = Workbook()
self.ws0 = self.wb.add_sheet('sudoku1',cell_overwrite_ok=True)
self.sw=1
self.cont=0
self.var=[]
self.stack=[]
self.file= open('log.txt','w')
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')
pages = get_url_page()
#run_page()
house_urls = []
for each_page in pages:
house_url = get_house_url_id(each_page)
#print '页数：%s, 地址集：%s' %(each_page, house_url)
print '页数:', each_page
print '房源页面:', house_url
house_urls = house_url + house_urls
print '房源总页面:', house_urls
i = 0
book = Workbook()
sheet_sm = book.add_sheet('siming')
for each_house in house_urls:
time.sleep(3)
html = open_url(each_house)
time.sleep(3)
print '房源排序号：%s' %(i+1)
house_estate = get_house_estate(html)
print '小区:', house_estate
house_type = get_house_type(html)
print '类型:', house_type
house_area = get_area(html)
print '面积：', house_area
house_perprice = get_per_price(html)
print '单价:', house_perprice
house_totalprice = get_total_price(html)
class ExcelWriter(BaseWriter):
_sheet_count = 0
def __init__(self, *args, **kw):
super(ExcelWriter, self).__init__(*args, **kw)
self._wb = Workbook()
def add_ideogram_table(self, analyses,
widths=None,
# configure_table=True,
add_title=False, add_header=False, tablenum=1, **kw):
sh = self._wb.add_sheet(analyses[0].labnumber)
# 'sheet{:02n}'.format(self._sheet_count))
self._sheet_count += 1
cols = ['Status', 'N', 'Power', 'Moles_40Ar',
'Ar40', 'Ar40Er',
'Ar39', 'Ar39Er',
'Ar38', 'Ar38Er',
'Ar37', 'Ar37Er',
'Ar36', 'Ar36Er',
'Blank',
'Ar40', 'Ar40Er',
'Ar39', 'Ar39Er',
'Ar38', 'Ar38Er',
'Ar37', 'Ar37Er',
'Ar36', 'Ar36Er'
]
# write header row
hrow = 0
borders = Borders()
borders.bottom = 2
style = XFStyle()
style.borders = borders
for i, ci in enumerate(cols):
sh.write(hrow, i, ci, style=style)
numstyle = XFStyle()
numstyle.num_format_str = '0.00'
from operator import attrgetter
def getnominal_value(attr):
def get(x):
v = getattr(x, attr)
return float(v.nominal_value)
return get
def getstd_dev(attr):
def get(x):
v = getattr(x, attr)
return float(v.std_dev)
return get
def getfloat(attr):
def get(x):
v = getattr(x, attr)
return float(v)
return get
def null_value():
return lambda x: ''
attrs = [(attrgetter('step'),),
(getfloat('extract_value'),),
(null_value(),),
(getnominal_value('Ar40'),),
(getstd_dev('Ar40'),),
(getnominal_value('Ar39'),),
(getstd_dev('Ar39'),),
(getnominal_value('Ar38'),),
(getstd_dev('Ar38'),),
(getnominal_value('Ar37'),),
(getstd_dev('Ar37'),),
(getnominal_value('Ar36'),),
(getstd_dev('Ar36'),),
(null_value(),),
(getnominal_value('Ar40_blank'),),
(getstd_dev('Ar40_blank'),),
(getnominal_value('Ar39_blank'),),
(getstd_dev('Ar39_blank'),),
(getnominal_value('Ar38_blank'),),
(getstd_dev('Ar38_blank'),),
(getnominal_value('Ar37_blank'),),
(getstd_dev('Ar37_blank'),),
(getnominal_value('Ar36_blank'),),
(getstd_dev('Ar36_blank'),),
]
for i, ai in enumerate(analyses):
for j, arg in enumerate(attrs):
if len(arg) == 2:
func, style = arg
else:
func, style = arg[0], Style.default_style
sh.write(i + hrow + 1, 1 + j, func(ai),
style
)
def publish(self):
self._wb.save(self.filename)
b = html.find("',", a)
return html[a:b]
#进入指定房源页面获取房源面积
def get_area(html):
a = html.find("area:'") + 6
b = html.find("',", a)
return html[a:b]
if __name__ == '__main__':
city = 'xm'
dist = 'huli'
total_page = 3
house_ids = get_all_house_ids(city, dist, total_page)
book = Workbook()
sheet = book.add_sheet(dist)
i = 0
for house_id in house_ids:
file = 'd://lianjia_xm_house//'+city+'_'+dist+'_' + str(house_id) +'.html'
html = get_house_html(file)
house_estate = get_house_estate(html).decode('utf-8')
print '小区:', house_estate
house_type = get_house_type(html).decode('utf-8')
print '类型:', house_type
house_area = get_area(html).decode('utf-8')
print '面积：', house_area
house_perprice = get_per_price(html).decode('utf-8')
print '单价:', house_perprice
house_totalprice = get_total_price(html).decode('utf-8')
print '总价:', house_totalprice
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 write(self, stream):
wb = WtWorkbook()
for sheet in self._sheets:
sheet.write(wb)
wb.save(stream)
# -*-coding:UTF-8 -*-
from pymysql import connect
from xlwt.Workbook import Workbook
# 链接MySQL数据库
conn = connect(host='192.168.1.4',
user='******',
password="******",
database='cms',
port=3306)
# 游标
cursor = conn.cursor()
# 引出表格
cursor.execute('select * from wang_student')
result = cursor.fetchall()
print(result)
cursor.close()
conn.close()
wb = Workbook('utf-8')
# -*-coding:UTF-8 -*-
from xlwt.Workbook import Workbook
from xlwt.Formatting import Alignment
from xlwt.Style import XFStyle
# wb = Workbook('utf-8')
# sheet = wb.add_sheet('test')
# sheet.write(2, 2, 'test')
# wb.save(r'd:/test.xls')
wb = Workbook('utf-8')
sheet = wb.add_sheet('test2')
title = ['编号', '姓名', '职业', '上级', '入职日期']
title_style = XFStyle()
content_style = XFStyle()
alignment = Alignment()
alignment.horz = alignment.HORZ_CENTER
title_style.alignment = alignment
content_style.alignment = alignment
for i in range(len(title)):
sheet.write(0, i, title[i], title_style)
content = [[7369, 'SMITH', 'CLERK', 7902, '12/17/1980'],
[7499, 'ALLEN', 'SALESMAN', 7698, '2/20/1981'],
[7521, 'WARD', 'SALESMAN', 7698, '2/22/1981'],
[7566, 'JONES', 'MANAGER', 7839, '4/2/1981'],
[7654, 'MARTIN', 'SALESMAN', 7698, '9/28/1981'],
[7698, 'BLAKE', 'MANAGER', 7839, '5/1/1981'],
user='******',
password="******",
database='cms',
port=3306)
cursor = conn.cursor()
cursor.execute('select * from _cai_student_cai')
result = cursor.fetchall()
#print(result)
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
# 对str做分词
str = seg_depart(str)
str = str.strip()
rowList.append(str)
# 添加对应的标签0或1
# rowList.append(0)
rowList.append(1)
excellist.append(rowList)
print(excellist)
# excel表格式
book = Workbook()
sheet1 = book.add_sheet('Sheet1')
row0 = ['review', 'label']
for i in range(len(row0)):
sheet1.write(0, i, row0[i])
# 两个for循环，第一个for循环针对写入excel的每行，第二个for循环针对每行的各列
for i, li in enumerate(excellist):
print('i:{}, li:{}'.format(i, li))
for j, lj in enumerate(li):
sheet1.write(i+1, j, lj)
# 数据存入excel文件
book.save('neg_test_excel.xls')
book.save('pos_test_excel.xls')
def get_old_deposit_transfers_task():
old_transfers = DepositTransferNew.objects.order_by("user", "-created_at")
try:
book = Workbook()
chongzhi_sheet = book.add_sheet('ChongZhi')
koukuan_sheet = book.add_sheet('KouKuan')
chongzhi_row = 0
koukuan_row = 0
chongzhi_sheet.write(chongzhi_row, 0, u"客户编号")
chongzhi_sheet.write(chongzhi_row, 1, u"金额")
chongzhi_sheet.write(chongzhi_row, 2, u"单号")
chongzhi_sheet.write(chongzhi_row, 3, u"描述")
chongzhi_sheet.write(chongzhi_row, 4, u"时间")
chongzhi_sheet.write(chongzhi_row, 5, u"货币")
koukuan_sheet.write(koukuan_row, 0, u"客户编号")
koukuan_sheet.write(koukuan_row, 1, u"金额")
koukuan_sheet.write(koukuan_row, 2, u"单号")
koukuan_sheet.write(koukuan_row, 3, u"描述")
koukuan_sheet.write(koukuan_row, 4, u"时间")
koukuan_sheet.write(koukuan_row, 5, u"货币")
koukuan_sheet.write(koukuan_row, 6, u"已扣金额")
koukuan_sheet.write(koukuan_row, 7, u"申报应付金额")
koukuan_sheet.write(koukuan_row, 8, u"库房应付金额")
koukuan_sheet.write(koukuan_row, 9, u"计费重量")
koukuan_sheet.write(koukuan_row, 10, u"申报重量")
koukuan_sheet.write(koukuan_row, 11, u"申报长度")
koukuan_sheet.write(koukuan_row, 12, u"申报宽度")
koukuan_sheet.write(koukuan_row, 13, u"申报高度")
koukuan_sheet.write(koukuan_row, 14, u"库房重量")
koukuan_sheet.write(koukuan_row, 15, u"库房长度")
koukuan_sheet.write(koukuan_row, 16, u"库房宽度")
koukuan_sheet.write(koukuan_row, 17, u"库房高度")
chongzhi_row = 1
koukuan_row = 1
for old_transfer in old_transfers:
if old_transfer.amount >= 0.01:
chongzhi_sheet.write(chongzhi_row, 0, old_transfer.user.userprofile.customer_number)
chongzhi_sheet.write(chongzhi_row, 1, round(Decimal(old_transfer.amount), 2))
chongzhi_sheet.write(chongzhi_row, 2, old_transfer.origin)
chongzhi_sheet.write(chongzhi_row, 3, old_transfer.ref)
chongzhi_sheet.write(chongzhi_row, 4, old_transfer.created_at.strftime("%Y-%m-%d %H:%M:%S"))
chongzhi_sheet.write(chongzhi_row, 5, old_transfer.user.userprofile.deposit_currency_type)
chongzhi_row += 1
else:
koukuan_sheet.write(koukuan_row, 0, old_transfer.user.userprofile.customer_number)
koukuan_sheet.write(koukuan_row, 1, round(Decimal(old_transfer.amount), 2))
koukuan_sheet.write(koukuan_row, 2, old_transfer.origin)
koukuan_sheet.write(koukuan_row, 3, old_transfer.ref)
koukuan_sheet.write(koukuan_row, 4, old_transfer.created_at.strftime("%Y-%m-%d %H:%M:%S"))
koukuan_sheet.write(koukuan_row, 5, old_transfer.user.userprofile.deposit_currency_type)
try:
intl_parcel = IntlParcel.objects.get(yde_number=old_transfer.origin)
koukuan_sheet.write(koukuan_row, 6, round(Decimal(intl_parcel.booked_fee or 0), 2))
#logger.error('axxx')
koukuan_sheet.write(koukuan_row, 7, round(Decimal(intl_parcel.get_fee() or 0), 2)) # u"申报应付金额")
#logger.error('bxxx')
try:
real_fee=intl_parcel.get_real_fee()
except:
real_fee=0
koukuan_sheet.write(koukuan_row, 8, round(Decimal(real_fee), 2)) # u"库房应付金额")
#logger.error('cxxx')
vweight = intl_parcel.length_cm * intl_parcel.width_cm * intl_parcel.height_cm / 6000
real_vweight = intl_parcel.real_length_cm * intl_parcel.real_width_cm * intl_parcel.real_height_cm / 6000
chargeable_weight = max(w for w in [intl_parcel.weight_kg, vweight, intl_parcel.real_weight_kg, real_vweight])
#logger.error('dxxx')
koukuan_sheet.write(koukuan_row, 9, round(Decimal(chargeable_weight), 2)) # u"计费重量")
koukuan_sheet.write(koukuan_row, 10, round(Decimal(intl_parcel.weight_kg or 0), 2)) # u"申报重量")
koukuan_sheet.write(koukuan_row, 11, round(Decimal(intl_parcel.length_cm or 0), 2)) # u"申报长度")
koukuan_sheet.write(koukuan_row, 12, round(Decimal(intl_parcel.width_cm or 0), 2)) # u"申报宽度")
koukuan_sheet.write(koukuan_row, 13, round(Decimal(intl_parcel.height_cm or 0), 2)) # u"申报高度")
koukuan_sheet.write(koukuan_row, 14, round(Decimal(intl_parcel.real_weight_kg or 0), 2)) # u"库房重量")
koukuan_sheet.write(koukuan_row, 15, round(Decimal(intl_parcel.real_length_cm or 0), 2)) # u"库房长度")
koukuan_sheet.write(koukuan_row, 16, round(Decimal(intl_parcel.real_width_cm or 0), 2)) # u"库房宽度")
koukuan_sheet.write(koukuan_row, 17, round(Decimal(intl_parcel.real_height_cm or 0), 2)) # u"库房高度")
except IntlParcel.DoesNotExist:
try:
retoure = DhlRetoureLabel.objects.get(retoure_yde_number=old_transfer.origin)
koukuan_sheet.write(koukuan_row, 6, "DHL Retoure")
except DhlRetoureLabel.DoesNotExist:
koukuan_sheet.write(koukuan_row, 6, "NOOOOOOOOOO")
except DhlRetoureLabel.MultipleObjectsReturned:
koukuan_sheet.write(koukuan_row, 6, "Retoure Duplicated")
except Exception as e:
logger.error(e)
koukuan_sheet.write(koukuan_row, 6, "%s" % e)
except IntlParcel.MultipleObjectsReturned:
koukuan_sheet.write(koukuan_row, 6, "Intl Parcel Duplicated")
except Exception as e:
logger.error(e)
logger.error(intl_parcel.yde_number)
koukuan_sheet.write(koukuan_row, 6, "%s" % e)
koukuan_row += 1
book.save("/tmp/old_transfers.xls")
connection = get_connection(username=None,
password=None,
fail_silently=False)
mail = EmailMultiAlternatives(u"Old deposit transfers", "", u'韵达德国分公司 <*****@*****.**>', ["*****@*****.**"],
connection=connection)
mail.attach_file("/tmp/old_transfers.xls")
mail.send()
#return HttpResponse()
except Exception as e:
logger.error("%s" % e)
str += line
str = str.strip()
print('str:', str)
print('-----------------------------------------')
# 对str做分词
str = seg_depart(str)
str = str.strip()
rowList.append(str)
# 添加对应的标签0或1
# rowList.append(0)
rowList.append(1)
excellist.append(rowList)
# excel表格式
book = Workbook()
sheet1 = book.add_sheet('Sheet1')
row0 = ['review', 'label']
for i in range(len(row0)):
sheet1.write(0, i, row0[i])
# 两个for循环，第一个for循环针对写入excel的每行，第二个for循环针对每行的各列
for i, li in enumerate(excellist):
print('i:{}, li:{}'.format(i, li))
for j, lj in enumerate(li):
sheet1.write(i + 1, j, lj)
# 数据存入excel文件
# book.save('neg_fenci_excel.xls')
book.save('pos_fenci_excel.xls')
# -*-coding:UTF-8 -*-
from xlwt.Workbook import Workbook
from xlwt.Style import XFStyle
from importlib.resources import contents
Workbook
wb=Workbook('utf-8')
sheet=wb.add_sheet('测试')
title = ['编号', '姓名', '职业', '上级', '入职日期']
title_style = XFStyle()
for i in range(len(title)):
sheet.write(4,i + 4,title[i],title_style)
content = [7369,'SMITH','CLERK',7902,12/17/1980] ,[7499,'ALLEN','SALESMAN',7698,2/20/1981]
for i in range(len(content)):
for j in range(len(content[i])):
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 __init__(self, *args, **kw):
super(ExcelWriter, self).__init__(*args, **kw)
self._wb = Workbook()
class sudoku(object):
'''
classdocs
'''
def __init__(self):
self.sudoku=[]
self.sudokuCheck=[]
self.elements=[]
self.elementInit()
self.wb = Workbook()
self.ws0 = self.wb.add_sheet('sudoku1',cell_overwrite_ok=True)
self.sw=1
self.cont=0
self.var=[]
self.stack=[]
self.file= open('log.txt','w')
def elementInit(self):
self.elements=[1,2,3,4,5,6,7,8,9]
def writeExcel(self):
for i in range(0,9):
for j in range(0,9):
if len(self.sudokuCheck[i][j]) == 1:
n=self.sudokuCheck[i][j][0]
self.sudoku[i][j]=n
self.ws0.write(i,j,n)
else:
self.ws0.write(i,j,0.0)
self.sudoku[i][j]=0.0
def readSudoku(self):
wb = open_workbook('test.xls')
for s in wb.sheets():
print 'Sheet:',s.name
for row in range(s.nrows):
values = []
for col in range(s.ncols):
values.append(int(s.cell(row,col).value))
self.sudoku.append(values)
def globalCheck(self):
self.mostConstrainedVariable()
ng=30
value=0
for l in self.var:
cons=0
cons2=0
if len(self.sudokuCheck[l[0]][l[1]])>1:
for k in self.sudokuCheck[l[0]][l[1]]:
cons += self.checkRowConstrains(l[0], l[1], k)
cons += self.checkColumnConstrains(l[0], l[1], k)
cons += self.checkBoxConstrains(l[0], l[1], k)
cons2+=1
self.sw=1
else:
cons += self.checkRowConstrains(l[0], l[1], self.sudokuCheck[l[0]][l[1]][0])
cons += self.checkColumnConstrains(l[0], l[1], self.sudokuCheck[l[0]][l[1]][0])
cons += self.checkBoxConstrains(l[0], l[1], self.sudokuCheck[l[0]][l[1]][0])
self.sw=1
if cons<ng :
ng=cons
cons3=cons2
value=copy.deepcopy(l)
if(value!=0):
sc=copy.deepcopy(self.sudokuCheck)
self.file.writelines("----Introduccion de Elemento----\n")
self.file.writelines("se introdujo el valor:" + str(self.sudokuCheck[value[0]][value[1]][cons3-1]) +" En la posicion: "+ str(value[0]+1)+","+str(value[1]+1)+ " ya que es la variable mas restringida y menos restrictora\n")
self.file.writelines( "--------------------------------\n")
self.file.writelines( "----Sudoku----\n")
self.file.writelines( str(self.sudoku)+"\n")
self.sudoku[value[0]][value[1]]=self.sudokuCheck[value[0]][value[1]][cons3-1]
stackItem=[value[0],value[1],sc[value[0]][value[1]]]
self.stack.append(stackItem)
sc[value[0]][value[1]].pop(cons3-1)
self.file.writelines( str(self.sudoku )+"\n")
self.file.writelines( "----Sudoku----\n")
def checkSudokuCheck(self):
for i in range(0,9):
for j in range(0,9):
if(len(self.sudokuCheck[i][j])==0):
self.file.writelines( "----- Backtrack -----\n" )
return 0
return 1
def cycleSudoku(self):
cont=0
while self.sw:
self.sudokuCheck=[]
self.sw=0
self.checkSudoku()
cont+=1
self.file.writelines( "---- Numero de Iteraciones ----\n")
self.file.writelines(str( cont)+"\n")
self.file.writelines( "-------------------------------\n")
self.writeExcel()
self.wb.save('sudoku.xls')
self.file.close()
print "El sudoku a sido resuelto\n...\n...\n...\nlog.txt creado\nsudoku.xls creado"
def checkSudoku(self):
for i in range(0,9):
jcheck=[]
for j in range(0,9):
v=[]
if self.sudoku[i][j]==0.0:
self.checkRow(i, j)
self.checkColumn(i, j)
self.checkBox(i, j)
v=self.elements
self.elementInit()
else:
v.append(self.sudoku[i][j])
jcheck.append(v)
self.sudokuCheck.append(jcheck)
self.file.writelines( "----- Matriz de Posibilidades -----\n")
for k1 in range(0,9):
s=[]
for k2 in range(0,9):
s.append(self.sudokuCheck[k1][k2])
self.file.writelines( str(s)+"\n")
self.file.writelines( "-------------------------\n")
if(self.checkSudokuCheck()):
self.globalCheck()
else:
self.sudokuBacktrack()
def sudokuBacktrack(self):
sw1=1
while sw1:
b=self.stack.pop()
if len(b[2])!=0:
self.file.writelines( "backtracking en la posicion: "+str(b[0]+1)+","+str(b[1]+1)+"... Posibilidades disponibles: "+str(b[2])+"\n")
sw1=0
else:
self.sudoku[b[0]][b[1]]=0
maxc=30
i2=0
for i in range(0,len(b[2])):
conti=0
conti+=self.checkRowConstrains(b[0], b[1], b[2][i])
conti+=self.checkColumnConstrains(b[0], b[1], b[2][i])
conti+=self.checkBoxConstrains(b[0], b[1], b[2][i])
if conti<maxc:
maxc=conti
i2=i
self.sudoku[b[0]][b[1]]=b[2].pop(i2)
self.stack.append(b)
self.sw=1
def checkRow(self,i,j):
for k in range(0,9):
if(self.elements.count((self.sudoku[i][k]))>0):
self.elements.remove(self.sudoku[i][k])
def checkColumn(self,i,j):
for k in range(0,9):
if(self.elements.count((self.sudoku[k][j]))>0):
self.elements.remove(self.sudoku[k][j])
def checkBox(self,i,j):
if i<3:
if j<3:
i2=0
j2=0
elif j>=3 and j<6:
i2=0
j2=3
else:
i2=0
j2=6
elif i>=3 and i<6:
if j<3:
i2=3
j2=0
elif j>=3 and j<6:
i2=3
j2=3
else:
i2=3
j2=6
else:
if j<3:
i2=6
j2=0
elif j>=3 and j<6:
i2=6
j2=3
else:
i2=6
j2=6
for i3 in range(i2,i2+3):
for j3 in range(j2,j2+3):
if(self.elements.count((self.sudoku[i3][j3]))>0):
self.elements.remove(self.sudoku[i3][j3])
def checkRowConstrains(self,i,j,n):
rowConst=0
for k in range(0,9):
if(k!=j and self.sudokuCheck[i][k].count(n)==1):
rowConst += 1
return rowConst
def checkColumnConstrains(self,i,j,n):
columnConst=0
for k in range(0,9):
if(k!=i and self.sudokuCheck[k][j].count(n)==1):
columnConst += 1
return columnConst
def checkBoxConstrains(self,i,j,n):
boxConst=0
if i<3:
if j<3:
i2=0
j2=0
elif j>=3 and j<6:
i2=0
j2=3
else:
i2=0
j2=6
elif i>=3 and i<6:
if j<3:
i2=3
j2=0
elif j>=3 and j<6:
i2=3
j2=3
else:
i2=3
j2=6
else:
if j<3:
i2=6
j2=0
elif j>=3 and j<6:
i2=6
j2=3
else:
i2=6
j2=6
for i3 in range(i2,i2+3):
for j3 in range(j2,j2+3):
if i3!=i and j3!=j and self.sudokuCheck[i3][j3].count(n)==1:
boxConst+=1
return boxConst
def mostConstrainedVariable(self):
m=10
var=[]
for i in range(0,9):
for j in range(0,9):
if self.sudoku[i][j]==0:
if len(self.sudokuCheck[i][j]) <= m:
m=len(self.sudokuCheck[i][j])
var.append([i,j])
var2=copy.deepcopy(var)
for v in var2:
if len(self.sudokuCheck[v[0]][v[1]])>m:
var.remove(v)
self.var=copy.deepcopy(var)
def _new_workbook(self):
return Workbook()
# -*-coding:UTF-8 -*-
from xlwt.Formatting import Borders, Font, Alignment, Pattern
from xlwt.Style import XFStyle
from xlwt.Workbook import Workbook
from tkinter import font
wb = Workbook()
sheet = wb.add_sheet('测试报告')
sheet.write(1, 1, '超级无敌龟派气功')
wb.save(r'd:\自动化测试.xls')
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
# -*- 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 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 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)
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
# -*-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 admin_excel_users(request):
book = Workbook()
sheet = book.add_sheet('users')
row = 0
sheet.write(row, 0, u'电子邮件')
sheet.write(row, 1, u'姓名')
sheet.write(row, 2, u'历史总单量')
sheet.write(row, 3, u'1510单量')
sheet.write(row, 4, u'1511单量')
sheet.write(row, 5, u'1512单量')
sheet.write(row, 6, u'1601单量')
sheet.write(row, 7, u'1602单量')
sheet.write(row, 8, u'1603单量')
sheet.write(row, 9, u'1604单量')
sheet.write(row, 10, u'联系电话')
sheet.write(row, 11, u'城市')
sheet.write(row, 12, u'公司')
sheet.write(row, 13, u'地址')
row += 1
try:
for user in User.objects.all():
try:
userprofile=user.userprofile
except:
continue
shipments = IntlParcel.objects.filter(user=user)
sheet.write(row, 0, user.email)
sheet.write(row, 1, user.get_full_name() or "")
sheet.write(row, 2, shipments.exclude(is_deleted=True).count())
sheet.write(row, 3, shipments.exclude(is_deleted=True).filter(created_at__year="2015").filter(created_at__month="10").count())
sheet.write(row, 4, shipments.exclude(is_deleted=True).filter(created_at__year="2015").filter(created_at__month="11").count())
sheet.write(row, 5, shipments.exclude(is_deleted=True).filter(created_at__year="2015").filter(created_at__month="12").count())
sheet.write(row, 6, shipments.exclude(is_deleted=True).filter(created_at__year="2016").filter(created_at__month="01").count())
sheet.write(row, 7, shipments.exclude(is_deleted=True).filter(created_at__year="2016").filter(created_at__month="02").count())
sheet.write(row, 8, shipments.exclude(is_deleted=True).filter(created_at__year="2016").filter(created_at__month="03").count())
sheet.write(row, 9, shipments.exclude(is_deleted=True).filter(created_at__year="2016").filter(created_at__month="04").count())
address=""
tel=""
city=""
company=""
if userprofile and userprofile.street and userprofile.street not in ["-", "_", ]:
address = user.get_full_name() + ", " + userprofile.street + " " + (userprofile.hause_number or "")
tel=userprofile.tel or ""
city=userprofile.city or ""
company=userprofile.company or ""
elif shipments.count()>0:
shipment=shipments.order_by("-created_at")[0]
address = shipment.sender_name + ", "+(shipment.sender_name2 or "")+", " + shipment.sender_street + " " + (shipment.sender_hause_number or "")
tel=shipment.sender_tel or ""
city=shipment.sender_city or ""
company=shipment.sender_company or ""
sheet.write(row, 10, tel or "")
sheet.write(row, 11, city or "")
sheet.write(row, 12, company or "")
sheet.write(row, 13, address or "")
sheet.write(row, 14, userprofile.level and userprofile.level.code or "")
row += 1
response = HttpResponse(content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=users-%s.xls' % datetime.now().strftime('%Y%m%d%H%M%S')
book.save(response)
return response
except Exception as e:
logger.error(e)
return HttpResponse(e)
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)
def generate_excel(request,surveyId):
survey = Survey.objects.get(id=surveyId)
surveyItemCollection = SurveyItem.objects.filter(survey=survey)
wb = Workbook()
for index,surveyItem in enumerate(surveyItemCollection):
surveyReportObject = SurveyReportObject()
work_sheet = wb.add_sheet("work-sheet" + str(index))
if surveyItem.item_type == 'SINGLE_CHOICE' or surveyItem.item_type == 'MULTIPLE_CHOICE':
surveyAnswerCollection = SurveyItemAnswer.objects.filter(survey_item=surveyItem)
for idx,surveyAnswer in enumerate(surveyAnswerCollection):
surveyReportObject.surveyAnswerValueDict[surveyAnswer.id]=0
surveyReportObject.surveyAnswerTextDict[surveyAnswer.id]=surveyAnswer.question_text
surveyResultCollection = SurveyResult.objects.filter(survey=survey,survey_item=surveyItem)
for result in surveyResultCollection:
if result.survey_result_type == 'STANDARD':
surveyReportObject.surveyAnswerValueDict[result.survey_item_answer_item.id] += 1
else:
surveyReportObject.surveyOptionValues.append(result.survey_item_answer_value)
work_sheet.write(0,0,surveyItem.item_name)
for idx,surveyAnswer in enumerate(surveyAnswerCollection):
work_sheet.write(1,idx,surveyReportObject.surveyAnswerTextDict[surveyAnswer.id])
work_sheet.write(2,idx,surveyReportObject.surveyAnswerValueDict[surveyAnswer.id])
if len(surveyReportObject.surveyOptionValues) > 0:
work_sheet.write(4,0,u"其他答案")
for idx,optionValues in enumerate(surveyReportObject.surveyOptionValues):
work_sheet.write(5+idx,0,optionValues)
elif surveyItem.item_type == 'TEXT' or surveyItem.item_type == 'TEXT_AREA':
work_sheet.write(0,0,surveyItem.item_name)
surveyResultCollection = SurveyResult.objects.filter(survey=survey,survey_item=surveyItem)
for idx,result in enumerate(surveyResultCollection):
work_sheet.write(idx+1,0,result.survey_item_answer_value)
elif surveyItem.item_type == 'METRIX':
work_sheet.write(0,0,surveyItem.item_name)
surveyAnswerCollection = SurveyItemAnswer.objects.filter(survey_item=surveyItem)
# itemValues = surveyAnswerCollection[0].question_value
answer_position_dict = {}
for idx,answer in enumerate(surveyAnswerCollection):
answer_position_dict[answer.id]=1+idx
work_sheet.write(1,1+idx,answer.question_text)
# for col in range(len(surveyAnswerCollection[0].question_value.split("\n"))):
# for row in range(len(answer_position_dict)):
# work_sheet.write(2+col,1+row,0)
for idx,item_value in enumerate(surveyAnswerCollection[0].question_value.split("\n")):
work_sheet.write(2+idx,0,item_value)
item_value_dict = {}
surveyResultCollection = SurveyResult.objects.filter(survey=survey,survey_item=surveyItem)
for result_idx,result in enumerate(surveyResultCollection):
if result.survey_item_answer_value == item_value:
if item_value_dict.has_key(result.survey_item_answer_item.id):
item_value_dict[result.survey_item_answer_item.id] += 1
else:
item_value_dict[result.survey_item_answer_item.id] = 1
for key in item_value_dict.iterkeys():
work_sheet.write(2+idx,answer_position_dict[key],item_value_dict[key])
elif surveyItem.item_type == 'MULTIPLE_TEXT':
work_sheet.write(0,0,surveyItem.item_name)
surveyAnswerCollection = SurveyItemAnswer.objects.filter(survey_item=surveyItem)
for idx,answer in enumerate(surveyAnswerCollection):
resultCollection = []
surveyResultCollection = SurveyResult.objects.filter(survey=survey,survey_item=surveyItem)
for result in surveyResultCollection:
if result.survey_item_answer_item == answer:
resultCollection.append(result.survey_item_answer_value)
work_sheet.write(1,idx,answer.question_text)
for result_idx,result in enumerate(resultCollection):
work_sheet.write(result_idx+2,idx,result)
wb.save('/tmp/' + surveyId + ".xls")
f=open('/tmp/' + surveyId + ".xls",'rb')
f.seek(0,0)
my_data = f.readlines()
response = HttpResponse(my_data, content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=report.xls'
os.remove('/tmp/' + surveyId + ".xls")
return response
