Python xlwt.Font類代碼示例

def defineFontStyle(argFontName,argIsBold):
    style = XFStyle()
    font = Font()
    font.name = argFontName
    font.bold = argIsBold
    style.font = font
    return style

def get_styles():
	style = easyxf(
		'font: name Arial;'
	)
	style_s = easyxf(
		'font: name Arial;'
		'borders: left thin, right thin, top medium, bottom thin;'
	)
	style_m = easyxf(
		'font: name Arial, bold True;'
		'borders: left thin, right thin, top thin, bottom thin;'
	)
	font = Font()
	font.bold = 1
	font.height = 320
	style.font = font
	style.alignment.horz = Alignment.HORZ_LEFT
	style.alignment.vert = Alignment.VERT_CENTER
	style_s.font.height = 200
	style_s.font.bold = 1
	style_s.alignment.horz = Alignment.HORZ_CENTER
	style_s.alignment.vert = Alignment.VERT_CENTER
	style_s.alignment.wrap = True
	
	return style, style_s, style_m

def getDefualtStyle():
	fnt = Font()
	fnt.name = 'Arial'
	
	borders = Borders()
	borders.left = Borders.THIN
	borders.right = Borders.THIN
	borders.top = Borders.THIN
	borders.bottom = Borders.THIN
	
	#pattern = Pattern()
	#pattern.pattern = Style.pattern_map['solid']
	###pattern.pattern_back_colour = 0xBFBFBF
	#pattern.pattern_fore_colour = 0x37
	
	
	alignment = Alignment()
	#alignment.horizontal = Alignment.HORZ_LEFT 
	alignment.horizontal = Alignment.HORZ_RIGHT
	
	
	style = XFStyle()
	#~ style.num_format_str='0.000%'
	#~ style.num_format_str='0+'
	#~ style.font = fnt
	style.align = alignment
	#style.borders = borders
	#~ style.pattern = pattern
	
	return style

    def xls_headers(self, text, title):
        u"""

        """
        # headers style
        header1 = easyxf()
        header2 = easyxf()
            # font
        font1 = Font()
        font1.name = 'Times New Roman'
        font1.bold = True
            # alignment
        alig1 = Alignment()
        alig1.horz = 2
            # assign
        header1.font = font1
        header1.alignment = alig1
        header2.font = font1

        # headers 1st sheet
        self.feuy1.write_merge(0, 0, 0, 1,  text.get('titre') +
                                            title,
                                            header1)
        self.feuy1.write(1, 0, text.get('nomfic'), header2)
        self.feuy1.write(2, 0, text.get('mtcthem'), header2)
        self.feuy1.write(3, 0, text.get('mtcgeo'), header2)
        self.feuy1.write(4, 0, text.get('description'), header2)
        self.feuy1.write(5, 0, text.get('cadre'), header2)
        self.feuy1.write(6, 0, text.get('num_objets'), header2)
        self.feuy1.write(7, 0, text.get('num_attrib'), header2)
        self.feuy1.write(8, 0, text.get('date_crea'), header2)
        self.feuy1.write(9, 0, text.get('date_actu'), header2)
        self.feuy1.write(10, 0, text.get('source'), header2)
        self.feuy1.write(11, 0, text.get('diffusion'), header2)
        self.feuy1.write(12, 0, text.get('responsable'), header2)
        self.feuy1.write(13, 0, text.get('ptcontact'), header2)
        self.feuy1.write(14, 0, text.get('siteweb'), header2)
        self.feuy1.write(15, 0, text.get('geometrie'), header2)
        self.feuy1.write(16, 0, text.get('echelle'), header2)
        self.feuy1.write(17, 0, text.get('precision'), header2)
        self.feuy1.write(18, 0, text.get('srs'), header2)
        self.feuy1.write(19, 0, text.get('emprise'), header2)
        # headers 2nd sheet
        self.feuy2.write(0, 0,  text.get('numero'), header2)
        self.feuy2.write(0, 1,  text.get('nom'), header2)
        self.feuy2.write(0, 2,  text.get('type'), header2)
        self.feuy2.write(0, 3,  text.get('longueur'), header2)
        self.feuy2.write(0, 4,  text.get('precision'), header2)
        self.feuy2.write(0, 5,  text.get('description'), header2)
        self.feuy2.write(0, 6,  text.get('somme'), header2)
        self.feuy2.write(0, 7,  text.get('moyenne'), header2)
        self.feuy2.write(0, 8,  text.get('mediane'), header2)
        self.feuy2.write(0, 9,  text.get('min'), header2)
        self.feuy2.write(0, 10,  text.get('max'), header2)
        self.feuy2.write(0, 11,  text.get('ecartype'), header2)

        # End of function
        return self.feuy1, self.feuy2

    def setupFormat(self):
        headFont = Font()
        headFont.bold = True
        alignmentSetup = Alignment()
        alignmentSetup.wrap = True
        borders = Borders()
        borders.left = 1
        borders.right = 1
        borders.top = 1
        borders.bottom = 1

        self.header_style = XFStyle()
        self.header_style.font = headFont

        self.table_style = XFStyle()
        self.table_style.borders = borders
        self.table_style.alignment = alignmentSetup

