python Xls文檔讀寫

標籤：語文   __name__   cin   lda   SM   tst   標題   color   info   

1.模組安裝

    

    

2.python 代碼

  1 import  xlrd  2 import  xlwt  3 import  datetime   4   5 def set_style(name,height,format,bold=False):  6     style = xlwt.XFStyle()  7     if  format.strip()!=‘‘:  8         style.num_format_str =format  9     font  = xlwt.Font() 10     font.name=name 11     font.bold=bold 12     font.color_index=4 13     font.height=height 14     alignment = xlwt.Alignment() 15     #HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED 16     alignment.horz = xlwt.Alignment.HORZ_CENTER 17     #VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED 18     alignment.vert = xlwt.Alignment.VERT_CENTER 19     style.alignment = alignment 20     style.font=font 21     return style 22      23 def set_colstyle(sheet,cindex): 24     col=sheet.col(cindex) 25     col.width =256*20 26     #col.height =100 27      28 def  writeXls(path): 29     wb = xlwt.Workbook() 30     sheet = wb.add_sheet(‘測試‘,cell_overwrite_ok=True) 31     set_colstyle(sheet,3) 32     #標題 33     heads=[‘姓名‘,‘學科‘,‘分數‘,‘日期‘] 34     for  h  in range(0,len(heads)): 35         sheet.write(0,h,heads[h],set_style(‘Arial‘,300,‘‘,True)) 36  37      38     #資料 39      40     sheet.write_merge(1,2,0,0,‘張三‘,set_style(‘Arial‘,300,‘‘,False)) 41     sheet.write(1,1,‘語文‘,set_style(‘Arial‘,240,‘‘,False)) 42     sheet.write(1,2,85,set_style(‘Arial‘,240,‘‘,False)) 43     sheet.write(1,3,datetime.date.today(),set_style(‘Arial‘,240,‘yyyy/mm/dd‘,False)) 44     sheet.write(2,1,‘數學‘,set_style(‘Arial‘,240,‘‘,False)) 45     sheet.write(2,2,85,set_style(‘Arial‘,240,‘‘,False)) 46     sheet.write(2,3,datetime.date.today(),set_style(‘Arial‘,240,‘yyyy/mm/dd‘,False)) 47  48     sheet.write_merge(3,4,0,0,‘李四‘,set_style(‘Arial‘,300,‘‘,False)) 49     sheet.write(3,1,‘語文‘,set_style(‘Arial‘,240,‘‘,False)) 50     sheet.write(3,2,95,set_style(‘Arial‘,240,‘‘,False)) 51     sheet.write(3,3,datetime.date.today(),set_style(‘Arial‘,240,‘yyyy/mm/dd‘,False)) 52     sheet.write(4,1,‘數學‘,set_style(‘Arial‘,240,‘‘,False)) 53     sheet.write(4,2,95,set_style(‘Arial‘,240,‘‘,False)) 54     sheet.write(4,3,datetime.date.today(),set_style(‘Arial‘,240,‘yyyy/mm/dd‘,False)) 55      56  57     wb.save(path) 58  59 def ismerge(sheet,merge,r,c): 60     #merge=sheet.merged_cells 61     for m in merge: 62        if r>=m[0] and r<m[1] and c==m[2]: 63            r=m[0] 64            c==m[2] 65            break 66          67     return r,c 68  69 def  readXls(path): 70     wb=xlrd.open_workbook(path,formatting_info=True) 71     #sheetname=wb.sheet_names()[0] 72     sheet=wb.sheet_by_index(0) 73     rows=sheet.nrows 74     cols=sheet.ncols 75     merge=sheet.merged_cells 76     #merged_cells返回的這四個參數的含義是：(row,row_range,col,col_range), 77     #其中[row,row_range)包括row,不包括row_range 78      79     print(‘--------------------------------------------------------------------‘) 80     for r in range(0,rows): 81         listStr = [] 82          83         for c in range(0,cols): 84             merg=ismerge(sheet,merge,r,c) 85             if (sheet.cell(merg[0],merg[1]).ctype==3): 86                 data_value=xlrd.xldate_as_tuple(sheet.cell_value(merg[0],merg[1]),wb.datemode) 87                 #print(datetime.date(*data_value[:3]).strftime(‘%Y/%m/%d‘)) 88                 listStr.append(datetime.date(*data_value[:3]).strftime(‘%Y/%m/%d‘)) 89             else: 90                 #print(sheet.cell_value(merg[0],merg[1])) 91                 listStr.append(sheet.cell_value(merg[0],merg[1])) 92             #print(sheet.cell(merg[0],merg[1]).value) 93             #print(sheet.cell(r,c).ctype) 94  95         print(‘ |\t‘.join(str(s) for s in listStr if s not in [None])) 96         print(‘--------------------------------------------------------------------‘) 97      98 if __name__ == ‘__main__‘: 99 100     #writeXls(‘H:\測試.xls‘)101     readXls(‘H:\測試.xls‘)102     103     104 105     106     107     

3.效果展示

 

python Xls文檔讀寫