import xlrd
def xl_read():
"""Excel读取"""
book = xlrd.open_workbook('producct.xls') # 打开一个Excel文件,获取到句柄
for sheet in book.sheets(): # book.sheets()是获取所有的工作簿标签
print(sheet.name) # sheet.name是工作簿的名称,名称不能有多余的符号
def xl_read_data():
"""数据读取"""
book = xlrd.open_workbook('producct.xls') # 打开一个Excel文件,获取到句柄
sheet = book.sheet_by_name('Porduct') # 根据name找到工作簿
print('工作簿:', sheet.name) # 查看标签名称
print('数据行:', sheet.nrows) # 查看有多少行数据
print('产品数据' + '=' * 50)
for i in range(sheet.nrows): # 遍历行数
print(sheet.row_values(i)) # 获取索引指定的数据行
对于Excel文件的各种操作,还有其他更专业的第三方库,一般办公自动化、数据分析经常会用到,之后有时间整理
案例:
检测手机号
import xlrd
import csv
def Deduplication(): # 去重
with open(r'C:\Users\Administrator\Desktop\实号.txt') as f:
a = f.read()
b = a.split('\n')
c = set(b)
print(c)
d = '\n'.join(c)
print(d)
with open(r'C:\Users\Administrator\Desktop\去重后.txt', 'w') as f:
f.write(d)
def xl_read_data():
with open('phone.txt', encoding='utf-8') as f:
phones = f.readlines()
"""数据读取"""
book = xlrd.open_workbook('50.xlsx') # 打开一个Excel文件,获取到句柄
sheet = book.sheet_by_name('Sheet1') # 根据name找到工作簿
print('工作簿:', sheet.name) # 查看标签名称
print('数据行:', sheet.nrows) # 查看有多少行数据
headers = sheet.row_values(0)
rows = []
for i in range(sheet.nrows): # 遍历行数
# print(sheet.row_values(i)) # 获取索引指定的数据行
# if i != 0:
# print(sheet.row_values(i))
# rows.append(sheet.row_values(i))
phone = sheet.row_values(i)[4] # 手机号
# if str(phone).split('.')[0] + '\n' not in phones and i != 0: # 判断手机号是否在实号文本里
if str(phone).split('.')[0] + '\n' in phones: # 判断手机号是否在实号文本里
print(sheet.row_values(i))
rows.append(sheet.row_values(i))
# 写出csv文件
with open('phone.csv', 'w', newline='') as f:
writer = csv.writer(f) # 构造写入器
writer.writerow(headers) # 写入表头
writer.writerows(rows) # 写入内容
if __name__ == '__main__':
xl_read_data()
# quChongFu()