1. xlrd模块简介
1)xlrd是可用于读取excel表格数据(不支持写操作,写操作需要xlwt模块实现)
2)支持 xlsx和xls 格式的excel表格(不支持csv文件,csv文件可用python自带的csv模块操作)
3)模块安装方式:pip3 install xlrd
4)模块导入方式: import xlrd
2. xlrd模块的使用
在实操中对xlrd对各个函数进行说明
用来实操的表格info.xlsx分为Sheet1和Sheet2两个sheet页,且只有Sheet1页中有数据,数据格式如下:
2.1 单元格中数据类型
python获取单元格中数据类型后,打印出来对并不是数据类型,而是用不同对数字映射对应对数据类型;
数据类型和数字对映射关系如下:
# 0 empty,
# 1 string(text),
# 2 number,
# 3 date,
# 4 boolean,
# 5 error,
# 6 blank(空白表格)
2.2 导入xlrd模块
import xlrd
2.3 打开一个xlsx文件(实例化)
方法
data = xlrd.open_workbook(fileName)
实操
import os
fileName = 'info.xlsx'
dirPath = os.path.dirname(os.path.abspath(__file__))
filePath = os.path.join(dirPath,fileName)
x1 = xlrd.open_workbook(filePath) # 打开xlsx文件;打开workbook,获取一个Book对象
2.4 常用函数
常用函数分为四大类:sheet页操作
,行操作
,列操作
,单元格操作
1)对sheet页的操作
方法
table = data.sheets()[0] #通过索引顺序获取
table = data.sheet_by_index(sheet_indx)) #通过索引顺序获取
table = data.sheet_by_name(sheet_name)#通过名称获取
以上三个函数都会返回一个xlrd.sheet.Sheet()对象
names = data.sheet_names() #返回book中所有工作表的名字
data.sheet_loaded(sheet_name or indx) # 检查某个sheet是否导入完毕
实操
sheetName = x1.sheet_names() # 获取sheet页名称(以列表形式)
print(sheetName) # ['Sheet1', 'Sheet2']
all_sheetsObj = x1.sheets() # 获取所有sheet对象(以列表形式)
print(all_sheetsObj) # [<xlrd.sheet.Sheet object at 0x105956a58>, <xlrd.sheet.Sheet object at 0x105956eb8>]
sheetObj_01 = x1.sheet_by_index(0) # 按照索引获取sheet对象
print(sheetObj_01) # <xlrd.sheet.Sheet object at 0x105a559e8>
sheetObj_02 = x1.sheet_by_name('Sheet1') # 按照sheet页名获取sheet对象
print(sheetObj_02) # <xlrd.sheet.Sheet object at 0x105254a90>
2)对行的操作
方法
nrows = table.nrows #获取该sheet中的有效行数
table.row(rowx) #返回由该行中所有的单元格对象组成的列表
table.row_slice(rowx) #返回由该列中所有的单元格对象组成的列表
table.row_types(rowx, start_colx=0, end_colx=None) #返回由该行中所有单元格的数据类型组成的列表
table.row_values(rowx, start_colx=0, end_colx=None) #返回由该行中所有单元格的数据组成的列表
table.row_len(rowx) #返回该列的有效单元格长度
实操
## 3.1 对行的操作
rowsNum = sheetObj_01.nrows # 获取该sheet页数据的行数
print(rowsNum) # 5
obj_row0 = sheetObj_01.row(0) # 获取该sheet页中第n+1行对象,返回值为列表,格式为 单元类型:单元数据
print(obj_row0) # [text:'Host', text:'User', text:'PassWord', text:'Port']
obj_row0_1_3 = sheetObj_01.row_slice(0,start_colx=1,end_colx=3) # 获取该sheet页第n+1行,索引从1-3(不包含3)的单元格对象,返回值为列表,格式为:单元类型:单元数据
print(obj_row0_1_3) # [text:'User', text:'PassWord']
data_row0 = sheetObj_01.row_values(0) # 获取该sheet页中第n+1行第数据,返回值为列表
print(data_row0) # ['Host', 'User', 'PassWord', 'Port']
data_row0_1_3 = sheetObj_01.row_values(0,1,3) # 获取该sheet页中第n+1行,索引从1-3(不包含3)的数据,返回值为列表
print(data_row0_1_3) # ['User', 'PassWord']
data_row0_1_3_type = sheetObj_01.row_types(0,1,3) # 获取该sheet页中第n+1行,索引从1-3(不包含3)的数据类型,返回值为列表
print(data_row0_1_3_type,type(data_row0_1_3_type)) # array('B', [1, 1]) <class 'array.array'>
row_len = sheetObj_01.row_len(0) # 返回n+1行有多少列
print(row_len) # 4
3) 对列的操作
方法
ncols = table.ncols #获取列表的有效列数
table.col(colx, start_rowx=0, end_rowx=None) #返回由该列中所有的单元格对象组成的列表
table.col_slice(colx, start_rowx=0, end_rowx=None) #返回由该列中所有的单元格对象组成的列表
table.col_types(colx, start_rowx=0, end_rowx=None) #返回由该列中所有单元格的数据类型组成的列表
table.col_values(colx, start_rowx=0, end_rowx=None) #返回由该列中所有单元格的数据组成的列表
实操
colsNum = sheetObj_01.ncols # 获取该sheet页对列数
print(colsNum) # 4
obj_col0 = sheetObj_01.col(0) # 获取该sheet页中第n+1列对象,返回值为列表,格式为 单元类型:单元数据
print(obj_col0) # [text:'Host', text:'192.168.1.1', text:'192.168.2.1', text:'192.168.3.1', text:'192.168.4.1']
obj_col0_1_3 = sheetObj_01.col_slice(0,start_rowx=1,end_rowx=3) # 获取该sheet页第n+1列,索引从1-3(不包含3)的单元格对象,返回值为列表,格式为:单元类型:单元数据
print(obj_col0_1_3) # [text:'192.168.1.1', text:'192.168.2.1']
data_col0 = sheetObj_01.col_values(0) # 获取该sheet页中第n+1行第数据,返回值为列表
print(data_col0) # ['Host', '192.168.1.1', '192.168.2.1', '192.168.3.1', '192.168.4.1']
data_col0_1_3 = sheetObj_01.col_values(0,start_rowx=1,end_rowx=3) # 获取该sheet页中第n+1列,索引从1-3(不包含3)的数据,返回值为列表
print(data_col0_1_3) # ['192.168.1.1', '192.168.2.1']
data_col0_1_3_type = sheetObj_01.col_types(0,1,3) # 获取该sheet页中第n+1列,索引从1-3(不包含3)的数据类型,返回值为列表
print(data_col0_1_3_type) # [1, 1]
4) 对单元格的操作
方法
table.cell(rowx,colx) #返回单元格对象
table.cell_type(rowx,colx) #返回单元格中的数据类型
table.cell_value(rowx,colx) #返回单元格中的数据
table.cell_xf_index(rowx, colx) # 在网上查找,没有找到该方法对用法及目的,暂时搁浅
实操
obj_cell_1_1 = sheetObj_01.cell(1,1) # 返回指定单元格对象;格式为 数据类型:数据
print(obj_cell_1_1) # text:'root'
data_cell_1_1 = sheetObj_01.cell_value(1,1) # 返回指定单元格中的数据
print(data_cell_1_1) # root
data_cell_1_1_type = sheetObj_01.cell_type(1,1) # 返回指定单元格中的数据类型
print(data_cell_1_1_type) # 1
3. 脚本实战:读取excel文件并写入conf文件中
1)题目描述
读取上述描述的info.xlsx文件,并将其内容按照如下格式写入到ansible.conf文件中
2)实战脚本
import os
import xlrd
fileName = 'info.xlsx'
dirPath = os.path.dirname(os.path.abspath(__file__))
filePath = os.path.join(dirPath,fileName)
x1 = xlrd.open_workbook(filePath)
sheetName = x1.sheet_names()[0]
sheetObj = x1.sheet_by_name(sheetName)
rowsNum = sheetObj.nrows
with open('ansible.conf','a',encoding='utf-8') as f:
f.write('[test1]'+'\n')
for row in range(1,rowsNum):
with open('ansible.conf','a',encoding='utf-8') as f:
f.write('Host='+sheetObj.row_values(row)[0]+' '
+'User='+ '"'+sheetObj.row_values(row)[1]+'"'+' '
+'PassWord='+'"'+sheetObj.row_values(row)[2]+'"'+' '
+'Port='+sheetObj.row_values(row)[3]+'\n')
《关于xlrd读取整数默认带小数位的问题解决》,参考:
关于xlrd读取整数默认带小数位的问题解决方法