xlwt 模块操作excel

1、xlwt 基本用法

import xlwt

#1 新建文件

new_file = open('test.xls', 'w')

new_file.close()

#2 创建工作簿

wookbook = xlwt.Workbook(encoding = 'utf-8')  

#3 添加sheet页

sheet1 = wookbook.add_sheet('sheet1_name', cell_overwrite_ok='True') 

#4 样式

style= xlwt.easyxf('pattern: pattern solid, fore_colour 0x31; font: bold on;alignment:HORZ CENTER;'

                         'borders:left 1,right 1,top 1,bottom 1,bottom_colour 0x3A') 

#5 写入数据table.write(行,列,value)

sheet1.write(0, 0, '需求编号', style) 

#6 合并单元格写入数据

sheet1.write_merge(1, 1, 1,3,'合并单元格', style)

#7 写入公式

sheet1.write(3, 2, xlwt.Formula("SUM(C1:C2)") 

#8 写入一个超链接

worksheet.write(0, 0, xlwt.Formula('HYPERLINK("http://www.google.com";"Google")'))

#9 插入图片

path_py = "\\images\python.bmp" #读取插入图片以.py运行时路径，images和.py在同一目录下

path_exe = "\\images\python.bmp"   #读取插入图片以.exe运行时路径，.exe可以移到其他任意目录下运行但images和.exe在同一目录下

path = cur_file_dir(path_py,path_exe)   #获取文件的相对路径

filename = path                         #检测当前目录下是否有python.bmp图片，

if os.path.exists(filename):

    print u'python.bmp图片存在'

else：

    print u'python.bmp图片不存在'

sheet.insert_bitmap(path, 2, 9)         #插入一个图片

#10 保存

wookbook.save(new_file)

2、样式设置

#1 赋值style为XFStyle()，初始化样式

style = xlwt.XFStyle()     
 

#2 设置单元格背景颜色

pattern = xlwt.Pattern()       # 创建一个模式

pattern.pattern = Pattern.SOLID_PATTERN     # 设置其模式为实型

pattern.pattern_fore_colour = 5 #设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta....

style.pattern = pattern  #将赋值好的模式参数导入Style   
 

#3 字体样式

for i in range(0x00,0xff):   # 设置单元格内字体样式

    fnt = xlwt.Font()            # 创建一个文本格式，包括字体、字号和颜色样式特性

    fnt.name = '微软雅黑'    # 设置其字体为微软雅黑

    fnt.colour_index = i      # 设置其字体颜色

    fnt.struck_out =True    # 删除线

    fnt.underline = True # 下划线

    font.italic = True # 斜体字

    fnt.bold = True         # 加粗

    style.font = fnt          #将赋值好的模式参数导入Style  
 

#4 边框设置

borders = xlwt.Borders()  # Create Borders

borders.left = xlwt.Borders.DASHED  # DASHED虚线  NO_LINE没有  THIN实线  #May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.

borders.right = xlwt.Borders.DASHED

borders.top = xlwt.Borders.DASHED

borders.bottom = xlwt.Borders.DASHED

borders.left_colour = 0x40

borders.right_colour = 0x40

borders.top_colour = 0x40

borders.bottom_colour = 0x40

style.borders = borders  #将赋值好的模式参数导入Style      
 

#5 设置单元格的宽度

sheet1.col(2).width = (10 * 367)

#6 设置单元格内容的对其方式

alignment = xlwt.Alignment()

alignment.horz = xlwt.Alignment.HORZ_CENTER # 可以为: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED

alignment.vert = xlwt.Alignment.VERT_CENTER # 可以为: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED

style.alignment = alignment #将赋值好的模式参数导入Style

#7 输入一个日期到单元格:

style.num_format_str = 'M/D/YY' # 其它:D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0

worksheet.write(0, 0, datetime.datetime.now(), style)

xlwt

xlwt 模块 操作excel

xlwt 模块操作excel