csv文件读写操作
官网文档: https://docs.python.org/zh-cn/3.7/library/csv.html
open file 读写文件操作:
https://docs.python.org/zh-cn/3.7/tutorial/inputoutput.html#reading-and-writing-files
data.csv
username,age
xiaoming,10
xiaohong,20
读文件
import csv
# 1.打开文件
with open('./data.csv') as f:
read = csv.reader(f) #csv 模块读数据
for line in read: # 循环里面的每一条数据
print(line)
执行,输出结果:
['username', 'age']
['xiaoming', '10']
['xiaohong', '20']
过滤第一行
import csv
# 1.打开文件
with open('./data.csv') as f:
read = csv.reader(f) # csv 模块读数据
next(read)
for line in read: # 循环里面的一条数据
print(line)
执行,输出结果:
['xiaoming', '10']
['xiaohong', '20']
写入文件
import csv
def writefile():
"""
mode: r read 读取文件
w write 写入文件
a append 追加
:return:
"""
with open('./data2.txt', mode='a', newline='', encoding='utf8')as f:
# f.writelines(['aaa'])
writer = csv.writer(f)
for x in range(10):
writer.writerow(['xxx', 'yyyyyy', '通用'])
writefile()
字典类型
import csv
def dict_write():
with open('names.csv', 'w', newline='') as csvfile:
fieldnames = ['first_name', 'last_name']
writer = csv.DictWriter(csvfile, fieldnames=fieldnames) # 指定字典 字段名称
writer.writeheader() # 写入表头
writer.writerow({'first_name': 'Baked', 'last_name': 'Beans'})
writer.writerow({'first_name': 'Lovely', 'last_name': 'Spam'})
writer.writerow({'first_name': 'Wonderful', 'last_name': 'Spam'})
dict_write()
Excel文件读写
https://openpyxl.readthedocs.io/en/stable/
openpyxl的安装
当前使用版本为 3.0.3
写入文件
from openpyxl import Workbook
def write_excel():
wb = Workbook() # 打开excel
wc = wb.active # 获取默认的工作 sheet
wc['A1'] = 42 # A1单元格的值设置为 42
wc.append(['1','3','3']) # 在A1 下面一行 追加1行,设置值为 1,3,3
import datetime
wc['A2'] = datetime.datetime.now() # A2 新值为 当前时间
wb.save('sample.xlsx')
读取文件:
from openpyxl import load_workbook
def read_excel():
wb = load_workbook(filename='./sample.xlsx') # 加载 excel文件
ws = wb['Sheet']
print(ws['A1'].value)
print(ws['A2'].value)
read_excel()
作业思考-参考文档:
参考文档:https://www.yuque.com/ifd39g/zdmqip/revq81
Excel文件处理
data.xlsx
基本读取
from openpyxl import load_workbook
workbook = load_workbook("./data.xlsx")
# 获取文件中所有的sheet name
names = workbook.sheetnames
print(names)
ws = workbook["注册"]
print(ws)
print(ws.title)
print(ws['A1'].value)
输出结果
['登录', '注册']
<Worksheet "注册">
注册
用户名
循环读取数据
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
workbook = load_workbook("./data.xlsx")
# 获取文件中所有的sheet name
names = workbook.sheetnames
print(names)
# 声明 ws的类型,方便下面编码
ws:Worksheet = workbook["注册"]
# 获取 第一行 第二列的数据
print(ws.cell(row=1,column=2).value)
# 注意 行和列 是从1开始计数
for x in range(1,4):
for y in range(1,6):
print(ws.cell(row=x,column=y).value)
数据整理
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
workbook = load_workbook("./data.xlsx")
# 获取文件中所有的sheetname
names = workbook.sheetnames
print(names)
# 声明ws的类型方便下面编码
ws: Worksheet = workbook['注册']
# 获取 第一行 第二列的数据
print(ws.cell(row=1, column=2).value)
alldata = []
# 注意 行和列 是从1开始计数
for x in range(1, 4):
row = []
for y in range(1, 6):
print(ws.cell(row=x, column=y).value)
row.append(ws.cell(row=x, column=y).value)
alldata.append(row)
print(alldata)
输出结果
[['用户名', '邮件', '密码', '期望结果', '备注'], [1111, None, 2222, '信息不完整', '注册失败'], [11111, 1111, 222222, '邮件格式错误', '注册失败']]
通过内置方法迭代行或者迭代列读取数据
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
workbook = load_workbook("./data.xlsx")
# 获取文件中所有的sheet name
names = workbook.sheetnames
print(names)
# 声明 ws的类型,方便下面编码
ws:Worksheet = workbook["注册"]
# 通过指定行和列 来访问单元格数据 数据结果以行为单位
for row in ws.iter_rows(min_row=1,max_row=3,min_col=1,max_col=5,values_only=True):
print(row)
# 通过指定行和列 来访问单元格数据 数据结果以列为单位
for col in ws.iter_cols(min_row=1,max_row=3,min_col=1,max_col=5,values_only=True):
print(col)
运行结果
['登录', '注册', 'Sheet3']
邮件
('用户名', '邮件', '密码', '期望结果', '备注')
(1111, None, 2222, '信息不完整', '注册失败')
(111111, 1111, 222222, '邮件格式错误', '注册失败')
('用户名', 1111, 111111)
('邮件', None, 1111)
('密码', 2222, 222222)
('期望结果', '信息不完整', '邮件格式错误')
('备注', '注册失败', '注册失败')
作业讲解
import sys
print(sys.path)
import pytest
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
wb = load_workbook(filename='./data.xlsx')
ws:Worksheet = wb['登录']
test_data= []
for row in ws.iter_rows(min_row=2,max_row=4,min_col=1,max_col=3,values_only=True):
test_data.append(row)
print(test_data)
test_ids = []
for col in ws.iter_cols(min_row=2,max_row=4,min_col=4,max_col=4,values_only=True):
test_ids = list(col)
print(test_ids)
@pytest.fixture(params=test_data,ids=test_ids)
def a(request):
return request.param
def test_data(a):
print(f'==={a}===')