def estiloCeldasSeparadas():
    fnt = Font()
    fnt.bold=True
    #fnt.colour_index=2
    borders = Borders()
    borders.left = Borders.MEDIUM
    borders.right = Borders.MEDIUM
    borders.top = Borders.MEDIUM
    borders.bottom = Borders.MEDIUM
    pattern = Pattern()
    pattern.pattern = Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = 0x18#0x99
    style = XFStyle()   
    style.num_format_str='YYYY-MM-DD'
    style.font = fnt
    style.borders = borders
    style.pattern = pattern
    return style

 def __init__(self):
     self.style = XFStyle()
     base_font = Font()  # 設置基本字體
     base_font.height = FONT_SIZE_UNIT * 12
     self.style.font = base_font
     alignment = Alignment()  # 設置對齊
     alignment.horz = Alignment.HORZ_CENTER
     alignment.vert = Alignment.VERT_CENTER
     self.style.alignment = alignment
     borders = Borders()  # 設置邊框
     borders.left = Borders.THIN
     borders.right = Borders.THIN
     borders.top = Borders.THIN
     borders.bottom = Borders.THIN
     borders.left_colour = COLOUR_BLACK
     borders.right_colour = COLOUR_BLACK
     borders.top_colour = COLOUR_BLACK
     borders.bottom_colour = COLOUR_BLACK
     self.style.borders = borders
     self.style.num_format_str = '#,##0'  # 設置數字格式

