python-lwt模块详解解读

《python-lwt模块详解解读》由会员分享，可在线阅读，更多相关《python-lwt模块详解解读（10页珍藏版）》请在装配图网上搜索。

1、python模块介绍-xlwt创建xls文件(excel)2013-06-24磁针石转自：31.1.1创建简单的excel文件131.1.2插入图片2使用insert_bitmap来插入图片。331.1.3设置样式331.1.4更多实例4输出了红色的”Test，并在第3行包含了公式：4这里另有一个公式的实例：5日期格式的实例6下面展示了不同边框和删除样式的字体：7下面展示了不同颜色的字体，可以作为颜色参考：7通过unicode输出字符9冻结：10更多的公式：12保护实例：1331.1.5完整实例16xlwt-实例创建简单的excel文件下面例子，创建一个名为mini.xls的文件，它有一个空s

2、heet：xlwtwashere。fromxlwtimport*w=Workbook。ws=w.add_sheet(xlwtwashere)w.save(mini.xls)Workbook类初始化时有encoding禾口style_compression参数。encoding,设置字符编码，一般要这样设置：w=Workbook(encoding=utf-8),就可以在excel中输出中文了。默认是ascii。当然要记得在文件头部添加：#!/usr/bin/envpython#-*-coding:utf-8-*-style_compression表示是否压缩，不常用。Workbook还有一些属性

3、：Owner设置文档所有者。country_code:国家码wnd_protect:窗口保护obj_protect:对象保护Protect:保护backup_on_save:保存时备份Hpos:横坐标Vpos:纵坐标Width:宽度Height:高度active_sheet：活动sheettab_width:tab宽度wnd_visible:窗口是否可见wnd_mini:窗口最小化hscroll_visible:横向滚动条是否可见。vscroll_visible:纵向滚动条是否可见。tabs_visible:tab是否可见。dates_1904：是否使用1904日期系统use_cell_val

4、ues:单元格的值default_style:默认样式colour_RGB:颜色比如设置国家码：Fromxlwtimport*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_sheetnameconvert_sheetindex，setup_xcall，add_sheet_reference31.1.2插入图片add_shee

5、t会返回一个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_hid

6、den，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_spli

7、ts,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,s

8、how_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_margi

9、n,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

10、_protect,protect,seen_protect,password。方法有：get_parent,write,write_rich_text,merge,write_merge,insert_bitmap,col,row,row_height,col_width。使用insert_bitmap来插入图片fromxlwtimport*，w=Workbook。ws=w.add_sheet(lmage)ws.insert_bitmap(python.bmp,2,2)ws.insert_bitmap(python.bmp,10,2)w.save(image.xls)改变字体的高度#!/usr

11、/bin/envpython#-*-coding:utf-8-*-#Copyright(C)2005KiseliovRomanfromxlwtimport*w=Workbook(encoding=utf-8)ws=w.add_sheet(Hey,Dude)foriinrange(6,80):fnt=Font()fnt.height=i*20style=XFStyle()style.font=fntws.write(i,1,武冈)ws.row(i).set_style(style)w.save(row_styles.xls)XFStyle用于设置字体样式，有描述字符串num_format_str

12、，字体font,居中alignment,边界borders,模式pattern,保护protection等属性。另外还可以不写单元格，接设置格式，比如：frompyExceleratorimport*w=Workbook。ws=w.add_sheet(Hey,Dude)foriinrange(6,80):fnt=Font()fnt.height=i*20style=XFStyle()style.font=fntws.row(i).set_style(style)w.saveCrow_styles_empty.xls)设置列宽：ws.col(i).width=0x0d00+i31.1.4更多实例

13、输出了红色的”Test”，并在第3行包含了公式：importxlwtfromdatetimeimportdatetimefont0=xlwt.Font()font0.name=TimesNewRomanfont0.colour_index=2font0.bold=Truestyle0=xlwt.XFStyle()style0.font=font0style仁xlwt.XFStyle()style1.num_format_str=D-MMM-YYwb=xlwt.Workbook()ws=wb.add_sheet(ATestSheet)ws.write(O,O,Test,styleO)ws.wri

14、te(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)这里另有一个公式的实例：fromxlwtimportExcelFormulaParser,ExcelFormulaimportsysf=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

