def get(self, request):
content_title = '故障列表'
web_title = 'record'
web_func = 'record_list'
user_list = UserProfile.objects.all()
records = FaultRecord.objects.all().order_by('-start_time')
tag_list = RecordTags.objects.all()
export_status = request.GET.get('export', '')
# 导出所有记录
if export_status == 'all':
export_data = records
if export_data:
# 创建工作簿
new_excel = xlwt.Workbook(encoding='utf-8')
excel_page = new_excel.add_sheet(u'故障记录')
# 插入第一行标题
excel_page.write(0, 0, u'省份')
excel_page.write(0, 1, u'市')
excel_page.write(0, 2, u'区')
excel_page.write(0, 3, u'平台名称')
excel_page.write(0, 4, u'故障事件')
excel_page.write(0, 5, u'故障时间')
excel_page.write(0, 6, u'故障原因')
excel_page.write(0, 7, u'故障标签')
excel_page.write(0, 8, u'处理办法')
excel_page.write(0, 9, u'处理人')
excel_page.write(0, 10, u'处理时间')
excel_page.write(0, 11, u'处理结果')
excel_page.write(0, 12, u'备注')
excel_page.write(0, 13, u'记录添加修改时间')
# 初始行
excel_row = 1
# 插入数据
for each in export_data:
# 获取数据
province = each.province.name
city = each.city
if (city is not None) and (city != ''):
city = city.name
else:
city = '无'
area = each.area
if (area is not None) and (area != ''):
area = each.area.name
else:
area = '无'
platform = each.platform
incident = each.incident
start_time = each.start_time
reason = each.reason
tag_obj = each.tag.all()
if (tag_obj.exists()):
# 拼接标签
tag = ''
line = [' / '] * len(tag_obj)
line[-1] = ''
for each_tag, eachline in zip(tag_obj, line):
tag = tag + each_tag.name + eachline
else:
tag = '暂无标签'
handling_method = each.handling_method
handling_person_obj = each.handling_person.all()
# 拼接用户
handling_person = ''
line = [' / '] * len(handling_person_obj)
line[-1] = ''
for user, eachline in zip(handling_person_obj, line):
handling_person = handling_person + str(user.nick_name) + eachline
handling_time = each.handling_time
if each.result == 0:
result = '已完成'
elif each.result == 1:
result = '未完成'
else:
result = '暂时无法完成'
ps = each.ps
if (ps is None) or (ps == ''):
ps = '无'
date = each.date
time_style = 'YYYY/MM/DD HH:mm'
# time_style = 'YYYY/MM/DD hh:mm AM/PM'
style = xlwt.XFStyle()
style.num_format_str = time_style
# 写数据
excel_page.write(excel_row, 0, province)
excel_page.write(excel_row, 1, city)
excel_page.write(excel_row, 2, area)
excel_page.write(excel_row, 3, platform)
excel_page.write(excel_row, 4, incident)
excel_page.write(excel_row, 5, start_time, style)
excel_page.write(excel_row, 6, reason)
excel_page.write(excel_row, 7, tag)
excel_page.write(excel_row, 8, handling_method)
excel_page.write(excel_row, 9, handling_person)
excel_page.write(excel_row, 10, handling_time, style)
excel_page.write(excel_row, 11, result)
excel_page.write(excel_row, 12, ps)
excel_page.write(excel_row, 13, date, style)
# 行数加1
excel_row += 1
time_now = time.strftime("%Y%m%d%H%M%S", time.localtime())
file_name = 'AllRecord_' + time_now + '.xls'
# 网页下载
response = HttpResponse(content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment;filename={}'.format(file_name)
output = BytesIO()
new_excel.save(output)
output.seek(0)
response.write(output.getvalue())
return response
# 用户搜索
keywords = request.GET.get('keywords', '')
if keywords != '':
records = records.filter(
Q(province__name__icontains=keywords) | \
Q(city__name__icontains=keywords) | \
Q(area__name__icontains=keywords) | \
Q(platform__icontains=keywords) | \
Q(incident__icontains=keywords) | \
Q(reason__icontains=keywords) | \
Q(handling_method__icontains=keywords) | \
Q(ps__icontains=keywords)
)
content_title = '关键字 <span style="color:orangered">"' + str(keywords) + '"</span> 搜索结果'
# 搜索条件
search_province = request.GET.get('province', '')
search_city = request.GET.get('city', '')
search_area = request.GET.get('area', '')
search_start_time = request.GET.get('start_time', '')
search_stop_time = request.GET.get('stop_time', '')
search_tag = request.GET.getlist('tag')
search_user = request.GET.getlist('user')
search_result = request.GET.get('result', '')
# 搜索条件筛选
if (search_province is not None) and (search_province != ''):
records = records.filter(province__id=int(search_province))
content_title = '故障搜索结果'
if (search_city is not None) and (search_city != ''):
records = records.filter(city__id=int(search_city))
content_title = '故障搜索结果'
if (search_area is not None) and (search_area != ''):
records = records.filter(area__id=int(search_area))
content_title = '故障搜索结果'
if (search_start_time is not None) and (search_start_time != ''):
records = records.filter(start_time__gt=search_start_time)
content_title = '故障搜索结果'
if (search_stop_time is not None) and (search_stop_time != ''):
records = records.filter(start_time__lt=search_stop_time)
content_title = '故障搜索结果'
if (len(search_tag) != 0):
records = records.filter(tag__in=search_tag).distinct()
content_title = '故障搜索结果'
if (len(search_user) != 0):
records = records.filter(handling_person__in=search_user).distinct()
content_title = '故障搜索结果'
if (search_result is not None) and (search_result != ''):
if search_result != 'all':
records = records.filter(result=int(search_result))
content_title = '故障搜索结果'
# 导出搜索记录
if export_status == 'search':
export_data = records
if export_data:
# 创建工作簿
new_excel = xlwt.Workbook(encoding='utf-8')
excel_page = new_excel.add_sheet(u'故障记录')
# 插入第一行标题
excel_page.write(0, 0, u'省份')
excel_page.write(0, 1, u'市')
excel_page.write(0, 2, u'区')
excel_page.write(0, 3, u'平台名称')
excel_page.write(0, 4, u'故障事件')
excel_page.write(0, 5, u'故障时间')
excel_page.write(0, 6, u'故障原因')
excel_page.write(0, 7, u'故障标签')
excel_page.write(0, 8, u'处理办法')
excel_page.write(0, 9, u'处理人')
excel_page.write(0, 10, u'处理时间')
excel_page.write(0, 11, u'处理结果')
excel_page.write(0, 12, u'备注')
excel_page.write(0, 13, u'记录添加修改时间')
# 初始行
excel_row = 1
# 插入数据
for each in export_data:
# 获取数据
province = each.province.name
city = each.city
if (city is not None) and (city != ''):
city = city.name
else:
city = '无'
area = each.area
if (area is not None) and (area != ''):
area = each.area.name
else:
area = '无'
platform = each.platform
incident = each.incident
start_time = each.start_time
reason = each.reason
tag_obj = each.tag.all()
if (tag_obj.exists()):
# 拼接标签
tag = ''
line = [' / '] * len(tag_obj)
line[-1] = ''
for each_tag, eachline in zip(tag_obj, line):
tag = tag + each_tag.name + eachline
else:
tag = '暂无标签'
handling_method = each.handling_method
handling_person_obj = each.handling_person.all()
# 拼接用户
handling_person = ''
line = [' / '] * len(handling_person_obj)
line[-1] = ''
for user, eachline in zip(handling_person_obj, line):
handling_person = handling_person + str(user.nick_name) + eachline
handling_time = each.handling_time
if each.result == 0:
result = '已完成'
elif each.result == 1:
result = '未完成'
else:
result = '暂时无法完成'
ps = each.ps
if (ps is None) or (ps == ''):
ps = '无'
date = each.date
time_style = 'YYYY/MM/DD HH:mm'
# time_style = 'YYYY/MM/DD hh:mm AM/PM'
style = xlwt.XFStyle()
style.num_format_str = time_style
# 写数据
excel_page.write(excel_row, 0, province)
excel_page.write(excel_row, 1, city)
excel_page.write(excel_row, 2, area)
excel_page.write(excel_row, 3, platform)
excel_page.write(excel_row, 4, incident)
excel_page.write(excel_row, 5, start_time, style)
excel_page.write(excel_row, 6, reason)
excel_page.write(excel_row, 7, tag)
excel_page.write(excel_row, 8, handling_method)
excel_page.write(excel_row, 9, handling_person)
excel_page.write(excel_row, 10, handling_time, style)
excel_page.write(excel_row, 11, result)
excel_page.write(excel_row, 12, ps)
excel_page.write(excel_row, 13, date, style)
# 行数加1
excel_row += 1
time_now = time.strftime("%Y%m%d%H%M%S", time.localtime())
file_name = 'SearchRecord_' + time_now + '.xls'
# 网页下载
response = HttpResponse(content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment;filename={}'.format(file_name)
output = BytesIO()
new_excel.save(output)
output.seek(0)
response.write(output.getvalue())
return response
# 记录数量
record_nums = records.count()
# 判断页码
try:
page = request.GET.get('page', 1)
except PageNotAnInteger:
page = 1
# 对取到的数据进行分页，记得定义每页的数量
p = Paginator(records, 10, request=request)
# 分页处理后的 QuerySet
records = p.page(page)
context = {
'content_title': content_title,
'web_title': web_title,
'web_func': web_func,
'records': records,
'user_list': user_list,
'record_nums': record_nums,
'tag_list': tag_list,
# 搜索选项
'search_province': search_province,
'search_city': search_city,
'search_area': search_area,
'search_start_time': search_start_time,
'search_stop_time': search_stop_time,
'search_tag': search_tag,
'search_user': search_user,
'search_result': search_result,
}
return render(request, 'record/record_list.html', context=context)
def search_tb(path, page, num, text):
global lock1
global sign_stop
if lock1 == 1:
return
else:
lock1 = 1
if sign_stop == 1:
sign_stop = 0
if path == "":
show_process(text, "[Errno 1]输入文件为空\n")
return
if page == "" or num_check(page, text) < 1 or num_check(page, text) > 100:
show_process(text, "[Errno 1] 输入页数有误\n")
return
if num == "" or num_check(num, text) < 1 or num_check(num, text) > 44:
show_process(text, "[Errno 1] 输入个数有误\n")
return
try:
if sign_stop == 1:
lock1 = 0
sign_stop = 0
clear_process(text)
return
today_date = datetime.datetime.today().strftime("%Y-%m-%d_%H%M%S")
show_process(text, "Open file:" + path + "\n")
with open(path, 'r', encoding='utf-8') as file:
list = file.readlines()
count = 0
excel1 = xlwt.Workbook()
sheet1 = excel1.add_sheet('商品表', cell_overwrite_ok=True)
sheet1.col(0).width = 2000
sheet1.col(1).width = 15000
sheet1.col(2).width = 2000
sheet1.col(3).width = 1500
sheet1.col(4).width = 3000
sheet1.col(5).width = 5000
sheet1.col(6).width = 2000
sheet1.col(7).width = 3000
sheet1.col(8).width = 36000
#样式组件
alignment1 = xlwt.Alignment()
alignment1.horz = 0x01
alignment1.vert = 0x00
border1 = xlwt.Borders()
border1.left = xlwt.Borders.THICK
border1.right = xlwt.Borders.THICK
border1.top = xlwt.Borders.THICK
border1.bottom = xlwt.Borders.THICK
border1.left_colour = 0x3A
border1.right_colour = 0x3A
border1.top_colour = 0x3A
border1.bottom_colour = 0x3A
pattern1 = xlwt.Pattern()
pattern1.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern1.pattern_fore_colour = 3 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
pattern2 = xlwt.Pattern()
pattern2.pattern = xlwt.Pattern.SOLID_PATTERN
pattern2.pattern_fore_colour = 22
font1 = xlwt.Font()
font1.name = u'宋体'
font2 = xlwt.Font()
font2.name = u'微软雅黑'
font2.bold = True
font2.colour_index = 0x50
#样式配置
style1 = xlwt.XFStyle()
style1.border = border1
style1.alignment = alignment1
style1.font = font1
style2 = xlwt.XFStyle()
style2.border = border1
style2.alignment = alignment1
style2.font = font2
style2.pattern = pattern1
style3 = xlwt.XFStyle()
style3.border = border1
style3.alignment = alignment1
style3.font = font2
style3.pattern = pattern2
#标题
sheet1.write(count, 0, "ID", style2)
sheet1.write(count, 1, "商品名称", style2)
sheet1.write(count, 2, "价格", style2)
sheet1.write(count, 3, "运费", style2)
sheet1.write(count, 4, "销量", style2)
sheet1.write(count, 5, "商铺", style2)
sheet1.write(count, 6, "评论数", style2)
sheet1.write(count, 7, "城市", style2)
sheet1.write(count, 8, "图片链接", style2)
count = count + 1
for p in range(0, len(list)):
list[p] = list[p].rstrip('\n')
if list[p] == "":
continue
print(list[p])
show_process(text, "正在查询：" + list[p] + "\n")
flag = 0
while (flag == 0):
# 定义要查询的商品关键词
sheet1.write(count, 0, str(count), style1)
sheet1.write_merge(count, count, 1, 8,
"【" + str(p + 1) + "】" + list[p], style3)
count = count + 1
keywords = urllib.request.quote(list[p])
print(keywords)
# 定义要爬取的页数
for i in range(int(page)):
if sign_stop == 1:
lock1 = 0
sign_stop = 0
clear_process(text)
return
url = "https://list.tmall.com/search_product.htm?q=" + keywords + "&type=p&vmarket=&spm=a211oj.0.a2227oh.d100&from=..pc_1_searchbutton"
#url ="https://s.taobao.com/search?q="+ keywords +"&imgfile=&js=1&stats_click=search_radio_all%3A1&initiative_id=staobaoz_20190226&ie=utf8"
#url ="https://s.taobao.com/search?q="+ keywords + "&imgfile=&commend=all&ssid=s5-e&search_type=item&sourceId=tb.index&spm=a21bo.2017.201856-taobao-item.1&ie=utf8&bcoffset=4&ntoffset=4&p4ppushleft=1%2C48&s="+ str(i * 44)
#url = "https://s.taobao.com/search?q=" + keywords + "&imgfile=&commend=all&ssid=s5-e&search_type=item&sourceId=tb.index&spm=a21bo.50862.201856-taobao-item.1&ie=utf8&bcoffset=4&ntoffset=4&p4ppushleft=1%2C48&s=" + str(i * 44)
#url = "https://s.taobao.com/search?q="+ keywords +"&imgfile=&js=1&stats_click=search_radio_all%3A1&initiative_id=staobaoz_20190304&ie=utf8"
#url = "https://s.taobao.com/search?ie=utf8&initiative_id=staobaoz_20170905&stats_click=search_radio_all%3A1&js=1&imgfile=&q="+keywords+"&suggest=0_1&_input_charset=utf-8&wq=u&suggest_query=u&source=suggest&p4ppushleft=5%2C48&s="+ str(i * 44)
data = url_open(url)
print(url)
# 定义各个字段正则匹配规则
img_pat = '"pic_url":"(//.*?)"'
name_pat = '"raw_title":"(.*?)"'
nick_pat = '"nick":"(.*?)"'
price_pat = '"view_price":"(.*?)"'
fee_pat = '"view_fee":"(.*?)"'
sales_pat = '"view_sales":"(.*?)"'
comment_pat = '"comment_count":"(.*?)"'
city_pat = '"item_loc":"(.*?)"'
#detail_url_pat = 'detail_url":"(.*?)"'
# 查找满足匹配规则的内容，并存在列表中
imgL = re.compile(img_pat).findall(data)
nameL = re.compile(name_pat).findall(data)
nickL = re.compile(nick_pat).findall(data)
priceL = re.compile(price_pat).findall(data)
feeL = re.compile(fee_pat).findall(data)
salesL = re.compile(sales_pat).findall(data)
commentL = re.compile(comment_pat).findall(data)
cityL = re.compile(city_pat).findall(data)
#detail_urlL = re.compile(detail_url_pat).findall(data)
# print('正在爬取第' + str(i+1) + "页，第" + str(j) + "个商品信息...")
show_process(
text, " 正在爬取第" + str(i + 1) + "页的" + str(int(num)) +
"个商品信息...\n")
if len(imgL) > 0:
flag = 1
continue
for j in range(len(imgL)):
if sign_stop == 1:
lock1 = 0
sign_stop = 0
clear_process(text)
return
if j > int(num):
continue
img = "http:" + imgL[j] # 商品图片链接
name = nameL[j] # 商品名称
nick = nickL[j] # 淘宝店铺名称
price = priceL[j] # 商品价格
fee = feeL[j] # 运费
sales = salesL[j] # 商品付款人数
comment = commentL[j] # 商品评论数，会存在为空值的情况
if (comment == ""):
comment = 0
city = cityL[j] # 店铺所在城市
#detail_url = detail_urlL[j] # 商品链接
# sql = "insert into list2(name,price,fee,sales,comment,city,nick,img,detail_url) values('%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (name, price, fee, sales, comment, city, nick, img, detail_url)
# data_Import(sql)
sheet1.write(count, 0, str(count), style1)
sheet1.write(count, 1, name, style1)
sheet1.write(count, 2, price, style1)
sheet1.write(count, 3, fee, style1)
sheet1.write(count, 4, sales, style1)
sheet1.write(count, 5, nick, style1)
sheet1.write(count, 6, comment, style1)
sheet1.write(count, 7, city, style1)
sheet1.write(count, 8, img, style1)
count = count + 1
time.sleep(0.2)
# print("爬取完成，且数据已存入数据库")
show_process(text, " 该页爬取完成，数据已存入Excel缓存\n")
percent = round(float((p + 1) * 100 / len(list)), 2)
show_process(text, "检索进度：" + str(percent) + "%\n")
save_path = "商品信息" + today_date + ".xls"
excel1.save(save_path)
#excel1.save('Commodity.xls')
show_process(
text, "#############################################\n# 导出 " +
save_path +
" 成功 #\n#############################################\n")
lock1 = 0
except Exception as e:
#print(str(e))
show_process(text, str(e) + '\n')
def get_xls(allWord, filename_1, filename_2):
work_book = xlwt.Workbook(encoding='utf-8')
borders = xlwt.Borders() # 都有黑边框
borders1 = xlwt.Borders() # 右边框没有
borders2 = xlwt.Borders() # 左边框没有
borders3 = xlwt.Borders() # 左右都没有边框
borders.left = xlwt.Borders.THIN
borders1.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders2.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders1.top = xlwt.Borders.THIN
borders2.top = xlwt.Borders.THIN
borders3.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders1.bottom = xlwt.Borders.THIN
borders2.bottom = xlwt.Borders.THIN
borders3.bottom = xlwt.Borders.THIN
alignment = xlwt.Alignment() # 对齐情况
alignment.horz = xlwt.Alignment.HORZ_CENTER # 水平对齐
style = xlwt.XFStyle() # 第一行格式,全黑边框，居中，宋体
style1 = xlwt.XFStyle() # 第一列格式,全黑边框，默认左对齐，宋体
style2 = xlwt.XFStyle() # 中间格式,只有上下边框，居中，宋体
style3 = xlwt.XFStyle() # 左中格式，右没有边框，居中，宋体
style4 = xlwt.XFStyle() # 右中格式，左没有边框，居中，宋体
font = xlwt.Font()
font.name = u'宋体' # 字体设置成宋体
style.font = font
style1.font = font
style2.font = font
style3.font = font
style4.font = font
style.alignment = alignment
style2.alignment = alignment
style3.alignment = alignment
style4.alignment = alignment
style.borders = borders
style1.borders = borders
style2.borders = borders3
style3.borders = borders1
style4.borders = borders2
# 创建了3个sheet
sheet1 = work_book.add_sheet('recall')
sheet2 = work_book.add_sheet('far')
sheet3 = work_book.add_sheet('frr')
sheet4 = work_book.add_sheet('right')
sheet5 = work_book.add_sheet('error')
sheet6 = work_book.add_sheet('wav_result')
# 设置sheet1格式
first_col = sheet1.col(0) # 设置第一列的宽度xlwt中是行和列都是从0开始计算的
first_col.width = 256 * 15
# 设置第一列的宽度
first_col1 = sheet1.col(0)
first_col2 = sheet2.col(0)
first_col3 = sheet3.col(0)
first_col1.width = 256 * 15
first_col2.width = 256 * 15
first_col3.width = 256 * 15
# 设置sheet2格式
first_col = sheet2.col(0)
first_col.width = 256 * 15
sheet2.write(0, 0, 'labels', style1)
sheet2.write(0, 1, 'TOTAL', style)
sheet2.write(0, 2, 'FP', style)
sheet2.write(0, 3, 'FAR', style)
# 设置sheet3格式
first_col.width = 256 * 15
sheet3.write(0, 0, 'labels', style)
sheet3.write(0, 1, 'TOTAL', style)
sheet3.write(0, 2, 'FN', style)
sheet3.write(0, 3, 'frr', style)
sheet4.write_merge(0, 0, 0, 4, '各个词识别正确的得分情况', style)
sheet5.write_merge(0, 0, 0, 4, '各个词识别错误的得分情况', style)
sheet6.write(0, 0, '播放词')
sheet6.write(0, 1, 'wav名称', style)
sheet6.write(0, 2, '识别结果', style)
sheet6.write(0, 3, '识别词', style)
a = 1
for i in range(0, 12):
sheet3.write(0, i + 3 + a, '误识词' + str(a), style)
sheet3.write(0, i + 4 + a, '误识率', style)
a += 1
# 写入sheet1的数据
msg = filename_2.split('_')
sheet1.write(1, 0, '板卡信息', style)
sheet1.write(1, 1, msg[2], style)
sheet1.write(1, 2, '板卡增益', style)
sheet1.write_merge(1, 1, 3, 4, msg[4], style)
sheet1.write_merge(2, 3, 0, 0, '词条', style)
sheet1.write_merge(2, 2, 1, 4, 'overall', style)
sheet1.write(3, 1, 'TOTAL', style)
sheet1.write(3, 2, 'TP', style)
sheet1.write(3, 3, 'FN', style)
sheet1.write(3, 4, 'recall', style)
wordlist, ver = del_slaver(filename_2) # 获取slave的识别结果词
wavList = get_play_time(filename_1, allWord)
sheet1.write(0, 0, '版本信息', style)
sheet1.write_merge(0, 0, 1, 4, ver, style)
if wavList:
result_sort = sort_list(wavList, wordlist)
write_log(result_sort, filename_2)
dicnew, wav_result = del_result(result_sort, allWord)
LR, LE, LL = reco_dic(dicnew)
Lrlen = len(LR) // 2
Lelen = len(LE) // 3
Lllen = len(LL)
All = Lrlen + Lllen + Lelen
wakeall = 0
waketp = 0
cmdtp = 0
cmdall = 0
cmdfp = 0
for i in range(len(allWord)):
if '你好nomi' == i:
continue
le = 0
FP = 0
TP = LR.count(allWord[i])
re = 1 # 标记误识别的列数
sheet5.write(1, i, allWord[i])
for j in range(0, len(LE) - 1, 3):
five_col = sheet5.col(i)
five_col.width = 256 * 18
if LE[j] == allWord[i]:
le += 1
re += 1
sheet5.write(re, i, LE[j + 1] + ' ' + LE[j + 2], style)
if LE[j + 1] == allWord[i]:
FP += 1
FN = le + LL.count(allWord[i])
TOTAL = TP + FN
# 进行了命令词和唤醒词的情况分类
if 'XIAOMEI' in filename_1 or 'FUQIANG' in filename_1 or 'JIANFENG' in filename_1:
if allWord[i] == '你好小美':
waketp += TP
wakeall += TOTAL
else:
cmdtp += TP
cmdall += TOTAL
cmdfp += FP
elif 'XIAORUI' in filename_1:
if allWord[i] == '你好小睿' or allWord[i] == '小睿你好':
waketp += TP
wakeall += TOTAL
else:
cmdtp += TP
cmdall += TOTAL
cmdfp += FP
elif 'DAXIANJICHENGZAO' in filename_1:
if allWord[i] == '森歌森歌' or allWord[i] == '达显达显':
waketp += TP
wakeall += TOTAL
else:
cmdtp += TP
cmdall += TOTAL
cmdfp += FP
elif 'TONGYONGYUYINDENG' in filename_1:
if allWord[i] == '你好小美' or allWord[i] == '智能管家' or allWord[i] == '你好小白'\
or allWord[i] == '小白你好':
waketp += TP
wakeall += TOTAL
else:
cmdtp += TP
cmdall += TOTAL
cmdfp += FP
elif 'TONGYONGYUYINKONGTIAO' in filename_1:
if allWord[i] == '空调空调' or allWord[i] == '你好空调' or allWord[i] == '你好小美'\
or allWord[i] == '小康小康':
waketp += TP
wakeall += TOTAL
else:
cmdtp += TP
cmdall += TOTAL
cmdfp += FP
elif 'XIAOYOU' in filename_1:
if allWord[i] == '小优小优':
waketp += TP
wakeall += TOTAL
else:
cmdtp += TP
cmdall += TOTAL
cmdfp += FP
elif 'XIAOKANGAC' in filename_1 or 'XIAOKANGCURTAIN' in filename_1:
if allWord[i] == '小康小康':
waketp += TP
wakeall += TOTAL
else:
cmdtp += TP
cmdall += TOTAL
cmdfp += FP
elif 'DIYUAN' in filename_1:
if allWord[i] == '你好帝源':
waketp += TP
wakeall += TOTAL
else:
cmdtp += TP
cmdall += TOTAL
cmdfp += FP
elif 'TUOBANGZHINENGMATONG' in filename_1:
if allWord[i] == '智能马桶':
waketp += TP
wakeall += TOTAL
else:
cmdtp += TP
cmdall += TOTAL
cmdfp += FP
elif 'XIAOKA' in filename_1:
if allWord[i] == '你好小咖':
waketp += TP
wakeall += TOTAL
else:
cmdtp += TP
cmdall += TOTAL
cmdfp += FP
elif 'SIJIMUGE' in filename_1:
if allWord[i] == '小沐你好':
waketp += TP
wakeall += TOTAL
else:
cmdtp += TP
cmdall += TOTAL
cmdfp += FP
elif 'SANXING' in filename_1 or 'TUOBANGXIAOMA' in filename_1 or 'YUXIANQIUJIU' in filename_1 or 'LAJIFENLEI' in filename_1:
waketp = 0
wakeall = 0
cmdtp += TP
cmdall += TOTAL
cmdfp += FP
if TOTAL == 0:
recall = 0
frr = 0
else:
recall = '%.2f%%' % (TP / TOTAL * 100)
frr = '%.2f%%' % (FN / TOTAL * 100)
sheet1.write(i + 4, 0, allWord[i], style1)
sheet1.write(i + 4, 1, TOTAL, style)
sheet1.write(i + 4, 2, TP, style)
sheet1.write(i + 4, 3, FN, style)
sheet1.write(i + 4, 4, recall, style)
if All == TOTAL:
FAR = 0
else:
FAR = '%.2f%%' % (FP / (All - TOTAL) * 100)
sheet2.write(i + 1, 0, allWord[i], style1)
sheet2.write(i + 1, 1, All - TOTAL, style)
sheet2.write(i + 1, 2, FP, style)
sheet2.write(i + 1, 3, FAR, style)
sheet3.write(i + 1, 0, allWord[i], style)
sheet3.write(i + 1, 1, TOTAL, style2)
sheet3.write(i + 1, 2, FN, style2)
sheet3.write(i + 1, 3, frr, style4)
for key, value in recol_word(allWord, LE, LL).items():
if allWord[i] == key:
numdict = Counter(value)
numlist = numdict.most_common()
fr = 1
for n in range(len(numlist)):
if TOTAL == 0:
fw = 0
else:
fw = numlist[n][1] / TOTAL
sheet3.write(i + 1, 3 + n + fr, numlist[n][0], style4)
sheet3.write(i + 1, 4 + n + fr, fw, style4)
fr += 1
sheet4.write(1, i, allWord[i])
rr = 1
for k in range(0, len(LR) - 1, 2):
# 标记正确的列数
four_col = sheet4.col(i)
four_col.width = 256 * 18
if allWord[i] == LR[k]:
rr += 1
sheet4.write(rr, i, LR[k + 1], style)
sheet1.write(len(allWord) + 4, 0, '总计', style)
sheet1.write(len(allWord) + 5, 0, '唤醒情况统计', style)
sheet1.write(len(allWord) + 5, 1, wakeall, style)
sheet1.write(len(allWord) + 5, 2, waketp, style)
sheet1.write(len(allWord) + 5, 3, wakeall - waketp, style)
if wakeall == 0:
Rcall = 0
else:
Rcall = '%.2f%%' % (waketp / wakeall * 100)
if cmdall == 0:
cmdrcall = 0
else:
cmdrcall = '%.2f%%' % (cmdtp / cmdall * 100)
if All == 0:
cmdfar = 0
else:
cmdfar = FAR = '%.2f%%' % (cmdfp / All * 100)
sheet1.write(len(allWord) + 5, 4, Rcall, style)
sheet1.write(len(allWord) + 6, 0, '识别情况统计', style)
sheet1.write(len(allWord) + 6, 1, cmdall, style)
sheet1.write(len(allWord) + 6, 2, cmdtp, style)
sheet1.write(len(allWord) + 6, 3, cmdall - cmdtp, style)
sheet1.write(len(allWord) + 6, 4, cmdrcall, style)
sheet1.write(len(allWord) + 4, 1, All, style)
sheet1.write(len(allWord) + 4, 2, Lrlen, style)
sheet1.write(len(allWord) + 4, 3, Lelen + Lllen, style)
sheet1.write(
len(allWord) + 4, 4, '%.2f%%' % (Lrlen / All * 100), style)
sheet2.write(len(allWord) + 1, 0, '命令词误识别统计', style)
sheet2.write(len(allWord) + 1, 1, All, style)
sheet2.write(len(allWord) + 1, 2, cmdfp, style)
sheet2.write(len(allWord) + 1, 3, cmdfar, style)
y = 1
x = 0
for w in range(len(wav_result)):
if x != 3:
sheet6.write(y, x, wav_result[w], style)
x += 1
elif x == 3:
sheet6.write(y, x, wav_result[w], style)
y += 1
x = 0
name = get_new_name(filename_1, filename_2)
if 'XIAOMEI' in filename_1:
work_book.save(r'D:\XIAOMEI' + name)
elif 'XIAORUI' in filename_1:
work_book.save(r'D:\XIAORUI' + name)
elif 'XIAOYOU' in filename_1:
work_book.save(r'D:\XIAOYOU' + name)
elif 'XIAOKANGAC' in filename_1:
work_book.save(r'D:\XIAOKANGAC' + name)
elif 'XIAOKANGCURTAIN' in filename_1:
work_book.save(r'D:\XIAOKANGCURTAIN' + name)
elif 'FUQIANG' in filename_1:
work_book.save(r'D:\FUQIANG' + name)
elif 'SANXING' in filename_1:
work_book.save(r'D:\SANXING' + name)
elif 'JIANFENG' in filename_1:
work_book.save(r'D:\JIANFENG' + name)
elif 'TUOBANGXIAOMA' in filename_1:
work_book.save(r'D:\TUOBANGXIAOMA' + name)
elif 'DIYUAN' in filename_1:
work_book.save(r'D:\DIYUAN' + name)
elif 'TUOBANGZHINENGMATONG' in filename_1:
work_book.save(r'D:\TUOBANGZHINENGMATONG' + name)
elif 'YUXIANQIUJIU' in filename_1:
work_book.save(r'D:\YUXIANQIUJIU' + name)
elif 'TONGYONGYUYINDENG' in filename_1:
work_book.save(r'D:\TONGYONGYUYINDENG' + name)
elif 'TONGYONGYUYINKONGTIAO' in filename_1:
work_book.save(r'D:\TONGYONGYUYINKONGTIAO' + name)
elif 'XIAOKA' in filename_1:
work_book.save(r'D:\XIAOKA' + name)
elif 'SIJIMUGE' in filename_1:
work_book.save(r'D:\SIJIMUGE' + name)
elif 'LAJIFENLEI' in filename_1:
work_book.save(r'D:\LAJIFENLEI' + name)
else:
pass
def write_prices(self, ws, _fields, category_name, start_date, end_date, supplier,
pricelist_partnerinfo_elmts, pricelist, body):
style = xlwt.XFStyle()
font = xlwt.Font()
font.bold = True
style.font = font
elements = [_('Product'.upper()), _('Start Date'.upper()), _('End Date'.upper())]
product_rate_supplement = self.env['product.rate.supplement']
body += """
<thead>
<caption style=text-align=center; align=top><h1>""" + pricelist.name + """</h1>
</caption>
<tr>
"""
for x in range(0, 3):
ws.write(0, x, elements[x], style)
body += """
<th>
""" + elements[x] + """</th>
"""
count = 3
if category_name.lower() == 'hotel':
tmp_fields = []
for x in _fields:
if x[1].lower() == 'price':
tmp_fields.append((x[0] + 3, 'double', 'Double'))
elif x[1].lower() not in ['simple', 'triple', 'start_date', 'end_date']:
tmp_fields.append((x[0] + 5, x[1], x[2]))
elif x[1].lower() == 'simple':
tmp_fields.append((x[0] + 1, x[1], x[2]))
elif x[1].lower() == 'triple':
tmp_fields.append((x[0] + 3, x[1], x[2]))
else:
tmp_fields.append((x[0], x[1], x[2]))
_fields = tmp_fields
for x in sorted(_fields):
if x[1].lower() != 'start_date' and x[1].lower() != 'end_date':
ws.write(0, count, _(fix(str(x[1]).upper())), style)
body += """
<th>
""" + _(fix(str(x[1]).upper())) + """</th>
"""
count += 1
if category_name.lower() == 'hotel':
tmp_fields = []
for x in _fields:
if x[1].lower() == 'double':
tmp_fields.append((x[0], 'price', 'Price'))
else:
tmp_fields.append((x[0], x[1], x[2]))
_fields = tmp_fields
non_repeated_supplemetes = []
for partnerinfo in pricelist_partnerinfo_elmts:
supplements = product_rate_supplement.search(
[('suppinfo_id', '=', getattr(partnerinfo.suppinfo_id, 'id'))])
for supplement in supplements:
if supplement and start_date <= supplement.start_date <= end_date:
if supplement.supplement_id.id not in non_repeated_supplemetes:
non_repeated_supplemetes.append(supplement.supplement_id.id)
x = supplement.supplement_id.name
ws.write(0, count, _(fix(str(x).upper())), style)
body += """
<th>
""" + _(fix(str(x).upper())) + """</th>
"""
count += 1
count = 1
body += """
</tr>
</thead>
<tbody>
"""
for partnerinfo in pricelist_partnerinfo_elmts:
if start_date <= partnerinfo.start_date <= end_date:
ws.write(count, 0, str(partnerinfo.suppinfo_id.product_tmpl_id.name))
ws.write(count, 1, str(partnerinfo.start_date))
ws.write(count, 2, str(partnerinfo.end_date))
body += """
<tr>
<td style="text-align=center;"> <span>""" + str(
partnerinfo.suppinfo_id.product_tmpl_id.name) + """</span></td> """ + """<td style="text-align=center;"><span>""" + str(
partnerinfo.start_date) + """</span></td> """ + """<td style="text-align=center;"><span>""" + str(
partnerinfo.end_date) + """</span></td> """
res = self.get_customer_price(partnerinfo, pricelist, [f[1] for f in _fields], category_name.lower())
second_count = 3
for x in sorted(_fields):
if x[1].lower() != 'start_date' and x[1].lower() != 'end_date':
try:
ws.write(count, second_count, _(str(res[x[1]])))
body += """<td style="text-align=center;"><span>""" + _(
str(res[x[1]])) + """</span></td> """
except KeyError:
ws.write(count, second_count, _('Empty'))
body += """<td style="text-align=center;"><span>""" + _('Empty') + """</span></td> """
second_count += 1
supplements = product_rate_supplement.search(
[('suppinfo_id', '=', getattr(partnerinfo.suppinfo_id, 'id'))])
for supplement in supplements:
if supplement and start_date <= supplement.start_date <= end_date:
x = supplement.price
ws.write(count, second_count, _(str(x)))
body += """<td style="text-align=center;"><span>""" + _(
str(x)) + """</span></td> """
second_count += 1
count += 1
body += """
</tr>"""
return ws, body
def tenant_exporter(request, id):
tenant = Tenant.objects.get(id=id)
bills = Bill.objects.filter(tenant=tenant)
payments = Payment.objects.filter(tenant=tenant)
response = HttpResponse(content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment;filename=Tenant_' + str(
tenant.name) + '.xls'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('Bills')
row_num = 6
font_style = xlwt.XFStyle()
font_style.font.bold = True
#tenant details
ws.write(0, 0, "Name", font_style)
ws.write(0, 1, str(tenant.name), font_style)
ws.write(1, 0, "Mobile", font_style)
ws.write(1, 1, str(tenant.mobile_no), font_style)
ws.write(2, 0, "date", font_style)
ws.write(2, 1, str(datetime.datetime.today()), font_style)
ws.write(3, 0, "balance", font_style)
ws.write(3, 1, str(tenant.balance), font_style)
columns = [
'Date', 'From', 'To', 'Rent', 'units', 'Price per unit',
'electric total', 'wifi', 'water', 'total'
]
for col_no in range(len(columns)):
ws.write(row_num, col_no, columns[col_no], font_style)
font_style.font.bold = False
for row in bills:
# print(row)
row_num += 1
ws.write(row_num, 0, str(row.date), font_style)
ws.write(row_num, 1, str(row.start_date), font_style)
ws.write(row_num, 2, str(row.end_date), font_style)
ws.write(row_num, 3, str(row.rent), font_style)
ws.write(row_num, 4, str(row.units), font_style)
ws.write(row_num, 5, str(row.price_per_unit), font_style)
ws.write(row_num, 6, str(row.electric_total), font_style)
ws.write(row_num, 7, str(row.wifi_charge), font_style)
ws.write(row_num, 8, str(row.water_bill), font_style)
ws.write(row_num, 9, str(row.total), font_style)
ws2 = wb.add_sheet('Payments')
row_num = 1
font_style.font.bold = True
columns = ['Datetime', 'Amount']
for col_no in range(len(columns)):
ws2.write(row_num, col_no, columns[col_no], font_style)
font_style.font.bold = False
for row in payments:
row_num += 1
ws2.write(row_num, 0, str(row.date), font_style)
ws2.write(row_num, 1, str(row.amount), font_style)
wb.save(response)
return response
import xlwt
# 第一步：创建工作簿
wb = xlwt.Workbook()
# 第二步：创建工作表1
ws = wb.add_sheet("CNY")
# 第三步：填充数据
# 表头（合并单元格）
titleStyle = xlwt.XFStyle() # 初始化样式
titleFont = xlwt.Font()
titleFont.name = "宋体"
titleFont.bold = True # 加粗
titleFont.height = 11 * 20
titleFont.colour_index = 0x08
titleStyle.font = titleFont
# 单元格对齐方式
cellAlign = xlwt.Alignment()
cellAlign.horz = 0x02 # 水平居中
cellAlign.vert = 0x01 # 垂直居中
titleStyle.alignment = cellAlign
# 边框
borders = xlwt.Borders()
borders.right = xlwt.Borders.DASHED
borders.bottom = xlwt.Borders.DOTTED
titleStyle.borders = borders
# 背景颜色
dataStyle = xlwt.XFStyle()
bgcolor = xlwt.Pattern()
bgcolor.pattern = xlwt.Pattern.SOLID_PATTERN
bgcolor.pattern_fore_colour = 22 # 背景颜色
dataStyle.pattern = bgcolor
m=1
today = datetime.date.today()
# 如果要跑昨天的代码，改这里%%%%%%%%%%%%%%%%%%%%%%%%%
oneday = datetime.timedelta(days=7)
yesterday = today - oneday
print yesterday
ws=w.add_sheet(str(yesterday).decode('utf-8'),cell_overwrite_ok=True)
font = xlwt.Font() # Create the Font
font.name = 'Times New Roman'
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
borders.bottom_colour=0x3A
style = xlwt.XFStyle()
style.borders = borders
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style = xlwt.XFStyle()
style.borders = borders
style.alignment = alignment
ws.write(0,0,'日期'.decode('utf-8'),style)
ws.write(0,1,'NGID'.decode('utf-8'),style)
ws.write(0,2,'FREQ'.decode('utf-8'),style)
ws.write(0,3,'错误数量'.decode('utf-8'),style)
ws.write(0,4,'错误总和'.decode('utf-8'),style)
ws.panes_frozen= True
ws.horz_split_pos= 1
# for area in cdn_list:
import sys
from tablib.compat import BytesIO, xrange
import tablib
import xlrd
import xlwt
from datetime import datetime, date
from xlrd.biffh import XLRDError
title = 'xls'
extensions = ('xls', )
# special styles
wrap = xlwt.easyxf("alignment: wrap on")
bold = xlwt.easyxf("font: bold on")
date_format = xlwt.XFStyle()
date_format.num_format_str = 'dd/mm/yyyy'
def detect(stream):
"""Returns True if given stream is a readable excel file."""
try:
xlrd.open_workbook(file_contents=stream)
return True
except (TypeError, XLRDError):
pass
try:
xlrd.open_workbook(file_contents=stream.read())
return True
except (AttributeError, XLRDError):
pass
def rec2excel(r,
ws,
formatd=None,
rownum=0,
colnum=0,
nanstr='NaN',
infstr='Inf'):
"""
save record array r to excel xlwt worksheet ws
starting at rownum. if ws is string like, assume it is a
filename and save to it
start writing at rownum, colnum
formatd is a dictionary mapping dtype name -> mlab.Format instances
nanstr is the string that mpl will put into excel for np.nan value
The next rownum after writing is returned
"""
autosave = False
if cbook.is_string_like(ws):
filename = ws
wb = excel.Workbook()
ws = wb.add_sheet('worksheet')
autosave = True
if formatd is None:
formatd = dict()
formats = []
font = excel.Font()
font.bold = True
stylehdr = excel.XFStyle()
stylehdr.font = font
for i, name in enumerate(r.dtype.names):
dt = r.dtype[name]
format = formatd.get(name)
if format is None:
format = mlab.defaultformatd.get(dt.type, mlab.FormatObj())
format = xlformat_factory(format)
ws.write(rownum, colnum + i, name, stylehdr)
formats.append(format)
rownum += 1
ind = np.arange(len(r.dtype.names))
for row in r:
for i in ind:
val = row[i]
format = formats[i]
val = format.toval(val)
if mlab.safe_isnan(val):
ws.write(rownum, colnum + i, nanstr)
elif mlab.safe_isinf(val):
sgn = np.sign(val)
if sgn > 0: s = infstr
else: s = '-%s' % infstr
ws.write(rownum, colnum + i, s)
elif format.xlstyle is None:
ws.write(rownum, colnum + i, val)
else:
ws.write(rownum, colnum + i, val, format.xlstyle)
rownum += 1
if autosave:
wb.save(filename)
return rownum
def export_results_xls(request):
import xlwt
response = HttpResponse(content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename=result.xls'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet("Результаты")
votes = Vote.objects.select_related('teacher', 'response_student',
'response_student__group').all()
teachers = Teacher.objects.select_related('department').all()
row_num = 0
columns = [
(u"Кафедра", 8000),
(u"Преподаватель", 8000),
(u"Сумма голосов", 8000),
(u"Количство голосов", 8000),
(u"Результат", 8000),
]
font_style_head = xlwt.XFStyle()
font_style_head.font.bold = True
for col_num in range(len(columns)):
ws.write(row_num, col_num, columns[col_num][0], font_style_head)
# set column width
ws.col(col_num).width = columns[col_num][1]
font_style = xlwt.XFStyle()
font_style.alignment.wrap = 1
for t in teachers:
t.sum_votes = 0.0
t.count_votes = 0.0
t.result = 0.0
check = []
for obj in t.votes.select_related('response_student').all():
if obj.response_student.badge_number in check:
continue
check.append(obj.response_student.badge_number)
s = int(obj.mark1) * 3 + int(obj.mark2) * 2 + int(
obj.mark3) + int(obj.mark4) * 3 + int(obj.mark5) * 2 + int(
obj.mark6) * 4 + int(obj.mark7) * 3 + int(obj.mark8) * 2
if s > 0:
t.sum_votes += s
t.count_votes += 1
if t.count_votes != 0:
t.result = t.sum_votes / t.count_votes
ttt = list(teachers)
ttt.sort(key=lambda x: x.result, reverse=True)
deps = {}
for t in ttt:
row_num += 1
row = [
t.department.name, t.last_name, t.sum_votes, t.count_votes,
t.result
]
if not t.department.name in deps:
deps[t.department.name] = []
deps[t.department.name].append(row)
for col_num in range(len(row)):
ws.write(row_num, col_num, str(row[col_num]), font_style)
n = 0
for d in deps.keys():
n += 1
ws = wb.add_sheet(str(n))
row_num = 0
for col_num in range(len(columns)):
ws.write(row_num, col_num, columns[col_num][0], font_style_head)
ws.col(col_num).width = columns[col_num][1]
for row in deps[d]:
row_num += 1
for col_num in range(len(row)):
ws.write(row_num, col_num, str(row[col_num]), font_style)
wb.save(response)
return response
def generate_excel_report(self, result, browser_type):
"""main method"""
plan_list = xlrd.open_workbook(default_excel_report_path) # 获取excel report数据表
table = plan_list.sheet_by_name(browser_type) # 获取sheet名为browser_type的表
col_count = table.ncols # 获取列数
row_count = table.nrows # 获取行数
work_book = xlwt.Workbook(encoding='ascii')
work_sheet = work_book.add_sheet(browser_type)
# 设置列宽
work_sheet.col(0).width = 256 * 8
work_sheet.col(1).width = 256 * 30
work_sheet.col(2).width = 256 * 20
work_sheet.col(3).width = 256 * 20
work_sheet.col(4).width = 256 * 30
work_sheet.col(5).width = 256 * 12
work_sheet.col(6).width = 256 * 12
work_sheet.col(7).width = 256 * 20
work_sheet.col(8).width = 256 * 20
work_sheet.col(9).width = 256 * 10
work_sheet.col(10).width = 256 * 10
# 设置对齐方式
align_center = xlwt.Alignment()
align_center.horz = align_center.HORZ_CENTER
align_center.vert = align_center.VERT_CENTER
# 设置字体加粗
bold_font = xlwt.Font()
bold_font.bold = True
# 设置表头格式
style_bold = xlwt.XFStyle()
style_bold.font = bold_font
style_bold.alignment = align_center
# 设置result列的背景色及对齐方式
style_back_red = xlwt.easyxf('pattern: pattern solid, fore_colour red;')
style_back_red.alignment = align_center
style_back_rose = xlwt.easyxf('pattern: pattern solid, fore_colour rose;')
style_back_rose.alignment = align_center
style_back_green = xlwt.easyxf('pattern: pattern solid, fore_colour light_green;')
style_back_green.alignment = align_center
style_back_yellow = xlwt.easyxf('pattern: pattern solid, fore_colour yellow;')
style_back_yellow.alignment = align_center
style_back_gray = xlwt.easyxf('pattern: pattern solid, fore_colour gray25;')
style_back_gray.alignment = align_center
for j in range(0, col_count):
work_sheet.write(0, j, table.cell_value(0, j), style_bold)
for i in range(1, row_count):
case_value = table.cell_value(i, 3)
if table.cell_value(i, 6) == '':
data_row_num = "1-1"
else:
data_row_num = table.cell_value(i, 6)
start_row = min(int(data_row_num.split("-")[0]), int(data_row_num.split("-")[1]))
end_row = max(int(data_row_num.split("-")[0]), int(data_row_num.split("-")[1]))
if start_row == end_row:
test_num = "_%s" % str(start_row)
else:
test_num = "_%s_%s" % ((end_row - start_row + 1), end_row)
test_case_name = case_value + test_num
for single_result in result:
if single_result[1].id().split('.')[-1] == test_case_name:
start_time = single_result[1].start_time
stop_time = single_result[1].stop_time
duration = str(stop_time - start_time).split(".")[0]
for j in range(0, col_count):
if j < 7 and j != 5:
work_sheet.write(i, j, table.cell_value(i, j))
else:
if j == 5: work_sheet.write(i, j, "Y")
if j == 7: work_sheet.write(i, j, str(start_time).split(".")[0])
if j == 8: work_sheet.write(i, j, str(stop_time).split(".")[0])
if j == 9: work_sheet.write(i, j, duration)
if j == 10:
if single_result[0] == 0:
result_status = 'Pass'
work_sheet.write(i, j, result_status, style_back_green)
if single_result[0] == 1:
result_status = 'Fail'
work_sheet.write(i, j, result_status, style_back_rose)
if single_result[0] == 2:
result_status = 'Error'
work_sheet.write(i, j, result_status, style_back_red)
if single_result[0] == 3:
result_status = 'Skip'
work_sheet.write(i, j, result_status, style_back_yellow)
break
else:
for j in range(0, col_count):
if j < 7 and j != 5:
work_sheet.write(i, j, table.cell_value(i, j))
else:
if j == 5: work_sheet.write(i, j, "N")
if j == 10: work_sheet.write(i, j, "Not Run", style_back_gray)
work_book.save(default_excel_report_path)
def SimpleSn(filePath):
if not os.path.exists('自动生成文件文件夹'):
os.mkdir('自动生成文件文件夹')
j = 1
# 设置居中
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER # 水平方向
alignment.vert = xlwt.Alignment.VERT_TOP # 垂直方向
# 设置背景色
pattern = xlwt.Pattern() # Create the Pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 17
pattern3 = xlwt.Pattern() # Create the Pattern
pattern3.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern3.pattern_fore_colour = 18
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
style = xlwt.XFStyle() # Create the Pattern
# style.pattern = pattern # Add Pattern to Style
style.alignment = alignment
pattern3 = xlwt.Pattern() # Create the Pattern
pattern3.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern3.pattern_fore_colour = 18
style3 = xlwt.XFStyle() # Create the Pattern
style3.pattern = pattern3 # Add Pattern to Style
style3.alignment = alignment
style1 = xlwt.XFStyle()
style1.alignment = alignment
# 第一行参数----------------------------------
worksheet.write(0, 0, '序号', style)
worksheet.write(0, 1, '设备型号', style)
worksheet.write(0, 2, '设备名称', style)
worksheet.write(0, 3, 'meth口IP', style)
worksheet.write(0, 4, 'loopback0口IP', style)
worksheet.write(0, 5, 'loopback1口IP', style)
worksheet.write(0, 6, '设备版本', style)
worksheet.write(0, 7, '设备补丁', style)
worksheet.write(0, 8, '设备sn', style)
worksheet.write(0, 9, '设备license状态', style)
worksheet.col(1).width = 0x0d00
for i in range(1, 10):
if i == 2 or i == 8:
worksheet.col(i).width = 0x0d00 + 80 * 50
elif i == 6 or i == 7:
worksheet.col(i).width = 0x0d00 + 100 * 60
else:
worksheet.col(i).width = 0x0d00 + 30 * 50
sortedInfos = []
sortedInfos1 = []
files = os.listdir(filePath)
files = [os.path.join(filePath, file) for file in files]
for file in files:
deviceModel = sysname = methIp = loopback0Ip = loopback1Ip = deviceVersion = devicePatch = deviceSn = licenseStatus = ''
with open(file, 'rb') as f:
string = ''
for line in f:
line = line.decode('utf8', 'ignore')
string += line
if '#' in line.strip():
if re.search('Board\s*Type([\s\S]*?)\n', string):
deviceModel = re.search('Board\s*Type\s*:([\s\S]*?)\n',
string).groups()[0].strip()
if re.search('sysname([\s\S]*?)\n', string):
sysname = re.search('sysname([\s\S]*?)\n',
string).groups()[0].strip()
if re.search('interface\s*MEth0/0/0',
string) and re.search('ip\s*address', string):
methIp = re.search(
'ip\s*address\s*(\d*\.\d*\.\d*\.\d*)',
string).groups()[0].strip()
if re.search('interface\s*LoopBack0',
string) and re.search('ip\s*address', string):
loopback0Ip = re.search(
'ip\s*address\s*(\d*\.\d*\.\d*\.\d*)',
string).groups()[0].strip()
# print(string)
if re.search('interface\s*LoopBack1',
string) and re.search('ip\s*address', string):
loopback1Ip = re.search(
'ip\s*address\s*(\d*\.\d*\.\d*\.\d*)',
string).groups()[0].strip()
if re.search('Configured\s*startup\s*system\s*software',
string):
# print(string)
deviceVersion = re.findall(
'startup\s*system\s*software:\s*flash:/([\s\S]*?\.cc)',
string)
deviceVersion = ' '.join(list(set(deviceVersion)))
devicePatch = re.findall(
'patch\s*package:\s*flash:/([\s\S]*?\.PAT)',
string)
devicePatch = ' '.join(list(set(devicePatch)))
if re.search('ESN:([\s\S]*?)\n', string):
deviceSn = re.search('ESN:([\s\S]*?)\n',
string).groups()[0].strip()
if re.search('Active\s*License', string):
licenseStatus = re.search(
'Expired\s*date\s*:([\s\S]*?)\n',
string).groups()[0].strip()
string = ''
if methIp.strip() == '':
sortedInfos1.append({
'deviceModel': deviceModel,
'sysname': sysname,
'methIp': methIp.strip(),
'loopback0Ip': loopback0Ip,
'loopback1Ip': loopback1Ip,
'deviceVersion': deviceVersion,
'devicePatch': devicePatch,
'deviceSn': deviceSn,
'licenseStatus': licenseStatus
})
else:
sortedInfos.append({
'deviceModel': deviceModel,
'sysname': sysname,
'methIp': methIp.strip(),
'loopback0Ip': loopback0Ip,
'loopback1Ip': loopback1Ip,
'deviceVersion': deviceVersion,
'devicePatch': devicePatch,
'deviceSn': deviceSn,
'licenseStatus': licenseStatus
})
# print(sortedInfos)
sortedInfos = sorted(
sortedInfos,
key=lambda file:
(int(file['methIp'].split('.')[-1]) + 1000 * int(file['methIp'].split(
'.')[-2]) + 1000 * 1000 * int(file['methIp'].split('.')[1])))
# print(sortedInfos)
for info in sortedInfos:
worksheet.write(j, 0, str(j), style)
worksheet.write(j, 1, info['deviceModel'], style)
worksheet.write(j, 2, info['sysname'], style)
worksheet.write(j, 3, info['methIp'], style)
worksheet.write(j, 4, info['loopback0Ip'], style)
worksheet.write(j, 5, info['loopback1Ip'], style)
worksheet.write(j, 6, info['deviceVersion'], style)
worksheet.write(j, 7, info['devicePatch'], style)
worksheet.write(j, 8, info['deviceSn'], style)
worksheet.write(j, 9, info['licenseStatus'], style)
j += 1
for info in sortedInfos1:
worksheet.write(j, 0, str(j), style)
worksheet.write(j, 1, info['deviceModel'], style)
worksheet.write(j, 2, info['sysname'], style)
worksheet.write(j, 3, info['methIp'], style)
worksheet.write(j, 4, info['loopback0Ip'], style)
worksheet.write(j, 5, info['loopback1Ip'], style)
worksheet.write(j, 6, info['deviceVersion'], style)
worksheet.write(j, 7, info['devicePatch'], style)
worksheet.write(j, 8, info['deviceSn'], style)
worksheet.write(j, 9, info['licenseStatus'], style)
j += 1
workbook.save('自动生成文件文件夹/采集SN信息.xls')
def Sn(filePath):
if not os.path.exists('自动生成文件文件夹'):
os.mkdir('自动生成文件文件夹')
print('开始采集SN、IP、设备类型、License等相关信息......')
j = 1
# 设置居中
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER # 水平方向
alignment.vert = xlwt.Alignment.VERT_TOP # 垂直方向
# 设置背景色
pattern = xlwt.Pattern() # Create the Pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 17
pattern3 = xlwt.Pattern() # Create the Pattern
pattern3.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern3.pattern_fore_colour = 18
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
style = xlwt.XFStyle() # Create the Pattern
style.pattern = pattern # Add Pattern to Style
style.alignment = alignment
pattern3 = xlwt.Pattern() # Create the Pattern
pattern3.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern3.pattern_fore_colour = 18
style3 = xlwt.XFStyle() # Create the Pattern
style3.pattern = pattern3 # Add Pattern to Style
style3.alignment = alignment
style1 = xlwt.XFStyle()
style1.alignment = alignment
# 第一行参数----------------------------------
worksheet.write(0, 0, '序号', style)
worksheet.write(0, 1, '设备名称', style)
worksheet.write(0, 2, '管理IP地址', style3)
worksheet.write(0, 3, 'Loopback0地址', style3)
worksheet.write(0, 4, 'Loopback1地址', style3)
worksheet.write(0, 5, '设备类型', style)
worksheet.write(0, 6, '版本信息', style3)
worksheet.write(0, 7, '补丁信息', style3)
worksheet.write(0, 8, 'License文件名', style3)
worksheet.write(0, 9, 'License状态', style3)
worksheet.write(0, 10, '机框SN信息', style)
worksheet.write(0, 11, '板卡SN信息', style)
worksheet.write(0, 12, '电源SN信息', style)
worksheet.write(0, 13, '电源状态', style)
worksheet.write(0, 14, '风扇SN信息', style)
worksheet.write(0, 15, '风扇状态', style)
worksheet.write(0, 16, '光模板SN信息', style)
# 设置单元格长度
for i in range(17):
if i == 1 or i == 6 or i >= 10:
worksheet.col(i).width = 0x0d00 + 100 * 50
elif i == 0:
worksheet.col(i).width = 0x0a00
else:
worksheet.col(i).width = 0x0d00 + 100 * 20
BKSNIF, DYSNIF, FSSNIF, GMKSNIF = [True, True, True, True]
files = os.listdir(filePath)
files = [os.path.join(filePath, file) for file in files]
flag = interfFlag = versionFlag = patchFlag = licenseFlag = False
for file in files:
deviceInfo = ''
snAllInfo = []
loopback0 = loopback1 = manageIp = version = deviceType = patch = license = licensestate = deviceName = jksn = ''
time = 0
with open(file, 'rb') as f:
temp = re.search('device\s*status([\s\S]*?)<',
f.read().decode('utf8', 'ignore').lower())
try:
deviceInfo = temp.groups()[0]
except:
pass
with open(file, 'rb') as f:
for line in f:
if re.search('<[\s\S]*?>', line.decode('utf8', 'ignore')):
if time == 0:
deviceName = re.search(
'<([\s\S]*?)>', line.decode('utf8',
'ignore')).groups()[0]
time += 1
flag = interfFlag = versionFlag = patchFlag = licenseFlag = False
if flag:
if 'Equipment SN(ESN)' in line.decode('utf8', 'ignore'):
jksn = line.decode('utf8',
'ignore').split(':')[-1].strip()
continue
snAllInfo.append(line.decode('utf8', 'ignore'))
if 'dissnall' in re.sub(' ', '', line.decode(
'utf8', 'ignore')) or 'displaysnall' in re.sub(
' ', '', line.decode('utf8', 'ignore')):
flag = True
if 'disipinterfacebrief' in re.sub(
' ', '', line.decode(
'utf8',
'ignore')) or 'displayipinterfacebrief' in re.sub(
' ', '', line.decode('utf8', 'ignore')):
interfFlag = True
if 'disversion' in re.sub(' ', '', line.decode(
'utf8', 'ignore')) or 'displayversion' in re.sub(
' ', '', line.decode('utf8', 'ignore')):
versionFlag = True
if 'dispatch-information' in re.sub(
' ', '', line.decode(
'utf8',
'ignore')) or 'displaypatch-information' in re.sub(
' ', '', line.decode('utf8', 'ignore')):
patchFlag = True
if 'displaylicense' in re.sub(
' ', '', line.decode(
'utf8', 'ignore')) or 'dislicense' in re.sub(
' ', '', line.decode('utf8', 'ignore')):
licenseFlag = True
if interfFlag:
line = line.decode('utf8', 'ignore')
if re.search('LoopBack0', line):
loopback0 = [
i for i in line.split(' ') if re.search('\S', i)
][1]
if re.search('LoopBack1', line):
loopback1 = [
i for i in line.split(' ') if re.search('\S', i)
][1]
if re.search('meth0/0/0', line.lower()):
manageIp = [
i for i in line.split(' ') if re.search('\S', i)
][1]
if versionFlag:
line = line.decode('utf8', 'ignore')
if re.search('VRP.*?software,', line):
version = [
i for i in line.split('(') if re.search('\S', i)
][-1]
version = version.split(')')[0]
version = version.strip().split(' ')[-1]
if re.search('HUAWEI(.*?)uptime is ', line):
deviceType = re.search('HUAWEI(.*?)uptime is ',
line).groups()[0]
if patchFlag:
line = line.decode('utf8', 'ignore')
if re.search('PatchPackageVersion', re.sub(' ', '', line)):
patch = line.split(':')[-1].strip()
if licenseFlag:
line = line.decode('utf8', 'ignore')
if re.search('Active License', line) and re.search(
':/', line):
license = line.split(':/')[-1].strip()
if re.search('License state', line):
licensestate = line.split(':')[-1].strip().split(
'.')[0]
if loopback0 != '':
worksheet.write(j, 3, loopback0, style1)
else:
worksheet.write(j, 3, '--', style1)
if loopback1 != '':
worksheet.write(j, 4, loopback1, style1)
else:
worksheet.write(j, 4, '--', style1)
if manageIp != '':
worksheet.write(j, 2, manageIp, style1)
else:
worksheet.write(j, 2, '--', style1)
if version != '':
worksheet.write(j, 6, version, style1)
else:
worksheet.write(j, 6, '--', style1)
if patch != '':
worksheet.write(j, 7, patch, style1)
else:
worksheet.write(j, 7, '--', style1)
if license != '':
worksheet.write(j, 8, license, style1)
else:
worksheet.write(j, 8, '--', style1)
if licensestate != '':
worksheet.write(j, 9, licensestate, style1)
else:
worksheet.write(j, 9, '--', style1)
worksheet.write(j, 0, j, style1)
if deviceName != '':
worksheet.write(j, 1, deviceName, style1)
else:
worksheet.write(j, 1, '--', style1)
if deviceType != '':
worksheet.write(j, 5, deviceType, style1)
else:
worksheet.write(j, 5, '--', style1)
if jksn != '':
worksheet.write(j, 10, jksn, style1)
else:
worksheet.write(j, 10, '--', style1)
if BKSNIF:
bksn = []
for info in snAllInfo:
if jksn not in info and not re.search(
'PWR', info) and not re.search(
'FAN', info) and not re.search(
'\dGE\d/\d/\d', info):
break
elif re.search('^\d[\S\s]*?--[\S\s]*?\n', info):
info = [i for i in info.split(' ') if re.search('\S', i)]
if info[-2] != '--' and info[-2] not in bksn:
bksn.append(info[-2])
if bksn:
worksheet.write(j, 11, ';'.join(bksn), style1)
else:
worksheet.write(j, 11, '--', style1)
else:
worksheet.write(j, 11, '--', style1)
if DYSNIF:
dysn = []
for info in snAllInfo:
if re.search('PWR\d', info):
info = [i for i in info.split(' ') if re.search('\S', i)]
if info[2] not in dysn:
dysn.append(info[-2])
if dysn:
worksheet.write(j, 12, ';'.join(dysn), style1)
else:
worksheet.write(j, 12, '--', style1)
dystatus = []
for infotemp in deviceInfo.split('\n'):
if re.search('pwr\d', infotemp.lower()):
if re.search(
'pwr\d[\s\S]*?present[\s\S]*?on\s*registered\s*normal',
infotemp.lower()):
dystatus.append('normal')
else:
dystatus.append('error')
if dystatus:
worksheet.write(j, 13, ';'.join(dystatus), style1)
else:
worksheet.write(j, 13, '--', style1)
else:
worksheet.write(j, 12, '--', style1)
worksheet.write(j, 13, '--', style1)
if FSSNIF:
fssn = []
for info in snAllInfo:
if re.search('FAN\d', info):
info = [i for i in info.split(' ') if re.search('\S', i)]
if info[2] not in fssn:
fssn.append(info[-2])
if fssn:
worksheet.write(j, 14, ';'.join(fssn), style1)
else:
worksheet.write(j, 14, '--', style1)
fsstatus = []
for infotemp in deviceInfo.split('\n'):
if re.search('fan\d', infotemp.lower()):
if re.search(
'fan\d[\s\S]*?present[\s\S]*?on\s*registered\s*normal',
infotemp.lower()):
fsstatus.append('normal')
else:
fsstatus.append('error')
if fsstatus:
worksheet.write(j, 15, ';'.join(fsstatus), style1)
else:
worksheet.write(j, 15, '--', style1)
else:
worksheet.write(j, 14, '--', style1)
worksheet.write(j, 15, '--', style1)
if GMKSNIF:
gmksn = []
for info in snAllInfo:
if re.search('\dGE\d/\d/\d', info):
info = [i for i in info.split(' ') if re.search('\S', i)]
if info[2] != '--':
if info[2] not in gmksn:
gmksn.append(info[2])
if gmksn:
worksheet.write(j, 16, ';'.join(gmksn), style1)
else:
worksheet.write(j, 16, '--', style1)
else:
worksheet.write(j, 16, '--', style1)
# print('\r完成进度: %3.2f%%' %((j - 1) / len(files) * 100), end='')
j += 1
# print('\r完成进度: %3.2f%%' %(100), end='')
workbook.save('自动生成文件文件夹/采集SN信息.xls')
easygui.msgbox('提取SN作业完成')
def Excel(caseResult):
# 获取信息配置表
arr = ["手机品牌", "手机型号", "系统版本", "CPU核心数", "运行内存大小", "手机分辨率", "测试期间耗电"]
# 品牌、型号、系统版本、CPU核心数、运行内存、手机分辨率
arr2 = [
readConfig.getConfig("phoneConf", "brand"),
readConfig.getConfig("phoneConf", "model"),
readConfig.getConfig("phoneConf", "systemVersion"),
readConfig.getConfig("phoneConf", "cpu"),
readConfig.getConfig("phoneConf", "men"),
readConfig.getConfig("phoneConf", "appPix")
]
startBc = int(readConfig.getConfig("phoneConf", "startPower"))
def batteryCapacity():
get_cmd = os.popen("adb shell dumpsys battery").readlines()
for i in range(0, len(get_cmd)):
a = str(get_cmd[i])
b = 'level'
p = a.find(b)
try:
if p != -1:
s = get_cmd[i].split('level')
Battery = "".join(s).strip('\n').strip("'").strip(' : ')
return int(Battery)
except:
return '获取电量失败'
# 获取配置信息
applicationName = readConfig.getConfig("baseconf",
"applicationName") # 获取应用名
applicationVersion = readConfig.getConfig("baseconf",
"applicationVersion") # 获取应用版本信息
fileSize = readConfig.getConfig("baseconf", "fileSize") # 获取app文件大小
# excel样式
def set_style(name, height, bold):
u'字体，高度，背景色，加粗，字体色'
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = name # 'Times New Roman'
font.bold = bold
font.color_index = 4
font.height = height
style.font = font
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment
return style
def s_style(name, height, lei, bold, s):
u'字体，高度，背景色'
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = name # 'Times New Roman'
font.colour_index = s # 设置其字体颜色
font.bold = bold
font.color_index = 4
font.height = height
style.font = font
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment
pattern = xlwt.Pattern() # Create the Pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = lei
style.pattern = pattern # Add Pattern to Style创建模式
return style
w = xlwt.Workbook() # 创建一个工作簿
ws = w.add_sheet('Hey, Hades') # 创建一个工作表
# 合并单元格
ws.write_merge(0, 0, 0, 4, u'测试报告总概况', set_style(u'宋体', 360,
True)) # 合并行单元格
ws.write_merge(1, 1, 0, 4, u'测试概况', s_style(u'宋体', 270, 4, False,
0x01)) # 合并行单元格
ws.write_merge(8, 8, 0, 6, u'测试手机详情', s_style(u'宋体', 270, 4, False,
0x01)) # 合并行单元格
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
style = xlwt.XFStyle() # Create Style
style.alignment = alignment # Add Alignment to Style
end = datetime.datetime.now()
print '计时结束时间：%s' % end
timeConsuming = str(end -
start)[:-7] # 用测试结束时间-开始时间得到测试耗时，再把时间转成字符串并去掉小数部分
print '测试耗时：%s' % timeConsuming
endBc = batteryCapacity()
bC = str(startBc - endBc) + '%'
arr2.append(bC)
dk = u'%s + Appium:%s + Python:%s' % (platform.platform(
), os.popen('appium -v').readlines()[0].split('\n')[0],
platform.python_version())
app1 = ["APP名称", applicationName, "用例总数", caseResult[0], "测试环境"]
app2 = ["APP大小", fileSize, "通过总数", caseResult[1]]
app3 = [
"APP版本", applicationVersion, "失败总数",
str(int(caseResult[2]) + int(caseResult[3]))
]
app4 = [
"测试日期",
time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())),
"测试耗时", timeConsuming + "秒"
]
ws.write_merge(3, 5, 4, 4, dk, set_style(u'宋体', 270, False)) # 合并行单元格
for i in range(0, len(app1)):
# print app1[i]
ws.write(2, i, app1[i].decode('utf-8'), set_style(u'宋体', 270, False))
for i in range(0, len(app2)):
ws.write(3, i, app2[i].decode('utf-8'), set_style(u'宋体', 270, False))
for i in range(0, len(app3)):
ws.write(4, i, app3[i].decode('utf-8'), set_style(u'宋体', 270, False))
for i in range(0, len(app4)):
ws.write(5, i, app4[i].decode('utf-8'), set_style(u'宋体', 270, False))
for i in range(0, len(arr)): # 写入第一行arr的内容
ws.write(9, i, arr[i].decode('utf-8'), set_style(u'宋体', 270, False))
for i in range(0, len(arr2)): # 写入第二行arr2的内容
ws.write(10, i, arr2[i].decode('utf-8'), set_style(u'宋体', 270, False))
createFolder('../report/', '/log') # 判断文件夹是否存在，不存在则创建
timestr = time.strftime('%Y-%m-%d-%H', time.localtime(time.time()))
path = '../report/%s/%s/log/%s_%s.xls' \
% (timestr[:10], timestr[11:], arr2[0].strip("\r"), arr2[1].strip("\r").replace(' ', '_'))
w.save(path.decode('utf-8')) # 以“品牌_机型”命名保存
print '导出结束'
def create_confusion_sheet(self, book, tag_list, confusion_matrix_to_write,
sheet_name):
"""
this method creates a new confusion matrix sheet by the name sheet_name
:param sheet_name:
:param book: the excel workbook object
:param tag_list: list of all the tags
:param confusion_matrix_to_write:
:return: None
"""
sheet1 = book.add_sheet(sheet_name)
# Header pattern
header_pattern = xlwt.Pattern()
header_pattern.pattern = xlwt.Pattern.SOLID_PATTERN
header_pattern.pattern_fore_colour = 9
bold_font = xlwt.Font()
bold_font.bold = True
align = xlwt.Alignment()
align.horz = xlwt.Alignment.HORZ_CENTER
thick_border = xlwt.Borders()
thick_border.right = xlwt.Borders.THICK
thick_border.left = xlwt.Borders.THICK
thick_border.top = xlwt.Borders.THICK
thick_border.bottom = xlwt.Borders.THICK
header_style = xlwt.XFStyle()
header_style.pattern = header_pattern
header_style.borders = thick_border
header_style.font = bold_font
header_style.alignment = align
# Regualr pattern
reg_border = xlwt.Borders()
reg_border.right = xlwt.Borders.DASHED
reg_border.left = xlwt.Borders.DASHED
reg_border.top = xlwt.Borders.DASHED
reg_border.bottom = xlwt.Borders.DASHED
style = xlwt.XFStyle()
style.borders = reg_border
style.num_format_str = '0'
style.alignment = align
# mistakes pattern
pattern_mistake = xlwt.Pattern()
pattern_mistake.pattern = xlwt.Pattern.SOLID_PATTERN
pattern_mistake.pattern_fore_colour = 29
style_mistake = xlwt.XFStyle()
style_mistake.pattern = pattern_mistake
style_mistake.num_format_str = '0'
style_mistake.borders = reg_border
style_mistake.alignment = align
# correct pattern
pattern_hit = xlwt.Pattern()
pattern_hit.pattern = xlwt.Pattern.SOLID_PATTERN
pattern_hit.pattern_fore_colour = 42
style_hit = xlwt.XFStyle()
style_hit.pattern = pattern_hit
style_hit.num_format_str = '0'
style_hit.borders = reg_border
style_hit.alignment = align
# sum pattern
pattern_sum = xlwt.Pattern()
pattern_sum.pattern = xlwt.Pattern.SOLID_PATTERN
pattern_sum.pattern_fore_colour = 22
style_sum = xlwt.XFStyle()
style_sum.pattern = pattern_sum
style_sum.num_format_str = '0'
style_sum.borders = thick_border
style_sum.font = bold_font
style_sum.alignment = align
# FP pattern
style_fp = xlwt.XFStyle()
style_fp.pattern = pattern_sum
style_fp.num_format_str = '0.00%'
style_fp.borders = thick_border
style_fp.font = bold_font
style_fp.alignment = align
last_pos = len(tag_list) + 1
sheet1.write(0, 0, ' ', header_style)
for idx_tag, cur_tag in enumerate(tag_list):
sheet1.write(0, idx_tag + 1, cur_tag, header_style)
sheet1.write(0, last_pos, 'Recall', header_style)
sheet1.write(0, last_pos + 1, 'Total', header_style)
col_count_hit = [0] * len(tag_list)
col_count_miss = [0] * len(tag_list)
for row_tag_idx, row_tag in enumerate(tag_list):
row_count_hit = 0
row_count_miss = 0
sheet1.write(row_tag_idx + 1, 0, row_tag, header_style)
for col_tag_idx, col_tag in enumerate(tag_list):
cur_value = confusion_matrix_to_write["{0}_{1}".format(
row_tag, col_tag)]
if cur_value == 0:
sheet1.write(row_tag_idx + 1, col_tag_idx + 1, cur_value,
style)
else:
if row_tag_idx == col_tag_idx:
sheet1.write(row_tag_idx + 1, col_tag_idx + 1,
cur_value, style_hit)
row_count_hit += cur_value
col_count_hit[col_tag_idx] += cur_value
else:
sheet1.write(row_tag_idx + 1, col_tag_idx + 1,
cur_value, style_mistake)
row_count_miss += cur_value
col_count_miss[col_tag_idx] += cur_value
row_count = row_count_hit + row_count_miss
if row_count == 0:
sheet1.write(row_tag_idx + 1, last_pos, row_count,
style_fp) # recall
else:
sheet1.write(row_tag_idx + 1, last_pos,
row_count_hit / row_count, style_fp) # recall
sheet1.write(row_tag_idx + 1, last_pos + 1, row_count,
style_sum) # total
sheet1.write(last_pos, 0, 'Precision', header_style)
sheet1.write(last_pos + 1, 0, 'Total', header_style)
total_count = 0
total_hit = 0
for col_idx, col_hit in enumerate(col_count_hit):
col_count = col_hit + col_count_miss[col_idx]
if col_count == 0:
sheet1.write(last_pos, col_idx + 1, col_count,
style_fp) # recall
else:
sheet1.write(last_pos, col_idx + 1, col_hit / col_count,
style_fp) # recall
total_count += col_count
total_hit += col_hit
sheet1.write(last_pos + 1, col_idx + 1, col_count, style_sum)
sheet1.write(last_pos, last_pos, total_hit / total_count, style_fp)
sheet1.write(last_pos, last_pos + 1, ':Accuracy', style)
return
def export_payments_xls(request):
"""분양대금 수납 내역"""
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response[
'Content-Disposition'] = 'attachment; filename={date}-payments.xls'.format(
date=datetime.now().strftime('%Y-%m-%d'))
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('분양대금납부_내역') # 시트 이름
# get_data: ?project=1&sd=2020-12-01&ed=2020-12-02&ipo=4&ba=5&up=on&q=#
project = Project.objects.get(pk=request.GET.get('project'))
sd = request.GET.get('sd')
ed = request.GET.get('ed')
ipo = request.GET.get('ipo')
ba = request.GET.get('ba')
up = request.GET.get('up')
q = request.GET.get('q')
today = TODAY
sd = sd if sd else '1900-01-01'
ed = ed if ed else today
obj_list = ProjectCashBook.objects.filter(
project=project,
project_account_d2__in=(1, 2),
deal_date__range=(sd, ed)).order_by('-deal_date', '-created_at')
if ipo:
obj_list = obj_list.filter(installment_order_id=ipo)
if ba:
obj_list = obj_list.filter(bank_account__id=ba)
if up:
obj_list = obj_list.filter((Q(is_contract_payment=False)
| Q(contract__isnull=True))
& (Q(project_account_d1_id__in=(1, 2))
| Q(project_account_d2_id__in=(1, 2))))
if q:
obj_list = obj_list.filter(
Q(contract__contractor__name__icontains=q) | Q(trader__icontains=q)
| Q(content__icontains=q) | Q(note__icontains=q))
# Sheet Title, first row
row_num = 0
style = xlwt.XFStyle()
style.font.bold = True
style.font.height = 300
style.alignment.vert = style.alignment.VERT_CENTER # 수직정렬
ws.write(row_num, 0, str(project) + ' 계약자 대금 납부내역', style)
ws.row(0).height_mismatch = True
ws.row(0).height = 38 * 20
# title_list
resources = [['거래일자', 'deal_date'],
['차수', 'contract__order_group__order_group_name'],
['타입', 'contract__contractunit__unit_type__name'],
['일련번호', 'contract__serial_number'],
['계약자', 'contract__contractor__name'], ['입금 금액', 'income'],
['납입회차', 'installment_order__pay_name'],
['수납계좌', 'bank_account__alias_name'], ['입금자', 'trader']]
columns = []
params = []
for rsc in resources:
columns.append(rsc[0])
params.append(rsc[1])
rows = obj_list.values_list(*params)
# Sheet header, second row
row_num = 1
style = xlwt.XFStyle()
style.font.bold = True
# 테두리 설정
# 가는 실선 : 1, 작은 굵은 실선 : 2,가는 파선 : 3, 중간가는 파선 : 4, 큰 굵은 실선 : 5, 이중선 : 6,가는 점선 : 7
# 큰 굵은 점선 : 8,가는 점선 : 9, 굵은 점선 : 10,가는 이중 점선 : 11, 굵은 이중 점선 : 12, 사선 점선 : 13
style.borders.left = 1
style.borders.right = 1
style.borders.top = 1
style.borders.bottom = 1
style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
style.pattern.pattern_fore_colour = xlwt.Style.colour_map['silver_ega']
style.alignment.vert = style.alignment.VERT_CENTER # 수직정렬
style.alignment.horz = style.alignment.HORZ_CENTER # 수평정렬
for col_num in range(len(columns)):
ws.write(row_num, col_num, columns[col_num], style)
# Sheet body, remaining rows
style = xlwt.XFStyle()
# 테두리 설정
style.borders.left = 1
style.borders.right = 1
style.borders.top = 1
style.borders.bottom = 1
style.alignment.vert = style.alignment.VERT_CENTER # 수직정렬
# style.alignment.horz = style.alignment.HORZ_CENTER # 수평정렬
for row in rows:
row_num += 1
for col_num, col in enumerate((columns)):
row = list(row)
if col_num == 0:
style.num_format_str = 'yyyy-mm-dd'
ws.col(col_num).width = 110 * 30
if '금액' in col:
style.num_format_str = '#,##'
ws.col(col_num).width = 110 * 30
if col == '차수' or col == '납입회차' or col == '일련번호':
ws.col(col_num).width = 110 * 30
if col == '수납계좌':
ws.col(col_num).width = 170 * 30
if col == '입금자' or col == '계약자':
ws.col(col_num).width = 110 * 30
ws.write(row_num, col_num, row[col_num], style)
wb.save(response)
return response
def export_round_record(game):
filename = tkFileDialog.asksaveasfilename(initialdir='./',
defaultextension='.xls',
initialfile='第%d轮比赛记录表' %
(game.get_stageno() + 1))
wbk = xlwt.Workbook(encoding='utf-8')
sheet = wbk.add_sheet('扑克牌掼蛋比赛记分表')
sheet.set_row_default_height(ROW_HEIGHT)
#设定列宽
sheet.col(0).width = 1111 #赛号
sheet.col(1).width = 3333 #姓名
sheet.col(2).width = 3333 #单位
sheet.col(19).width = 1111 #级差
for i in range(16):
sheet.col(i + 3).width = 999
#设定字体
al = xlwt.Alignment()
al.horz = xlwt.Alignment.HORZ_CENTER
al.vert = xlwt.Alignment.VERT_CENTER
font = xlwt.Font()
font.bold = True
font.height = 300
borders = xlwt.Borders() # Create Borders
borders.left = xlwt.Borders.THIN # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
title_style = xlwt.XFStyle()
title_style.font = font
title_style.alignment = al
border_style = xlwt.XFStyle()
border_style.borders = borders
#写入内容
rows_every_desk = 10
for i, desk in enumerate(game.get_ballot_result()):
for no in range(10):
sheet.row(i * 10 + no).height = ROW_HEIGHT
#第一行标题
sheet.write_merge(rows_every_desk * i, rows_every_desk * i, 0, 19,
game.config.get_title(), title_style)
#第二行
sheet.write(rows_every_desk * i + 1, 1,
'第%d轮' % (game.get_stageno() + 1))
sheet.write_merge(rows_every_desk * i + 1, rows_every_desk * i + 1, 3,
5, '时间：')
sheet.write_merge(rows_every_desk * i + 1, rows_every_desk * i + 1, 6,
13, game.config.get_time())
sheet.write_merge(rows_every_desk * i + 1, rows_every_desk * i + 1, 14,
15, '桌号')
sheet.write_merge(rows_every_desk * i + 1, rows_every_desk * i + 1, 16,
18, '第%d桌' % desk.get_did())
#第三行
sheet.write(rows_every_desk * i + 2, 0, '赛号', border_style)
sheet.write(rows_every_desk * i + 2, 1, '姓名', border_style)
sheet.write(rows_every_desk * i + 2, 2, '单位', border_style)
for num in range(16):
sheet.write(rows_every_desk * i + 2, num + 3, str(num + 1),
border_style)
sheet.write(rows_every_desk * i + 2, 19, '级差', border_style)
#第四-七行
for j, team in enumerate(desk.get_teams()):
sheet.write_merge(rows_every_desk * i + j * 2 + 3,
rows_every_desk * i + j * 2 + 4, 0, 0,
team.get_no(), border_style)
sheet.write_merge(rows_every_desk * i + j * 2 + 3,
rows_every_desk * i + j * 2 + 4, 1, 1,
team.get_p1() + '/' + team.get_p2(),
border_style)
sheet.write_merge(rows_every_desk * i + j * 2 + 3,
rows_every_desk * i + j * 2 + 4, 2, 2,
team.get_cname(), border_style)
for x in range(3, 20):
sheet.write(rows_every_desk * i + j * 2 + 3, x, '',
border_style)
sheet.write(rows_every_desk * i + j * 2 + 4, x, '',
border_style)
#第八行
sheet.write_merge(rows_every_desk * i + 7, rows_every_desk * i + 7, 0,
1, '胜方签字：')
sheet.write_merge(rows_every_desk * i + 7, rows_every_desk * i + 7, 6,
8, '负方签字：')
sheet.write_merge(rows_every_desk * i + 7, rows_every_desk * i + 7, 9,
11, '')
sheet.write_merge(rows_every_desk * i + 7, rows_every_desk * i + 7, 12,
16, '裁判员：')
sheet.write_merge(rows_every_desk * i + 7, rows_every_desk * i + 7, 17,
18, '胜方画圈')
#第九行
sheet.write_merge(rows_every_desk * i + 8, rows_every_desk * i + 8, 0,
1, '违例警告说明：')
wbk.save(filename)
return filename
def export_project_cash_xls(request):
"""프로젝트별 입출금 내역"""
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response[
'Content-Disposition'] = 'attachment; filename={date}-project-cashbook.xls'.format(
date=datetime.now().strftime('%Y-%m-%d'))
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('프로젝트_입출금_내역') # 시트 이름
# get_data: ?project=1&sdate=2020-12-01&edate=2020-12-31&sort=1&d1=1&d2=1&bank_acc=5&q=ㅁ
project = Project.objects.get(pk=request.GET.get('project'))
sdate = request.GET.get('sdate')
edate = request.GET.get('edate')
sort = request.GET.get('sort')
d1 = request.GET.get('d1')
d2 = request.GET.get('d2')
bank_acc = request.GET.get('bank_acc')
q = request.GET.get('q')
today = TODAY
sdate = sdate if sdate else '1900-01-01'
edate = edate if edate else today
obj_list = ProjectCashBook.objects.filter(
project=project,
deal_date__range=(sdate, edate)).order_by('-deal_date', '-created_at')
if sort:
obj_list = obj_list.filter(cash_category1__icontains=sort)
if d1:
obj_list = obj_list.filter(project_account_d1__id=d1)
if d2:
obj_list = obj_list.filter(project_account_d2__id=d2)
if bank_acc:
obj_list = obj_list.filter(bank_account__id=bank_acc)
if q:
obj_list = obj_list.filter(
Q(content__icontains=q) | Q(trader__icontains=q))
# Sheet Title, first row
row_num = 0
style = xlwt.XFStyle()
style.font.bold = True
style.font.height = 300
style.alignment.vert = style.alignment.VERT_CENTER # 수직정렬
ws.write(row_num, 0, str(project) + ' 입출금 내역', style)
ws.row(0).height_mismatch = True
ws.row(0).height = 38 * 20
# title_list
resources = [['거래일자', 'deal_date'], ['구분', 'cash_category1'],
['현장 계정', 'project_account_d1__name'],
['현장 세부계정', 'project_account_d2__name'], ['적요', 'content'],
['거래처', 'trader'], ['거래 계좌', 'bank_account__alias_name'],
['입금 금액', 'income'], ['출금 금액', 'outlay'],
['증빙 자료', 'evidence'], ['비고', 'note']]
columns = []
params = []
for rsc in resources:
columns.append(rsc[0])
params.append(rsc[1])
rows = obj_list.values_list(*params)
# Sheet header, second row
row_num = 1
style = xlwt.XFStyle()
style.font.bold = True
# 테두리 설정
# 가는 실선 : 1, 작은 굵은 실선 : 2,가는 파선 : 3, 중간가는 파선 : 4, 큰 굵은 실선 : 5, 이중선 : 6,가는 점선 : 7
# 큰 굵은 점선 : 8,가는 점선 : 9, 굵은 점선 : 10,가는 이중 점선 : 11, 굵은 이중 점선 : 12, 사선 점선 : 13
style.borders.left = 1
style.borders.right = 1
style.borders.top = 1
style.borders.bottom = 1
style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
style.pattern.pattern_fore_colour = xlwt.Style.colour_map['silver_ega']
style.alignment.vert = style.alignment.VERT_CENTER # 수직정렬
style.alignment.horz = style.alignment.HORZ_CENTER # 수평정렬
for col_num in range(len(columns)):
ws.write(row_num, col_num, columns[col_num], style)
# Sheet body, remaining rows
style = xlwt.XFStyle()
# 테두리 설정
style.borders.left = 1
style.borders.right = 1
style.borders.top = 1
style.borders.bottom = 1
style.alignment.vert = style.alignment.VERT_CENTER # 수직정렬
# style.alignment.horz = style.alignment.HORZ_CENTER # 수평정렬
for row in rows:
row_num += 1
for col_num, col in enumerate((columns)):
row = list(row)
if col == '거래일자':
style.num_format_str = 'yyyy-mm-dd'
ws.col(col_num).width = 110 * 30
if col == '구분':
if row[col_num] == '1':
row[col_num] = '입금'
if row[col_num] == '2':
row[col_num] = '출금'
if row[col_num] == '3':
row[col_num] = '대체'
if col == '현장 계정':
ws.col(col_num).width = 110 * 30
if col == '현장 세부계정':
ws.col(col_num).width = 160 * 30
if col == '적요' or col == '거래처':
ws.col(col_num).width = 180 * 30
if col == '거래 계좌':
ws.col(col_num).width = 170 * 30
if '금액' in col:
style.num_format_str = '#,##'
ws.col(col_num).width = 110 * 30
if col == '증빙 자료':
if row[col_num] == '0':
row[col_num] = '증빙 없음'
if row[col_num] == '1':
row[col_num] = '세금계산서'
if row[col_num] == '2':
row[col_num] = '계산서(면세)'
if row[col_num] == '3':
row[col_num] = '신용카드전표'
if row[col_num] == '4':
row[col_num] = '현금영수증'
if row[col_num] == '5':
row[col_num] = '간이영수증'
ws.col(col_num).width = 100 * 30
if col == '비고':
ws.col(col_num).width = 256 * 30
ws.write(row_num, col_num, row[col_num], style)
wb.save(response)
return response
mlist.close()
"""
myfile = open("mlist")
dst = xlwt.Workbook(encoding='utf-8')
table = dst.add_sheet('framework', cell_overwrite_ok=True)
row = 2
lines = myfile.readlines()
myfont = xlwt.Font()
myfont.bold = True
myfont.height = 100
myfont.colour_index = 5
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
#myfont.colour_index = 3 # 3 red 28 blue
blue_style = xlwt.XFStyle()
blue_style.font = myfont
blue_style.alignment = alignment
table.write(1, 0, "file name", blue_style)
table.write(1, 1, "增加行数", blue_style)
table.write(1, 2, "减少行数", blue_style)
table.write(1, 3, "文件类型", blue_style)
table.write(1, 4, "change_type", blue_style)
#print myfile
myfile.close()
for i in lines:
name = i.strip()
print name
code = os.system("git diff --numstat origin/QCOM_8992_ori %s > %s.tmp" %
(name, name))
#print code
def export_sites_xls(request):
"""프로젝트 지번별 토지목록"""
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response[
'Content-Disposition'] = 'attachment; filename={date}-sites.xls'.format(
date=datetime.now().strftime('%Y-%m-%d'))
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('지번별_토지목록') # 시트 이름
# get_data: ?project=1
project = Project.objects.get(pk=request.GET.get('project'))
obj_list = Site.objects.filter(project=project)
# Sheet Title, first row
# -----------------------
row_num = 0
style = xlwt.XFStyle()
style.font.bold = True
style.font.height = 300
style.alignment.vert = style.alignment.VERT_CENTER # 수직정렬
style.alignment.horz = style.alignment.HORZ_CENTER # 수평정렬
ws.write(row_num, 0, str(project) + ' 토지목록 조서', style)
rc = 7 if project.is_returned_area else 5
ws.merge(0, 0, 0, rc)
ws.row(0).height_mismatch = True
ws.row(0).height = 38 * 20
# -----------------------
# Sheet space, second row
# -----------------------
row_num = 1
style = xlwt.XFStyle()
style.alignment.vert = style.alignment.VERT_CENTER # 수직정렬
style.alignment.horz = style.alignment.HORZ_RIGHT # 수평정렬
ws.write(row_num, 7, TODAY + ' 현재', style)
# -----------------------
# title_list
resources = [
['No', 'order'],
['행정동', 'district'],
['지번', 'lot_number'],
['지목', 'site_purpose'],
['대지면적', 'official_area'],
]
if project.is_returned_area:
resources.append(['환지면적', 'returned_area'])
columns = []
params = []
for rsc in resources:
columns.append(rsc[0])
params.append(rsc[1])
rows = obj_list.values_list(*params)
# Sheet header, second row - 1
# -----------------------
row_num = 2
style = xlwt.XFStyle()
style.font.bold = True
# 테두리 설정
# 가는 실선 : 1, 작은 굵은 실선 : 2,가는 파선 : 3, 중간가는 파선 : 4, 큰 굵은 실선 : 5, 이중선 : 6,가는 점선 : 7
# 큰 굵은 점선 : 8,가는 점선 : 9, 굵은 점선 : 10,가는 이중 점선 : 11, 굵은 이중 점선 : 12, 사선 점선 : 13
style.borders.left = 1
style.borders.right = 1
style.borders.top = 1
style.borders.bottom = 1
style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
style.pattern.pattern_fore_colour = xlwt.Style.colour_map['silver_ega']
style.alignment.vert = style.alignment.VERT_CENTER # 수직정렬
style.alignment.horz = style.alignment.HORZ_CENTER # 수평정렬
for col_num, col in enumerate(columns):
if '면적' in col:
columns.insert(col_num + 1, '(평)')
ws.write(row_num, col_num, columns[col_num], style)
# -----------------------
# Sheet body, remaining rows
style = xlwt.XFStyle()
# 테두리 설정
style.borders.left = 1
style.borders.right = 1
style.borders.top = 1
style.borders.bottom = 1
style.alignment.vert = style.alignment.VERT_CENTER # 수직정렬
# style.alignment.horz = style.alignment.HORZ_CENTER # 수평정렬
for row in rows:
row_num += 1
for col_num, col in enumerate((columns)):
row = list(row)
if '면적' in col:
row.insert(col_num + 1, float(row[col_num]) * 0.3025)
ws.write(row_num, col_num, row[col_num], style)
wb.save(response)
return response
def write_output(output, output1, filename):
wb = xlwt.Workbook(encoding="utf-8")
sheet1 = wb.add_sheet("发货单", cell_overwrite_ok=True)
sheet2 = wb.add_sheet("采购单", cell_overwrite_ok=True)
# 设置列宽
sheet1.col(0).width = 3333 * 1
sheet1.col(1).width = 3333 * 3
sheet1.col(2).width = 4200
sheet1.col(2).height = 3333
sheet1.col(3).width = 1666
sheet1.col(4).width = 2000
sheet1.col(5).width = 2000
sheet1.col(6).width = 2000
sheet1.col(7).width = 2000
sheet1.col(8).width = 3333 * 1
sheet1.col(9).width = 3333
sheet1.col(10).width = 3333 * 3
sheet2.col(0).width = 1500
sheet2.col(1).width = 3333 * 3
sheet2.col(2).width = 4200
sheet2.col(3).width = 1666
sheet2.col(4).width = 2666
sheet2.col(5).width = 1666
sheet2.col(6).width = 2666
sheet2.col(7).width = 3333
sheet2.col(8).width = 3333
# 头部颜色，边框
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 7
# 标题背景
pattern_title = xlwt.Pattern()
pattern_title.pattern = xlwt.Pattern.SOLID_PATTERN
pattern_title.pattern_fore_colour = 3
# 边框
borders = xlwt.Borders()
borders.left = xlwt.Borders.HAIR
borders.right = xlwt.Borders.HAIR
borders.top = xlwt.Borders.HAIR
borders.bottom = xlwt.Borders.HAIR
# 居中
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT
# 头部样式
header_style = xlwt.XFStyle()
header_style.pattern = pattern
header_style.borders = borders
header_style.alignment = alignment
# 标题字体
font_title = xlwt.Font()
font_title.bold = True
font_title.height = 12 * 20
# 标题样式
title_style = xlwt.XFStyle()
title_style.pattern = pattern_title
title_style.alignment = alignment
title_style.font = font_title
center_style = xlwt.XFStyle()
center_style.alignment = alignment
center_style.borders = borders
# 颜色区分
pattern1 = xlwt.Pattern()
pattern1.pattern = xlwt.Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = 22
color_style = xlwt.XFStyle()
color_style.pattern = pattern1
color_style.borders = borders
color_style.alignment = alignment
header = [
"店主", "商品名", "规格", "数量", "实际斤数", "店铺价格", "单件价格", "总额（含运费）", "付款状态",
"电话", "地址"
]
title = "菜鲜省发货单"
goods_list_len = 0
keys = output.keys()
for key_index in range(len(output)):
key = keys[key_index]
# 标题写入
sheet1.write_merge(key_index + goods_list_len,
key_index + goods_list_len, 0,
len(header) - 1, title, title_style)
# 日期写入
for index in range(len(header) - 1):
sheet1.write(key_index + goods_list_len + 1, index, "",
title_style)
sheet1.write(key_index + goods_list_len + 1,
len(header) - 1, filename, title_style)
# 头部内容写入
for index in range(0, len(header)):
sheet1.write(key_index + goods_list_len + 2, index, header[index],
header_style)
# 设置头部高度
sheet1.row(key_index + goods_list_len + 2).height_mismatch = True
sheet1.row(key_index + goods_list_len + 2).height = 256 * 3
# 商品
for goods_index in range(0, len(output[key]["goods_list"])):
# 商品名
sheet1.write(
goods_index + key_index + goods_list_len + 3, 1,
output[key]["goods_list"][goods_index]["name"].decode("gbk"),
center_style)
# 商品规格
sheet1.write(
goods_index + key_index + goods_list_len + 3, 2,
output[key]["goods_list"][goods_index]["flavor"].decode("gbk"),
center_style)
# 商品数量
sheet1.write(
goods_index + key_index + goods_list_len + 3, 3,
output[key]["goods_list"][goods_index]["number"].decode("gbk"),
center_style)
for e_i in range(4, 9):
sheet1.write(goods_index + key_index + goods_list_len + 3, e_i,
"", center_style)
# 店主
sheet1.write_merge(
key_index + goods_list_len + 3, key_index + goods_list_len + 3 +
len(output[key]["goods_list"]) - 1, 0, 0,
keys[key_index].decode("gbk"), center_style)
# 总额
sheet1.write_merge(
key_index + goods_list_len + 3, key_index + goods_list_len + 3 +
len(output[key]["goods_list"]) - 1, 7, 7, "", center_style)
# 付款状态
sheet1.write_merge(
key_index + goods_list_len + 3, key_index + goods_list_len + 3 +
len(output[key]["goods_list"]) - 1, 8, 8,
output[key]["pay_status"].decode("gbk"), center_style)
# 电话
sheet1.write_merge(
key_index + goods_list_len + 3, key_index + goods_list_len + 3 +
len(output[key]["goods_list"]) - 1, 9, 9, output[key]["phone"],
center_style)
# 地址
sheet1.write_merge(
key_index + goods_list_len + 3, key_index + goods_list_len + 3 +
len(output[key]["goods_list"]) - 1, 10, 10,
output[key]["address"].decode("gbk"), center_style)
goods_list_len += len(output[key]["goods_list"]) + 3
header1 = [
"序号", "商品名", "规格", "数量", "进货斤数", "进货价", "对外售价", "购货人", "总规格", "总数量"
]
title1 = "菜鲜省采购清单 " + filename
# 标题写入
sheet2.write_merge(0, 0, 0, 0 + len(header1) - 1, title1, title_style)
flavor_list_len = 0
# 头部写入
for index in range(len(header1)):
sheet2.write(1, index, header1[index], header_style)
# 头部行高
sheet2.row(1).height_mismatch = True
sheet2.row(1).height = 256 * 2
keys = output1.keys()
for key_index in range(1, len(output1)):
style = center_style
if key_index % 2 == 0:
style = color_style
key = keys[key_index]
# 总规格
total_flavor = 0
# 总数量
total_number = 0
# 规格单位
flavor_unit = ""
# 数量单位
number_unit = ""
for flavor_index in range(0, len(output1[key]["flavors"])):
flavor_re = re.findall(
r'\d+',
output1[key]["flavors"][flavor_index]["flavor"].decode("gbk"))
if flavor_re:
total_flavor += int(flavor_re[-1])
number_re = re.findall(
r'\d+',
output1[key]["flavors"][flavor_index]["number"].decode("gbk"))
if number_re:
total_number += int(number_re[-1])
flavor_unit_re = re.findall(
r'.*\d+(.+?),',
output1[key]["flavors"][flavor_index]["flavor"].decode("gbk"))
if flavor_unit_re:
flavor_unit = flavor_unit_re[-1]
number_unit_re = re.findall(
r'.*\d+(.+?)$',
output1[key]["flavors"][flavor_index]["number"].decode("gbk"))
if number_unit_re:
number_unit = number_unit_re[-1]
# 规格
sheet2.write(
flavor_index + flavor_list_len + 2, 2,
output1[key]["flavors"][flavor_index]["flavor"].decode("gbk"),
style)
# 数量
sheet2.write(
flavor_index + flavor_list_len + 2, 3,
output1[key]["flavors"][flavor_index]["number"].decode("gbk"),
style)
# 进货斤数
sheet2.write(flavor_index + flavor_list_len + 2, 4, "", style)
# 进货价
sheet2.write(flavor_index + flavor_list_len + 2, 5, "", style)
# 对外售价
sheet2.write(flavor_index + flavor_list_len + 2, 6, "", style)
# 购货人
sheet2.write(
flavor_index + flavor_list_len + 2, 7, output1[key]["flavors"]
[flavor_index]["bussiness_name"].decode("gbk"), style)
# 序号
sheet2.write_merge(flavor_list_len + 2,
flavor_list_len + len(output1[key]["flavors"]) + 1,
0, 0, key_index, style)
# 商品名
sheet2.write_merge(flavor_list_len + 2,
flavor_list_len + len(output1[key]["flavors"]) + 1,
1, 1, key.decode("gbk"), style)
# 总规格
sheet2.write_merge(flavor_list_len + 2,
flavor_list_len + len(output1[key]["flavors"]) + 1,
8, 8,
str(total_flavor) + flavor_unit, style)
# 数量
sheet2.write_merge(flavor_list_len + 2,
flavor_list_len + len(output1[key]["flavors"]) + 1,
9, 9,
str(total_number) + number_unit, style)
flavor_list_len += len(output1[key]["flavors"])
wb.save(os.path.join(OUTPUT, filename + "发货采购单.xls"))
def export_cashbook_xls(request):
"""본사 입출금 내역"""
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response[
'Content-Disposition'] = 'attachment; filename={date}-cashbook.xls'.format(
date=datetime.now().strftime('%Y-%m-%d'))
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('본사_입출금_내역') # 시트 이름
# get_data: ?s_date=2018-06-30&e_date=&category1=&category2=&bank_account=&search_word=
s_date = request.GET.get('s_date')
e_date = request.GET.get('e_date')
category1 = request.GET.get('category1')
category2 = request.GET.get('category2')
bank_account = request.GET.get('bank_account')
search_word = request.GET.get('search_word')
company = Company.objects.first()
today = TODAY
s_date = s_date if s_date else '1900-01-01'
e_date = e_date if e_date else today
obj_list = CashBook.objects.filter(company=company,
deal_date__range=(s_date, e_date))
if category1:
obj_list = obj_list.filter(cash_category1__icontains=category1)
if category2:
obj_list = obj_list.filter(cash_category2__icontains=category2)
if bank_account:
obj_list = obj_list.filter(bank_account__id=bank_account)
if search_word:
obj_list = obj_list.filter(
Q(account__name__icontains=search_word)
| Q(content__icontains=search_word)
| Q(trader__icontains=search_word))
# Sheet Title, first row
row_num = 0
style = xlwt.XFStyle()
style.font.bold = True
style.font.height = 300
style.alignment.vert = style.alignment.VERT_CENTER # 수직정렬
ws.write(row_num, 0, str(company) + ' 입출금 내역', style)
ws.row(0).height_mismatch = True
ws.row(0).height = 38 * 20
# title_list
resources = [['거래일자', 'deal_date'], ['구분', 'cash_category1'],
['계정', 'cash_category1'], ['세부계정', 'account__name'],
['적요', 'content'], ['거래처', 'trader'],
['거래계좌', 'bank_account__alias_name'], ['입금금액', 'income'],
['출금금액', 'outlay'], ['증빙 자료', 'evidence'], ['비고', 'note']]
columns = []
params = []
for rsc in resources:
columns.append(rsc[0])
params.append(rsc[1])
rows = obj_list.values_list(*params)
# Sheet header, second row
row_num = 1
style = xlwt.XFStyle()
style.font.bold = True
# 테두리 설정
# 가는 실선 : 1, 작은 굵은 실선 : 2,가는 파선 : 3, 중간가는 파선 : 4, 큰 굵은 실선 : 5, 이중선 : 6,가는 점선 : 7
# 큰 굵은 점선 : 8,가는 점선 : 9, 굵은 점선 : 10,가는 이중 점선 : 11, 굵은 이중 점선 : 12, 사선 점선 : 13
style.borders.left = 1
style.borders.right = 1
style.borders.top = 1
style.borders.bottom = 1
style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
style.pattern.pattern_fore_colour = xlwt.Style.colour_map['silver_ega']
style.alignment.vert = style.alignment.VERT_CENTER # 수직정렬
style.alignment.horz = style.alignment.HORZ_CENTER # 수평정렬
for col_num in range(len(columns)):
ws.write(row_num, col_num, columns[col_num], style)
# Sheet body, remaining rows
style = xlwt.XFStyle()
# 테두리 설정
style.borders.left = 1
style.borders.right = 1
style.borders.top = 1
style.borders.bottom = 1
style.alignment.vert = style.alignment.VERT_CENTER # 수직정렬
# style.alignment.horz = style.alignment.HORZ_CENTER # 수평정렬
for row in rows:
row_num += 1
for col_num, col in enumerate((columns)):
row = list(row)
if col == '거래일자':
style.num_format_str = 'yyyy-mm-dd'
ws.col(col_num).width = 110 * 30
if col == '구분':
if row[col_num] == '1':
row[col_num] = '입금'
if row[col_num] == '2':
row[col_num] = '출금'
if row[col_num] == '3':
row[col_num] = '대체'
if col == '계정':
if row[col_num] == '1':
row[col_num] = '자산'
if row[col_num] == '2':
row[col_num] = '부채'
if row[col_num] == '3':
row[col_num] = '자본'
if row[col_num] == '4':
row[col_num] = '수익'
if row[col_num] == '5':
row[col_num] = '비용'
if row[col_num] == '6':
row[col_num] = '대체'
if col == '현장 계정':
ws.col(col_num).width = 110 * 30
if col == '세부계정':
ws.col(col_num).width = 160 * 30
if col == '적요' or col == '거래처':
ws.col(col_num).width = 180 * 30
if col == '거래계좌':
ws.col(col_num).width = 170 * 30
if '금액' in col:
style.num_format_str = '#,##'
ws.col(col_num).width = 110 * 30
if col == '증빙자료':
if row[col_num] == '0':
row[col_num] = '증빙 없음'
if row[col_num] == '1':
row[col_num] = '세금계산서'
if row[col_num] == '2':
row[col_num] = '계산서(면세)'
if row[col_num] == '3':
row[col_num] = '신용카드전표'
if row[col_num] == '4':
row[col_num] = '현금영수증'
if row[col_num] == '5':
row[col_num] = '간이영수증'
ws.col(col_num).width = 100 * 30
if col == '비고':
ws.col(col_num).width = 256 * 30
ws.write(row_num, col_num, row[col_num], style)
wb.save(response)
return response
每个好友就对应一个字典，每个字典用js格式存着各种信息'''
#'Sex','NickName','City','UserName','Signature'这是我想要的，你们也可以自己去字典找你们想用的
friend = [] #新建一个空列表
for i in range(len(friend_data)): #循环*次，*为字典个数，也就是你好友的数量
sex = friend_data[i].get('Sex') #get到性别
nickname = friend_data[i].get('NickName') #get到昵称
city = friend_data[i].get('City') #get到次城市
signature = friend_data[i].get('Signature') #get到个性签名
username = friend_data[i].get('UserName')
afdata = [sex,nickname,city,signature,username] #每次get到的都是一个好友的信息，把它们装进列表
friend.append(afdata) #把好友的列表信息装进上面建好的空列表
'''经过上面的for循环就把每个好友的信息装进friend中
大概是这个样子的 [[***],******] '''
workbook = xlwt.Workbook(encoding='utf-8') # 新建一个表格
worksheet = workbook.add_sheet('好友名单') # 给表格新建一个名为 好友名单 的工作簿
al = xlwt.Alignment() #对齐设置
al.horz = 0x02 #水平方向对齐
al.vert = 0x01 #垂直方向对齐
style = xlwt.XFStyle() # 创建一个样式对象，初始化样式
style.alignment = al #添加对齐设置
title = ['Sex','NickName','City','UserName','Signature'] #标题
for t in range(len(title)): #循环5次，也就是标题个数
worksheet.write(0,t,title[t],style) #写入，参数分别为(第0行，第t列，内容，样式）
for i in range(len(friend)): #循环n次，n个好友，一个好友写入一行
for j in range(len(friend[i])): #循环5次，之前代码中afdata的个数
worksheet.write(i+1,j,friend[i][j],style) #写入，第一行为标题，所以要i+1
workbook.save('微信好友一览表.xls') #保存表格，参数为文件名，记得加上格式.xls哦
#到这里程序就任务就完成了-------------------------------------------
print('总共有好友%d个'%len(friend)) #用来打印好友个数（无关紧要）
def write_excel(data_array, filename, name_key):
'''
data_array为一个字典，字典key为sheet名字，值为对应内容
函数功能为将多个sheet页保存为excel格式文件名为filename
name_key : sheet0中， 表名的rule
'''
if not data_array:
return False
xls = xlwt.Workbook()
_index = 1
# 先判断
if "field" in data_array.keys():
field_item = {"table_name": 0, "col_name": 1, "col_value": 2}
sheet = xls.add_sheet('field')
print(data_array['field'])
field_index = 0
for item in data_array['field']:
for _key, _value in field_item.items():
field_row = 1
sheet.write(0, field_index + _value, _key)
for _item in item[_key]:
sheet.write(field_row, field_index + _value, _item)
field_row += 1
field_index += 3
data_array.pop("field")
_index = 2
sheetNmae = "Tables"
if isinstance(name_key[0], list):
sheetNmae = "Tables-key"
sheet = xls.add_sheet(sheetNmae)
row = 0
for name in data_array.keys():
sheet.write(row, 0, row + _index)
sheet.write(row, 1, name)
if isinstance(name_key[0], list):
assert name == name_key[row][0]
sheet.write(row, 2, json.dumps(name_key[row][1]))
row += 1
key = _index
for name in data_array.keys():
try:
if len(name) >= 31:
sheet = xls.add_sheet(str(key))
else:
sheet = xls.add_sheet(name)
key += 1
except Exception as e:
print(e)
return False
col = 0
style = xlwt.XFStyle()
style.num_format_str = 'yyyy/m/d h:mm:ss'
for each_col in data_array[name]:
row = 0
for each_row in each_col:
if isinstance(each_row, datetime.datetime):
sheet.write(col, row, each_row, style)
elif isinstance(each_row, datetime.date):
sheet.write(col, row, str(each_row))
else:
try:
sheet.write(col, row, each_row)
except Exception as err:
print(err)
row += 1
col += 1
xls.save(filename)
return True
def print_inventory_export_report(self):
cr, uid, context = self.env.args
if context is None:
context = {}
context = dict(context)
data = self.read()[0]
start_date = data.get('date_start', False)
end_date = data.get('date_end', False)
if start_date and end_date and end_date < start_date:
raise Warning(_("End date should be greater than start date!"))
res_user = self.env["res.users"].browse(uid)
export = data.get('export_report', False)
# Create Inventory Export report in Excel file.
workbook = xlwt.Workbook(style_compression=2)
worksheet = workbook.add_sheet('Sheet 1')
font = xlwt.Font()
font.bold = True
header = xlwt.easyxf('font: bold 1, height 280')
# start_date = datetime.strptime(str(context.get("date_from")), DEFAULT_SERVER_DATE_FORMAT)
# start_date_formate = start_date.strftime('%d/%m/%Y')
# end_date = datetime.strptime(str(context.get("date_to")), DEFAULT_SERVER_DATE_FORMAT)
# end_date_formate = end_date.strftime('%d/%m/%Y')
# start_date_to_end_date = tools.ustr(start_date_formate) + ' To ' + tools.ustr(end_date_formate)
style = xlwt.easyxf('align: wrap yes')
worksheet.row(0).height = 500
worksheet.row(1).height = 500
for x in range(0, 41):
worksheet.col(x).width = 6000
borders = xlwt.Borders()
borders.top = xlwt.Borders.MEDIUM
borders.bottom = xlwt.Borders.MEDIUM
border_style = xlwt.XFStyle() # Create Style
border_style.borders = borders
border_style1 = xlwt.easyxf('font: bold 1')
border_style1.borders = borders
style = xlwt.easyxf('align: wrap yes', style)
ids_location = []
ids_categ = []
ids_product = []
where_end_date_awal = " sm.date is null "
where_start_date = " 1=1 "
if start_date:
where_start_date = " sm.date + interval '7 hour' >= '%s 00:00:00' " % start_date
where_end_date_awal = " sm.date + interval '7 hour' < '%s 00:00:00' " % start_date
where_end_date = " 1=1 "
if end_date:
where_end_date = " sm.date + interval '7 hour' <= '%s 23:59:59'" % end_date
where_location = " 1=1 "
if ids_location:
where_location = """(sm.location_id in %s
or sm.location_dest_id in %s)""" % (str(
tuple(ids_location)).replace(
',)', ')'), str(tuple(ids_location)).replace(',)', ')'))
where_categ = " 1=1 "
if ids_categ:
where_categ = "pt.categ_id in %s" % str(tuple(ids_categ)).replace(
',)', ')')
where_product = " 1=1 "
if ids_product:
where_product = "pp.id in %s" % str(tuple(ids_product)).replace(
',)', ')')
if export == "BC 2.5":
where_export = "BC 2.5"
elif export == "BC 2.6.1":
where_export = "BC 2.6.1"
elif export == "BC 2.7":
where_export = "BC 2.7"
elif export == "BC 3.0":
where_export = "BC 3.0"
elif export == "BC 3.3":
where_export = "BC 3.3"
elif export == "BC 4.1":
where_export = "BC 4.1"
query = """
SELECT
sp.jenis_dokumen, sp.no_dokumen AS no_dokumen_pabean, sp.tanggal_dokumen AS tanggal_dokumen_pabean, sp.name AS no_dokumen,
sp.date_done AS tanggal_dokumen, rp.name AS nama_mitra, pp.default_code AS kode_barang, pt.name AS nama_barang, uu.name,
sm.product_uom_qty, coalesce(sm.subtotal_price,0) AS nilai_barang, spt.code AS status_type, rc.symbol
FROM stock_move sm
INNER JOIN stock_picking sp ON sm.picking_id=sp.id
LEFT JOIN res_partner rp ON sp.partner_id=rp.id
LEFT JOIN product_product pp ON pp.id=sm.product_id
LEFT JOIN uom_uom uu ON uu.id=sm.product_uom
LEFT JOIN product_template pt ON pt.id=pp.product_tmpl_id
LEFT JOIN stock_picking_type spt ON spt.id=sm.picking_type_id
LEFT JOIN sale_order_line sol ON sol.id=sm.sale_line_id
LEFT JOIN res_currency rc ON rc.id = sp.currency_id
WHERE sm.state = 'done'
AND ((sm.location_id = '12' AND sm.location_dest_id != '12')
OR (sm.location_id = '26' AND sm.location_dest_id = '24'))
AND """ + where_start_date + """
AND """ + where_end_date + """
AND sp.jenis_dokumen = '""" + where_export + """'
ORDER BY sp.tanggal_dokumen ASC, sp.no_dokumen ASC
"""
self._cr.execute(query)
hasil = self._cr.fetchall()
company = self.env.user.company_id.name
start_date_format = start_date.strftime('%d/%m/%Y')
end_date_format = end_date.strftime('%d/%m/%Y')
# date_format = xlwt.XFStyle()
# date_format.num_format_str = 'dd/mm/yyyy'
worksheet.write_merge(
1, 1, 0, 4, "LAPORAN PENGELUARAN BARANG PER DOKUMEN " + export,
xls_format.font_style(position='left',
bold=1,
fontos='black',
font_height=300))
worksheet.write_merge(
2, 2, 0, 4, "KAWASAN BERIKAT " + str(company).upper(),
xls_format.font_style(position='left',
bold=1,
fontos='black',
font_height=300))
worksheet.write_merge(
3, 3, 0, 4, "LAPORAN PENGELUARAN BARANG PER DOKUMEN " + export,
xls_format.font_style(position='left',
bold=1,
fontos='black',
font_height=300))
worksheet.write_merge(
5, 5, 0, 1, "PERIODE : " + str(start_date_format) + " S.D " +
str(end_date_format),
xls_format.font_style(position='left',
bold=1,
fontos='black',
font_height=200))
row = 7
worksheet.write_merge(
7, 8, 0, 0, "No",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
worksheet.write_merge(
7, 8, 1, 1, "Jenis Dokumen",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
worksheet.write_merge(
7, 7, 2, 3, "Dokumen Pabean",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
worksheet.write(
8, 2, "Nomor",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
worksheet.write(
8, 3, "Tanggal",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
worksheet.write_merge(
7, 7, 4, 5, "Bukti/Dokumen Pengeluaran",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
worksheet.write(
8, 4, "Nomor",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
worksheet.write(
8, 5, "Tanggal",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
worksheet.write_merge(
7, 8, 6, 6, "Pembeli/Penerima",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
worksheet.write_merge(
7, 8, 7, 7, "Kode Barang",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
worksheet.write_merge(
7, 8, 8, 8, "Nama Barang",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
worksheet.write_merge(
7, 8, 9, 9, "Sat",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
worksheet.write_merge(
7, 8, 10, 10, "Jumlah",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
worksheet.write_merge(
7, 8, 11, 11, "Currency",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
worksheet.write_merge(
7, 8, 12, 12, "Nilai Barang",
xls_format.font_style(position='center',
bold=1,
border=1,
fontos='black',
font_height=200,
color='grey'))
row += 2
no = 1
for val in hasil:
worksheet.write(
row, 0, no,
xls_format.font_style(position='center',
border=1,
fontos='black',
font_height=200,
color='false'))
worksheet.write(
row, 1, val[0],
xls_format.font_style(position='center',
border=1,
fontos='black',
font_height=200,
color='false'))
worksheet.write(
row, 2, val[1],
xls_format.font_style(position='center',
border=1,
fontos='black',
font_height=200,
color='false'))
worksheet.write(
row, 3, str(val[2].strftime('%d/%m/%Y')),
xls_format.font_style(position='center',
border=1,
fontos='black',
font_height=200,
color='false'))
worksheet.write(
row, 4, val[3],
xls_format.font_style(position='center',
border=1,
fontos='black',
font_height=200,
color='false'))
worksheet.write(
row, 5, str(val[4].strftime('%d/%m/%Y')),
xls_format.font_style(position='center',
border=1,
fontos='black',
font_height=200,
color='false'))
worksheet.write(
row, 6, val[5],
xls_format.font_style(position='center',
border=1,
fontos='black',
font_height=200,
color='false'))
worksheet.write(
row, 7, val[6],
xls_format.font_style(position='center',
border=1,
fontos='black',
font_height=200,
color='false'))
worksheet.write(
row, 8, val[7],
xls_format.font_style(position='center',
border=1,
fontos='black',
font_height=200,
color='false'))
worksheet.write(
row, 9, val[8],
xls_format.font_style(position='center',
border=1,
fontos='black',
font_height=200,
color='false'))
worksheet.write(
row, 10, val[9],
xls_format.font_style(position='center',
border=1,
fontos='black',
font_height=200,
color='false'))
worksheet.write(
row, 11, val[12],
xls_format.font_style(position='center',
border=1,
fontos='black',
font_height=200,
color='false'))
worksheet.write(
row, 12, val[10],
xls_format.font_style(position='center',
border=1,
fontos='black',
font_height=200,
color='false'))
row += 1
no += 1
fp = BytesIO()
workbook.save(fp)
# fp.seek(0)
# data = fp.read()
# fp.close()
res = base64.encodestring(fp.getvalue())
res_id = self.env['inventory.excel.pengeluaran.export.summary'].create(
{
'fname': 'Laporan Pengeluaran Barang.xls',
'file': res
})
return {
'type':
'ir.actions.act_url',
'url':
'/web/binary/download_document?model=inventory.excel.pengeluaran.export.summary&field=file&id=%s&filename=Laporan Pengeluaran Barang.xls'
% (res_id.id),
'target':
'new',
}
def main(path):
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('Sheet1')
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = 'Times New Roman'
font.bold = True # 黑体
font.underline = True # 下划线
font.italic = True # 斜体字
style.font = font # 设定样式
worksheet.col(0).width = 0x0d00 + 50 * 50
for i in range(1, 10):
worksheet.col(i).width = 0x0d00
worksheet.write(0, 0, '<SYSNAME>')
worksheet.write(0, 1, '<文件IP名称>')
worksheet.write(0, 2, '<A对端>')
worksheet.write(0, 3, '<A本端>')
worksheet.write(0, 4, '<B对端>')
worksheet.write(0, 5, '<B本端>')
worksheet.write(0, 6, '<C对端>')
worksheet.write(0, 7, '<C本端>')
worksheet.write(0, 8, '<D对端>')
worksheet.write(0, 9, '<D本端>')
i = 1
# print(path)
files = os.listdir(path)
files = sorted(files,
key=lambda file:
(int(file.split('--')[0].split('.')[-1]) + 1000 * int(
file.split('--')[0].split('.')[-2])))
for file in files:
print('提取' + file + '文件中peer参数.......')
device = ad = ab = bd = bb = cd = cb = dd = db = ''
file = os.path.join(path, file)
with open(file, 'rb') as f:
string = ''
for line in f:
string += line.decode('utf8').strip() + '\n'
if re.findall('sysname (.*?)', string):
device = re.search('sysname (.*?)\n', string).groups()[0]
if re.findall('interface 40GE1/0/1\n', string):
try:
ad = re.findall('description To_.*?_(.*?)_.*?\n',
string)[0]
ab = re.findall('ip address (.*?) 255.*?\n', string)[0]
# print(ad, ab)
except:
pass
if re.findall('interface 40GE1/0/2\n', string):
try:
bd = re.findall('description To_.*?_(.*?)_.*?\n',
string)[0]
bb = re.findall('ip address (.*?) 255.*?\n', string)
except:
pass
if re.findall('interface 40GE1/0/3\n', string):
try:
cd = re.findall('description To_.*?_(.*?)_.*?\n',
string)[0]
cb = re.findall('ip address (.*?) 255.*?\n', string)[0]
except:
pass
if re.findall('interface 40GE1/0/4\n', string):
try:
dd = re.findall('description To_.*?_(.*?)_.*?\n',
string)[0]
db = re.findall('ip address (.*?) 255.*?\n', string)[0]
# print(dd, db)
except:
pass
if line.decode('utf8').strip() == '#':
string = ''
worksheet.write(i, 0, device)
worksheet.write(i, 1, os.path.basename(file).split('--')[0])
worksheet.write(i, 2, ad)
worksheet.write(i, 3, ab)
worksheet.write(i, 4, bd)
worksheet.write(i, 5, bb)
worksheet.write(i, 6, cd)
worksheet.write(i, 7, cb)
worksheet.write(i, 8, dd)
worksheet.write(i, 9, db)
i += 1
workbook.save('自动生成文件文件夹' + '/' + 'peer.xls')
def stock_rotation_export_excel(self):
"""
This methods make Export in Stock Rotation Excel
"""
import base64
filename = 'Stock Rotation Export.xls'
workbook = xlwt.Workbook()
style = xlwt.XFStyle()
tall_style = xlwt.easyxf('font:height 720;') # 36pt
# Create a font to use with the style
font = xlwt.Font()
font.name = 'Times New Roman'
font.bold = True
font.height = 250
style.font = font
xlwt.add_palette_colour("custom_colour", 0x21)
workbook.set_colour_RGB(0x21, 105, 105, 105)
xlwt.add_palette_colour("custom_colour_new", 0x22)
workbook.set_colour_RGB(0x22, 169, 169, 169)
worksheet = workbook.add_sheet("Stock Rotation")
styleheader = xlwt.easyxf('font: bold 1, colour white, height 245; pattern: pattern solid, fore_colour custom_colour')
stylecolumnheader = xlwt.easyxf('font: bold 1, colour white, height 200; pattern: pattern solid, fore_colour custom_colour')
zero_col = worksheet.col(0)
zero_col.width = 236 * 11
first_col = worksheet.col(1)
first_col.width = 236 * 40
second_col = worksheet.col(2)
second_col.width = 236 * 18
third_col = worksheet.col(3)
third_col.width = 236 * 13
fourth_col = worksheet.col(4)
fourth_col.width = 236 * 13
fifth_col = worksheet.col(5)
fifth_col.width = 236 * 13
sixth_col = worksheet.col(6)
sixth_col.width = 236 * 13
seven_col = worksheet.col(7)
seven_col.width = 236 * 13
eight_col = worksheet.col(8)
eight_col.width = 236 * 13
nine_col = worksheet.col(9)
nine_col.width = 236 * 16
ten_col = worksheet.col(10)
ten_col.width = 236 * 19
eleven_col = worksheet.col(11)
eleven_col.width = 236 * 13
twelve_col = worksheet.col(12)
twelve_col.width = 236 * 15
thirteen_col = worksheet.col(13)
thirteen_col.width = 236 * 13
#HEADER
worksheet.write_merge(0, 1, 0, 13, ' Stock Rotation/Movement of Products', style = styleheader)
worksheet.write_merge(2, 2, 0, 1, 'Start Date : '+ str(self.date_from))
worksheet.write_merge(3, 3, 0, 1, 'End Date : '+ str(self.date_to))
#SUB-HEADER
row = 4
worksheet.write(row, 0, 'Product ID', stylecolumnheader)
worksheet.write(row, 1, 'Product Name', stylecolumnheader)
worksheet.write(row, 2, 'Internal Reference', stylecolumnheader)
worksheet.write(row, 3, 'Cost Price', stylecolumnheader)
worksheet.write(row, 4, 'Sale Price', stylecolumnheader)
worksheet.write(row, 5, 'Opening', stylecolumnheader)
worksheet.write(row, 6, 'Incoming', stylecolumnheader)
worksheet.write(row, 7, 'Outgoing', stylecolumnheader)
worksheet.write(row, 8, 'Internal', stylecolumnheader)
worksheet.write(row, 9, 'Purchase Count', stylecolumnheader)
worksheet.write(row, 10, 'Last Purchase Date', stylecolumnheader)
worksheet.write(row, 11, 'Sale Count', stylecolumnheader)
worksheet.write(row, 12, 'Last Sale Date', stylecolumnheader)
worksheet.write(row, 13, 'Ending', stylecolumnheader)
row = 5
data = self.get_stock_moves_details()
for warehouse in data:
worksheet.write_merge(row, row, 0, 13, 'Warehouse: '+ str(warehouse))
for category in data[warehouse]:
row += 1
worksheet.write_merge(row, row, 0, 13, 'Category: '+ str(category))
for line in data[warehouse][category]:
row += 1
worksheet.write(row, 0, line.get('product_id'))
worksheet.write(row, 1, line.get('product'))
worksheet.write(row, 2, line.get('reference'))
worksheet.write(row, 3, line.get('cost_price'))
worksheet.write(row, 4, line.get('sale_price'))
worksheet.write(row, 5, line.get('opening'))
worksheet.write(row, 6, line.get('incooming_qty'))
worksheet.write(row, 7, line.get('internal_qty'))
worksheet.write(row, 8, line.get('outgoing_qty'))
worksheet.write(row, 9, line.get('purchase_count'))
worksheet.write(row, 10, line.get('last_purchase_date'))
worksheet.write(row, 11, line.get('sale_count'))
worksheet.write(row, 12, line.get('last_sale_date'))
worksheet.write(row, 13, line.get('ending'))
row += 2
buffer = BytesIO()
workbook.save(buffer)
export_id = self.env['stock.rotation.export.excel'].create(
{'excel_file': base64.encodestring(buffer.getvalue()), 'file_name': filename})
buffer.close()
return {
'view_mode': 'form',
'res_id': export_id.id,
'res_model': 'stock.rotation.export.excel',
'view_mode': 'form',
'type': 'ir.actions.act_window',
'target': 'new',
}
from tkinter import ttk
from datetime import datetime
import tkinter as tk
import xlrd
from xlutils.copy import copy
import xlwt
from PIL import ImageTk, Image
# make the excel file to store information
style1 = xlwt.XFStyle()
style1.num_format_str = "D-MM-YY"
# wb = xlwt.Workbook()
# sheet1 = wb.add_sheet("May 2018", cell_overwrite_ok=True)
# sheet1.write(0, 0, datetime.now(), style1)
#
#
# wb.save("May 2018 orders.xls")
read_workbook = xlrd.open_workbook("May 2018 orders.xls", formatting_info=True)
read_sheet = read_workbook.sheet_by_index(0)
workbook = copy(read_workbook)
sheet = workbook.get_sheet(0)
sheet.write(0, 0, datetime.now(), style1)
sheet.write(0, 1, "Item")
sheet.write(0, 2, "Size")
sheet.write(0, 3, "Color")
sheet.write(0, 4, "Quantity")
def PrintReport(self, list_room_id, report_type, date):
try:
report = get_report(list_room_id, report_type, date)
print("========================PrintReport========================")
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Report')
borders = xlwt.Borders() # Create Borders
borders.left = xlwt.Borders.THICK
borders.right = xlwt.Borders.THICK
borders.top = xlwt.Borders.THICK
borders.bottom = xlwt.Borders.THICK
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
pattern = xlwt.Pattern() # Create the Pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
style = xlwt.XFStyle() # Create the Pattern
style.pattern = pattern # Add Pattern to Style
style.borders = borders # Add Borders to Style
titles = ['Date', 'Room_ID', 'Service_ID', 'TimesOfOnOff', 'Duration', 'TotalFee', 'TimesOfDispatch', 'NumberOfRdr',
'TimesOfChangeTemp', 'TimesOfChangeSpeed']
for i in range(0, 10):
worksheet.col(i).width = 5000
worksheet.write(0, i, titles[i], style)
j = 1
for reporter in report:
for reporterr in reporter:
for i in range(0, 10):
worksheet.write(j, i, reporterr[i])
j += 1
file_name = time.strftime('%Y-%m-%d_%H-%M-%S')
file_name = str(file_name) + '_Report.xls'
workbook.save(file_name)
return True
except:
return False
'''SetRdr设置详单
room_id int, //房间编号
day_in timestamp, //入住时间
fanspeed int, //风速
feerate float, //费率
fee float, //费用'''
def SetRdr(self, room_id, day_in, fanspeed, feerate, fee):
set_rdr(room_id,0, day_in, fanspeed, feerate, fee)
'''SetInvoice设置账单
room_id int, //房间编号
day_in timestamp, //入住时间
total_fee float //总费用'''
def SetInvoice(self, room_id, day_in, total_fee):
set_invoice(room_id, day_in, total_fee)
'''SetReport设置报表
date timestamp, //入住日期
room_id int, //房间编号
times_of_onoff int, //开关次数
duration int, //经过时间
total_fee float, //总费用
times_of_dispatch int, //调度次数
number_of_rdr int, //详单数
times_of_changetemp int, //调温次数
times_of_changespeed int //变速次数'''
def SetReport(self, date, room_id, service_id, times_of_onoff, duration, total_fee, times_of_dispatch, number_of_rdr,
times_of_changetemp, times_of_changespeed):
set_report(date, room_id, service_id, times_of_onoff, duration, total_fee, times_of_dispatch, number_of_rdr,
times_of_changetemp, times_of_changespeed)
#退房间 里面设置报表
def CheckOut(self, room_id):
for room_service in self.lists:
if room_service[0] == room_id:
service_id = room_service[1]
service = self.sq.get_service(service_id)
NumberOfRdr = 12
self.SetReport(self.day_in[room_id], room_id, service_id, self.TimesOfOnoff[room_id], service.service_time,
self.GetRoomFee(room_id, self.day_in[room_id]), self.TimesOfDispatch[room_id], NumberOfRdr,
self.TimesOfChangetemp[room_id], self.TimesOfChangespeed[room_id])
curtime = time.strftime('%Y-%m-%d')
self.day_out.setdefault(room_id, curtime)
def print_queue(self):
print("=========================================")
print("service_queue")
for i in range(0, len(self.sq.service_queue)):
print("service_id: ",self.sq.service_queue[i][0],"||", end=" ")
print("start_time: ",self.sq.service_queue[i][1].start_time,"|",
"current_time: ",self.sq.service_queue[i][1].current_time,"|",
"service_time: ",self.sq.service_queue[i][1].service_time,"|",
"wait_time: ",self.sq.service_queue[i][1].wait_time,"|",
"indoor_temp: ", self.sq.service_queue[i][1].indoor_temp,"|",
"temperature: ", self.sq.service_queue[i][1].temperature,"|",
"fan_speed: ", self.sq.service_queue[i][1].fan_speed)
print("---------------------------------------")
print("wait_queue")
for i in range(0, len(self.wq.wait_queue)):
print("service_id: ", self.wq.wait_queue[i][0],"||", end=" ")
print("start_time: ", self.wq.wait_queue[i][1].start_time,"|",
"current_time: ", self.wq.wait_queue[i][1].current_time,"|",
"service_time: ", self.wq.wait_queue[i][1].service_time,"|",
"wait_time: ", self.wq.wait_queue[i][1].wait_time,"|",
"indoor_temp: ", self.wq.wait_queue[i][1].indoor_temp,"|",
"temperature: ", self.wq.wait_queue[i][1].temperature,"|",
"fan_speed: ", self.wq.wait_queue[i][1].fan_speed)
def check_room_state(self):
dicts = []
for room_service in self.lists:
room_id = room_service[0]
service_id, service = self.find_service(room_id)
# state 只有开机的服务，关机的已被删除
if self.sq.get_service(service_id) is not None:
state = 2 # 表示正在服务
else:
state = 1 # 表示正在等待
current_temp = service.indoor_temp
target_temp = service.temperature
fan_speed = service.fan_speed
fee_rate = 1 #三种费率选哪个
day_in = "2019-4-5"
fee = self.GetRoomFee(room_id, day_in) #day_in从哪获得？
duration = service.service_time #哪里获得duration
dicts.append([room_id,state, current_temp, target_temp, fan_speed, fee_rate, fee, duration])
def WriteExcel(filename):
#加入写Excel
import xlwt
workbook = xlwt.Workbook() #注意Workbook的开头W要大写
sheet1 = workbook.add_sheet('sheet1', cell_overwrite_ok=True)
#cell_overwrite_ok参数用于确认同一个cell单元是否可以重设值。
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = 'SimSun' # 指定“宋体”
font.height = 230
style.font = font
#上设置字体
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
borders.bottom_colour = 0x3A
style.borders = borders
#上设置框线
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER #水平居中
alignment.vert = xlwt.Alignment.VERT_CENTER #垂直居中
style.alignment = alignment
#设置字体居中
first_col = sheet1.col(0)
first_col.width = 350 * 20
second_col = sheet1.col(1)
second_col.width = 270 * 20
third_col = sheet1.col(2)
third_col.width = 270 * 20
fourth_col = sheet1.col(3)
fourth_col.width = 270 * 20
fifth_col = sheet1.col(4)
fifth_col.width = 270 * 20
sixth_col = sheet1.col(5)
sixth_col.width = 270 * 20
seventh_col = sheet1.col(6)
seventh_col.width = 270 * 20
seventh_col = sheet1.col(7)
seventh_col.width = 270 * 20
seventh_col = sheet1.col(8)
seventh_col.width = 270 * 20
#上设置横行的长度
################设置第一行字体的格式###############
geshi = xlwt.XFStyle()
font = xlwt.Font()
font.name = 'SimSun' # 指定“宋体”
font.height = 350
font.bold = 'on'
geshi.font = font
#上设置字体
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
borders.bottom_colour = 0x3A
geshi.borders = borders
##############设置第一行字体的格式完成#############
sheet1.write(0, 0, '银行卡号', geshi)
sheet1.write(0, 1, '所属银行', geshi)
sheet1.write(0, 2, '所在城市', geshi)
sheet1.write(0, 3, '所在区县', geshi)
num = 1
LastBankInfo = ReadEcxecl(filename)
for don in LastBankInfo:
sheet1.write(num, 0, don[0], style) # 银行卡号
sheet1.write(num, 1, don[1], style) # 所属银行
sheet1.write(num, 2, don[2], style) # 所在城市
sheet1.write(num, 3, don[3], style) # 所在区县
num = num + 1
now = time.strftime("%Y-%m-%d-%H_%M_%S", time.localtime(time.time()))
workbook.save(now + '生成银行卡结果.xls')
print("===================生成银行卡完成===================")
