读取excel:
#coding:utf-8
import xlrd
def read_excel():
workbook = xlrd.open_workbook(r'excel.xlsx')
lis = workbook.sheet_names()
for i in lis:
print i
sheet = workbook.sheet_by_index(0)
name = sheet.name
row = sheet.nrows
col = sheet.ncols
count = 0
while count<row:
num =0
while num<col:
value = sheet.cell_value(count,num)
if value:
print value
num += 1
count += 1
read_excel()
使用xlwt的python包操作Excelz写:
import xlwt
def maketitlestye():
style = xlwt.XFStyle() #创建style对象
alignment = xlwt.Alignment() #创建alignment用来设置单元格对齐方式
alignment.horz = xlwt.Alignment.HORZ_CENTER #设置水平对齐为居中
alignment.vert = xlwt.Alignment.VERT_CENTER# 设置垂直对齐为居中
style.alignment = alignment #加入到style中
fnt = xlwt.Font() #创建字体对象
fnt.name = u'微软雅黑' #字体
fnt.height = 210 #字体大小
style.font = fnt
fnt.bold = True #是否加粗
#pattern = xlwt.Pattern() #创建单元格的背景颜色
#pattern.pattern = xlwt.Pattern.SOLID_PATTERN
#pattern.pattern_fore_colour = 4 #4代表红色
#style.pattern = pattern
return style
def makelinestye(color=0,bold=False):
style = xlwt.XFStyle()
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment
fnt = xlwt.Font()
fnt.name = u'微软雅黑'
fnt.height = 200
style.font = fnt
style.font.colour_index=color
fnt.bold = bold
return style
创建Excel以及sheet:
book=xlwt.Workbook(encoding='utf-8',style_compression=0)
sheet=book.add_sheet('name',cell_overwrite_ok=True)
sheet2=book.add_sheet('name',cell_overwrite_ok=True)
设置style以及写入sheet::
style= maketitlestye()#之前创造的函数
basewith = 256*4
sheet.col(count).width = basewith * 4 #设置单元格大小
sheet.write(0,count,tx.decode('utf-8'),style)#写入第0行第count列
保存Excel:
book.save(r'PATH'.xls')