python Excel

不念不忘少年蓝@ 2022-05-17 03:22 127阅读 0赞

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