def Excel_Style():
"""
設置單元格格式
:return:
"""
# 設置居中
style_center = xlwt.XFStyle()
a1 = xlwt.Alignment()
a1.horz = 0x02 # 水平居中
a1.vert = 0x01 # 垂直居中
style_center.alignment = a1
#設置表頭為黃色背景色且居中
pattern_yellow = xlwt.Pattern()
pattern_yellow.pattern = xlwt.Pattern.SOLID_PATTERN
pattern_yellow.pattern_fore_colour=5# 5代表黃色
style_bgcolor_yellow = xlwt.XFStyle()
style_bgcolor_yellow.pattern = pattern_yellow
style_bgcolor_yellow.alignment=a1
#設置單元格紅色背景且居中
pattern_red = xlwt.Pattern()
pattern_red.pattern = xlwt.Pattern.SOLID_PATTERN
pattern_red.pattern_fore_colour = 2 # 2代表紅色
style_bgcolor_red = xlwt.XFStyle()
style_bgcolor_red.pattern = pattern_red
style_bgcolor_red.alignment = a1
#设置单元格宽度
#Cell_width=xlwt.Borders.width(25)
return [style_bgcolor_yellow, style_center, style_bgcolor_red]
def set_style(self):
#设置样式
self.style = xlwt.XFStyle()
self.style1 = xlwt.XFStyle()
am = xlwt.Alignment() #对齐格式
am1 = xlwt.Alignment() #对齐格式
font1 = xlwt.Font() # 为样式创建字体
font1.bold = True
borders = xlwt.Borders() #边框
pattern = xlwt.Pattern() #背景颜色
pattern1 = xlwt.Pattern() #背景颜色
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 1
pattern1.pattern = xlwt.Pattern.SOLID_PATTERN
pattern1.pattern_fore_colour = 22
#设置边框
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
am.vert = 0x01 # 设置水平居中
am1.vert = 0x01 # 设置水平居中
am1.horz = 0x02 # 设置垂直居中
self.style.alignment = am
self.style.borders = borders
self.style1.alignment = am1
self.style1.borders = borders
self.style1.pattern = pattern1
self.style.pattern = pattern
self.style1.font = font1
def writedifftalbeinfo2excel(excelfile, tablediffhashset, olddbcmpname,
oldtablename, oldslttableinfo, newdbcmpname,
newtablename, newslttableinfo):
'''Write content to excelfile.xlsx'''
global tableinfo
new_workbook = xlwt.Workbook(encoding='ascii')
ws = new_workbook.add_sheet(olddbcmpname + "_" + newdbcmpname)
oldpattern = xlwt.Pattern()
oldpattern.pattern = xlwt.Pattern.SOLID_PATTERN
oldpattern.pattern_fore_colour = 2
oldcell_style = xlwt.XFStyle()
oldcell_style.pattern = oldpattern
newpattern = xlwt.Pattern()
newpattern.pattern = xlwt.Pattern.SOLID_PATTERN
newpattern.pattern_fore_colour = 3
newcell_style = xlwt.XFStyle()
newcell_style.pattern = newpattern
newtablelist = tableinfo[newdbcmpname][newtablename]
primarykey = newtablelist[1]
varlist = newtablelist[4]
### 写入第一行
rownum = 0
ws.write(rownum, 0, "比对数据库类型")
primarykeylist = primarykey.split(',')
primarykeylistlen = len(primarykeylist)
for prykeycol in range(1, primarykeylistlen + 1):
ws.write(rownum, prykeycol, primarykeylist[prykeycol - 1])
varlistlen = len(varlist)
for varcol in range(primarykeylistlen + 1,
primarykeylistlen + varlistlen + 1):
ws.write(rownum, varcol, varlist[varcol - primarykeylistlen - 1])
### 写入第二行之后的数据,这个是一定要考虑一个hash下面是否对应多个主键了。
### {hashkey1:[(),(),...], hashkey2:[(),(),...]}
tuplelen = primarykeylistlen + varlistlen
rownum = rownum + 1
for tblhashtmp in tablediffhashset:
slttbllist = oldslttableinfo[tblhashtmp]
for curtupletmp in slttbllist:
ws.write(rownum, 0, olddbcmpname, oldcell_style)
for varcol in range(1, tuplelen + 1):
ws.write(rownum, varcol, curtupletmp[varcol - 1],
oldcell_style)
rownum = rownum + 1
slttbllist = newslttableinfo[tblhashtmp]
for curtupletmp in slttbllist:
ws.write(rownum, 0, newdbcmpname, newcell_style)
for varcol in range(1, tuplelen + 1):
ws.write(rownum, varcol, curtupletmp[varcol - 1],
newcell_style)
rownum = rownum + 1
excelfile = excelfile + "_" + olddbcmpname + "_" + newdbcmpname + ".xls"
new_workbook.save(excelfile)
def create_styles():
font = xlwt.Font()
font.name = 'Times New Roman'
currency_format = '"$"#,##0.00_);[Red]("$"#,##0.00)'
normal = xlwt.XFStyle()
normal.num_format_str = currency_format
four = xlwt.Pattern()
four.pattern = xlwt.Pattern.SOLID_PATTERN
four.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
style_four = xlwt.XFStyle()
style_four.pattern = four
style_four.font = font
five = xlwt.Pattern()
five.pattern = xlwt.Pattern.SOLID_PATTERN
five.pattern_fore_colour = xlwt.Style.colour_map['light_green']
style_five = xlwt.XFStyle()
style_five.pattern = five
style_five.font = font
style_five.num_format_str = currency_format
six = xlwt.Pattern()
six.pattern = xlwt.Pattern.SOLID_PATTERN
six.pattern_fore_colour = xlwt.Style.colour_map['light_yellow']
style_six = xlwt.XFStyle()
style_six.pattern = six
style_six.font = font
style_six.num_format_str = currency_format
seven = xlwt.Pattern()
seven.pattern = xlwt.Pattern.SOLID_PATTERN
seven.pattern_fore_colour = xlwt.Style.colour_map['rose']
style_seven = xlwt.XFStyle()
style_seven.pattern = seven
style_seven.font = font
style_seven.num_format_str = currency_format
eight = xlwt.Pattern()
eight.pattern = xlwt.Pattern.SOLID_PATTERN
eight.pattern_fore_colour = 22
style_eight = xlwt.XFStyle()
style_eight.pattern = eight
style_eight.font = font
style_eight.num_format_str = currency_format
borders = xlwt.Borders()
borders.left = xlwt.Borders.DOUBLE
borders.right = xlwt.Borders.DOUBLE
borders.top = xlwt.Borders.DOUBLE
borders.bottom = xlwt.Borders.DOUBLE
style_eight.borders = borders
styles = [style_four, style_five, style_six, style_seven, style_eight, normal]
return styles
def define_style():
"""
返回自定义表格标题和内容的格式
:param filename:
:return:
"""
# 背景色
title_pattern = xlwt.Pattern()
title_pattern.pattern = xlwt.Pattern.SOLID_PATTERN # 有背景色
title_pattern.pattern_fore_colour = 3 # 设置背景颜色
# 字体
title_font = xlwt.Font()
title_font.bold = True
title_font.shadow = True
title_font.height = 280 # 200为10号字体
title_font.name = "Times New Roman"
title_font.colour_index = 5 # 字体颜色 0：黑，1：白，2：红，3：绿，4：蓝，5：黄
# 对齐
title_alignment = xlwt.Alignment()
title_alignment.vert = xlwt.Alignment.VERT_CENTER
title_alignment.horz = xlwt.Alignment.HORZ_CENTER
content_pattern = xlwt.Pattern()
content_pattern.pattern_fore_colour = 3
content_font = xlwt.Font()
content_font.height = 200 # 200为10号字体
content_font.family = xlwt.Font.FAMILY_SWISS
content_alignment = xlwt.Alignment()
content_alignment.vert = xlwt.Alignment.VERT_BOTTOM
content_alignment.horz = xlwt.Alignment.HORZ_LEFT
# 边框
border = xlwt.Borders()
border.left = xlwt.Borders.THIN_DASH_DOT_DOTTED
border.right = xlwt.Borders.THIN_DASH_DOT_DOTTED
border.top = xlwt.Borders.THIN_DASH_DOT_DOTTED
border.bottom = xlwt.Borders.THIN_DASH_DOT_DOTTED
title_style = xlwt.XFStyle()
title_style.pattern = title_pattern
title_style.font = title_font
title_style.borders = border
title_style.alignment = title_alignment
content_style = xlwt.XFStyle()
content_style.pattern = content_pattern
content_style.font = content_font
content_style.borders = border
content_style.alignment = content_alignment
return title_style, content_style
def copy_excel(file_path, res_flags, request_urls, responses):
'''''
:param file_path: 测试用例的路径
:param res_flags: 测试结果的list
:param request_urls: 请求报文的list
:param responses: 返回报文的list
:return:
'''
'''''
这个函数的作用是写excel，把请求报文、返回报文和测试结果写到测试用例的excel中
因为xlrd模块只能读excel，不能写，所以用xlutils这个模块，但是python中没有一个模块能
直接操作已经写好的excel，所以只能用xlutils模块中的copy方法，copy一个新的excel，才能操作
'''
# 打开原来的excel，获取到这个book对象
book = xlrd.open_workbook(file_path)
# 复制一个new_book
new_book = copy.copy(book)
# 然后获取到这个复制的excel的第一个sheet页
sheet = new_book.get_sheet(0)
i = 1
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 = 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...
style = xlwt.XFStyle() # Create the Pattern
style.pattern = pattern # Add Pattern to Style
pattern1 = xlwt.Pattern() # Create the Pattern
pattern1.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern1.pattern_fore_colour = 2 # 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...
style1 = xlwt.XFStyle() # Create the Pattern
style1.pattern = pattern1 # Add Pattern to Style
for request_url, response, flag in zip(request_urls, responses, res_flags):
'''''
同时遍历请求报文、返回报文和测试结果这3个大的list
然后把每一条case执行结果写到excel中，zip函数可以将多个list放在一起遍历
因为第一行是表头，所以从第二行开始写，也就是索引位1的位置，i代表行
所以i赋值为1，然后每写一条，然后i+1， i+=1同等于i=i+1
请求报文、返回报文、测试结果分别在excel的11、12、14列，列是固定的，所以就给写死了
后面跟上要写的值，因为excel用的是Unicode字符编码，所以前面带个u表示用Unicode编码
否则会有乱码
'''
sheet.write(i, 11, u'%s' % request_url)
sheet.write(i, 12, u'%s' % response)
if flag == 'pass':
sheet.write(i, 14, u'%s' % flag, style)
else:
sheet.write(i, 14, u'%s' % flag, style1)
i += 1
# 写完之后在当前目录下(可以自己指定一个目录)保存一个以当前时间命名的测试结果，time.strftime()是格式化日期
new_book.save('../../report/%s_测试结果.xls'.decode('utf-8') %
time.strftime('%Y%m%d%H%M%S'))
def write(cls, input, outxlsx_path="测试报告.xls"):
workbook = xlwt.Workbook() # Create workbook
worksheet = workbook.add_sheet('My sheet')
pattern_yellow = xlwt.Pattern() # Create the pattern
pattern_yellow.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern_yellow.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_yellow = xlwt.XFStyle() # Create the pattern
style_yellow.pattern = pattern_yellow # Add pattern to style
pattern_green = xlwt.Pattern()
pattern_green.pattern = xlwt.Pattern.SOLID_PATTERN
pattern_green.pattern_fore_colour = 3
style_Green = xlwt.XFStyle() # Create the pattern
style_Green.pattern = pattern_green # Add pattern to style
pattern_red = xlwt.Pattern()
pattern_red.pattern = xlwt.Pattern.SOLID_PATTERN
pattern_red.pattern_fore_colour = 2
style_Red = xlwt.XFStyle() # Create the pattern
style_Red.pattern = pattern_red # Add pattern to style
clor = {"Pass": style_Green, "Fail": style_Red, "Error": style_yellow}
datas = cls.creatReportXlsx(input)
row_count = len(datas)
for row in range(0, row_count):
col_count = len(datas[row])
for col in range(0, col_count):
if 4 <= col <= 7: # 设置表头单元格的格式
if datas[row][-4] in clor.keys():
worksheet.write(row, col, datas[row][col],
clor[datas[row][-4]])
else:
worksheet.write(
row,
col,
datas[row][col],
)
else: # 表头下面的数据格式
worksheet.write(
row,
col,
datas[row][col],
)
# worksheet.write(2, 5, 'Cell contents', style_Red)
r'D:\Script\BI6.0.6_BI_PORTAL_003_version_6_综合\report\测试告.xls'
outxlsx_path = r"%s\report\%s" % (backage, outxlsx_path)
workbook.save(outxlsx_path)
def yangshique(me):
if me == 'pass':
style = yangshi1()
Pattern = xlwt.Pattern()
Pattern.pattern = xlwt.Pattern.SOLID_PATTERN
Pattern.pattern_fore_colour = xlwt.Style.colour_map['green']
style.pattern = Pattern
else:
style = yangshi2()
Pattern = xlwt.Pattern()
Pattern.pattern = xlwt.Pattern.SOLID_PATTERN
Pattern.pattern_fore_colour = xlwt.Style.colour_map['red']
style.pattern = Pattern
return style
def style4(m):#测试结果的样式设置
if m=="pass":
sty4=style1()
Pattern=xlwt.Pattern()
Pattern.pattern=xlwt.Pattern.SOLID_PATTERN
Pattern.pattern_fore_colour=xlwt.Style.colour_map['green']
sty4.pattern=Pattern
else:
sty4=style2()
Pattern=xlwt.Pattern()
Pattern.pattern=xlwt.Pattern.SOLID_PATTERN
Pattern.pattern_fore_colour=xlwt.Style.colour_map['red']
sty4.pattern=Pattern
return sty4
def defaultStyle(me):
if me == 'pass':
style = Style1()
Pattern = xlwt.Pattern()
Pattern.pattern = xlwt.Pattern.SOLID_PATTERN
Pattern.pattern_fore_colour = xlwt.Style.colour_map['green']
style.pattern = Pattern
else:
style = Style2()
Pattern = xlwt.Pattern()
Pattern.pattern = xlwt.Pattern.SOLID_PATTERN
Pattern.pattern_fore_colour = xlwt.Style.colour_map['red']
style.pattern = Pattern
return style
def _on_save_test_category_output(self, categoryTable, mainWidget):
filename = unicode(QtGui.QFileDialog.getSaveFileName(self, 'Save File', '', ".xls(*.xls)"))
wbk = xlwt.Workbook()
sheet = wbk.add_sheet("sheet", cell_overwrite_ok=True)
for currentColumn in range(categoryTable.cols):
for currentRow in range(categoryTable.rows):
try:
teext = u"{0}".format(categoryTable.item(currentRow, currentColumn).text())
teext.strip()
if teext == "YELLOW":
style = xlwt.XFStyle()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['light_yellow']
style.pattern = pattern
sheet.write(currentRow, currentColumn, teext, style)
elif teext == "RED":
style = xlwt.XFStyle()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['red']
style.pattern = pattern
sheet.write(currentRow, currentColumn, teext, style)
elif teext == "GREEN":
style = xlwt.XFStyle()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['green']
style.pattern = pattern
sheet.write(currentRow, currentColumn, teext, style)
elif teext == "ORANGE":
style = xlwt.XFStyle()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['orange']
style.pattern = pattern
sheet.write(currentRow, currentColumn, teext, style)
elif teext == "ORANGE_LIGHT":
style = xlwt.XFStyle()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map['gold']
style.pattern = pattern
sheet.write(currentRow, currentColumn, teext, style)
else:
sheet.write(currentRow, currentColumn, teext)
except AttributeError:
pass
wbk.save(filename+".xls")
def get(self,request):
# serializer = StaffSerializer(self.get_queryset(), many=True)
# df = pd.DataFrame(data=serializer.data)
rjson = serialize('json', self.get_queryset(), use_natural_foreign_keys=True)
print(rjson)
r = [dict( **{'id': i['pk']},**i['fields']) for i in json.loads(rjson)]
# r = [dict(i['fields']) for i in json.loads(rjson)]
import xlwt
from datetime import datetime
# style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',
# num_format_str='#,##0.00')
# style1 = xlwt.easyxf(num_format_str='D-MMM-YY')
style2 = xlwt.easyxf('font: color-index red, bold on,height 200')
pattern=xlwt.Pattern()
pattern.pattern=xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 5
style3=xlwt.XFStyle()
font=xlwt.Font()
font.colour_index = 4
font.bold = True
font.shadow = True
style3.font=font
wb = xlwt.Workbook()
ws = wb.add_sheet('Sheet1')
title=[i.verbose_name for i in self.model._meta.get_fields() if i.concrete]
values=[i.values() for i in r]
print(values)
ws.panes_frozen = True
ws.horz_split_pos = 1
ws.horzi_split_fiHst_visibl = 3
for i,j in enumerate(title):
ws.write(0,i,j,style2)
ws.col(i).width= 5000
for i,j in enumerate(values):
for m,n in enumerate(j):
if i %2 == 0 :
style3.pattern = pattern
else:
style3.pattern = xlwt.Pattern()
ws.write(i+1,m,n,style3)
# f=StringIO()
# wb.save(f)
response=HttpResponse(content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename="somefilename.xls"'
wb.save(response)
return response
def get_excel_style(): # bold=True
style_center = xlwt.XFStyle()
alignment_center = xlwt.Alignment()
alignment_center.horz = xlwt.Alignment.HORZ_CENTER # 水平居中
alignment_center.vert = xlwt.Alignment.VERT_CENTER # 垂直居中
style_center.alignment = alignment_center
style_bold = xlwt.XFStyle()
font_bold = xlwt.Font()
font_bold.name = u'宋体'
font_bold.bold = True
style_bold.alignment = alignment_center
style_bold.font = font_bold
style_left = xlwt.XFStyle()
alignment_left = xlwt.Alignment()
alignment_left.horz = xlwt.Alignment.HORZ_LEFT
style_left.alignment = alignment_left
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 5
style_yellow = xlwt.XFStyle()
style_yellow.pattern = pattern
style_yellow.alignment = alignment_center
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 3
style_green = xlwt.XFStyle()
style_green.pattern = pattern
style_green.alignment = alignment_center
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 2
style_red = xlwt.XFStyle()
style_red.pattern = pattern
style_red.alignment = alignment_center
return {
'yellow': style_yellow,
'left': style_left,
'center': style_center,
'green': style_green,
'red': style_red,
'bold': style_bold
}
def data_process(filename1, filename2):
#建立写入文件
file = xlwt.Workbook()
sheet = file.add_sheet('result', cell_overwrite_ok=True)
#读取预处理过的文件
data = xlrd.open_workbook(filename1)
table = data.sheet_by_index(0)
cols, rows = table.ncols, table.nrows
time = [['09:10:10', '10:00:00'], ['11:00:00', '11:25:00'],
['14:10:00', '16:00:00'], ['17:00:00', '17:25:00'],
['19:00:00', '20:30:00'], ['20:00:00', '21:25:00']]
#黄色样式
yellow = xlwt.XFStyle()
pattern1 = xlwt.Pattern()
pattern1.pattern = 1
pattern1.pattern_fore_colour = 5 # 黄色
yellow.pattern = pattern1
#红色样式
red = xlwt.XFStyle()
pattern2 = xlwt.Pattern()
pattern2.pattern = 1
pattern2.pattern_fore_colour = 2 # 红色
red.pattern = pattern2
#先将姓名、打卡时间、标号写入新表格
for col in range(2, 5):
for row in range(rows):
value = table.cell(row, col).value
sheet.write(row, col - 2, value)
#边写入打卡时间，边标记迟到、缺勤
for col in range(data_begin, data_end + 1):
index = col % 5
if col == data_end:
index = 5
area = time[index]
for row in range(rows):
value = table.cell(row, col).value.encode('utf-8')
if value:
#迟到，黄色
if ((area[0] < value) & (value < area[1])):
sheet.write(row, col - 2, value, yellow)
else:
sheet.write(row, col - 2, value)
#没有打卡，红色
else:
sheet.write(row, col - 2, value, red)
#file1.save('result.xls')
file.save(filename2)
def style(self, boder):
style = xlwt.XFStyle() # style
font = xlwt.Font()
font.name = 'Times New Roman'
font.height = 200
font.bold = False
font.colour_index = 32767
borders = xlwt.Borders()
borders.bottom = xlwt.Borders.NO_LINE
borders.left = xlwt.Borders.NO_LINE # 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
borders.right = xlwt.Borders.NO_LINE
borders.top = xlwt.Borders.NO_LINE
style.font = font
style.borders = borders
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
fore_cor_name = 'white'
pattern.pattern_fore_colour = xlwt.Style.colour_map[fore_cor_name]
style.pattern = pattern
return style
def set_row_styles(p_fontsize,p_color):
cell_borders = xlwt.Borders()
cell_styles = xlwt.XFStyle()
# add font
font = xlwt.Font()
font.name = u'微软雅黑'
font.bold = True
font.size = p_fontsize
cell_styles.font = font
#add col style
cell_borders.left = xlwt.Borders.THIN
cell_borders.right = xlwt.Borders.THIN
cell_borders.top = xlwt.Borders.THIN
cell_borders.bottom = xlwt.Borders.THIN
row_pattern = xlwt.Pattern()
row_pattern.pattern = xlwt.Pattern.SOLID_PATTERN
row_pattern.pattern_fore_colour = p_color
# add alignment
cell_alignment = xlwt.Alignment()
cell_alignment.horz = xlwt.Alignment.HORZ_LEFT
cell_alignment.vert = xlwt.Alignment.VERT_CENTER
cell_styles.alignment = cell_alignment
cell_styles.borders = cell_borders
cell_styles.pattern = row_pattern
cell_styles.font = font
return cell_styles
def set_xlwt_style(color):
stylei = xlwt.XFStyle()
#set color
patterni = xlwt.Pattern()
patterni.pattern = 1
patterni.pattern_fore_colour = color
#color = 0 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, the list goes on...
patterni.pattern_back_colour = 35
#alignment
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_LEFT # 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
#border
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......
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
stylei.pattern = patterni #
stylei.alignment = alignment # Add Alignment to Style
stylei.borders = borders # Add Borders to Style
return stylei
def setup_sheet_title(worksheet):
style = xlwt.XFStyle()
# 设置边框
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
style.borders = borders
# 设置对齐方式
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
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 = 5
style.pattern = pattern
# 设置标题
worksheet.write_merge(0, 1, 0, 0, 'type', style)
worksheet.write_merge(0, 1, 1, 1, 'corpus', style)
worksheet.write_merge(0, 1, 2, 2, 'score', style)
def set_header_styles(p_fontsize,p_color):
header_borders = xlwt.Borders()
header_styles = xlwt.XFStyle()
# add table header style
header_borders.left = xlwt.Borders.THIN
header_borders.right = xlwt.Borders.THIN
header_borders.top = xlwt.Borders.THIN
header_borders.bottom = xlwt.Borders.THIN
header_styles.borders = header_borders
header_pattern = xlwt.Pattern()
header_pattern.pattern = xlwt.Pattern.SOLID_PATTERN
# 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
header_pattern.pattern_fore_colour = p_color
# add font
font = xlwt.Font()
font.name = u'微软雅黑'
font.bold = True
font.size = p_fontsize
header_styles.font = font
#add alignment
header_alignment = xlwt.Alignment()
header_alignment.horz = xlwt.Alignment.HORZ_CENTER
header_alignment.vert = xlwt.Alignment.VERT_CENTER
header_styles.alignment = header_alignment
header_styles.borders = header_borders
header_styles.pattern = header_pattern
return header_styles
def runud(is_delta):
sname = '-full' if not is_delta else '-delta'
for si in range(len(scores)):
ws = wb.add_sheet(scores[si] + sname)
for i, header in enumerate(headers):
ws.write(0, i, header)
ri = 1
for tb in sorted(result_info):
ws.write(ri, 0, tb)
train_result, test_result = result_info[tb]
# get our result
our_res = None
udpipe_style = None
if type(test_result) is str or type(train_result) is str:
if type(test_result) is not str:
pred_scores = test_result[si]
our_res = pred_scores[1] + " (" + pred_scores[
2] + "," + pred_scores[
3] + ",-1)" if not is_delta else pred_scores[2]
else:
our_res = None
if type(train_result) is str or len(train_result) == 2:
our_res = test_result
else:
assert (len(train_result) == 3)
if not is_delta:
our_res = train_result[
-1] + " (-1,-1," + train_result[1] + ")"
else:
our_res = train_result[-1]
else:
pred_scores = test_result[si]
train_time = train_result[1]
our_res = pred_scores[1] + " (" + pred_scores[
2] + "," + pred_scores[
3] + "," + train_time + ")" if not is_delta else pred_scores[
2]
if float(pred_scores[0]) > float(pred_scores[1]):
udpipe_style = xlwt.XFStyle()
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = xlwt.Style.colour_map[
'yellow']
udpipe_style.pattern = pattern
ws.write(ri, 2, our_res)
# get udpipe result
udpipe_res = None
if type(test_result) is str:
udpipe_res = test_result
else:
pred_scores = test_result[si]
udpipe_res = pred_scores[0]
if udpipe_style != None:
ws.write(ri, 1, udpipe_res, udpipe_style)
else:
ws.write(ri, 1, udpipe_res)
ri = ri + 1
def style2():
# 初始化样式(创建样式对象)
style = xlwt.XFStyle()
# 创建字体属性对象
font = xlwt.Font()
font.name = "宋体"
font.bold = False
font.height = 20*14
style.font = font
# 创建边界属性对象
borders = xlwt.Borders()
borders.top = 1
borders.bottom = 1
borders.left = 1
borders.right = 1
style.borders = borders
# 创建对齐属性对象
alignment = xlwt.Alignment()
alignment.vert = xlwt.Alignment.VERT_CENTER
alignment.horz = xlwt.Alignment.HORZ_CENTER
style.alignment = alignment
# 创建模式属性对象
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 1
style.pattern = pattern
# 调用风格样式
return style
def init_style(self, name, height, blod=False, bg_color=1, font_color=0):
"""
定义excel的样式
:return:
"""
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = name # 'Times New Roman'
font.bold = blod
font.color_index = font_color
font.height = height
borders = xlwt.Borders() # 为样式创建边框
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
pattern = xlwt.Pattern() # 为样式创建背景颜色
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = bg_color
al = xlwt.Alignment() # 为样式创建对其方式
al.horz = 0x02 # 设置水平居中
al.vert = 0x01 # 设置垂直居中
style.alignment = al
style.font = font
style.borders = borders
style.pattern = pattern
return style
def __styles(self):
header_pattern = xlwt.Pattern()
header_pattern.pattern = xlwt.Pattern.SOLID_PATTERN
header_pattern.pattern_fore_colour = xlwt.Style.colour_map['ocean_blue']
passed_font = xlwt.Font()
passed_font.colour_index = xlwt.Style.colour_map['black']
self.passed_style = xlwt.XFStyle()
self.passed_style.font = passed_font
failed_font = xlwt.Font()
failed_font.bold = True
failed_font.colour_index = xlwt.Style.colour_map['red']
self.failed_style = xlwt.XFStyle()
self.failed_style.font = failed_font
header_font = xlwt.Font()
header_font.bold = True
header_font.height = 260
header_font.italic = True
header_font.colour_index = xlwt.Style.colour_map['white']
title_font = xlwt.Font()
title_font.bold = True
title_font.height = 220
title_font.italic = True
self.header_style = xlwt.XFStyle()
self.header_style.font = header_font
self.header_style.pattern = header_pattern
self.title_style = xlwt.XFStyle()
self.title_style.font = title_font
def styel(self, colourtype):
self.colour_style = xlwt.XFStyle()
pattern_clour = xlwt.Pattern() # 创建pattern_red
pattern_clour.pattern = xlwt.Pattern.SOLID_PATTERN # 设置填充模式为全部填充
pattern_clour.pattern_fore_colour = int(colourtype) # 设置填充颜色为
self.colour_style.pattern = pattern_clour
return self.colour_style
def modifyExcel(self,filename,row,col,result):
"""
修改Excel内容--->添加测试执行结果
:param filename: Excel文件名
:return:
"""
with xlrd.open_workbook(self.dir_base(filename)) as f:
work_book=copy(f)#利用xlutils.copy下的copy函数复制
work_sheet=work_book.get_sheet(0)#获取第一个表单
style0 = xlwt.easyxf('font: name Times New Roman',
num_format_str='#,##0.00', ) # 字体的颜色
styleOK = xlwt.easyxf('pattern: fore_colour light_blue;'
'font: colour green, bold True;')
pattern = xlwt.Pattern() # 一个实例化的样式类
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # 固定的样式
if result == 'P':
pattern.pattern_fore_colour = xlwt.Style.colour_map['red'] # 背景颜色
if result == 'F':
pattern.pattern_fore_colour = xlwt.Style.colour_map['green'] # 背景颜色
styleOK.pattern = pattern
work_sheet.write(row, col, result, styleOK) # 使用样式
work_book.save(self.dir_base(filename))
def map_init(rows, cols):
# 生成表头
for i in range(rows):
alignment = xlwt.Alignment()
alignment.horz = 2 # 设置水平位置，1是左对齐，2是居中，3是右对齐
style.alignment = alignment
font = xlwt.Font() # 为样式创建字体
font.bold = True # 加粗
style.font = font # 定义格式-字体
worksheet.write(rows - i, 0, i + 1, style)
for i in range(cols):
# 背景颜色设置
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 7 # 给背景颜色赋值，2是红色，3是绿色
style.pattern = pattern # 把背景颜色加到表格样式里去
# 单元格位置设置
alignment = xlwt.Alignment()
alignment.horz = 2 # 设置水平位置，1是左对齐，2是居中，3是右对齐
style.alignment = alignment
# 为样式创建字体格式
font = xlwt.Font()
font.bold = True # 加粗
style.font = font # 定义格式-字体
worksheet.write(0, i + 1, i + 1, style)
def execl_style(self):
borders = xlwt.Borders() # Create Borders
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 5
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
font = xlwt.Font()
font.bold = True
font.name = 'Times New Roman'
self.style.borders = borders
self.style.pattern = pattern
self.style.alignment = alignment
self.style.font = font
self.style1.borders = borders
self.style1.alignment = alignment
self.style2.borders = borders
self.style2.alignment = alignment
self.style2.num_format_str = 'yyyy/mm/dd'
def set_common_style(name, bold, color_index, height, pattern_fore_colour):
"""单元格样式"""
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = name
font.bold = bold
font.color_index = color_index
font.height = height
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 # 水平对齐
alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT # 自动换行
style.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
"""
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...
"""
pattern.pattern_fore_colour = pattern_fore_colour
style.pattern = pattern
return style
def style(fontname, height, highlight, bold=False):
xlstyle = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = fontname # 'Times New Roman'
font.bold = bold
font.colour_index = 4
font.height = height
xlstyle.font = font
#print(xlwt.Style.colour_map)
if highlight:
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 = 2 # 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...
xlstyle.pattern = pattern # Add Pattern to Style
# borders= xlwt.Borders()
# borders.left= 6
# borders.right= 6
# borders.top= 6
# borders.bottom= 6
# xlstyle.borders = borders
return xlstyle
def get_header_style(self):
"""style of header"""
style = xlwt.XFStyle()
style.pattern = xlwt.Pattern()
style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
style.pattern.pattern_fore_colour = 22
return style