def queryExcel(filename, dategroup, response):
    filename += u'.xls'
    response['Content-Disposition'] = (u'attachment;filename=%s' % filename).encode('utf-8')
    import xlwt
    from xlwt import Font, Alignment

    style1 = xlwt.XFStyle()
    font1 = Font()
    font1.height = 360
    font1.name = u'仿宋'
    style1.font = font1
    algn = Alignment()
    algn.horz = Alignment.HORZ_LEFT
    style1.alignment = algn
    style1.font = font1
    style0 = xlwt.XFStyle()
    algn0 = Alignment()
    algn0.horz = Alignment.HORZ_CENTER
    font = Font()
    font.height = 320
    font.bold = False
    font.name = u'仿宋'
    style0.alignment = algn0
    style0.font = font
    wb = xlwt.Workbook()
    ws = wb.add_sheet(u"銷售報表", cell_overwrite_ok=True)
    rownum = 0
    ws.write_merge(rownum, rownum, 0, 0, u'序號', style0)
    ws.write_merge(rownum, rownum, 1, 1, u'品牌', style0)
    ws.write_merge(rownum, rownum, 2, 2, u'型號', style0)
    ws.write_merge(rownum, rownum, 3, 3, u'類型', style0)
    ws.write_merge(rownum, rownum, 4, 4, u'數量', style0)
    ws.write_merge(rownum, rownum, 5, 5, u'賬戶', style0)
    ws.write_merge(rownum, rownum, 6, 6, u'姓名', style0)
    ws.write_merge(rownum, rownum, 7, 7, u'主管', style0)
    rownum += 1
    datanum = 1
    for data in dategroup:
        ws.write_merge(rownum, rownum, 0, 7, u'日期：%s   廳台：%s   總計：%s 台' % (data['date'], data['officename'],data['totalnum']), style1)
        rownum += 1
        for i, row in enumerate(data['query']):
            ws.write_merge(rownum, rownum, 0, 0, datanum, style0)
            ws.write_merge(rownum, rownum, 1, 1, row['productbrandsname'], style0)
            ws.write_merge(rownum, rownum, 2, 2, row['productname'], style0)
            ws.write_merge(rownum, rownum, 3, 3, row['ordertypename'], style0)
            ws.write_merge(rownum, rownum, 4, 4, row['ordernum'], style0)
            ws.write_merge(rownum, rownum, 5, 5, row['username'], style0)
            ws.write_merge(rownum, rownum, 6, 6, row['get_full_name'], style0)
            ws.write_merge(rownum, rownum, 7, 7, row['managername'], style0)
            datanum += 1
            rownum += 1
    for i in range(8):
        ws.col(i).width = 256 * 20
    wb.save(response)

def creaHojaDeTraduccion(excel, nombreHoja="traduccion"):
    hoja=excel.creaHoja(nombreHoja)
    hoja.col(0).width=90*256
    hoja.col(1).width=20*256
    fnt = Font()
    fnt.bold=True
    #fnt.colour_index=2
    borders = Borders()
    borders.left = Borders.MEDIUM
    borders.right = Borders.MEDIUM
    borders.top = Borders.MEDIUM
    borders.bottom = Borders.MEDIUM
    pattern = Pattern()
    pattern.pattern = Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = 0x18#0x99
    style = XFStyle()   
    style.num_format_str='YYYY-MM-DD'
    style.font = fnt
    style.borders = borders
    style.pattern = pattern
    hoja.write(0,0,"Original Text",style)
    hoja.write(0,1,"Translation",style)
    return hoja

    def export(match_list, file_path):
        wb = xlwt.Workbook()
        ws = wb.add_sheet('Ranking')

        # writing header
        header = ['Ranking',
                  'Name',
                  'Rating',
                  'Reviews',
                  'Price',
                  'Cuisines',
                  'Edenred Name',
                  'Match',
                  'Edenred Address']
        font = Font()
        font.name = 'Arial'
        font.bold = True
        style = XFStyle()
        style.font = font
        for index in range(len(header)):
            ws.write(0, index, header[index], style)

        # writing restaurants
        for index in range(len(match_list)):
            match = match_list[index]
            ws.write(index+1, 0, int(match.restaurant1.ranking))
            ws.write(index+1, 1, xlwt.Formula('HYPERLINK("%s"; "%s")' % (match.restaurant1.url, match.restaurant1.name)))
            ws.write(index+1, 2, float(match.restaurant1.rating.replace(',', '.')))
            ws.write(index+1, 3, int(match.restaurant1.review_count))
            ws.write(index+1, 4, match.restaurant1.price)
            ws.write(index+1, 5, ', '.join(match.restaurant1.cuisines))
            ws.write(index+1, 6, match.restaurant2.name)
            ws.write(index+1, 7, int(100.0 * match.score))
            address = match.restaurant2.address + ', ' + match.restaurant2.postcode
            map_link = 'https://www.google.com/maps?f=q&source=s_q&hl=es&q=%s' % address
            ws.write(index+1, 8, xlwt.Formula('HYPERLINK("%s"; "%s")' % (map_link, address)))
        wb.save(file_path)

