做网站没什么用啊老师别人强,php源码项目门户网站开发,报价表制作,seo标题优化的方法python模块介绍- xlwt 创建xls文件#xff08;excel#xff09; 2013-06-24磁针石 #承接软件自动化实施与培训等gtalk#xff1a;ouyangchongwu#gmail.comqq 37391319 博客:http://blog.csdn.net/oychw #版权所有#xff0c;转载刊登请来函联系 # 深圳测试自动化python项目…python模块介绍- xlwt 创建xls文件excel 2013-06-24磁针石 #承接软件自动化实施与培训等gtalkouyangchongwu#gmail.comqq 37391319 博客:http://blog.csdn.net/oychw #版权所有转载刊登请来函联系 # 深圳测试自动化python项目接单群113938272深圳广州软件测试开发6089740 #深圳地摊群 66250781武冈洞口城步新宁乡情群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 创建简单的excel文件 下面例子创建一个名为mini.xls的文件它有一个空sheetxlwt 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(encodingutf-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_widthtab宽度 wnd_visible窗口是否可见 wnd_mini窗口最小化 hscroll_visible横向滚动条是否可见。 vscroll_visible纵向滚动条是否可见。 tabs_visibletab是否可见。 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_styleadd_fontadd_strdel_strstr_indexadd_rtrt_indexadd_sheetget_sheetraise_bad_sheetnameconvert_sheetindexsetup_xcalladd_sheet_reference。 31.1.2 插入图片 add_sheet 会返回一个Worksheet 类。创建的时候有可选参数cell_overwrite_ok表示是否可以覆盖单元格其实是Worksheet实例化的一个参数默认值是False。 Worksheet初始化的参数有sheetnameparent_bookcell_overwrite_ok。 Worksheet的属性有RowColumnexplicit_magn_setting默认Falsevisibility默认0split_position_units_are_twips默认Falserow_default_height_mismatchrow_default_hiddenrow_default_space_aboverow_default_space_belowlast_used_rowfirst_used_rowlast_used_colrow_tempfile。以上属性类定义中。 函数构成的属性有nameparent只读rows只读cols只读merged_ranges只读bmp_rec只读show_formulasshow_gridshow_headerspanes_frozenauto_colour_gridcols_right_to_leftshow_outlineremove_splitsselectedsheet_visiblepage_previewfirst_visible_rowfirst_visible_colgrid_colourpreview_magnnormal_magnscl_magnvert_split_poshorz_split_posvert_split_first_visiblehorz_split_first_visibleshow_auto_page_breaksdialogue_sheetauto_style_outlineoutline_belowoutline_rightfit_num_pagesshow_row_outlineshow_col_outlinealt_expr_evalalt_formula_entriesrow_default_heightcol_default_widthcalc_modecalc_countRC_ref_modeiterations_ondeltasave_recalcprint_headersprint_gridvert_page_breakshorz_page_breaksheader_strfooter_strprint_centered_vertprint_centered_horzleft_marginright_margintop_marginbottom_marginpaper_size_codeprint_scalingstart_page_numberfit_width_to_pagesfit_height_to_pagesprint_in_rowsportraitprint_colourprint_draftprint_notesprint_notes_at_endprint_omit_errorsprint_hresprint_vresheader_marginfooter_margincopies_numwnd_protectobj_protectprotectscen_protectpassword。 方法有get_parentwritewrite_rich_textmergewrite_mergeinsert_bitmapcolrowrow_heightcol_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(encodingutf-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(A3B3)) 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.kiseliovgmail.com?subjectpyExcelerator-feedbackBodyHello,%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,uA\u2262\u0391.) # RFC2152 example ws1.write(3,0,uHi Mom -\u263a-!) # RFC2152 example ws1.write(4,0,u\u65E5\u672C\u8A9E) # RFC2152 example ws1.write(5,0,uItem 3 is \u00a31.) # RFC2152 example ws1.write(8,0,u\N{INTEGRAL}) # RFC2152 example w.add_sheet(uA\u2262\u0391.) # RFC2152 example w.add_sheet(uHiMom -\u263a-!) # RFC2152 example one_more_ws w.add_sheet(u\u65E5\u672C\u8A9E) # RFC2152 example w.add_sheet(uItem3 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.00E00, # ?/?, # ??/??, 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.0E0, ] 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(-(11))) ws.write(1,0, Formula(-(11)/(-2-2))) ws.write(2,0, Formula(-(134.87807891))) ws.write(3,0, Formula(-(134.8780789e-101))) ws.write(4,0, Formula(-1/(11)9344)) ws.write(0,1, Formula(-(11))) ws.write(1,1, Formula(-(11)/(-2-2))) ws.write(2,1, Formula(-(134.87807891))) ws.write(3,1, Formula(-(134.8780789e-101))) ws.write(4,1, Formula(-1/(11)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(C1C2C3C4C5/(C1C2C3C4/(C1C2C3C4/(C1C2C3C4)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(A1A2;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(rowx1) # ingeneral, freeze after last heading row sheet.set_remove_splits(True) # if userdoes unfreeze, dont 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_stryyyy-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) 参考资料
