python-xlwt模块详解
python模块介绍- xlwt 创建xls文件（excel）
2013-06-24磁针石
转自：https://www.wendangku.net/doc/c38341896.html,/snake-hand/p/3153158.html
31.1 xlwt–实例 (1)
31.1.1 创建简单的excel文件 (1)
31.1.2 插入图片 (2)
使用insert_bitmap来插入图片。 (3)
31.1.3 设置样式 (3)
31.1.4 更多实例 (4)
输出了红色的”Test”，并在第3行包含了公式： (4)
这里另有一个公式的实例： (5)
日期格式的实例 (6)
下面展示了不同边框和删除样式的字体： (7)
下面展示了不同颜色的字体，可以作为颜色参考： (7)
通过unicode输出字符 (9)
冻结： (10)
更多的公式： (12)
保护实例： (13)
31.1.5 完整实例 (16)
31.1 xlwt–实例
31.1.1 创建简单的excel文件
下面例子，创建一个名为mini.xls的文件，它有一个空sheet：'xlwt was here'。
from xlwt import *
w = Workbook()
ws = w.add_sheet('xlwt was here')
w.save('mini.xls')
Workbook类初始化时有encoding和style_compression参数。
encoding，设置字符编码，一般要这样设置：w = Workbook(encoding='utf-8')，就可以在excel 中输出中文了。默认是ascii。当然要记得在文件头部添加：
#!/usr/bin/env python
# -*- coding: utf-8 -*-
style_compression表示是否压缩，不常用。
Workbook还有一些属性：
Owner设置文档所有者。
country_code：国家码
wnd_protect：窗口保护
obj_protect：对象保护
Protect：保护
backup_on_save：保存时备份
Hpos：横坐标
Vpos：纵坐标
Width：宽度
Height：高度
active_sheet：活动sheet
tab_width：tab宽度
wnd_visible：窗口是否可见
wnd_mini：窗口最小化
hscroll_visible：横向滚动条是否可见。
vscroll_visible：纵向滚动条是否可见。
tabs_visible：tab是否可见。
dates_1904：是否使用1904日期系统
use_cell_values：单元格的值
default_style：默认样式
colour_RGB：颜色
比如设置国家码：
From xlwt import *
w= Workbook()
w.country_code= 61
ws= w.add_sheet('AU')
w.save('country.xls')
方法有：add_style，add_font，add_str，del_str，str_index，add_rt，rt_index，add_sheet，get_sheet，raise_bad_sheetname，convert_sheetindex，setup_xcall，add_sheet_reference。
31.1.2 插入图片
add_sheet 会返回一个Worksheet 类。创建的时候有可选参数cell_overwrite_ok，表示是否可以覆盖单元格，其实是Worksheet实例化的一个参数，默认值是False。
Worksheet初始化的参数有: sheetname，parent_book，cell_overwrite_ok。
Worksheet的属性有：Row，Column，explicit_magn_setting（默认False），visibility（默认0），split_position_units_are_twips（默认False），row_default_height_mismatch，
row_default_hidden，row_default_space_above，row_default_space_below，last_used_row，
函数构成的属性有：name，parent（只读），rows（只读），cols（只读），merged_ranges （只读），bmp_rec（只读），show_formulas，show_grid，show_headers，panes_frozen，auto_colour_grid，cols_right_to_left，show_outline，remove_splits，selected，sheet_visible，page_preview，first_visible_row，first_visible_col，grid_colour，preview_magn，normal_magn，scl_magn，vert_split_pos，horz_split_pos，vert_split_first_visible，horz_split_first_visible，show_auto_page_breaks，dialogue_sheet，auto_style_outline，outline_below，outline_right，fit_num_pages，show_row_outline，show_col_outline，alt_expr_eval，alt_formula_entries，row_default_height，col_default_width，calc_mode，calc_count，RC_ref_mode，iterations_on，delta，save_recalc，print_headers，print_grid，vert_page_breaks，horz_page_breaks，header_str，footer_str，print_centered_vert，print_centered_horz，left_margin，right_margin，top_margin，bottom_margin，paper_size_code，print_scaling，start_page_number，fit_width_to_pages，
fit_height_to_pages，print_in_rows，portrait，print_colour，print_draft，print_notes，
print_notes_at_end，print_omit_errors，print_hres，print_vres，header_margin，footer_margin，copies_num，wnd_protect，obj_protect，protect，scen_protect，password。
方法有：get_parent，write，write_rich_text，merge，write_merge，insert_bitmap，col，row，row_height，col_width。
使用 insert_bitmap来插入图片。
from xlwt import *，
w = Workbook()
ws = w.add_sheet('Image')
ws.insert_bitmap('python.bmp', 2, 2)
ws.insert_bitmap('python.bmp', 10, 2)
w.save('image.xls')
31.1.3 设置样式
改变字体的高度:
#!/usr/bin/envpython
#-*- coding: utf-8 -*-
#Copyright (C) 2005 Kiseliov Roman
fromxlwt import *
w= Workbook(encoding='utf-8')
ws= w.add_sheet('Hey, Dude')
fori in range(6, 80):
fnt = Font()
fnt.height = i*20
style = XFStyle()
style.font = fnt
ws.write(i, 1, '武冈')
ws.row(i).set_style(style)
w.save('row_styles.xls')
XFStyle用于设置字体样式，有描述字符串num_format_str，字体font，居中alignment，边界borders，模式pattern，保护protection等属性。另外还可以不写单元格，直接设置格式，比如：
from pyExcelerator import *
w= Workbook()
ws= w.add_sheet('Hey, Dude')
fori in range(6, 80):
fnt = Font()
fnt.height = i*20
style = XFStyle()
style.font = fnt
ws.row(i).set_style(style)
w.save('row_styles_empty.xls')
设置列宽：ws.col(i).width = 0x0d00 + i
31.1.4 更多实例
输出了红色的”Test”，并在第3行包含了公式：
importxlwt
fromdatetime import datetime
font0= xlwt.Font()
https://www.wendangku.net/doc/c38341896.html,= 'Times New Roman'
font0.colour_index= 2
font0.bold= True
style0= xlwt.XFStyle()
style0.font= font0
style1= xlwt.XFStyle()
style1.num_format_str= 'D-MMM-YY'
wb= xlwt.Workbook()
ws= wb.add_sheet('A Test Sheet')
ws.write(0,0, 'Test', style0)
ws.write(1,0, datetime.now(), style1)
ws.write(2,0, 1)
ws.write(2,1, 1)
ws.write(2,2, xlwt.Formula("A3+B3"))
wb.save('example.xls')
这里另有一个公式的实例：
fromxlwt import ExcelFormulaParser, ExcelFormula importsys
f= ExcelFormula.Formula(
"""-((1.80 + 2.898 * 1)/(1.80 + 2.898))*
A VERAGE((1.80+ 2.898 * 1)/(1.80 + 2.898);
(1.80 + 2.898 * 1)/(1.80 + 2.898);
(1.80 + 2.898 * 1)/(1.80 + 2.898)) +
SIN(PI()/4)""")
合并单元格的实例
注意write_merge,1,2个参数表示行数，3,4的参数表示列数。
fromxlwt import *
wb= Workbook()
ws0= wb.add_sheet('sheet0')
fnt= Font()
https://www.wendangku.net/doc/c38341896.html,= 'Arial'
fnt.colour_index= 4
fnt.bold= True
borders= Borders()
borders.left= 6
borders.right= 6
borders.top= 6
borders.bottom= 6
style= XFStyle()
style.font= fnt
style.borders = borders
ws0.write_merge(3,3, 1, 5, 'test1', style) ws0.write_merge(4,10, 1, 5, 'test2', style) ws0.col(1).width= 0x0d00
wb.save('merged0.xls')
日期格式的实例
fromxlwt import *
fromdatetime import datetime
w= Workbook()
ws= w.add_sheet('Hey, Dude')
fmts= [
'M/D/YY',
'D-MMM-YY',
'D-MMM',
'MMM-YY',
'h:mm AM/PM',
'h:mm:ss AM/PM',
'h:mm',
'h:mm:ss',
'M/D/YY h:mm',
'mm:ss',
'[h]:mm:ss',
'mm:ss.0',
]
i= 0
forfmt in fmts:
ws.write(i, 0, fmt)
style = XFStyle()
style.num_format_str = fmt
ws.write(i, 4, datetime.now(), style)
i += 1
w.save('dates.xls')
下面展示了不同边框和删除样式的字体：
fromxlwt import *
font0= Font()
https://www.wendangku.net/doc/c38341896.html,= 'Times New Roman'
font0.struck_out= True
font0.bold= True
style0= XFStyle()
style0.font= font0
wb= Workbook()
ws0= wb.add_sheet('0')
ws0.write(1,1, 'Test', style0)
fori in range(0, 0x53):
borders = Borders()
borders.left = i
borders.right = i
borders.top = i
borders.bottom = i
style = XFStyle()
style.borders = borders
ws0.write(i, 2, '', style)
ws0.write(i, 3, hex(i), style0)
ws0.write_merge(5,8, 6, 10, "")
wb.save('blanks.xls')
下面展示了不同颜色的字体，可以作为颜色参考：
from xlwt import *
font0 = Font()
https://www.wendangku.net/doc/c38341896.html, = 'Times New Roman'
font0.struck_out = True
font0.bold = True
style0 = XFStyle()
style0.font = font0
wb = Workbook()
ws0 = wb.add_sheet('0')
ws0.write(1, 1, 'Test', style0)
for i in range(0, 0x53):
fnt = Font()
https://www.wendangku.net/doc/c38341896.html, = 'Arial'
fnt.colour_index = i
fnt.outline = True
borders = Borders()
borders.left = i
style = XFStyle()
style.font = fnt
style.borders =borders
ws0.write(i, 2,'colour', style)
ws0.write(i, 3,hex(i), style0)
wb.save('format.xls')
超级链接的插入方法：
fromxlwt import *
f= Font()
f.height= 20*72
https://www.wendangku.net/doc/c38341896.html,= 'Verdana'
f.bold= True
f.underline= Font.UNDERLINE_DOUBLE
f.colour_index= 4
h_style= XFStyle()
h_style.font= f
w= Workbook()
ws= w.add_sheet('F')
##############
##NOTE: parameters are separated by semicolon!!!
##############
n= "HYPERLINK"
ws.write_merge(1,1, 1, 10, Formula(n
+'("https://www.wendangku.net/doc/c38341896.html,/pub/irs-pdf/f1000.pdf";"f1000.pdf")'),h_style)
ws.write_merge(2,2, 2, 25, Formula(n
+'("mailto:roman.kiseliov@https://www.wendangku.net/doc/c38341896.html,?subject=pyExcelerator-feedback&Body=Hello,%20Roman !";"pyExcelerator-feedback")'),h_style)
w.save("hyperlinks.xls")
通过unicode输出字符
在没有指定编码的情况下，也可以通过unicode输出字符，不过这样比较费劲，建议还是使用utf-8编码：
fromxlwt import *
w= Workbook()
ws1= w.add_sheet(u'\N{GREEK SMALL LETTER ALPHA}\N{GREEK SMALL LETTER BETA}\N{GREEKSMALL LETTER GAMMA}')
ws1.write(0,0, u'\N{GREEK SMALL LETTER ALPHA}\N{GREEK SMALL LETTER BETA}\N{GREEK SMALLLETTER GAMMA}')
ws1.write(1,1, u'\N{GREEK SMALL LETTER DELTA}x = 1 + \N{GREEK SMALL LETTER DELTA}')
ws1.write(2,0,u'A\u2262\u0391.') # RFC2152 example
ws1.write(3,0,u'Hi Mom -\u263a-!') # RFC2152 example
ws1.write(4,0,u'\u65E5\u672C\u8A9E') # RFC2152 example
ws1.write(5,0,u'Item 3 is \u00a31.') # RFC2152 example
ws1.write(8,0,u'\N{INTEGRAL}') # RFC2152 example
w.add_sheet(u'A\u2262\u0391.') # RFC2152 example
w.add_sheet(u'HiMom -\u263a-!') # RFC2152 example
one_more_ws= w.add_sheet(u'\u65E5\u672C\u8A9E') # RFC2152 example w.add_sheet(u'Item3 is \u00a31.') # RFC2152 example
one_more_ws.write(0,0, u'\u2665\u2665')
w.add_sheet(u'\N{GREEKSMALL LETTER ETA WITH TONOS}')
w.save('unicode1.xls')
冻结：
fromxlwt import *
w= Workbook()
ws1= w.add_sheet('sheet 1')
ws2= w.add_sheet('sheet 2')
ws3= w.add_sheet('sheet 3')
ws4= w.add_sheet('sheet 4')
ws5= w.add_sheet('sheet 5')
ws6= w.add_sheet('sheet 6')
fori in range(0x100):
ws1.write(i/0x10, i%0x10, i)
fori in range(0x100):
ws2.write(i/0x10, i%0x10, i)
fori in range(0x100):
ws3.write(i/0x10, i%0x10, i)
fori in range(0x100):
ws4.write(i/0x10, i%0x10, i)
fori in range(0x100):
ws5.write(i/0x10, i%0x10, i)
fori in range(0x100):
ws6.write(i/0x10, i%0x10, i)
ws1.panes_frozen= True
ws1.horz_split_pos= 2
ws2.panes_frozen= True
ws2.vert_split_pos= 2
ws3.panes_frozen= True
ws3.horz_split_pos= 1
ws3.vert_split_pos= 1
ws4.panes_frozen= False
ws4.horz_split_pos= 12
ws4.horz_split_first_visible= 2
ws5.panes_frozen= False
ws5.vert_split_pos= 40
ws4.vert_split_first_visible= 2
ws6.panes_frozen= False
ws6.horz_split_pos= 12
ws4.horz_split_first_visible= 2 ws6.vert_split_pos= 40
ws4.vert_split_first_visible= 2 w.save('panes.xls')
各种数值格式：
fromxlwt import *
w= Workbook()
ws= w.add_sheet('Hey, Dude')
fmts= [
'general',
'0',
'0.00',
'#,##0',
'#,##0.00',
'"$"#,##0_);("$"#,##',
'"$"#,##0_);[Red]("$"#,##',
'"$"#,##0.00_);("$"#,##',
'"$"#,##0.00_);[Red]("$"#,##', '0%',
'0.00%',
'0.00E+00',
'# ?/?',
'# ??/??',
'M/D/YY',
'D-MMM-YY',
'D-MMM',
'MMM-YY',
'h:mm AM/PM',
'h:mm:ss AM/PM',
'h:mm',
'h:mm:ss',
'M/D/YY h:mm',
'_(#,##0_);(#,##0)',
'_(#,##0_);[Red](#,##0)',
'_(#,##0.00_);(#,##0.00)',
'_(#,##0.00_);[Red](#,##0.00)',
'_("$"* #,##0_);_("$"*(#,##0);_("$"* "-"_);_(@_)',
'_(* #,##0_);_(* (#,##0);_(*"-"_);_(@_)',
'_("$"*#,##0.00_);_("$"* (#,##0.00);_("$"*"-"??_);_(@_)', '_(* #,##0.00_);_(* (#,##0.00);_(*"-"??_);_(@_)',
'mm:ss',
'[h]:mm:ss',
'mm:ss.0',
'##0.0E+0',
'@'
]
i= 0
forfmt in fmts:
ws.write(i, 0, fmt)
style = XFStyle()
style.num_format_str = fmt
ws.write(i, 4, -1278.9078, style)
i += 1
w.save('num_formats.xls')
更多的公式：
fromxlwt import *
w= Workbook()
ws= w.add_sheet('F')
ws.write(0,0, Formula("-(1+1)"))
ws.write(1,0, Formula("-(1+1)/(-2-2)"))
ws.write(2,0, Formula("-(134.8780789+1)"))
ws.write(3,0, Formula("-(134.8780789e-10+1)"))
ws.write(4,0, Formula("-1/(1+1)+9344"))
ws.write(0,1, Formula("-(1+1)"))
ws.write(1,1, Formula("-(1+1)/(-2-2)"))
ws.write(2,1, Formula("-(134.8780789+1)"))
ws.write(3,1, Formula("-(134.8780789e-10+1)"))
ws.write(4,1, Formula("-1/(1+1)+9344"))
ws.write(0,2, Formula("A1*B1"))
ws.write(1,2, Formula("A2*B2"))
ws.write(2,2, Formula("A3*B3"))
ws.write(3,2, Formula("A4*B4*sin(pi()/4)"))
ws.write(4,2, Formula("A5%*B5*pi()/1000"))
##############
##NOTE: parameters are separated by semicolon!!!
##############
ws.write(5,2,Formula("C1+C2+C3+C4+C5/(C1+C2+C3+C4/(C1+C2+C3+C4/(C1+C2+C3+C4)+ C5)+C5)-20.3e-2"))
ws.write(5,3, Formula("C1^2"))
ws.write(6,2, Formula("SUM(C1;C2;;;;;C3;;;C4)"))
ws.write(6,3, Formula("SUM($A$1:$C$5)"))
ws.write(7,0, Formula('"lkjljllkllkl"'))
ws.write(7,1, Formula('"yuyiyiyiyi"'))
ws.write(7,2, Formula('A8 & B8 & A8'))
ws.write(8,2, Formula('now()'))
ws.write(10,2, Formula('TRUE'))
ws.write(11,2, Formula('FALSE'))
ws.write(12,3, Formula('IF(A1>A2;3;"hkjhjkhk")'))
w.save('formulas.xls')
保护实例：
fromxlwt import *
fnt= Font()
https://www.wendangku.net/doc/c38341896.html,= 'Arial'
fnt.colour_index= 4
fnt.bold= True
borders= Borders()
borders.left= 6
borders.right= 6
borders.top= 6
borders.bottom= 6
style= XFStyle()
style.font= fnt
style.borders= borders
wb= Workbook()
ws0= wb.add_sheet('Rows Outline')
ws0.write_merge(1,1, 1, 5, 'test 1', style) ws0.write_merge(2,2, 1, 4, 'test 1', style) ws0.write_merge(3,3, 1, 3, 'test 2', style) ws0.write_merge(4,4, 1, 4, 'test 1', style) ws0.write_merge(5,5, 1, 4, 'test 3', style) ws0.write_merge(6,6, 1, 5, 'test 1', style) ws0.write_merge(7,7, 1, 5, 'test 4', style) ws0.write_merge(8,8, 1, 4, 'test 1', style) ws0.write_merge(9,9, 1, 3, 'test 5', style)
ws0.row(1).level= 1
ws0.row(2).level= 1
ws0.row(3).level= 2
ws0.row(4).level= 2
ws0.row(5).level= 2
ws0.row(6).level= 2
ws0.row(7).level= 2
ws0.row(8).level= 1
ws0.row(9).level= 1
ws1= wb.add_sheet('Columns Outline')
ws1.write_merge(1,1, 1, 5, 'test 1', style)
ws1.write_merge(2,2, 1, 4, 'test 1', style)
ws1.write_merge(3,3, 1, 3, 'test 2', style)
ws1.write_merge(4,4, 1, 4, 'test 1', style)
ws1.write_merge(5,5, 1, 4, 'test 3', style)
ws1.write_merge(6,6, 1, 5, 'test 1', style)
ws1.write_merge(7,7, 1, 5, 'test 4', style)
ws1.write_merge(8,8, 1, 4, 'test 1', style)
ws1.write_merge(9,9, 1, 3, 'test 5', style)
ws1.col(1).level= 1
ws1.col(2).level= 1
ws1.col(3).level= 2
ws1.col(4).level= 2
ws1.col(5).level= 2
ws1.col(6).level= 2
ws1.col(7).level= 2
ws1.col(8).level= 1
ws1.col(9).level= 1
ws2= wb.add_sheet('Rows and Columns Outline')
ws2.write_merge(1,1, 1, 5, 'test 1', style)
ws2.write_merge(2,2, 1, 4, 'test 1', style)
ws2.write_merge(3,3, 1, 3, 'test 2', style)
ws2.write_merge(4,4, 1, 4, 'test 1', style)
ws2.write_merge(5,5, 1, 4, 'test 3', style)
ws2.write_merge(6,6, 1, 5, 'test 1', style)
ws2.write_merge(7,7, 1, 5, 'test 4', style)
ws2.write_merge(8,8, 1, 4, 'test 1', style)
ws2.write_merge(9,9, 1, 3, 'test 5', style)
ws2.row(1).level= 1
ws2.row(2).level= 1
ws2.row(3).level= 2
ws2.row(4).level= 2
ws2.row(5).level= 2
ws2.row(6).level= 2
ws2.row(7).level= 2
ws2.row(8).level= 1
ws2.row(9).level= 1
ws2.col(1).level= 1
ws2.col(2).level= 1
ws2.col(3).level= 2
ws2.col(4).level= 2
ws2.col(5).level= 2
ws2.col(6).level= 2
ws2.col(7).level= 2
ws2.col(8).level= 1
ws2.col(9).level= 1
ws0.protect= True
ws0.wnd_protect= True
ws0.obj_protect= True
ws0.scen_protect= True
ws0.password= "123456"
ws1.protect= True
ws1.wnd_protect= True
ws1.obj_protect= True
ws1.scen_protect= True
ws1.password= "abcdefghij"
ws2.protect= True
ws2.wnd_protect= True
ws2.obj_protect= True
ws2.scen_protect= True
ws2.password= "ok"
wb.protect= True
wb.wnd_protect= True
wb.obj_protect= True
wb.save('protection.xls')
31.1.5 完整实例
下面程序的write_xls对输出xls进行了一定封装，比较有实用意义。#Write an XLS file with a single worksheet, containing
#a heading row and some rows of data.
importxlwt
importdatetime
ezxf= xlwt.easyxf
defwrite_xls(file_name,sheet_name, headings, data, heading_xf, data_xfs): book = xlwt.Workbook()
sheet = book.add_sheet(sheet_name)
rowx = 0
for colx, value in enumerate(headings):
sheet.write(rowx, colx, value,heading_xf)
sheet.set_panes_frozen(True) # frozenheadings instead of split panes
sheet.set_horz_split_pos(rowx+1) # ingeneral, freeze after last heading row
sheet.set_remove_splits(True) # if userdoes unfreeze, don't leave a split there
for row in data:
rowx += 1
for colx, value in enumerate(row):
sheet.write(rowx, colx, value,data_xfs[colx])
book.save(file_name)
if__name__ == '__main__':
import sys
mkd = datetime.date
hdngs = ['Date', 'Stock Code', 'Quantity','Unit Price', 'Value', 'Message']
kinds = 'date text int price money text'.split()
data = [
[mkd(2007, 7, 1), 'ABC', 1000,1.234567, 1234.57, ''],
[mkd(2007, 12, 31), 'XYZ', -100,4.654321, -465.43, 'Goods returned'],
] + [
[mkd(2008, 6, 30), 'PQRCD', 100,2.345678, 234.57, ''],
] * 100
heading_xf = ezxf('font: bold on; align:wrap on, vert centre, horiz center')
kind_to_xf_map = {
'date':ezxf(num_format_str='yyyy-mm-dd'),
'int': ezxf(num_format_str='#,##0'),
'money': ezxf('font: italic on;pattern: pattern solid, fore-colour grey25',
num_format_str='$#,##0.00'),
'price': ezxf(num_format_str='#0.000000'),
'text': ezxf(),
}
data_xfs = [kind_to_xf_map[k] for k inkinds]
write_xls('xlwt_easyxf_simple_demo.xls','Demo', hdngs, data, heading_xf, data_xfs)
#承接软件自动化实施与培训等gtalk：ouyangchongwu#https://www.wendangku.net/doc/c38341896.html,qq 37391319 博客:http: //https://www.wendangku.net/doc/c38341896.html,/oychw
#版权所有，转载刊登请来函联系
# 深圳测试自动化python项目接单群113938272深圳广州软件测试开发6089740
#深圳地摊群66250781武冈洞口城步新宁乡情群49494279
#自动化测试和python群组：https://www.wendangku.net/doc/c38341896.html,/group/automation_testing_pyth on