沈阳新闻网,沈阳信息港,沈阳门户网站

当前位置: 主页 > 沈阳生活 >

xlwt 创建xls文件(excel)

时间:2018-04-17 17:04来源:网络整理 作者:我的网站
# 深圳测试自动化python项目接单群113938272深圳广州软件测试开发6089740 #深圳地摊群 66250781武冈洞口城步新宁乡情群49494279 #自动化测试和python群组: #参考资料:《The Python Standard Library by Example2011》 #https://pypi.python.org/pypi/xlw

# 深圳测试自动化python项目接单群113938272深圳广州软件测试开发6089740

#深圳地摊群 66250781武冈洞口城步新宁乡情群49494279

#自动化测试和python群组:

#参考资料:《The Python Standard Library by Example2011》

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

#

 

 

Xlrd是跨平台的excel生成 ?。

31 .1 xlwt– 实例 31 .1 .1 创建简单的excel文件

下面例子 ,创建一个名为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参数。

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日期系统

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 类。创建的时候有可选参数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来插入图片。

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 -*-

#Copyright (C) 2005 Kiseliov Roman

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个参数表示行数,3 ,4的参数表示列数。

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 [email protected] .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')

各种数值格式:

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

#p#分页标题#e#

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)

------分隔线----------------------------
栏目列表
推荐内容