15、.898*1)/(1.80+2.898)+SIN(PI()/4)”)合并单元格的实例注意write_merge,1,2个参数表示行数，3,4的参数表示列数。fromxlwtimport*wb=Workbook()ws0=wb.add_sheet(sheetO)fnt=Font()fnt.name=Arialfnt.colour_index=4fnt.bold=Trueborders=Borders()borders.left=6borders.right=6borders.top=6borders.bottom=6style=XFStyle()style.font=fntstyle.borde

16、rs=bordersws0.write_merge(3,3,1,5,testl,style)ws0.write_merge(4,10,1,5,test2,style)ws0.col(1).width=OxOdOOwb.save(mergedO.xls)日期格式的实例fromxlwtimport*fromdatetimeimportdatetimew=Workbook。ws=w.add_sheet(Hey,Dude)fmts=M/D/YY,D-MMM-YY,D-MMM,MMM-YY,h:mmAM/PM,h:mm:ssAM/PM,h:mm,h:mm:ss,M/D/YYh:mm,mm:ss,h:mm

17、:ss,mm:ss.O,i=0forfmtinfmts:ws.write(i,0,fmt)style=XFStyle()style.num_format_str=fmtws.write(i,4,datetime.now(),style)i+=1w.save(dates.xls)F面展示了不同边框和删除样式的字体:fromxlwtimport*font0=Font()font0.name=TimesNewRomanfont0.struck_out=Truefont0.bold=TruestyleO=XFStyle()styleO.font=font0wb=Workbook()ws0=wb.add

18、_sheet(O)ws0.write(1,1,Test,style0)foriinrange(0,0x53):borders=Borders()borders.left=iborders.right=iborders.top=iborders.bottom=istyle=XFStyle()style.borders=bordersws0.write(i,2,”，style)ws0.write(i,3,hex(i),style0)ws0.write_merge(5,8,6,10,)wb.save(blanks.xls)F面展示了不同颜色的字体，可以作为颜色参考:fromxlwtimport*fo

19、nt0=Font()font0.name=TimesNewRomanfont0.struck_out=TruefontO.bold=Truestyle0=XFStyle()style0.font=fontOwb=Workbook()ws0=wb.add_sheet(0)ws0.write(1,1,Test,style0)foriinrange(0,0x53):fnt=Font()fnt.name=Arialfnt.colour_index=ifnt.outline=Trueborders=Borders()borders.left=istyle=XFStyle()style.font=fnts

20、tyle.borders=bordersws0.write(i,2,colour,style)ws0.write(i,3,hex(i),style0)wb.save(format.xls)超级链接的插入方法fromxlwtimport*f=Font()f.height=20*72f.name=Verdanaf.bold=Truef.underline=Font.UNDERLINE_DOUBLEf.colourindex=4h_style=XFStyle()h_style.font=fw=Workbook。ws=w.add_sheet(F)#NOTE:parametersareseparated

21、bysemicoIon!#n=HYPERLINKws.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?subject=pyExcelerator-feedback&Body=Hello,%20Roman!;pyExcelerator-feedback),h_style)w.save(hyperlinks.xls)通过unicode输出字符在没有指定编

22、码的情况下，也可以通过unicode输出字符，不过这样比较费劲，建议还是使用utf-8编码：fromxlwtimport*w=Workbook()ws1=w.add_sheet(uNGREEKSMALLLETTERALPHANGREEKSMALLLETTERBETANGREEKSMALLLETTERGAMMA)ws1.write(0,0,uNGREEKSMALLLETTERALPHANGREEKSMALLLETTERBETANGREEKSMALLLETTERGAMMA)ws1.write(1,1,uNGREEKSMALLLETTERDELTAx=1+NGREEKSMALLLETTERDELTA

23、)ws1.write(2,0,uAu2262u0391.)#RFC2152examplews1.write(3,0,uHiMom-u263a-!)#RFC2152examplews1.write(4,0,uu65E5u672Cu8A9E)#RFC2152examplews1.write(5,0,ultem3isu00a31.)#RFC2152examplews1.write(8,0,uNINTEGRAL)#RFC2152examplew.add_sheet(uAu2262u0391.)#RFC2152examplew.add_sheet(uHiMom-u263a-!)#RFC2152examp

