xlwt模块
import xlwt
import xlrd
from xlutils. copy import copy
class ExcelWrite ( object ) :
def __init__ ( self, dir , file ) :
self. file_name = dir + file
def set_style ( self, name, colour_index, height, underline= False , italic= False , bold= False ) :
"""
:param name: 字体,如微软雅黑,Times New Roman等类型字体
:param colour_index: 字体颜色,如4
:param height: 字体大小
:param underline: 下划线
:param italic: 斜体
:param bold: 粗体
:return: 返回字体样式
"""
style = xlwt. XFStyle( )
font = xlwt. Font( )
font. name = name
font. colour_index = colour_index
font. height = height
font. underline = underline
font. italic = italic
font. bold = bold
style. font = font
return style
"""
按行插入数据
[["姓名", "年龄", "出生日期", "爱好"],["姓名", "年龄", "出生日期", "爱好"],["姓名", "年龄", "出生日期", "爱好"],["姓名", "年龄", "出生日期", "爱好"]]
"""
def write_excel_by_rows ( self, rows, sheet, style= None ) :
f = xlwt. Workbook( )
sheet = f. add_sheet( sheet, cell_overwrite_ok= True )
rows_length = len ( rows)
for i in range ( 0 , rows_length) :
row = rows[ i]
row_length = len ( row)
for j in range ( 0 , row_length) :
if style is None :
sheet. write( i, j, row[ j] )
else :
sheet. write( i, j, row[ j] , style)
f. save( self. file_name)
"""
按列插入数据
[["姓名", "年龄", "出生日期", "爱好"],["姓名", "年龄", "出生日期", "爱好"],["姓名", "年龄", "出生日期", "爱好"],["姓名", "年龄", "出生日期", "爱好"]]
"""
def write_excel_by_columns ( self, columns, sheet, style= None ) :
f = xlwt. Workbook( )
sheet = f. add_sheet( sheet, cell_overwrite_ok= True )
columns_length = len ( columns)
for i in range ( 0 , columns_length) :
column = columns[ i]
column_length = len ( column)
for j in range ( 0 , column_length) :
if style is None :
sheet. write( j, i, column[ j] )
else :
sheet. write( j, i, column[ j] , style)
f. save( self. file_name)
def append_value ( self, index_or_name, row, col, value) :
"""
:param index_or_name: 第几个表格或者表格的名字
:param row: 第几行-1
:param col: 第几列-1
:param value: 填入的值
"""
wb = xlrd. open_workbook( filename= self. file_name)
write_data = copy( wb)
write_data. get_sheet( index_or_name) . write( row, col, value)
write_data. save( self. file_name)
设置单元格宽度
sheet. col( 0 ) . width = 300
写入时间格式
import xlwt
import datetime
excel = xlwt. Workbook( encoding= 'utf-8' )
sheet = excel. add_sheet( 'xlwt_sheet1' )
style = xlwt. XFStyle( )
style. num_format_str = 'M/D/YY'
sheet. write( 2 , 0 , datetime. datetime. now( ) , style)
excel. save( 'C:/Users/ms/Desktop/test_xlwt.xls' )
date_time = datetime. datetime. strptime( '2013-08-28' , '%Y-%m-%d' )
sheet. write( 2 , 0 , date_time, style)
合并单元格
f = xlwt. Workbook( )
sheet1 = f. add_sheet( '学生' , cell_overwrite_ok= True )
sheet1. write_merge( 6 , 6 , 1 , 2 , '内容' , style)
x= 6 , h= 0 , y= 1 , w= 1
sheet1. insert_bitmap( img, x, y, x1, y1, scale_x= 0.8 , scale_y= 1 ) 同理