快速入门
下面例子，创建一个名为mini.xls的文件，它有一个空sheet：'xlwt was here'。代码见mini.py。
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 python3# -*- 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：颜色
比如设置国家码，代码：country.py：
from xlwt import *
w = Workbook()
w.country_code = 61ws = 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。
插入图片
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，first_used_row，last_used_col，row_tempfile。以上属性类定义中。
函数构成的属性有：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来插入图片。代码：image.py：
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')设置样式
下面例子改变字体的高度。代码：row_styles.py：
#!/usr/bin/env python3# -*- coding: utf-8 -*-# Copyright (C) 2005 Kiseliov Romanfrom xlwt import *
w = Workbook(encoding='utf-8')
ws = w.add_sheet('Hey, Dude')for i 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等属性。另外还可以不写单元格，直接设置格式，比如代码row_styles_empty：
from pyExcelerator import *
w = Workbook()
ws = w.add_sheet('Hey, Dude')for i 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
公式
Formula方法可以生成公式，注意Formula中的公式是不需要等号的。下面例子simple.py输出了红色的”Test”，并在第3行包含了公式。
import xlwtfrom datetime import datetime
font0 = xlwt.Font()
font0.name = 'Times New Roman'font0.colour_index = 2font0.bold = Truestyle0 = 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')这里另有一个公式的实例parse-fmla.py：
from xlwt import ExcelFormulaParser, ExcelFormulaimport sys
f = ExcelFormula.Formula(""" -((1.80 + 2.898 * 1)/(1.80 + 2.898))*
AVERAGE((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的参数表示列数。实例：merged0.py。
from xlwt import *
wb = Workbook()
ws0 = wb.add_sheet('sheet0')
fnt = Font()
fnt.name = 'Arial'fnt.colour_index = 4fnt.bold = Trueborders = Borders()
borders.left = 6borders.right = 6borders.top = 6borders.bottom = 6style = 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 = 0x0d00wb.save('merged0.xls')日期格式
XFStyle类的num_format_str属性可以设置数值的输出格式，也对日期生效。日期格式的实例, 代码：dates.py
from xlwt import *from datetime 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 = 0for fmt in fmts:
ws.write(i, 0, fmt)
style = XFStyle()
style.num_format_str = fmt
ws.write(i, 4, datetime.now(), style)
i += 1w.save('dates.xls')边框
Borders类的left，right，bottom，top属性分别可以设置左右低高的边框，Font类的name可以设置字体类型，struck_out为是否添加删除线，bold为是否为粗体，下面展示了不同边框和删除样式的字体, 代码blanks.py：
from xlwt import *
font0 = Font()
font0.name = 'Times New Roman'font0.struck_out = Truefont0.bold = Truestyle0 = XFStyle()
style0.font = font0
wb = Workbook()
ws0 = wb.add_sheet('0')
ws0.write(1, 1, 'Test', style0)for i 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')字体颜色
Font中的colour_index可以设置颜色，下面展示了不同颜色的字体，可以作为颜色参考, 代码format.py：
from xlwt import *
font0 = Font()
font0.name = 'Times New Roman'font0.struck_out = Truefont0.bold = Truestyle0 = 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()
fnt.name = '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')超级链接
Formula中的可以插入HYPERLINK超级链接，代码hyperlinks.py：
from xlwt import *
f = Font()
f.height = 20*72f.name = 'Verdana'f.bold = Truef.underline = Font.UNDERLINE_DOUBLE
f.colour_index = 4h_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 + '("http://www.irs.gov/pub/irs-pdf/f1000.pdf";"f1000.pdf")'), h_style)
ws.write_merge(2, 2, 2, 25, Formula(n + '("mailto:roman.kiseliov@gmail.com?subject=pyExcelerator-feedback&Body=Hello,%20Roman!";"pyExcelerator-feedback")'), h_style)
w.save("hyperlinks.xls")编码
在没有指定编码的情况下，也可以通过unicode输出字符，不过这样比较费劲，建议还是使用utf-8编码，代码unicode1.py：
from xlwt import *
w = Workbook()
ws1 = w.add_sheet(u'\N{GREEK SMALL LETTER ALPHA}\N{GREEK SMALL LETTER BETA}\N{GREEK SMALL LETTER GAMMA}')
ws1.write(0, 0, u'\N{GREEK SMALL LETTER ALPHA}\N{GREEK SMALL LETTER BETA}\N{GREEK SMALL LETTER 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 examplews1.write(3,0, u'Hi Mom -\u263a-!') # RFC2152 examplews1.write(4,0, u'\u65E5\u672C\u8A9E') # RFC2152 examplews1.write(5,0, u'Item 3 is \u00a31.') # RFC2152 examplews1.write(8,0, u'\N{INTEGRAL}') # RFC2152 examplew.add_sheet(u'A\u2262\u0391.') # RFC2152 examplew.add_sheet(u'Hi Mom -\u263a-!') # RFC2152 exampleone_more_ws = w.add_sheet(u'\u65E5\u672C\u8A9E') # RFC2152 examplew.add_sheet(u'Item 3 is \u00a31.') # RFC2152 exampleone_more_ws.write(0, 0, u'\u2665\u2665')
w.add_sheet(u'\N{GREEK SMALL LETTER ETA WITH TONOS}')
w.save('unicode1.xls')冻结
冻结设置panes_frozen为True，然后设置冻结的位置就好。支持行冻结，列冻结及相关的隐藏功能。代码：panes.py:
from xlwt 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')for i in range(0x100):
ws1.write(i/0x10, i%0x10, i)for i in range(0x100):
ws2.write(i/0x10, i%0x10, i)for i in range(0x100):
ws3.write(i/0x10, i%0x10, i)for i in range(0x100):
ws4.write(i/0x10, i%0x10, i)for i in range(0x100):
ws5.write(i/0x10, i%0x10, i)for i in range(0x100):
ws6.write(i/0x10, i%0x10, i)
ws1.panes_frozen = Truews1.horz_split_pos = 2ws2.panes_frozen = Truews2.vert_split_pos = 2ws3.panes_frozen = Truews3.horz_split_pos = 1ws3.vert_split_pos = 1ws4.panes_frozen = Falsews4.horz_split_pos = 12ws4.horz_split_first_visible = 2ws5.panes_frozen = Falsews5.vert_split_pos = 40ws4.vert_split_first_visible = 2ws6.panes_frozen = Falsews6.horz_split_pos = 12ws4.horz_split_first_visible = 2ws6.vert_split_pos = 40ws4.vert_split_first_visible = 2w.save('panes.xls')数值格式
和日期格式类似，代码：num_formats.py：
from xlwt 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 = 0for fmt in fmts:
ws.write(i, 0, fmt)
style = XFStyle()
style.num_format_str = fmt
ws.write(i, 4, -1278.9078, style)
i += 1w.save('num_formats.xls')更多公式
Formulas.py有更多公式可供参考：
from xlwt 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')保护
from xlwt import *
fnt = Font()
fnt.name = 'Arial'fnt.colour_index = 4fnt.bold = Trueborders = Borders()
borders.left = 6borders.right = 6borders.top = 6borders.bottom = 6style = 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 = 1ws0.row(2).level = 1ws0.row(3).level = 2ws0.row(4).level = 2ws0.row(5).level = 2ws0.row(6).level = 2ws0.row(7).level = 2ws0.row(8).level = 1ws0.row(9).level = 1ws1 = 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 = 1ws1.col(2).level = 1ws1.col(3).level = 2ws1.col(4).level = 2ws1.col(5).level = 2ws1.col(6).level = 2ws1.col(7).level = 2ws1.col(8).level = 1ws1.col(9).level = 1ws2 = 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 = 1ws2.row(2).level = 1ws2.row(3).level = 2ws2.row(4).level = 2ws2.row(5).level = 2ws2.row(6).level = 2ws2.row(7).level = 2ws2.row(8).level = 1ws2.row(9).level = 1ws2.col(1).level = 1ws2.col(2).level = 1ws2.col(3).level = 2ws2.col(4).level = 2ws2.col(5).level = 2ws2.col(6).level = 2ws2.col(7).level = 2ws2.col(8).level = 1ws2.col(9).level = 1ws0.protect = Truews0.wnd_protect = Truews0.obj_protect = Truews0.scen_protect = Truews0.password = "123456"ws1.protect = Truews1.wnd_protect = Truews1.obj_protect = Truews1.scen_protect = Truews1.password = "abcdefghij"ws2.protect = Truews2.wnd_protect = Truews2.obj_protect = Truews2.scen_protect = Truews2.password = "ok"wb.protect = Truewb.wnd_protect = Truewb.obj_protect = Truewb.save('protection.xls')综合实例
下面程序xlwt_easyxf_simple_demo.py的write_xls对输出xls进行了一定封装，比较有实用意义。
import xlwtimport datetime
ezxf = xlwt.easyxfdef write_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) # frozen headings instead of split panes
sheet.set_horz_split_pos(rowx+1) # in general, freeze after last heading row
sheet.set_remove_splits(True) # if user does 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 in kinds]
write_xls('xlwt_easyxf_simple_demo.xls', 'Demo', hdngs, data, heading_xf, data_xf
作者：python作业AI毕业设计
链接：https://www.jianshu.com/p/9853a9e97a4c
随时随地看视频
