python Xls文档读写

1.模块安装

    

    

2.python 代码

import  xlrd
import  xlwt
import  datetime 

def set_style(name,height,format,bold=False):
    style = xlwt.XFStyle()
    if  format.strip()!='':
        style.num_format_str =format
    font  = xlwt.Font()
    font.name=name
    font.bold=bold
    font.color_index=4
    font.height=height
    alignment = xlwt.Alignment()
    #HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
    alignment.horz = xlwt.Alignment.HORZ_CENTER
    #VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
    alignment.vert = xlwt.Alignment.VERT_CENTER
    style.alignment = alignment
    style.font=font
    return style
    
def set_colstyle(sheet,cindex):
    col=sheet.col(cindex)
    col.width =256*20
    #col.height =100
    
def  writeXls(path):
    wb = xlwt.Workbook()
    sheet = wb.add_sheet('测试',cell_overwrite_ok=True)
    set_colstyle(sheet,3)
    #标题
    heads=['姓名','学科','分数','日期']
    for  h  in range(0,len(heads)):
        sheet.write(0,h,heads[h],set_style('Arial',300,'',True))

    
    #数据
    
    sheet.write_merge(1,2,0,0,'张三',set_style('Arial',300,'',False))
    sheet.write(1,1,'语文',set_style('Arial',240,'',False))
    sheet.write(1,2,85,set_style('Arial',240,'',False))
    sheet.write(1,3,datetime.date.today(),set_style('Arial',240,'yyyy/mm/dd',False))
    sheet.write(2,1,'数学',set_style('Arial',240,'',False))
    sheet.write(2,2,85,set_style('Arial',240,'',False))
    sheet.write(2,3,datetime.date.today(),set_style('Arial',240,'yyyy/mm/dd',False))

    sheet.write_merge(3,4,0,0,'李四',set_style('Arial',300,'',False))
    sheet.write(3,1,'语文',set_style('Arial',240,'',False))
    sheet.write(3,2,95,set_style('Arial',240,'',False))
    sheet.write(3,3,datetime.date.today(),set_style('Arial',240,'yyyy/mm/dd',False))
    sheet.write(4,1,'数学',set_style('Arial',240,'',False))
    sheet.write(4,2,95,set_style('Arial',240,'',False))
    sheet.write(4,3,datetime.date.today(),set_style('Arial',240,'yyyy/mm/dd',False))
    

    wb.save(path)

def ismerge(sheet,merge,r,c):
    #merge=sheet.merged_cells
    for m in merge:
       if r>=m[0] and r<m[1] and c==m[2]:
           r=m[0]
           c==m[2]
           break
        
    return r,c

def  readXls(path):
    wb=xlrd.open_workbook(path,formatting_info=True)
    #sheetname=wb.sheet_names()[0]
    sheet=wb.sheet_by_index(0)
    rows=sheet.nrows
    cols=sheet.ncols
    merge=sheet.merged_cells
    #merged_cells返回的这四个参数的含义是：(row,row_range,col,col_range),
    #其中[row,row_range)包括row,不包括row_range
    
    print('--------------------------------------------------------------------')
    for r in range(0,rows):
        listStr = []
        
        for c in range(0,cols):
            merg=ismerge(sheet,merge,r,c)
            if (sheet.cell(merg[0],merg[1]).ctype==3):
                data_value=xlrd.xldate_as_tuple(sheet.cell_value(merg[0],merg[1]),wb.datemode)
                #print(datetime.date(*data_value[:3]).strftime('%Y/%m/%d'))
                listStr.append(datetime.date(*data_value[:3]).strftime('%Y/%m/%d'))
            else:
                #print(sheet.cell_value(merg[0],merg[1]))
                listStr.append(sheet.cell_value(merg[0],merg[1]))
            #print(sheet.cell(merg[0],merg[1]).value)
            #print(sheet.cell(r,c).ctype)

        print(' |	'.join(str(s) for s in listStr if s not in [None]))
        print('--------------------------------------------------------------------')
    
if __name__ == '__main__':

    #writeXls('H:测试.xls')
    readXls('H:测试.xls')
    
    

    
    
    

3.效果展示