python-xlwt模塊詳解

1、python模塊介紹- xlwt 創(chuàng)建xls文件（excel）2013-06-24磁針石轉(zhuǎn)自：31.1 xlwt 實(shí)例131.1.1 創(chuàng)建簡(jiǎn)單的excel文件131.1.2 插入圖片2使用 insert_bitmap來(lái)插入圖片。331.1.3 設(shè)置樣式331.1.4 更多實(shí)例4輸出了紅色的”Test”，并在第3行包含了公式：4這里另有一個(gè)公式的實(shí)例：5日期格式的實(shí)例6下面展示了不同邊框和刪除樣式的字體：7下面展示了不同顏色的字體，可以作為顏色參考：7通過(guò)unicode輸出字符9凍結(jié)：10更多的公式：12保護(hù)實(shí)例：1331.1.5 完整實(shí)例1631.1 xlwt 實(shí)例31.1.1 創(chuàng)建簡(jiǎn)單的ex

2、cel文件下面例子，創(chuàng)建一個(gè)名為mini.xls的文件，它有一個(gè)空sheet：xlwt was here。from xlwt import *w = Workbook()ws = w.add_sheet(xlwt was here)w.save(mini.xls)Workbook類初始化時(shí)有encoding和style_compression參數(shù)。encoding，設(shè)置字符編碼，一般要這樣設(shè)置：w = Workbook(encoding=utf-8)，就可以在excel中輸出中文了。默認(rèn)是ascii。當(dāng)然要記得在文件頭部添加：#!/usr/bin/env python# -*- coding:

3、 utf-8 -*-style_compression表示是否壓縮，不常用。Workbook還有一些屬性：Owner設(shè)置文檔所有者。country_code：國(guó)家碼wnd_protect：窗口保護(hù)obj_protect：對(duì)象保護(hù)Protect：保護(hù)backup_on_save：保存時(shí)備份Hpos：橫坐標(biāo)Vpos：縱坐標(biāo)Width：寬度Height：高度active_sheet：活動(dòng)sheettab_width：tab寬度wnd_visible：窗口是否可見wnd_mini：窗口最小化hscroll_visible：橫向滾動(dòng)條是否可見。vscroll_visible：縱向滾動(dòng)條是否可見。tabs

4、_visible：tab是否可見。dates_1904：是否使用1904日期系統(tǒng)use_cell_values：?jiǎn)卧竦闹礵efault_style：默認(rèn)樣式colour_RGB：顏色比如設(shè)置國(guó)家碼：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_

5、sheetindex，setup_xcall，add_sheet_reference。31.1.2 插入圖片add_sheet 會(huì)返回一個(gè)Worksheet 類。創(chuàng)建的時(shí)候有可選參數(shù)cell_overwrite_ok，表示是否可以覆蓋單元格，其實(shí)是Worksheet實(shí)例化的一個(gè)參數(shù)，默認(rèn)值是False。Worksheet初始化的參數(shù)有: sheetname，parent_book，cell_overwrite_ok。Worksheet的屬性有：Row，Column，explicit_magn_setting（默認(rèn)False），visibility（默認(rèn)0），split_position_uni

6、ts_are_twips（默認(rèn)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。以上屬性類定義中。函數(shù)構(gòu)成的屬性有：name，parent（只讀），rows（只讀），cols（只讀），merged_ranges（只讀），bmp_rec（只讀），show_formulas，show_grid，show_headers，panes_fr

7、ozen，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_she

8、et，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_

9、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，

10、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來(lái)插入圖片。from xlwt import *，w = Workbook()ws = w.add_sheet(Image)ws.insert_bitmap(pyt

11、hon.bmp, 2, 2)ws.insert_bitmap(python.bmp, 10, 2)w.save(image.xls)31.1.3 設(shè)置樣式改變字體的高度:#!/usr/bin/envpython#-*- coding: utf-8 -*-#Copyright (C) 2005 Kiseliov Romanfromxlwt 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(

12、) style.font = fnt ws.write(i, 1, 武岡) ws.row(i).set_style(style)w.save(row_styles.xls) XFStyle用于設(shè)置字體樣式，有描述字符串num_format_str，字體font，居中alignment，邊界borders，模式pattern，保護(hù)protection等屬性。另外還可以不寫單元格，直接設(shè)置格式，比如：from pyExcelerator import *w= Workbook()ws= w.add_sheet(Hey, Dude)fori in range(6, 80): fnt = Font()

13、 fnt.height = i*20 style = XFStyle() style.font = fnt ws.row(i).set_style(style)w.save(row_styles_empty.xls) 設(shè)置列寬：ws.col(i).width = 0x0d00 + i31.1.4 更多實(shí)例輸出了紅色的”Test”，并在第3行包含了公式：importxlwtfromdatetime import datetimefont0= xlwt.Font()= Times New Romanfont0.colour_index= 2font0.bold= Truesty

14、le0= xlwt.XFStyle()style0.font= font0style1= xlwt.XFStyle()style1.num_format_str= D-MMM-YYwb= 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)這里另有一個(gè)公式的實(shí)例：

15、fromxlwt import ExcelFormulaParser, 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.898 * 1)/(1.80 + 2.898) + SIN(PI()/4)合并單元格的實(shí)例注意write_merge,1,2個(gè)參數(shù)表示行數(shù)，3,4的參數(shù)表示列數(shù)。fromxlwt import *