def queryExcel(filename, dategroup, response):
    '''
    導出excel
    '''
    filename += u'.xls'
    response['Content-Disposition'] = (u'attachment;filename=%s' % filename).encode('utf-8')
    import xlwt
    from xlwt import Font, Alignment

    style1 = xlwt.XFStyle()
    font1 = Font()
    font1.height = 260
    font1.name = u'仿宋'
    style1.font = font1
    algn = Alignment()
    algn.horz = Alignment.HORZ_LEFT
    style1.alignment = algn
    style1.font = font1
    style0 = xlwt.XFStyle()
    algn0 = Alignment()
    algn0.horz = Alignment.HORZ_CENTER
    font = Font()
    font.height = 220
    font.bold = False
    font.name = u'仿宋'
    style0.alignment = algn0
    style0.font = font
    wb = xlwt.Workbook()
    ws = wb.add_sheet(u"考試報表", cell_overwrite_ok=True)
    rownum = 0
    ws.write_merge(rownum, rownum, 0, 0, u'序號', style0)
    ws.write_merge(rownum, rownum, 1, 1, u'員工ID', style0)
    ws.write_merge(rownum, rownum, 2, 2, u'姓名', style0)
    ws.write_merge(rownum, rownum, 3, 3, u'得分', style0)
    ws.write_merge(rownum, rownum, 4, 4, u'主管', style0)
    rownum += 1
    datanum = 1
    for data in dategroup:
        ws.write_merge(rownum, rownum, 0, 4, u'考試：%s  參考人數：%s  曠考人數：%s' % (data['name'],data['totalnum'],data['untotalnum']), style1)
        rownum += 1
        for i, row in enumerate(data['query']):
            ws.write_merge(rownum, rownum, 0, 0, datanum, style0)
            ws.write_merge(rownum, rownum, 1, 1, row['username'], style0)
            ws.write_merge(rownum, rownum, 2, 2, row['get_full_name'], style0)
            ws.write_merge(rownum, rownum, 3, 3, row['score'], style0)
            ws.write_merge(rownum, rownum, 4, 4, row['managername'], style0)

            datanum += 1
            rownum += 1
    for i in range(5):
        ws.col(i).width = 256 * 10
    wb.save(response)

def output(file_or_stream, sheetname, headers, values, encoding = 'utf8', footer_text = None, footer_link = None):
	import xlwt

	book = xlwt.Workbook(encoding)
	sh = book.add_sheet(sheetname)

	datestyle = XFStyle()
	datestyle.num_format_str = 'DD/MM/YYYY'

	timestyle = XFStyle()
	timestyle.num_format_str = 'HH:MM:SS'

	header_font = Font()
	header_font.bold = True

	al = Alignment()
	
	al.horz = Alignment.HORZ_CENTER
	header_style = XFStyle()
	header_style.font = header_font
	header_style.alignment = al

	for i,header in enumerate(headers):
		sh.write(0, i, header, header_style)

	sh.set_panes_frozen(True) # frozen headings instead of split panes
	sh.set_horz_split_pos(1) # in general, freeze after last heading row
	sh.set_remove_splits(True) # if user does unfreeze, don't leave a split there

	for j, row in enumerate(values):
		for i, value in enumerate(row):
			if value.__class__ == date:
				sh.write(j+1, i, value, datestyle)
			elif value.__class__ == time:
				sh.write(j+1, i, value, timestyle)
			else:
				sh.write(j+1, i, value)

	if footer_link and footer_text:
		link_font = Font()
		link_font.name = 'Verdana'
		link_font.colour_index = 4
		link_font.height = 20*8

		al = Alignment()
		al.horz = Alignment.HORZ_CENTER
		al.vert = Alignment.VERT_BOTTOM

		link_style = XFStyle()
		link_style.font = link_font
		link_style.alignment = al
		row = len(values) + 1
		sh.write_merge(row, row, 0, len(headers)-1, Formula('HYPERLINK("' + footer_link + '";"' + footer_text + '")'), link_style)

	book.save(file_or_stream)

