xlrd-读取xls和xlsx格式的excel⽂件
sheet相关代码：
1import xlrd
2 workbook=xlrd.open_workbook('1224.xlsx',on_demand=True)#打开⽂件
3 sheet_name=workbook.sheet_names() #所有sheet的名字
4print(sheet_name)
5print("*"*100)
6 sheets=workbook.sheets()#返回可迭代的sheets对象
7for i ,sheet in enumerate(sheets):
8print("the sheet{0}\'s sheetname is {1}".format(i+1,sheet.name))
9print(workbook.nsheets)#获取sheet的个数
10print("*"*100)
11print(sheets[0].name)
12print("*"*100)
13print(workbook.sheet_by_index(3).name)
14print("*"*100)
15print(workbook.sheet_by_name('Index').number)
16#注意：work.sheet_by_name和index获得的是对象
17print(workbook.sheet_by_name('Index'))
18print(workbook.sheet_by_index(3))
结果：
['TemplateInfo', 'Index', 'GBssFunction', 'GSiteBaBandShare', 'GMocnArea', 'GBssIftaMa', 'GGsmCell', 'GCellIfta', 'GHandoverControl', 'GPsHandoverControl']
****************************************************************************************************
the sheet1's sheetname is TemplateInfo
the sheet2's sheetname is Index
the sheet3's sheetname is GBssFunction
the sheet4's sheetname is GSiteBaBandShare
the sheet5's sheetname is GMocnArea
the sheet6's sheetname is GBssIftaMa
the sheet7's sheetname is GGsmCell
the sheet8's sheetname is GCellIfta
the sheet9's sheetname is GHandoverControl
the sheet10's sheetname is GPsHandoverControl
10
****************************************************************************************************
TemplateInfo
****************************************************************************************************
GSiteBaBandShare
****************************************************************************************************
1
<xlrd.sheet.Sheet object at 0x02F2ECB0>
<xlrd.sheet.Sheet object at 0x02F268B0>
Process finished with exit code 0
2.⾏列相关代码：
5while i <workbook.nsheets:
6 sheet_name=workbook .sheet_by_index(i).name
7 nrows=workbook.sheet_by_index(i).nrows
8 ncols=workbook.sheet_by_index(i).ncols
9print("{0} has {1} rows and {2} cols".format(sheet_name,nrows,ncols))
10 i+=1
11print("*"*100)
12print(workbook .sheet_by_index(5).col(5))
13print("*"*100)
14print(workbook.sheet_by_index(5).row_values(1))
15print("*"*100)
16print(workbook.sheet_by_index(5).col_values(1))
结果：
C:\Urs\zte\AppData\Local\Programs\Python\ C:/Urs/zte/PycharmProjects/radioparametercheck/workbook.py TemplateInfo has 6 rows and 2 cols
Index has 31 rows and 256 cols
GBssFunction has 6 rows and 164 cols
GSiteBaBandShare has 9 rows and 169 cols
GMocnArea has 5 rows and 8 cols
GBssIftaMa has 5 rows and 6 cols书籍封面
GGsmCell has 788 rows and 167 cols
GCellIfta has 788 rows and 7 cols
GHandoverControl has 788 rows and 166 cols
GPsHandoverControl has 788 rows and 9 cols
****************************************************************************************************
[text:'MaArfcnList', text:'Frequency List', text:'long[16][0..1023]', text:'Arfcn of MAARFCNLIST is parated by micolon', text:'R-W-I']
****************************************************************************************************
['RESULT', 'Modification Indication', 'Managed Element Object ID', 'IFTAMA ID', 'Frequency Band of IftaMa', 'Frequency List']
****************************************************************************************************
['MODIND', 'Modification Indication', 'A,D,M,P', 'A:Add, D:Delete, M:Modify, P:Pass', '']
Process finished with exit code 0
单元格的值：
5 sheet_name = workbook.sheet_by_index(5).name
6 nrows = workbook.sheet_by_index(5).nrows
7 ncols = workbook.sheet_by_index(5).ncols
8print(nrows,ncols)
9while i <nrows:
10 j=0
11while j <ncols:
郁闷是什么意思啊
12print(workbook.sheet_by_index(5).cell(i,j),end="")
13 j+=1
14print("")
15 i += 1
16print("*"*100)
17print(workbook.sheet_by_index(5).cell(4,3))
18print("*"*100)
19print(workbook.sheet_by_index(5).cell(4,3).value)
三不伤害
20print("*"*100)
21print(workbook.sheet_by_index(5).cell_value(4,3))
22print("*"*100)
23print(workbook.sheet_by_index(5).row(4)[3].value)
24print("*"*100)
25print(workbook.sheet_by_index(5).col(3)[4].value)
结果：
C:\Urs\zte\AppData\Local\Programs\Python\ C:/Urs/zte/PycharmProjects/radioparametercheck/workbook.py
5 6
text:'RESULT'text:'MODIND'text:'MEID'text:'GBssIftaMaId'text:'FreqBand'text:'MaArfcnList'
text:'RESULT'text:'Modification Indication'text:'Managed Element Object ID'text:'IFTAMA ID'text:'Frequency Band of IftaMa'text:'Frequency List' text:'S:Success\nF:Fail'text:'A,D,M,P'text:'long:[1..4095]'text:'long:[1..1]'text:'0:GSM900\\(0 .. 124，975 .. 1023\\)\n1:EGSM900\\(0 .. 124，975 .. 1023\\)\n2:DCS1800\\(512 .. 885\\)\n3:RGSM\\(0 .. 124，955 .. 1023\\)\n4:PCS1900\\(512 .. 810\\)\n7:GSM850\\(128..251\\)'text:'long[16][0..1023]' text:''text:'A:Add, D:Delete, M:Modify, P:Pass'text:''text:''text:''text:'Arfcn of MAARFCNLIST is parated by micolon'
text:''text:''text:'Primary Key'text:'Primary Key'text:'R-W-I'text:'R-W-I'
****************************************************************************************************
消息范文
text:'Primary Key'
****************************************************************************************************
Primary Key
****************************************************************************************************
Primary Key
****************************************************************************************************
Primary Key
****************************************************************************************************
Primary Key
Process finished with exit code 0
多⾏多列，制定⾏列切⽚的值：
1import xlrd
2 workbook=xlrd.open_workbook('1224.xlsx',on_demand=True)#打开⽂件
3 nrows = workbook.sheet_by_index(5).nrows
4 ncols = workbook.sheet_by_index(5).ncols
5print(nrows,ncols)
6 sheet=workbook.sheets()[5]
7#row_values(rowx,start_colx=0,end_colx=None)
w_values(4,3,5))
9#col_values(colx,start_rowx=0,end_rowx=None)
520的图片l_values(4,3,5))
结果：
C:\Urs\zte\AppData\Local\Programs\Python\ C:/Urs/zte/PycharmProjects/radioparametercheck/workbook.py
5 6
['Primary Key', 'R-W-I']
['', 'R-W-I']
Process finished with exit code 0
读取多⾏多列的值：
1def get_rows(sheet,start_rowx=0,end_rows=0):
2 rows_value=[w_values(rowx) for rowx in range (start_rowx,end_rows)]
3return rows_value
4print(get_rows(sheet,4,5))
5def get_cols(sheet,start_colx=0,end_colx=0):
6 cols_value=[l_values(colx) for colx in range (start_colx,end_colx)]
7return cols_value
8print(get_cols(sheet,1,3))
结果：
C:\Urs\zte\AppData\Local\Programs\Python\ C:/Urs/zte/PycharmProjects/radioparametercheck/workbook.py
5 6
[['', '', 'Primary Key', 'Primary Key', 'R-W-I', 'R-W-I']]
[['MODIND', 'Modification Indication', 'A,D,M,P', 'A:Add, D:Delete, M:Modify, P:Pass', ''], ['MEID', 'Managed Element Object ID', 'long:[1..4095]', '', 'Primary Key']]
Process finished with exit code 0
获得连续区域的值：
1import xlrd
2 workbook=xlrd.open_workbook('1224.xlsx',on_demand=True)#打开⽂件
3 nrows = workbook.sheet_by_index(5).nrows
4 ncols = workbook.sheet_by_index(5).ncols
5print(nrows,ncols)
6 sheet=workbook.sheets()[5]
7
8def get_range(sheet,start_rowx=0,end_rowx=0,start_colx=0,end_colx=0):
9 range_values=[w_values(rowx,start_colx,end_colx)for rowx in range(start_rowx,end_rowx)]
10return range_values
11print(get_range(sheet,0,1,1,3))
结果：
C:\Urs\zte\AppData\Local\Programs\Python\ C:/Urs/zte/PycharmProjects/radioparametercheck/workbook.py
5 6
[['MODIND', 'MEID']]
Process finished with exit code 0
读取单个sheet页的值
1import xlrd
2def readsheet(filename,sheetindex=0,sheetname=None):
3# 读取Excel⽂件的⼀个sheet页
4 workbook=xlrd.open_workbook(filename,on_demand=True)
5if sheetname is not None:
6 sheet=workbook.sheet_by_name(sheetname)
7el:
8 sheet=workbook.sheet_by_index(sheetindex)
9 rows=[w_values(row)for row in ws)]
10return rows
11 ret=readsheet('1224.xlsx',sheetindex=0)
12print(ret)
结果：
C:\Urs\zte\AppData\Local\Programs\Python\ C:/Urs/zte/PycharmProjects/radioparametercheck/workbook.py [['NE Type:', 'Multi-mode Controller'], ['Template Type:', 'Plan'], ['Template Version:', 'V12.11'], ['Data Type:', 'gsm_radio'], ['', ''], ['', '']]
Process finished with exit code 0
读取整个excel
1import xlrd
2def readbook(filename):
3"""
4读取整个Excel⽂件
5 :param filename:
6 :return: sheet名字列表
7字典类型的数据 bookdata={sheetname1：row1，sheetname2：row2}
8"""
9 workbook=xlrd.open_workbook(filename,on_demand=True)
10 sheetnames=workbook.sheet_names()
总有属于我的季节
课题论证11 bookdata={}
12for sheetname in sheetnames:
13 sheet=workbook.sheet_by_name(sheetname)
14 rows=[w_values(row)for row in ws)]
15 bookdata.tdefault(sheetname,rows)优秀团员个人事迹
16return sheetnames,bookdata
17print(readbook("1224.xlsx"))
