xlwt 官网上的实例展示

from time import *from xlwt.Workbook import *from xlwt.Style import *style = XFStyle()wb = Workbook()ws0 = wb.add_sheet('0')colcount = 200 + 1rowcount = 6000 + 1t0 = time()print("start: %s" % ctime(t0))print("Filling...")for col in xrange(colcount):print("[%d]" % col, end=' ') for row in xrange(rowcount):        ws0.write(row, col, "BIG")t1 = time() - t0print("since starting elapsed %.2f s" % (t1))print("Storing...")wb.save('big-16Mb.xls')t2 = time() - t0print("since starting elapsed %.2f s" % (t2))from xlwt import *font0 = Font()font0.name = 'Times New Roman'font0.struck_out = Truefont0.bold = Truestyle0 = XFStyle()style0.font = font0wb = 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')from xlwt import *w = Workbook()ws = w.add_sheet('Hey, Dude')for i in range(6, 80):    fnt = Font()    fnt.height = i*20style = XFStyle()    style.font = fnt    ws.write(1, i, 'Test')    ws.col(i).width = 0x0d00 + iw.save('col_width.xls')from xlwt import *from datetime import datetimew = 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')from xlwt import *font0 = Font()font0.name = 'Times New Roman'font0.struck_out = Truefont0.bold = Truestyle0 = XFStyle()style0.font = font0wb = 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')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"))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 *f = Font()f.height = 20*72f.name = 'Verdana'f.bold = Truef.underline = Font.UNDERLINE_DOUBLEf.colour_index = 4h_style = XFStyle()h_style.font = fw = Workbook()ws = w.add_sheet('F')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")from xlwt import *fnt = Font()fnt.name = 'Arial'fnt.colour_index = 4fnt.bold = Trueborders = Borders()borders.left = 6borders.right = 6borders.top = 6borders.bottom = 6al = Alignment()al.horz = Alignment.HORZ_CENTERal.vert = Alignment.VERT_CENTERstyle = XFStyle()style.font = fntstyle.borders = bordersstyle.alignment = alwb = Workbook()ws0 = wb.add_sheet('sheet0')ws1 = wb.add_sheet('sheet1')ws2 = wb.add_sheet('sheet2')for i in range(0, 0x200, 2):    ws0.write_merge(i, i+1, 1, 5, 'test %d' % i, style)    ws1.write_merge(i, i, 1, 7, 'test %d' % i, style)    ws2.write_merge(i, i+1, 1, 7 + (i%10), 'test %d' % i, style)wb.save('merged.xls')import xlwtbook = xlwt.Workbook()for magn in (0, 60, 100, 75, 150):for preview in (False, True):        sheet = book.add_sheet('magn%d%s' % (magn, "np"[preview]))if preview:            sheet.preview_magn = magnelse:            sheet.normal_magn = magn        sheet.page_preview = previewfor rowx in range(100):            sheet.write(rowx, 0, "Some text")book.save("zoom_magnification.xls")import xlwtimport datetimeezxf = xlwt.easyxfdef write_xls(file_name, sheet_name, headings, data, heading_xf, data_xfs):    book = xlwt.Workbook()    sheet = book.add_sheet(sheet_name)    rowx = 0for colx, value in enumerate(headings):        sheet.write(rowx, colx, value, heading_xf)    sheet.set_panes_frozen(True) # frozen headings instead of split panessheet.set_horz_split_pos(rowx+1) # in general, freeze after last heading rowsheet.set_remove_splits(True) # if user does unfreeze, don't leave a split therefor row in data:        rowx += 1for 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, ''],        ] * 100heading_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_xfs)from xlwt import *w = Workbook()ws = w.add_sheet('Hey, Dude')for i in range(6, 80):    fnt = Font()    fnt.height = i*20style = XFStyle()    style.font = fnt    ws.write(i, 1, 'Test')    ws.row(i).set_style(style)w.save('row_styles.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 = fntstyle.borders = borderswb = 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')from xlwt import Workbookfrom xlwt.BIFFRecords import PanesRecordw = Workbook()# do each of the 4 scenarios with each of the 4 possible# active pane settingsfor px,py in (    (0,0),   # no split(0,10),  # horizontal split(10,0),  # vertical split(10,10), # both split    ):    for active in range(4):# 0 - logical bottom-right pane# 1 - logical top-right pane# 2 - logical bottom-left pane# 3 - logical top-left pane# only set valid values:if active not in PanesRecord.valid_active_pane.get(            (int(px > 0),int(py > 0))            ):continuesheet = w.add_sheet('px-%i py-%i active-%i' %(                px,py,active                ))for rx in range(20):for cx in range(20):                sheet.write(rx,cx,'R%iC%i'%(rx,cx))        sheet.panes_frozen = False        sheet.vert_split_pos = px * 8.43sheet.horz_split_pos = py * 12.75sheet.active_pane = activew.save('panes3.xls')import xlwtw = xlwt.Workbook()sheets = [w.add_sheet('sheet ' + str(sheetx+1)) for sheetx in range(7)]ws1, ws2, ws3, ws4, ws5, ws6, ws7 = sheetsfor sheet in sheets:for i in range(0x100):        sheet.write(i // 0x10, i % 0x10, i)H = 1V = 2HF = H + 2VF = V + 2ws1.panes_frozen = Truews1.horz_split_pos = Hws1.horz_split_first_visible = HFws2.panes_frozen = Truews2.vert_split_pos = Vws2.vert_split_first_visible = VFws3.panes_frozen = Truews3.horz_split_pos = Hws3.vert_split_pos = Vws3.horz_split_first_visible = HFws3.vert_split_first_visible = VFH = 10V = 12HF = H + 2VF = V + 2ws4.panes_frozen = Falsews4.horz_split_pos = H * 12.75 # rowsws4.horz_split_first_visible = HFws5.panes_frozen = Falsews5.vert_split_pos = V * 8.43 # rowsws5.vert_split_first_visible = VFws6.panes_frozen = Falsews6.horz_split_pos = H * 12.75 # rowsws6.horz_split_first_visible = HFws6.vert_split_pos = V * 8.43 # colsws6.vert_split_first_visible = VFws7.split_position_units_are_twips = Truews7.panes_frozen = Falsews7.horz_split_pos = H * 250 + 240 # twipsws7.horz_split_first_visible = HFws7.vert_split_pos = V * 955 + 410 # twipsws7.vert_split_first_visible = VFw.save('panes2.xls')from xlwt import *wb = Workbook()ws0 = wb.add_sheet('sheet0')fnt1 = Font()fnt1.name = 'Verdana'fnt1.bold = Truefnt1.height = 18*0x14pat1 = Pattern()pat1.pattern = Pattern.SOLID_PATTERNpat1.pattern_fore_colour = 0x16brd1 = Borders()brd1.left = 0x06brd1.right = 0x06brd1.top = 0x06brd1.bottom = 0x06fnt2 = Font()fnt2.name = 'Verdana'fnt2.bold = Truefnt2.height = 14*0x14brd2 = Borders()brd2.left = 0x01brd2.right = 0x01brd2.top = 0x01brd2.bottom = 0x01pat2 = Pattern()pat2.pattern = Pattern.SOLID_PATTERNpat2.pattern_fore_colour = 0x01Ffnt3 = Font()fnt3.name = 'Verdana'fnt3.bold = Truefnt3.italic = Truefnt3.height = 12*0x14brd3 = Borders()brd3.left = 0x07brd3.right = 0x07brd3.top = 0x07brd3.bottom = 0x07fnt4 = Font()al1 = Alignment()al1.horz = Alignment.HORZ_CENTERal1.vert = Alignment.VERT_CENTERal2 = Alignment()al2.horz = Alignment.HORZ_RIGHTal2.vert = Alignment.VERT_CENTERal3 = Alignment()al3.horz = Alignment.HORZ_LEFTal3.vert = Alignment.VERT_CENTERstyle1 = XFStyle()style1.font = fnt1style1.alignment = al1style1.pattern = pat1style1.borders = brd1style2 = XFStyle()style2.font = fnt2style2.alignment = al1style2.pattern = pat2style2.borders = brd2style3 = XFStyle()style3.font = fnt3style3.alignment = al1style3.pattern = pat2style3.borders = brd3price_style = XFStyle()price_style.font = fnt4price_style.alignment = al2price_style.borders = brd3price_style.num_format_str = '_(#,##0.00_) "money"'ware_style = XFStyle()ware_style.font = fnt4ware_style.alignment = al3ware_style.borders = brd3ws0.merge(3, 3, 1, 5, style1)ws0.merge(4, 10, 1, 6, style2)ws0.merge(14, 16, 1, 7, style3)ws0.col(1).width = 0x0d00wb.save('merged1.xls')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')