本文实例讲述了Python简单读写Xls格式文档的方法。分享给大家供大家参考,具体如下:
1. 模块安装
使用pip install
命令安装,
即:
1
|
pip install xlrd
|
1
|
pip install xlwt
|
如下图:
2. python 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
|
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' )
|
3.效果展示
希望本文所述对大家Python程序设计有所帮助。
原文链接:https://www.cnblogs.com/linsu/p/8960766.html