24、leone_more_ws=w.add_sheet(uu65E5u672Cu8A9E)#RFC2152examplew.add_sheet(uItem3isu00a31.)#RFC2152exampleone_more_ws.write(0,0,uu2665u2665)w.add_sheet(uNGREEKSMALLLETTERETAWITHTONOS)w.save(unicode1.xls)冻结：fromxlwtimport*w=Workbook。ws1=w.add_sheet(sheet1)ws2=w.add_sheet(sheet2)ws3=w.add_sheet(sheet3)ws4=

25、w.add_sheet(sheet4)ws5=w.add_sheet(sheet5)ws6=w.add_sheet(sheet6)foriinrange(0x100):ws1.write(i/0x10,i%0x10,i)foriinrange(0x100):ws2.write(i/0x10,i%0x10,i)foriinrange(0x100):ws3.write(i/0x10,i%0x10,i)foriinrange(0x100):ws4.write(i/0x10,i%0x10,i)foriinrange(0x100):ws5.write(i/0x10,i%0x10,i)foriinrang

26、e(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.ve

27、rt_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)各种数值格式：fromxlwtimport*w=Workbook。ws=w.add_sheet(Hey,Dude)fmts=general,O,0.00,#,#0,#,#0.00,$#,#0_);($#,#,$#,#0_);Red($#,#,$#,#0.00_);($#

28、,#,”$#,#0.00_);Red($#,#,0%,0.00%,0.00E+00,#?/?,#?/?,M/D/YY,D-MMM-YY,D-MMM,MMM-YY,h:mmAM/PM,h:mm:ssAM/PM,h:mm,h:mm:ss,M/D/YYh:mm,_(#,#0_)；(#,#0),_(#,#O_)；Red(#,#O),_(#,#0.00_);(#,#0.00),_(#,#O.OO_);Red(#,#O.OO),_($*#,#0_);_($*(#,#0);_($*”-_)；_(_),_(*#,#0_);_(*(#,#0);_(*-_);_(_),_($*#,#0.00_);_($*(#,#

