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(' |\t'.join(str(s) for s in listStr if s not in [None]))

         print('--------------------------------------------------------------------')

 if __name__ == '__main__':

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

     readXls('H:\测试.xls')