python Excel

读取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')