def CreateExcelSpreadsheet(table, output_excel, use_alias=True):
    """Exports table to excel

    Required:
        table -- input table
        output_excel -- output excel table (.xlsx, .xls)

    Optional:
        use_alias -- use field alias name for column headers. Default is True
    """
    # build field dict
    fieldNames = [(f.name, f.aliasName) for f in arcpy.ListFields(table) if f.type != 'Geometry']
    fields = [f[1] if use_alias in ('true', True) else f[0] for f in fieldNames]
    widths = {i: arial10.fitwidth(f) + 1024 for i,f in enumerate(fields)}

    # get field values  *Changed from type dict to list
    with arcpy.da.SearchCursor(table, [f[0] for f in fieldNames]) as rows:
        values = [r for r in rows]

    # Create spreadsheet
    wb = Workbook()
    ws = wb.add_sheet('Sheet 1')
    cols = len(fields)
    rows = len(values) + 1

    # set styles
    #***************************************************************************
    borders = Borders()
    borders.left = Borders.THIN
    borders.right = Borders.THIN
    borders.top = Borders.THIN
    borders.bottom = Borders.THIN

    style = XFStyle()
    style.borders = borders

    # headers
    fntHeaders = Font()
    fntHeaders.bold = True
    fntHeaders.height = 220

    styleHeaders = XFStyle()
    styleHeaders.font = fntHeaders
    styleHeaders.borders = borders

    # for date fields
    styleDate = XFStyle()
    styleDate.borders = borders
    styleDate.num_format_str = 'MM/DD/YYYY'
    #***************************************************************************

    # write headers and freeze panes
    for ci,field in enumerate(fields):
        ws.write(0, ci, field, styleHeaders)

    # freeze headers
    ws.set_panes_frozen(True)
    ws.set_horz_split_pos(1)

    # fill in values
    start = 1
    for vals in values:
        for i, value in enumerate(vals):
            ws.write(start, i, value, styleDate if isinstance(value, datetime.datetime) else style)
            v_width = arial10.fitwidth(str(value).strip())
            if v_width > widths[i]:
                widths[i] = v_width
        start += 1

        if not start % 1000:
            ws.flush_row_data()

    # autofit column widths
    for ci,width in widths.iteritems():
        ws.col(ci).width = int(width + 256) # just a little more padding

    # save workbook
    wb.save(output_excel)
    del wb
    out_url = '/'.join([PATH_URL, os.path.basename(out_file)])
    arcpy.SetParameter(2, out_url)
    arcpy.AddMessage(out_url)
    return out_url

        unicode(localtime()[1]) + u"-" +\
        unicode(localtime()[2])    # date du jour

###################################
######### Fichier Excel ###########
###################################
# configuration du fichier excel de sortie
book = Workbook(encoding = 'utf8')
feuy1 = book.add_sheet(u'Dossiers', cell_overwrite_ok=True)
feuy2 = book.add_sheet(u'Shapes', cell_overwrite_ok=True)
feuy3 = book.add_sheet(u'Champs', cell_overwrite_ok=True)
feuy4 = book.add_sheet(u'MXD', cell_overwrite_ok=True)
feuy5 = book.add_sheet(u'Metadatos', cell_overwrite_ok=True)

# personnalisation du fichier excel
font1 = Font()             # cr?ation police 1
font1.name = 'Times New Roman'
font1.bold = True

entete = XFStyle()         # cr?ation style pour les en-t?tes
entete.font = font1             # application de la police 1 au style entete
hyperlien = easyxf(u'font: underline single')
erreur = easyxf(u'font: name Arial, bold 1, colour red')

