當前位置：首頁 > 编程语言 > python >内容正文

python

python模块介绍- xlwt 创建xls文件（excel）

發(fā)布時間：2023/11/29 python 34 豆豆

生活随笔收集整理的這篇文章主要介紹了 python模块介绍- xlwt 创建xls文件（excel）小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

python模塊介紹- xlwt 創(chuàng)建xls文件（excel）

2013-06-24磁針石

#承接軟件自動化實施與培訓等gtalk：ouyangchongwu#gmail.comqq 37391319 博客:http://blog.csdn.net/oychw

# 深圳測試自動化python項目接單群113938272深圳廣州軟件測試開發(fā)6089740

#深圳地攤群 66250781武岡洞口城步新寧鄉(xiāng)情群49494279

#自動化測試和python群組： http://groups.google.com/group/automation_testing_python

#參考資料：《The Python Standard Library by Example2011》

#https://pypi.python.org/pypi/xlwt/0.7.5

#http://www.python-excel.org/

Xlrd是跨平臺的excel生成模塊。

31.1 xlwt– 實例

31.1.1 創(chuàng)建簡單的excel文件

下面例子，創(chuàng)建一個名為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參數(shù)。

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日期系統(tǒng)

use_cell_values：單元格的值

default_style：默認樣式

colour_RGB：顏色

比如設置國家碼：

fromxlwt 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 類。創(chuàng)建的時候有可選參數(shù)cell_overwrite_ok，表示是否可以覆蓋單元格，其實是Worksheet實例化的一個參數(shù)，默認值是False。

Worksheet初始化的參數(shù)有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。以上屬性類定義中。

函數(shù)構成的屬性有：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 -*-

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等屬性。另外還可以不寫單元格，直接設置格式，比如：

frompyExcelerator 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()

font0.name= '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))*

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個參數(shù)表示行數(shù)，3,4的參數(shù)表示列數(shù)。

fromxlwt import *

wb= Workbook()

ws0= wb.add_sheet('sheet0')

fnt= Font()

fnt.name= '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()

font0.name= '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()

font0.name = '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()

??? 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')

超級鏈接的插入方法：

fromxlwt import *

f= Font()

f.height= 20*72

f.name= '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 +'("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編碼：

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')

各種數(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+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()

fnt.name= '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)

參考資料：

總結

以上是生活随笔為你收集整理的python模块介绍- xlwt 创建xls文件（excel）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。