操作xls 需要导入三方库
from xlwt import *
单元格内容格式化
font.bold = True # May be: True, False
font.italic = True # May be: True, False
font.struck_out = True # May be: True, False
font.underline = xlwt.Font.UNDERLINE_SINGLE # May be: UNDERLINE_NONE, UNDERLINE_SINGLE, UNDERLINE_SINGLE_ACC, UNDERLINE_DOUBLE, UNDERLINE_DOUBLE_ACC
font.escapement = xlwt.Font.ESCAPEMENT_SUPERSCRIPT # May be: ESCAPEMENT_NONE, ESCAPEMENT_SUPERSCRIPT, ESCAPEMENT_SUBSCRIPT
font.family = xlwt.Font.FAMILY_ROMAN # May be: FAMILY_NONE, FAMILY_ROMAN, FAMILY_SWISS, FAMILY_MODERN, FAMILY_SCRIPT, FAMILY_DECORATIVE
font.charset = xlwt.Font.CHARSET_ANSI_LATIN # May be: CHARSET_ANSI_LATIN, CHARSET_SYS_DEFAULT, CHARSET_SYMBOL, CHARSET_APPLE_ROMAN, CHARSET_ANSI_JAP_SHIFT_JIS, CHARSET_ANSI_KOR_HANGUL, CHARSET_ANSI_KOR_JOHAB, CHARSET_ANSI_CHINESE_GBK, CHARSET_ANSI_CHINESE_BIG5, CHARSET_ANSI_GREEK, CHARSET_ANSI_TURKISH, CHARSET_ANSI_VIETNAMESE, CHARSET_ANSI_HEBREW, CHARSET_ANSI_ARABIC, CHARSET_ANSI_BALTIC, CHARSET_ANSI_CYRILLIC, CHARSET_ANSI_THAI, CHARSET_ANSI_LATIN_II, CHARSET_OEM_LATIN_I
font.colour_index = ?
font.get_biff_record = ?
font.height = 0x00C8 # C8 in Hex (in decimal) = 10 points in height.
font.name = ?
font.outline = ?
font.shadow = ?
设置单元格宽度
import xltw
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, 'My Cell Contents')
worksheet.col(0).width = 3333 # 3333 = 1" (one inch).
workbook.save('Excel_Workbook.xls')
单元格添加超链接
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, xlwt.Formula('HYPERLINK("http://www.google.com";"Google")')) # Outputs the text "Google" linking to http://www.google.com
workbook.save('Excel_Workbook.xls')
合并单元格
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write_merge(0, 0, 0, 3, 'First Merge') # Merges row 0's columns 0 through 3.
font = xlwt.Font() # Create Font
font.bold = True # Set font to Bold
style = xlwt.XFStyle() # Create Style
style.font = font # Add Bold Font to Style
worksheet.write_merge(1, 2, 0, 3, 'Second Merge', style) # Merges row 1 through 2's columns 0 through 3.
workbook.save('Excel_Workbook.xls')
example:根据单元格数据设置底纹(监控)
# 给xls单元格加底纹
def changeColor(self,fields,results,outputpath):
workbook = Workbook(encoding='utf-8')
sheet_name='monitor'
sheet = workbook.add_sheet(sheet_name, cell_overwrite_ok=True)
pattern=Pattern()
fnt=Font()
style=XFStyle()
fnt.bold=True
style.font=fnt
pattern.pattern=Pattern.SOLID_PATTERN
pattern.pattern_fore_colour=23
style.pattern = pattern
for field in range(0, len(fields)):
# pattern.pattern_fore_colour=22
# style.pattern=pattern
sheet.write(0, field, fields[field][0],style)
row=1
col=0
for row in range(1, len(results) + 1):
for col in range(0, len(fields)):
pattern=Pattern()
#borders=Borders()
#borders.right=Borders.THIN
#borders.top=Borders.THIN
#borders.bottom=Borders.THIN
#borders.left_colour=0x40
#borders.right_colour=0x40
#borders.top_colour=0x40
#borders.bottom_colour=0x40
style=XFStyle()
#style.borders=borders
pattern.pattern=Pattern.SOLID_PATTERN
# 判断是否为字符串
if (isinstance(results[row - 1][col], basestring)):
# 判断字符串内是否包含‘%’
if "%" in results[row - 1][col]:
a=float(results[row - 1][col].strip('%')) / 100
# 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow,,,,
pattern.pattern_fore_colour=2
style.pattern=pattern
# 判断超过90%的显示红色
if (a >= 0.80):
#print a
sheet.write(row, col, u'%s' % results[row - 1][col], style)
# 80%~90% 的显示黄色
elif (0.6 < a < 0.8):
pattern.pattern_fore_colour=5
style.pattern=pattern
sheet.write(row, col, u'%s' % results[row - 1][col], style)
# 其余绿色显示
else:
pattern.pattern_fore_colour=3
style.pattern=pattern
sheet.write(row, col, u'%s' % results[row - 1][col], style)
else:
pattern.pattern_fore_colour = 22
style.pattern=pattern
sheet.write(row, col, u'%s' % results[row - 1][col])
else:
pattern.pattern_fore_colour=22
style.pattern=pattern
sheet.write(row, col, u'%s' % results[row - 1][col])
workbook.save(outputpath)