16、wb= Workbook()ws0= wb.add_sheet(sheet0)fnt= Font()= Arialfnt.colour_index= 4fnt.bold= Trueborders= Borders()borders.left= 6borders.right= 6borders.top= 6borders.bottom= 6style= XFStyle()style.font= fntstyle.borders = bordersws0.write_merge(3,3, 1, 5, test1, style)ws0.write_merge(4,10, 1, 5,

17、test2, style)ws0.col(1).width= 0x0d00wb.save(merged0.xls)日期格式的實(shí)例fromxlwt import *fromdatetime 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= 0forfmt in fmts: ws.write(i, 0,

18、 fmt) style = XFStyle() style.num_format_str = fmt ws.write(i, 4, datetime.now(), style) i += 1w.save(dates.xls)下面展示了不同邊框和刪除樣式的字體：fromxlwt import *font0= Font()= Times New Romanfont0.struck_out= Truefont0.bold= Truestyle0= XFStyle()style0.font= font0wb= Workbook()ws0= wb.add_sheet(0)ws0.wr

19、ite(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)下面展示了不同顏色的字體，可以作為顏

20、色參考：from xlwt import *font0 = Font() = Times New Romanfont0.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() = Arial fnt.colour_index = i fnt.outline = True

21、 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)超級(jí)鏈接的插入方法：fromxlwt import *f= Font()f.height= 20*72= Verdanaf.bold= Truef.underline= Font.UNDERLINE_DOUBLEf.colour_inde

22、x= 4h_style= XFStyle()h_style.font= fw= Workbook()ws= w.add_sheet(F)#NOTE: parameters are separated by semicolon!#n= HYPERLINKws.write_merge(1,1, 1, 10, Formula(n +(/pub/irs-pdf/f1000.pdf;f1000.pdf),h_style)ws.write_merge(2,2, 2, 25, Formula(n +(mailto:roman.kiseliov?subject=pyExcel

23、erator-feedback&Body=Hello,%20Roman!;pyExcelerator-feedback),h_style)w.save(hyperlinks.xls)通過(guò)unicode輸出字符 在沒有指定編碼的情況下，也可以通過(guò)unicode輸出字符，不過(guò)這樣比較費(fèi)勁，建議還是使用utf-8編碼：fromxlwt import *w= Workbook()ws1= w.add_sheet(uNGREEK SMALL LETTER ALPHANGREEK SMALL LETTER BETANGREEKSMALL LETTER GAMMA)ws1.write(0,0, uNGREE

24、K SMALL LETTER ALPHANGREEK SMALL LETTER BETANGREEK SMALLLETTER GAMMA)ws1.write(1,1, uNGREEK SMALL LETTER DELTAx = 1 + NGREEK SMALL LETTER DELTA)ws1.write(2,0,uAu2262u0391.) # RFC2152 examplews1.write(3,0,uHi Mom -u263a-!) # RFC2152 examplews1.write(4,0,uu65E5u672Cu8A9E) # RFC2152 examplews1.write(5,

25、0,uItem 3 is u00a31.) # RFC2152 examplews1.write(8,0,uNINTEGRAL) # RFC2152 examplew.add_sheet(uAu2262u0391.) # RFC2152 examplew.add_sheet(uHiMom -u263a-!) # RFC2152 exampleone_more_ws= w.add_sheet(uu65E5u672Cu8A9E) # RFC2152 examplew.add_sheet(uItem3 is u00a31.) # RFC2152 exampleone_more_ws.write(0,

26、0, uu2665u2665)w.add_sheet(uNGREEKSMALL LETTER ETA WITH TONOS)w.save(unicode1.xls)凍結(jié)：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/0

27、x10, 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= Truews1.horz_spli

28、t_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_s

29、plit_pos= 12ws4.horz_split_first_visible= 2ws6.vert_split_pos= 40ws4.vert_split_first_visible= 2w.save(panes.xls)各種數(shù)值格式：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

30、+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);_($*-

31、?_);_(_), _(* #,#0.00_);_(* (#,#0.00);_(*-?_);_(_), mm:ss, h:mm:ss, mm:ss.0, #0.0E+0, i= 0forfmt 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)更多的公式：fromxlwt import *w= Workbook()ws= w.add_sheet(F)ws.write(0,0

32、, 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.8780

33、789e-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+

34、C4/(C1+C2+C3+C4/(C1+C2+C3+C4)+C5)+C5)-20.3e-2)ws.write(5,3, Formula(C12)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(T

35、RUE)ws.write(11,2, Formula(FALSE)ws.write(12,3, Formula(IF(A1A2;3;hkjhjkhk)w.save(formulas.xls)保護(hù)實(shí)例：fromxlwt import *fnt= Font()= Arialfnt.colour_index= 4fnt.bold= Trueborders= Borders()borders.left= 6borders.right= 6borders.top= 6borders.bottom= 6style= XFStyle()style.font= fntstyle.borders

36、= 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)

37、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= w

38、b.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,

39、 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 Colum

40、ns 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)ws

41、2.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).

42、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= 1tect= Truews0.wnd_protect= Truews0.obj_protect= Truews0.scen_protect= Truews0.password= 123456tect= Truews1.wnd_protect= Truews1.obj_protect= Truews1.scen_protect=

43、Truews1.password= tect= Truews2.wnd_protect= Truews2.obj_protect= Truews2.scen_protect= Truews2.password= tect= Truewb.wnd_protect= Truewb.obj_protect= Truewb.save(protection.xls)31.1.5 完整實(shí)例下面程序的write_xls對(duì)輸出xls進(jìn)行了一定封裝，比較有實(shí)用意義。#Write an XLS file with a single worksheet, containing#a heading row and some rows of data.importxlwtimportdatetimeezxf= xlwt.easyxfdefwrite_xls(file_name,sheet_name, headings, data, h