29、0.00);_($*-?_);_(_),_(*#,#0.00_);_(*(#,#0.00);_(*-?_);_(_),mm:ss,h:mm:ss,mm:ss.0,#0.0E+0,i=0forfmtinfmts:ws.write(i,0,fmt)style=XFStyle()style.num_format_str=fmtws.write(i,4,-1278.9078,style)i+=1w.save(num_formats.xls)更多的公式：fromxlwtimport*w=Workbook。ws=w.add_sheet(F)ws.write(O,O,Formula(-(1+1)ws.wri

30、te(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

31、(-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:parametersareseparatedbysemicoIon!#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.

32、3e-2)ws.write(5,3,Formula(C1A2)ws.write(6,2,Formula(SUM(C1;C2;C3;C4)ws.write(6,3,Formula(SUM($A$1:$C$5)ws.write(7,0,Formulakjljllkllkl)ws.write(7,1,Formula(yuyiyiyiyi)ws.write(7,2,Formula(A8&B8&A8)ws.write(8,2,Formulanow()ws.write(10,2,Formula(TRUE)ws.write(11,2,Formula(FALSE)ws.write(12,3,Formula(I

33、F(A1A2;3;hkjhjkhk)w.save(formulas.xls)保护实例：fromxlwtimport*fnt=Font()fnt.name=Arialfnt.colour_index=4fnt.bold=Trueborders=Borders。borders.left=6borders.right=6borders.top=6borders.bottom=6style=XFStyle()style.font=fntstyle.borders=borderswb=Workbook。wsO=wb.add_sheet(RowsOutline)ws0.write_merge(1,1,1,

34、5,test1,style)ws0.write_merge(2,2,1,4,test1,style)ws0.write_merge(3,3,1,3,test2,style)ws0.write_merge(4,4,1,4,test1,style)ws0.write_merge(5,5,1,4,test3,style)ws0.write_merge(6,6,1,5,test1,style)ws0.write_merge(7,7,1,5,test4,style)ws0.write_merge(8,8,1,4,test1,style)ws0.write_merge(9,9,1,3,test5,styl

35、e)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(ColumnsOutline)ws1.write_merge(1,1,1,5,test1,style)ws1.write_merge(2,2,1,4,test1,style)ws1.write_merge(3,3,1,3,test2,st

36、yle)ws1.write_merge(4,4,1,4,test1,style)ws1.write_merge(5,5,1,4,test3,style)ws1.write_merge(6,6,1,5,test1,style)ws1.write_merge(7,7,1,5,test4,style)ws1.write_merge(8,8,1,4,test1,style)ws1.write_merge(9,9,1,3,test5,style)ws1.col(1).level=1ws1.col(2).level=1ws1.col(3).level=2ws1.col(4).level=2ws1.col(

37、5).level=2ws1.col(6).level=2ws1.col(7).level=2ws1.col(8).level=1ws1.col(9).level=1ws2=wb.add_sheet(RowsandColumnsOutline)ws2.write_merge(1,1,1,5,test1,style)ws2.write_merge(2,2,1,4,test1,style)ws2.write_merge(3,3,1,3,test2,style)ws2.write_merge(4,4,1,4,test1,style)ws2.write_merge(5,5,1,4,test3,style

38、)ws2.write_merge(6,6,1,5,test1,style)ws2.write_merge(7,7,1,5,test4,style)ws2.write_merge(8,8,1,4,test1,style)ws2.write_merge(9,9,1,3,test5,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).

39、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=TruewsO.wnd_protect=Truews0.obj_protect=Truews0.scen_protect=Truews0.password=123456ws1.protect=Truews1.wnd_protect=Tru

40、ews1.obj_protect=Truews1.scen_protect=Truews1.password=abcdefghijws2.protect=Truews2.wnd_protect=Truews2.obj_protect=Truews2.scen_protect=Truews2.password=okwb.protect=Truewb.wnd_protect=Truewb.obj_protect=Truewb.save(protection.xls)31.1.5完整实例下面程序的write_xls对输出xls进行了一定封装，比较有实用意义。#WriteanXLSfilewithas

41、ingleworksheet,containing#aheadingrowandsomerowsofdata.importxlwtimportdatetimeezxf=xlwt.easyxfdefwrite_xls(file_name,sheet_name,headings,data,heading_xf,data_xfs):book=xlwt.Workbook()sheet=book.add_sheet(sheet_name)rowx=0forcolx,valueinenumerate(headings):sheet.write(rowx,colx,value,heading_xf)shee

42、t.set_panes_frozen(True)#frozenheadingsinsteadofsplitpanessheet.set_horz_split_pos(rowx+1)#ingeneral,freezeafterlastheadingrowsheet.set_remove_splits(True)#ifuserdoesunfreeze,dontleaveasplitthereforrowindata:rowx+=1forcolx,valueinenumerate(row):sheet.write(rowx,colx,value,data_xfscolx)book.save(file

43、_name)if_name_=_main_:importsysmkd=datetime.datehdngs=Date,StockCode,Quantity,UnitPrice,Value,Messagekinds=datetextintpricemoneytext.split()data=mkd(2007,7,1),ABC,1000,1.234567,1234.57,mkd(2007,12,31),XYZ,-100,4.654321,-465.43,Goodsreturned,+mkd(2008,6,30),PQRCD,100,2.345678,234.57,*100heading_xf=ez

44、xf(font:boldon;align:wrapon,vertcentre,horizcenter)kind_to_xf_map=date:ezxf(num_format_str=yyyy-mm-dd),int:ezxf(num_format_str=#,#0),money:ezxf(font:italicon;pattern:patternsolid,fore-colourgrey25,num_format_str=$#,#0.00),price:ezxf(num_format_str=#0.000000),text:ezxf(),博客：http:data_xfs=kind_to_xf_mapkforkinkindswrite_xls(xlwt_easyxf_simple_demo.xls,Demo,hdngs,data,heading_xf,data_xfs)#承接软件自动化实施与培训等gtalk:ouyangchongwu#qq37391319/#版权所有，转载刊登请来函联系#深圳测试自动化python项目接单群113938272深圳广州软件测试开发6089740#深圳地摊群66250781武冈洞口城步新宁乡情群49494279#自动化测试和python群组：on