Пример #1
0
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]
Пример #2
0
 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
Пример #3
0
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
Пример #5
0
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
Пример #6
0
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'))
Пример #7
0
    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)
Пример #8
0
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
Пример #9
0
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
Пример #10
0
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
Пример #11
0
	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")
Пример #12
0
    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
Пример #13
0
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
    }
Пример #14
0
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)
Пример #15
0
    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
Пример #16
0
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
Пример #17
0
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
Пример #18
0
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)
Пример #19
0
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
Пример #20
0
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
Пример #21
0
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
Пример #22
0
 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
Пример #23
0
    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
Пример #24
0
 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
Пример #25
0
    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))
Пример #26
0
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)
Пример #27
0
 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'
Пример #28
0
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
Пример #29
0
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
Пример #30
0
 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