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