# colonnes feuille dossiers
feuy1.write(0, 0, u'Nom actuel', entete)
feuy1.write(0, 1, u'Nom chang?', entete)
feuy1.write(0, 2, u'Type g?om?trie', entete)
feuy1.write(0, 3, u'Emprise', entete)
feuy1.write(0, 4, u'Projection', entete)
feuy1.write(0, 5, u'EPSG', entete)

    def salary_payment_report(self,cr,uid,ids,context=None):
        #Define the font attributes for header
        fnt = Font()
        fnt.name = 'Arial'
        fnt.size=16
        fnt.style= 'Regular'
        #Define the font attributes for header
        content_fnt = Font()
        content_fnt.name ='Arial'
        content_fnt.size=16
        content_fnt.style= 'Regular'
        align_content = Alignment()
        align_content.horz= Alignment.HORZ_CENTER
        borders = Borders()
        borders.left = 0x01
        borders.right = 0x01
        borders.top = 0x01
        borders.bottom = 0x01
        #The text should be centrally aligned
        align = Alignment()
        align.horz = Alignment.HORZ_CENTER
        align.vert = Alignment.VERT_CENTER
        #We set the backgroundcolour here
        pattern = Pattern()
        pattern.pattern = Pattern.SOLID_PATTERN
        pattern.pattern_fore_colour =  0x1F
        #apply the above settings to the row(0) header
        style_header= XFStyle()
        style_header.font= fnt
        style_header.pattern= pattern
        style_header.borders = borders
        style_header.alignment=align    
        #Define the font attributes for header
        fnt3 = Font()
        fnt3.name = 'Arial'
        fnt3.size=16
        fnt3.style= 'Regular'
        #Define the font attributes for header
        content_fnt3 = Font()
        content_fnt3.name ='Arial'
        content_fnt3.size=16
        content_fnt3.style= 'Regular'
        align_content3 = Alignment()
        align_content3.horz= Alignment.HORZ_CENTER
        borders3 = Borders()
        borders3.left = 0x01
        borders3.right = 0x01
        borders3.top = 0x01
        borders3.bottom = 0x01
        #The text should be centrally aligned
        align3 = Alignment()
        align3.horz = Alignment.HORZ_CENTER
        align3.vert = Alignment.VERT_CENTER
        #We set the backgroundcolour here
        pattern3 = Pattern()
        #apply the above settings to the row(0) header
        style_header3= XFStyle()
        style_header3.font= fnt3
        style_header3.pattern= pattern3
        style_header3.borders = borders3
        style_header3.alignment=align3   
        #Define the font attributes for header
        fnt4 = Font()
        fnt4.name = 'Arial'
        #Define the font attributes for header
        content_fnt4 = Font()
        content_fnt4.name ='Arial'
        align_content4 = Alignment()
        align_content4.horz= Alignment.HORZ_LEFT
     
        borders4 = Borders()
        borders4.left = 0x01
        borders4.right = 0x01
        borders4.top = 0x01
        borders4.bottom = 0x01
        #The text should be centrally aligned
        align4 = Alignment()
        align4.horz = Alignment.HORZ_LEFT
        align4.vert = Alignment.VERT_CENTER
        #We set the backgroundcolour here
        pattern4 = Pattern()
        #apply the above settings to the row(0) header
        style_header4= XFStyle()
        style_header4.font= fnt4
        style_header4.pattern= pattern4
        style_header4.borders = borders4
        style_header4.alignment=align4
        #Define the font attributes for header
        fnt1 = Font()
        fnt1.name = 'Arial'
        fnt1.size=10
        fnt1.Style= 'Regular'
        #Define the font attributes for header
        content_fnt1 = Font()
        content_fnt1.name ='Arial'
        content_fnt1.size=10
        content_fnt1.Style= 'Regular'
        align_content1 = Alignment()
        align_content1.horz= Alignment.HORZ_RIGHT
        borders1 = Borders()
#.........這裏部分代碼省略.........